gpt4 book ai didi

sql - 填充 SQL Server 中两个日期之间的缺失日期

转载 作者:行者123 更新时间:2023-12-03 23:06:04 32 4
gpt4 key购买 nike

我有包含以下数据的临时表

Temp Table with customer's data

我只想填补开始日期和结束日期之间缺失的日期,并重复运行余额列,直到新余额未显示为止。每个客户也需要相同的重复。当显示新余额时,它将再次重复下一个值。

此外,我想选择每天的最大交易日期。

有什么帮助吗?

[1]: /image/Dt3mt.png

DECLARE @Date DATETIME, @StartDate DATETIME, @EndDate DATETIME, @Counter INT, @TotalDays INT

SET @Date = '2018-11-25'

SET @StartDate = CONVERT(varchar, dateadd(month,datediff(month,0,@Date),0), 111)
SET @EndDate = CONVERT(varchar, dateadd(day,-1,dateadd(month,datediff(month,-1,@Date),0)), 111)
SET @Counter = 0
SET @TotalDays = DAY(@EndDate)
----------------------------------------------------------------

CREATE TABLE #Customers
(
ID BIGINT IDENTITY(1,1),
Customer_ID BIGINT,
Transaction_Date DATETIME,
Serial_No INT
)

INSERT INTO #Customers
(Customer_ID, Transaction_Date, Serial_No)

SELECT tc.Customer_Id, tc.Transaction_Date, tc.Serial_No

FROM Transaction_Main AS tm
JOIN Transaction_Detail AS td
ON td.Transaction_Date = tm.Transaction_Date
AND td.Transaction_No = tm.Transaction_No
JOIN Transaction_Customer AS tc
ON tc.Transaction_Date = td.Transaction_Date
AND tc.Transaction_No = td.Transaction_No
AND tc.Serial_No = td.Serial_No

WHERE tm.Transaction_Status IN ('A', 'P')
AND tm.Transaction_Date BETWEEN @StartDate AND @EndDate + ' 23:59:59'
AND td.Transaction_Date BETWEEN @StartDate AND @EndDate + ' 23:59:59'
AND tc.Transaction_Date BETWEEN @StartDate AND @EndDate + ' 23:59:59'
AND tc.Customer_Id = 21
AND tm.Transaction_Type <> 4

最佳答案

您可以使用全外连接到您的选择查询,在下面的查询中使用Transaction_Date(下面的示例是带日期过滤器的):

create table #TempDate
(
Customer_Id int,
Transaction_Date datetime,
Running_Balance varchar(50)
)

insert into #TempDate (Customer_Id, Transaction_Date, Running_Balance) values (21, '2018-11-01 13:20:37.923', '41,863.54')
insert into #TempDate (Customer_Id, Transaction_Date, Running_Balance) values (21, '2018-11-02 13:20:37.923', '16,863.54')
insert into #TempDate (Customer_Id, Transaction_Date, Running_Balance) values (21, '2018-11-23 13:20:37.923', '11,179.54')
insert into #TempDate (Customer_Id, Transaction_Date, Running_Balance) values (21, '2018-11-26 13:20:37.923', '11,164.54')
insert into #TempDate (Customer_Id, Transaction_Date, Running_Balance) values (21, '2018-11-28 13:20:37.923', '6,164.54')

DECLARE @STARTDATE nvarchar(100) = '11/01/2018'
DECLARE @ENDDATE nvarchar(100) = '11/28/2018 23:59:59'

SELECT a.Customer_Id, b.Transaction_Date, a.Running_Balance FROM
(SELECT Customer_Id, CAST(Transaction_Date as DATE) AS Transaction_Date,
Running_Balance from #TempDate
WHERE Transaction_Date BETWEEN @STARTDATE AND @ENDDATE) a

FULL OUTER JOIN

(SELECT [Transaction_Date] = DATEADD(Day,Number,@STARTDATE)
FROM master..spt_values
WHERE Type='P'
AND DATEADD(day,Number,@STARTDATE) <= @ENDDATE) b
ON a.Transaction_Date = b.Transaction_Date ORDER BY b.Transaction_Date

关于sql - 填充 SQL Server 中两个日期之间的缺失日期,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54898882/

32 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com