Learn how to a build a cloud-first strategyRegister Now


Extract Cumulative data and daily data from same Stored PRocedure

Posted on 2014-08-15
Medium Priority
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 11

Accepted Solution

HuaMinChen earned 2000 total points
ID: 40264579
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 30

Expert Comment

by:Olaf Doschke
ID: 40264783
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? http://technet.microsoft.com/en-US/library/ms187926(v=SQL.105).aspx

Bye, Olaf.

Author Closing Comment

ID: 40265285
This works great - Thank you sir!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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