March 20, 2009 at 3:23 pm
Hey Folks,
Thought I'd toss out a general question to see if I can get some ideas about how to do this...
I'm looping on a range of dates, executing a parameterized stored procedure and I want to combine all of the separate result sets into a single results set. Can it be done?
Here's the T-SQL code in question:
DECLARE @store INT, @returnVal INT
DECLARE @begdate DATETIME, @enddate DATETIME, @saledate DATETIME
DECLARE @myDateString VARCHAR(10), @msg VARCHAR(80)
SET @begdate = convert(datetime,'3/12/2009')
SET @enddate = convert(datetime,'3/19/2009')
SET @saledate = @begdate
SET @store = 1
SET @myDateString = CONVERT( varchar(10), @saledate, 101 )
PRINT @saledate
PRINT @store
WHILE @saledate <= @enddate
BEGIN
EXEC usp_Cash_Trans_pct @store, @saledate
SET @saledate = DATEADD( d, 1, @saledate) -- increment date counter
END
March 20, 2009 at 3:50 pm
Create a #temp table to hold the data, then INSERT.. EXEC to capture that output of the stored procedure calls.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2009 at 4:07 pm
A thousand "Thank you's to you Barry!"
I had searched BOL using various combinations of Stored Procedures and multiple result sets etc to no avail. Your advice had me look at the INSERT statement instead and there I found a sample of what I wanted to do!
I'm pasting the altered code below as a sample of what the desired resulting T-SQL code looks like. I am always disappointed to see questions and samples posted here before the final product gets built. Here is a final product that does what I wanted!
DECLARE @store INT, @returnVal INT
DECLARE @begdate DATETIME, @enddate DATETIME, @saledate DATETIME
DECLARE @myDateString VARCHAR(10), @msg VARCHAR(80)
SET @begdate = convert(datetime,'3/12/2009')
SET @enddate = convert(datetime,'3/19/2009')
SET @saledate = @begdate
SET @store = 1
CREATE TABLE #myCashAnal (
Store INT NOT NULL,
SaleDate DATETIME NOT NULL,
All_Sales MONEY,
Cash_Sales MONEY,
Pct_Cash NUMERIC );
WHILE @saledate <= @enddate
BEGIN
INSERT #myCashAnal EXEC usp_Cash_Trans_pct @store, @saledate
SET @saledate = DATEADD( d, 1, @saledate) -- increment date counter
END
SELECT * FROM #myCashAnal
Thanks!
Larry
March 20, 2009 at 4:26 pm
Glad I could help, Larry. Yeah, I wish BOL would mention this capability under the EXECUTE statement entry. You only know to look for it under INSERT if you already know about it (typical "Reference" problem).
...
Of course, I am compelled to let you know that you could do this without a loop, and I encourage you to do so. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2009 at 5:39 pm
Any chance of listing the code for EXEC usp_Cash_Trans_pct? I wanna see what's in it that requires the RBAR associated with processing just one sales date at a time.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2009 at 7:38 am
Yikes! I always reluctant to post code up here Jeff because I'm such a neophyte when it comes to the inner workings of SQL Server, but I'll paste it in below.
Some explanation might help folks understand a bit about the foundation of it. I have code the collects the complete sales transaction logs from each of our 60 stores. This particular snippet of code is used in our analysis of sales volume in order to find out how many employees to schedule for each of the respective departments. Some of the cash registers we call "satellite sales" registers because they represent stand-alone departments such as Hot Deli sales, and gas station/convenience store and so those registers are excluded from analysis of the entire store and they are analyzed separately. We need to find out the ratios of cash sales versus other payment types which we accept such as Welfare and Food stamp programs. The reason is because cash transactions go quick and a typical cashier might be able to process 50 cash sales transactions in an hour where they might only be able to run 25 transactions through if they involve slower payment methods.
Here is the usp_Cash_Trans_Pct code.
USE [TLog]
GO
/****** Object: StoredProcedure [dbo].[usp_Cash_Trans_pct] Script Date: 03/23/2009 08:24:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Larry Kruse
-- Create date: 3/19/2009
-- Description:Used for cash transaction analysis
-- =============================================
ALTER PROCEDURE [dbo].[usp_Cash_Trans_pct]
-- Add the parameters for the stored procedure here
@store int = 0,
@sale_date datetime = 0
AS
BEGIN
DECLARE @all MONEY, @cash MONEY
SET NOCOUNT ON;
SELECT @all = (SELECT
SUM( transtotal + taxtotal ) As AllSales
FROM TLog_Archive As t1
WHERE t1.stornum = @store
and t1.dayid = (SELECT dayid FROM day_calendar_dim WHERE daydt = @sale_date)
and t1.seqnum = 1
and t1.terminal NOT IN (SELECT terminal
FROM TLog_Summary_Satellite_Reg_XRef
WHERE STORE_ID = @store ))
SELECT @cash = (SELECT SUM( CashSalesTndr1)As AllCashSales
FROM (
SELECT
t1.dayid,
t1.stornum,
SUM(
(t1.transtotal + taxtotal)
-
(CASE
WHEN t1.tndrtype1 = 11 THEN 0 ELSE t1.tndramt1
END)
-
(CASE
WHEN t1.tndrtype2 = 11 THEN 0 ELSE t1.tndramt2
END)
-
(CASE
WHEN t1.tndrtype3 = 11 THEN 0 ELSE t1.tndramt3
END)
) As CashSalesTndr1
FROM TLog_Archive As t1
WHERE t1.stornum = @store
and t1.dayid = (SELECT dayid FROM day_calendar_dim WHERE daydt = @sale_date)
and t1.seqnum = 1
and t1.terminal NOT IN (SELECT terminal
FROM TLog_Summary_Satellite_Reg_XRef
WHERE STORE_ID = @store )
GROUP BY
t1.dayid,
t1.stornum
) As x)
SELECT
@store As Store,
CONVERT( varchar(10),@sale_date, 101) As Sale_Date,
@all As All_Sales,
@cash As All_Cash_Sales,
(@cash / @all) * 100 As Pct_Cash
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply