(SQL) Retrieve Total results in the separate rows...

Hi guys,

I have script which retrieve total results of two different transformation in one row for provided day

DECLARE @Tbl as TABLE (RECEIVED int, [SENT] int)	
BEGIN
		INSERT into @Tbl Select 
		[RECEIVED]		=   CASE WHEN transformId	= 21 then transformId END,
		[SENT]			=   CASE WHEN transformId	= -2 then transformId END	
     // This is date format in the table
		FROM Documents WHERE dateAdded between '2016-10-12 05:00:00.000'  and '2016-10-12 22:30:30.000'   
END
SELECT count([RECEIVED])as [TOTAL RECEIVED],count([SENT]) AS [TOTAL SENT] FROM @Tbl

Open in new window

results:
TOTAL RECEIVED      TOTAL SENT
3163                       3182



Now I need to retrieve results of last 7 days. Results should be of 7 past days, including provided day. And transformation id's totals in the separated row for every day.

Expecting results table:
DATE                TOTAL RECEIVED          TOTAL SENT
2016-10-12       3163                          3182
2016-10-11       2162                          1182
2016-10-10       3112                          2182
2016-10-09       3132                          1182
2016-10-08       3143                          2182
2016-10-07       3145                          182
2016-10-06       3167                          82



This is defiantly incorrect and gives zero values and providing in one row for all week...

DECLARE @Tbl		 as TABLE (RECEIVED int, [SENT] int)	
BEGIN
		INSERT into @Tbl Select 
            //  [Date]                      ==
		[RECEIVED]		=   CASE WHEN transformId	= 21 then transformId END,
		[SENT]			=   CASE WHEN transformId	= -2 then transformId END		
// this is wrong way to used and will gives results for 7 days in total ???
		FROM Documents WHERE dateAdded =  DATEDIFF(DD, cast('2016-10-12 00:00:00.000' as nvarchar), -6) 
END
SELECT count([RECEIVED])as [TOTAL RECEIVED],count([SENT]) AS [TOTAL SENT] FROM @Tbl

Open in new window


Any ideas? Thank you!
DariusAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
Can you try like this?
SELECT CONVERT(DATE, dateAdded) dateAdded,
       COUNT(CASE WHEN transformId = 21 then transformId END) [RECEIVED],
       COUNT(CASE WHEN transformId = -2 then transformId END) [SENT]
  FROM Documents
 WHERE dateAdded >= CONVERT(DATE,DATEADD(dd, -7, GETDATE()))
 GROUP BY CONVERT(DATE, dateAdded)

Open in new window

0
 
DariusAuthor Commented:
Every day have default time:
'2016-10-12 05:00:00.000'  and '2016-10-12 22:30:30.000'
'2016-10-11 05:00:00.000'  and '2016-10-11 22:30:30.000'
'2016-10-10 05:00:00.000'  and '2016-10-10 22:30:30.000'
. . .
. . .
0
 
DariusAuthor Commented:
Sharath,
  Thank You!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.