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

Posted on 2014-08-16
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 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

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      
Question by:Saxitalis
    LVL 29

    Expert Comment

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

    Accepted Solution

    You could write:

    	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


    Author Closing Comment

    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],
          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'

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    728 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

    18 Experts available now in Live!

    Get 1:1 Help Now