Cursors, Loops and Cross DB queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cursors, Loops and Cross DB queries

Author

Topic

BrettD
Starting Member

14 Posts

Posted - 2008-08-20 : 09:47:18

Hi All, I'm not the most seasoned of SQLers so I hope you guys can help me!

I need to write a procedure that loops through Branches and Weeks in the year to fill up another table full of sales figure goodness that will be fed into a bunch of graphs and act as KPIs.

I need to run the query from one DB and query another (called SalesAnalysis in this instance), finally filling the table in the initial DB.

What I need to do is loop through all of the branches and get their targets and sales for each week of the year. The branches, targets and sales tables are all in the 'Sales Analysis' database. So far I have this SQL that loops through the weeks:

DECLARE @WeekID INT
DECLARE @BranchID INT
DECLARE @Target DECIMAL(10, 2)
DECLARE @Sales DECIMAL(10, 2)

DECLARE @SQL VARCHAR(8000);

-- drop temp table if it exists
IF object_id('tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END

-- create our temp table
CREATE TABLE #temp(
[ID] [int] IDENTITY(1,1) NOT NULL,
[WeekID] [int] NOT NULL,
[BranchID] [int] NOT NULL,
[SalesTarget] [money] NOT NULL,
[ActualSales] [money] NOT NULL)

-- Set initial values for params
SET @WeekID = 1
SELECT @BranchID = SalesAnalysis.dbo.[Branches].BranchID
SELECT @Target = SalesAnalysis.dbo.Targets.Target
WHERE SalesAnalysis.dbo.Targets.BranchID = @BranchID
SELECT @Sales = Sum(SalesAnalysis.dbo.Booking.Gross)
WHERE SalesAnalysis.dbo.Booking.BranchID = @BranchID
-- need to get @WeekID

-- do the loop
WHILE @WeekID <= 53
BEGIN
SET @SQL = 'INSERT INTO #temp ([WeekID], [BranchID], [Sales Target], [ActualSales])
SELECT
' + @WeekID + ' AS [WeekID],
' + @BranchID + ' AS [BranchID],
ISNULL(Cast(@Target AS MONEY), 0) AS [SalesTarget],
ISNULL(Cast(@Sales AS MONEY), 0) AS [ActualSales]'
SET @WeekID = @WeekID + 1
EXECUTE (@SQL)
END

-- insert into real table
INSERT INTO [SalesBranch]([WeekID], [BranchID], [Sales Target], [ActualSales])

SELECT [WeekID], [BranchID], [Sales Target], [ActualSales]
FROM [#temp]

-- drop the temp table
DROP TABLE [#temp]

I think I've got the loop for the weeks sorted but how do I then loop through each of the Branches to then start another loop for the weeks?

I'm thinking cursors but are they frowned upon for this kind of query or is there another less-expensive way of achieving what I want?

Hope someone can help.
Brett

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 09:54:16

quote:
SELECT @BranchID = SalesAnalysis.dbo.[Branches].BranchID
SELECT @Target = SalesAnalysis.dbo.Targets.Target
WHERE SalesAnalysis.dbo.Targets.BranchID = @BranchID
SELECT @Sales = Sum(SalesAnalysis.dbo.Booking.Gross)
WHERE SalesAnalysis.dbo.Booking.BranchID = @BranchID

Where are the FROM parts?
Are you using MICROSOFT SQL Server?

E 12°55'05.25"
N 56°04'39.16"

BrettD
Starting Member

14 Posts

Posted - 2008-08-20 : 09:57:35

quote:
Originally posted by Peso

Where are the FROM parts?
Are you using MICROSOFT SQL Server?

E 12°55'05.25"
N 56°04'39.16"

That's a very good point...

Here's the revised section of that code:

SELECT @BranchID = SalesAnalysis.dbo.[Branches].BranchID FROM SalesAnalysis.dbo.Branches
SELECT @Target = SalesAnalysis.dbo.Targets.Target FROM SalesAnalysis.dbo.Targets
WHERE SalesAnalysis.dbo.Targets.BranchID = @BranchID
SELECT @Sales = Sum(SalesAnalysis.dbo.Booking.Gross) FROM SalesAnalysis.dbo.Booking
WHERE SalesAnalysis.dbo.Booking.BranchID = @BranchID

Yes I'm using SQL Server 2005.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 10:04:02

Replace all code with this single SELECT statement

INSERT		SalesBranch
		(
			WeekID,
			BranchID,
			[Sales Target],
			[ActualSales]
		)
SELECT		v.Number,
		d.BranchID,
		SUM(d.Target),
		SUM(d.Sales)
FROM		(
			SELECT		BranchID,
					SUM(Target) AS Target,
					0 AS Sales
			FROM		SalesAnalysis.dbo.Targets
			GROUP BY	BranchID

			UNION ALL

			SELECT		BranchID,
					0,
					SUM(Gross)
			FROM		SalesAnalysis.dbo.Booking
			GROUP BY	BranchID
		) AS d
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number BETWEEN 1 AND 53
GROUP BY	v.Number,
		d.BranchID

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 10:05:23

quote:
Originally posted by BrettD

quote:
Originally posted by Peso

Where are the FROM parts?
Are you using MICROSOFT SQL Server?

E 12°55'05.25"
N 56°04'39.16"

That's a very good point...

Here's the revised section of that code:

SELECT @BranchID = SalesAnalysis.dbo.[Branches].BranchID FROM SalesAnalysis.dbo.Branches
SELECT @Target = SalesAnalysis.dbo.Targets.Target FROM SalesAnalysis.dbo.Targets
WHERE SalesAnalysis.dbo.Targets.BranchID = @BranchID
SELECT @Sales = Sum(SalesAnalysis.dbo.Booking.Gross) FROM SalesAnalysis.dbo.Booking
WHERE SalesAnalysis.dbo.Booking.BranchID = @BranchID

Yes I'm using SQL Server 2005.

This also has a problem if the queries return more than 1 value you will not be able to get all of them as variable can hold only a single value at a time.And if you want to grab each value one by one, then you need to use it inside a loop.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 10:08:39

Here is a complete suggestion

INSERT		SalesBranch
		(
			WeekID,
			BranchID,
			[Sales Target],
			[ActualSales]
		)
SELECT		v.Number,
		d.BranchID,
		SUM(d.Target),
		SUM(d.Sales)
FROM		(
			SELECT		BranchID,
					0 AS Target,
					0 AS Sales
			FROM		SalesAnalysis.dbo.Branches

			UNION ALL

			SELECT		BranchID,
					SUM(Target),
					0
			FROM		SalesAnalysis.dbo.Targets
			GROUP BY	BranchID

			UNION ALL

			SELECT		BranchID,
					0,
					SUM(Gross)
			FROM		SalesAnalysis.dbo.Booking
			GROUP BY	BranchID
		) AS d
INNER JOIN	master..spt_values AS v ON v.Type = 'P'
WHERE		v.Number BETWEEN 1 AND 53
GROUP BY	v.Number,
		d.BranchID

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources