Extract Cumulative data and daily data from same Stored PRocedure

Posted on 2014-08-15
Last Modified: 2014-08-16
Hello Experts,
I have a table called Tb_1 that looks like this:
Wt     Date
1.5      1/20/2014
2.3      1/20/2014
6.2      1/21/2014
3.8      1/22/2014

I would like to filter the stored procedure like this:
Select SUM(Wt) From Tb_1
Where Date Between ‘1/20/2014’ And  ‘1/21/2014’
[Output = 10.0]

Select SUM(Wt) From Tb_1
Where = ‘1/21/2014’
[Output = 6.2]

The First filter returns the total Wt of 10.0 between 1/20/2014 and 1/21/2014.
The Second filter returns total Wt of 6.21 for 1/21/2014 only.

Of course this is easy to do by writing two separate stored procedures but I would like to get both results for SUM(Wt) of 10.0 and 6.2 (in this example) from the same stored procedure.

Does anyone know how to do this?

Question by:Saxitalis
    LVL 10

    Accepted Solution

    Try to put this

    select (Select SUM(Wt) From Tb_1
    Where Date Between ‘1/20/2014’ And  ‘1/21/2014’) sum1,
    (Select SUM(Wt) From Tb_1
    Where = ‘1/21/2014’) sum2

    Open in new window

    within your SP
    LVL 29

    Expert Comment

    by:Olaf Doschke
    Simply parameterize your SP and then do the query

    Select SUM(Wt) From Tb_1
    Where Date Between @MinDate And  @MaxDate

    If you want data from a single day simply call it with min date = max date.

    Details about parameters needed?

    Bye, Olaf.

    Author Closing Comment

    This works great - Thank you sir!

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    732 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

    22 Experts available now in Live!

    Get 1:1 Help Now