CODEWITHSUNDEEP
sql-server
Nhorr
Nhorr

Reputation: 23

New to SQL. How could I make the following query give daily results?

Ok, so I have the following query but I'm trying to wrap my head around how to make give me daily results within the given date range. As of right now, this is basically the query I have:

SELECT Source = CASE WHEN Project = 'CODE' OR Code = '1' THEN 'SOURCE1'
                       WHEN Project <> 'CODE' AND Code = '2' THEN 'SOURCE2'
                       WHEN Project <> 'CODE' AND Code = '3' THEN 'SOURCE3'
                       WHEN Project <> 'CODE' AND Code = '4' THEN 'SOURCE4'
                       ELSE Code END, Count(Document) as TotalCount, SUM(Amount) as TotalAmount                       

-- Joins here to gather and refine data collected

WHERE Date >= '03/20/2017'
       AND Date < '03/23/2017'
GROUP BY CASE WHEN Project = 'CODE' OR Code = '1' THEN 'SOURCE1'
                           WHEN Project <> 'CODE' AND Code = '2' THEN 'SOURCE2'
                           WHEN Project <> 'CODE' AND Code = '3' THEN 'SOURCE3'
                           WHEN Project <> 'CODE' AND Code = '4' THEN 'SOURCE4'
                           ELSE Code END
ORDER BY TotalCount DESC

Which gives:

SOURCE1 123 1225
SOURCE2 23  126
SOURCE3 17  78
SOURCE4 12  56
MISCSOURCE1 6   78

Ideally it'd give me:

Date         Source   TotalCount  TotalAmount
2017-03-20   SOURCE1  60          625
2017-03-20   SOURCE2  13          60
-- etc
2017-03-21   SOURCE1  63          600
2017-03-21   SOURCE2  10          66
-- etc

It would also have to give individual MISCSOURCEs (i.e. 1/+ extra sources besides the main 4 that are re-labeled) total counts and amounts. How would I go about doing this? Thanks in advance for helping a newbie out!

Upvotes: 2

Views: 69

Answers (3)

Sturgus
Sturgus

Reputation: 686

It sounds like you're looking to aggregate on the date field. So adjusting your logic to be something like this may work for you:

SELECT [Date]
, 'SOURCE' + Code AS Source
, COUNT(Document) as TotalCount
, SUM(Amount) as TotalAmount
--FROM...                       
GROUP BY [Date], 'SOURCE' + Code
ORDER BY COUNT(Document) DESC

This should give you a more similar output to what you're requesting. You may have to add an extra bit of logic back in if you indeed have instances where 'Code' does not equal 1 sometimes for Source1's. Making the following adjustment may work: 

SELECT [Date]
, 'SOURCE' + CASE WHEN Project = 'CODE' OR Code = '1' THEN '1' ELSE Code END AS Source
, COUNT(Document) as TotalCount
, SUM(Amount) as TotalAmount     
--FROM...                  
GROUP BY [Date]
, 'SOURCE' + CASE WHEN Project = 'CODE' OR Code = '1' THEN '1' ELSE Code END
ORDER BY COUNT(Document) DESC

That should do it.

Upvotes: 1

Nhorr
Nhorr

Reputation: 23

@ChadPortman was close, only was missing Date in the Group By section. Final query looks like this:

SELECT Date, Source = CASE WHEN Project = 'CODE' OR Code = '1' THEN 'SOURCE1'
                   WHEN Project <> 'CODE' AND Code = '2' THEN 'SOURCE2'
                   WHEN Project <> 'CODE' AND Code = '3' THEN 'SOURCE3'
                   WHEN Project <> 'CODE' AND Code = '4' THEN 'SOURCE4'
                   ELSE Code END, Count(Document) as TotalCount, SUM(Amount) as TotalAmount                       

-- Joins here to gather and refine data collected

WHERE Date >= '03/20/2017'
       AND Date < '03/23/2017'
GROUP BY Date, CASE WHEN Project = 'CODE' OR Code = '1' THEN 'SOURCE1'
                           WHEN Project <> 'CODE' AND Code = '2' THEN 'SOURCE2'
                           WHEN Project <> 'CODE' AND Code = '3' THEN 'SOURCE3'
                           WHEN Project <> 'CODE' AND Code = '4' THEN 'SOURCE4'
                           ELSE Code END
ORDER BY Date, TotalCount DESC

This gives me exactly what I needed. Thank you all for your replies!

Upvotes: 0

Chad Portman
Chad Portman

Reputation: 1226

If you just add date to the select and the group by you should get what you are looking for I think. Like this:

SELECT Date,Source = CASE WHEN Project = 'CODE' OR Code = '1' THEN 'SOURCE1'
                       WHEN Project <> 'CODE' AND Code = '2' THEN 'SOURCE2'
                       WHEN Project <> 'CODE' AND Code = '3' THEN 'SOURCE3'
                       WHEN Project <> 'CODE' AND Code = '4' THEN 'SOURCE4'
                       ELSE Code END, Count(Document) as TotalCount, SUM(Amount) as TotalAmount                       

-- Joins here to gather and refine data collected

WHERE Date >= '03/20/2017'
       AND Date < '03/23/2017'
GROUP BY CASE WHEN Project = 'CODE' OR Code = '1' THEN 'SOURCE1'
                           WHEN Project <> 'CODE' AND Code = '2' THEN 'SOURCE2'
                           WHEN Project <> 'CODE' AND Code = '3' THEN 'SOURCE3'
                           WHEN Project <> 'CODE' AND Code = '4' THEN 'SOURCE4'
                           ELSE Code END
ORDER BY date,TotalCount DESC

Upvotes: 0

Related Questions