April 4, 2016 at 4:13 pm
Hi Sql Experts,
Need help in finding total for last two days data. on 3/3/2016 i need to get sum of 03/01/2016 and 03/02/2016 for 03/04/2016 i need to get sum of 03/03/2016 and 03/02/2016 sum and so onn, irrespective of month. i.e; on apr 1 04/02/2016 i need 03/31/2016 and 04/01/2016 sum.
Here i am finding last two daysTotal column . Please let me know if you have any questions. Thanks in Advance.
below is the sample data in SQL
declare @a table
(
admits int,
day date
)
insert into @a(admits,day)
values(
30,'3/1/2016')
insert into @a(admits,day)
values(20,'3/1/2016')
insert into @a(admits,day)
values(2,'3/1/2016')
insert into @a(admits,day)
values(5,'3/2/2016')
insert into @a(admits,day)
values(3,'3/2/2016')
insert into @a(admits,day)
values(6,'3/3/2016')
insert into @a(admits,day)
values(2,'3/3/2016')
insert into @a(admits,day)
values(5,'3/4/2016')
insert into @a(admits,day)
values(6,'3/4/2016')
insert into @a(admits,day)
values(2,'3/5/2016')
insert into @a(admits,day)
values(1,'3/5/2016')
insert into @a(admits,day)
values(2,'3/6/2016')
Select * from @a
last two daysTotalAdmitsday
30 3/1/2016
20 3/1/2016
2 3/1/2016
5 3/2/2016
3 3/2/2016
60 6 3/3/2016
60 2 3/3/2016
16 5 3/4/2016
16 6 3/4/2016
19 2 3/5/2016
19 1 3/5/2016
14 2 3/6/2016
April 4, 2016 at 8:04 pm
If you had 2012 this would be much easier. I have to think about this a little; the most straight-forward solution is a "triangular join" that looks like this:
WITH totals(d, s) AS
(
SELECT day, SUM(admits)
FROM @a cur
GROUP BY day
)
SELECT d, ISNULL(x2s,0)
FROM totals a
OUTER APPLY
(
SELECT SUM(s)
FROM totals b
WHERE b.d BETWEEN DATEADD(DAY,-2,a.d) AND DATEADD(DAY,-1,a.d)
) X2(x2s);
But this won't perform well. I'm thinking about this and will post something better.
-- Itzik Ben-Gan 2001
April 4, 2016 at 8:38 pm
Using GetNumsAB[/url], this may be better:
WITH base AS
(
SELECT rn, d = DATEADD(DAY,n2,mn), mn = DATEADD(DAY,rn,mn), mx = DATEADD(DAY,n1,mn)
FROM
(
SELECT DATEADD(DAY,-2,MIN(day)), MAX(day)
FROM @a
) totals(mn,mx)
CROSS APPLY dbo.GetNumsAB(1,DATEDIFF(DAY,mn,mx)-1,1,0)
)
SELECT d, ISNULL(SUM(admits),0)
FROM base b
LEFT JOIN @a a ON a.day BETWEEN mn AND mx
GROUP BY d;
It avoids the triangular join, you'll have to test both solutions out in your environment to see which works best for you.
Update: realized that a self-join (though not pretty) might be the best option.
Here's the updated sample data with a clustered index and three solutions to try:
IF OBJECT_ID('tempdb..#a') IS NOT NULL DROP TABLE #a
CREATE TABLE #a
(
aID int identity NOT NULL,
admits int NOT NULL,
admitDate date,
CONSTRAINT pk_a PRIMARY KEY CLUSTERED (admitDate, aID)
);
INSERT #a (admits, admitDate)
SELECT TOP(1000000)
ABS(CHECKSUM(newid())%30)+1, DATEADD(DAY,ABS(CHECKSUM(newid())%740),'20150101')
FROM sys.all_columns a, sys.all_columns b
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
PRINT 'OUTER APPLY:'+CHAR(10)+REPLICATE('-',50);
WITH totals(d, s) AS
(
SELECT admitDate, SUM(admits)
FROM #a cur
GROUP BY admitDate
)
SELECT d, ISNULL(x2s,0)
FROM totals a
OUTER APPLY
(
SELECT SUM(s)
FROM totals b
WHERE b.d BETWEEN DATEADD(DAY,-2,a.d) AND DATEADD(DAY,-1,a.d)
) X2(x2s);
PRINT CHAR(10)+'Tally Table:'+CHAR(10)+REPLICATE('-',50);
WITH base AS
(
SELECT rn, d = DATEADD(DAY,n2,mn), mn = DATEADD(DAY,rn,mn), mx = DATEADD(DAY,n1,mn)
FROM
(
SELECT DATEADD(DAY,-2,MIN(admitDate)), MAX(admitDate)
FROM #a
) totals(mn,mx)
CROSS APPLY dbo.GetNumsAB(1,DATEDIFF(DAY,mn,mx)-1,1,0)
)
SELECT d, ISNULL(SUM(admits),0)
FROM base b
LEFT JOIN #a a ON a.admitDate BETWEEN mn AND mx
GROUP BY d;
PRINT CHAR(10)+'Simple Self-Join:'+CHAR(10)+REPLICATE('-',50);
WITH X AS
(
SELECT admitDate, s = SUM(admits)
FROM #a
GROUP BY admitDate
)
SELECT a.admitDate, ISNULL(b.s,0)+ISNULL(c.s,0)
FROM X a
LEFT JOIN X b ON a.admitDate = DATEADD(DAY,1,b.admitDate)
LEFT JOIN X c ON a.admitDate = DATEADD(DAY,2,c.admitDate)
--ORDER BY a.admitDate --not required by helps for testing
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
On my system the OUTER APPLY solution gets a parallel plan and seems to perform best. The tally table version gets fewer reads but performs worse, the self-join solution gets the fewest reads (by far) but gets a serial plan and is bested by the OUTER APPLY solution.
Results:
OUTER APPLY:
--------------------------------------------------
Table '#a__________________________________________________________________________________________________________________000000000148'.
Scan count 749, logical reads 10747, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1030 ms, elapsed time = 175 ms.
Tally Table:
--------------------------------------------------
Table '#a__________________________________________________________________________________________________________________000000000148'.
Scan count 742, logical reads 8081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 969 ms, elapsed time = 977 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
Simple Self-Join:
--------------------------------------------------
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#a__________________________________________________________________________________________________________________000000000148'.
Scan count 3, logical reads 7449, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 360 ms, elapsed time = 352 ms.
-- Itzik Ben-Gan 2001
April 5, 2016 at 8:44 am
Thank you Alan,these are working for me. Outer Apply is better for me as well. Thank you so much for your help!
April 5, 2016 at 11:16 am
NP. 😀
-- Itzik Ben-Gan 2001
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply