Extract Cumulative data and daily data from same TSQL query for multiple records

Hello Experts,
I have a SQL table called Tb_2 that looks like this:
Type  Wt     Date
   1         1.5   1/20/2014
   2         2.3   1/20/2014
   4         6.2   1/21/2014
   4         3.8   1/21/2014

I am able to extract a daily total (of 10) and cumulative total (of 13.8) for the Wt column like this:

select
(Select SUM(Wt) From Tb_2
Where Date = '1/21/2014') sumLastDay,
(Select SUM(Wt) From Tb_2
Where Date Between '1/20/2014' And  '1/21/2014') sumCUM

(Output)
sumLastDay            sumCUM
9.99999976158142      13.7999997138977

--------------------------------------------------------------------------------------------------------------------------------------------------
The above works fine!

Now I would like to get these results from one query:

Type        Date            sumLastDay      sumCUM
   1              1/20/14                                   1.5
   2              1/20/14                                   2.3
   4              1/21/14        10                        10      

I can do this with two queries:

Select Date, [TYPE], SUM(Wt) As sumLastDay From Tb_2
Where Date = '1/21/2014'
Group By Date, [TYPE])

Select Date, [TYPE], SUM(Wt) sumCUM From Tb_2
Where Date Between '1/20/2014' And  '1/21/2014'
Group By Date, [TYPE]

Does anyone know how to combine the two queries above into one query to get the desired results of:

Type        Date            sumLastDay      sumCUM
   1              1/20/14                                   1.5
   2              1/20/14                                   2.3
   4              1/21/14        10                        10      
Thanks!
SaxitalisAsked:
Who is Participating?
 
Habib PourfardSoftware DeveloperCommented:
You could write:

SELECT	[TYPE], 
	Date, 
	CASE WHEN Date = '1/21/2014' THEN SUM(Wt) ELSE 0 END sumLastDay,
	CASE WHEN Date BETWEEN '1/20/2014' And '1/21/2014' THEN SUM(Wt) ELSE 0 END sumCUM
FROM Tb_2
GROUP BY Date, [TYPE]

Open in new window

0
 
Olaf DoschkeSoftware DeveloperCommented:
If you sort by date the sumLastDay is the same as the sumCUM of the last record. I wouldn't waste a column for that.

SELECT Type, Date, SUM(Wt) AS sumCUM FROM Tb_2 GROUP BY Type, Date Order By Date, Type

Open in new window


And sumLastDay simply is the last sumCUM.

Bye, Olaf.
0
 
SaxitalisAuthor Commented:
Your Solution got me 90% there.
I was able to get the desired results by:
1. Creating a temp table
2. Inserting into the temp table from your code (thanks)
3. Querying the temp table

Code below
--Drop temp table
Drop Table #Tb_2
--Create temp table
          CREATE TABLE #Tb_2
     (
     Date smalldatetime,
     [Type] int,
     sumWt_LastDay real,
       sumWt_CUM real
     )
--
-- Insert records from Tb_2 query into #Tb_2
DELETE #Tb_2
WHERE Date BETWEEN '1/20/2014' And '1/21/2014'
INSERT INTO #Tb_2(Date, [Type], sumWt_LastDay, sumWt_CUM)
SELECT      Date, [TYPE],
      --Date,
      CASE WHEN Date = '1/21/2014' THEN SUM(Wt) ELSE 0 END sumWt_LastDay,
      CASE WHEN Date BETWEEN '1/20/2014' And '1/21/2014' THEN SUM(Wt) ELSE 0 END sumWt_CUM
FROM Tb_2
WHERE Date BETWEEN '1/20/2014' And '1/21/2014'
GROUP BY Date, [TYPE]
--
--Query #Tb_2 for SPROC
Select [Type], SUM(sumWt_LastDay) AS sumWt_LastDay, SUM(sumWt_CUM) AS sumWt_CUM
FROM #Tb_2
WHERE Date BETWEEN '1/20/2014' And '1/21/2014'
GROUP BY [TYPE]
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.