I am create one query for the get daily last 7 dates email count in the sql server. and i am write one query but not given expected output the table. right now just give the count in last 7 dates. here below write my query and tell me where is my mistake.
This is my query =>
SELECT dateadd(day, T.i, CAST(GETDATE() AS DATE)) AS DateColumn, uf.TotalCount
FROM (VALUES (-6), (-5), (-4), (-3), (-2), (-1), (0)) AS T(i)
OUTER APPLY
(
SELECT Count(InsertDateTime) AS TotalCount
FROM Email
WHERE Datediff(day,InsertDateTime, dateadd(day, T.i, getdate())) = 0
) uf
This is my o/p=>
DateColumn | Count
15-06-2017 10
16-06-2017 05
17-06-2017 20
18-06-2017 10
19-06-2017 30
20-06-2017 50
21-06-2017 40
This is my Expected output =>
DateColumn | Count
03-06-2017 10
06-06-2017 05
09-06-2017 20
12-06-2017 10
15-06-2017 30
18-06-2017 50
21-06-2017 40
any one know how can do that please tell me.
If you want the same Count value output then change it.
SELECT dateadd(day, T.i * 3, CAST(GETDATE() AS DATE)) AS DateColumn, uf.TotalCount
FROM (VALUES (-6), (-5), (-4), (-3), (-2), (-1), (0)) AS T(i)
OUTER APPLY
(
SELECT Count(InsertDateTime) AS TotalCount
FROM Email
WHERE Datediff(day,InsertDateTime, dateadd(day, T.i * 3, getdate())) = 0
) uf
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments