Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-16
3
Medium Priority
?
524 Views
Last Modified: 2014-08-17
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!
0
Comment
Question by:Saxitalis
3 Comments
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 40265365
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
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 2000 total points
ID: 40265807
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
 

Author Closing Comment

by:Saxitalis
ID: 40266597
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

810 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