Solved

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

Posted on 2016-10-12
3
30 Views
Last Modified: 2016-10-13
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!
0
Comment
Question by:Darius
  • 2
3 Comments
 

Author Comment

by:Darius
Comment Utility
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
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
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
 

Author Comment

by:Darius
Comment Utility
Sharath,
  Thank You!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now