Writing a Looping Query - SQL Server Forums

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

Writing a Looping Query

Author

Topic

Sunny155
Starting Member

15 Posts

Posted - 2009-03-31 : 13:45:48

Hi guys, I am new here. I need help fixing a query or maybe need advise to write it diffrenetly.

I have 5 tables in a database - Transactions, Product, Service, ServProvider and GlbServiceD.

I want to query the transaction, product, service, servprovidet table based on unique id of service table. Service table has got 8 records so 8 ids. I want to insert the result of the qury into 5th table.

The query is like this (The joins are self explanatory)-

DECLARE @LoopID as int

DECLARE @MaxLoopID as int

SET @LoopId = 1

SET @MaxLoopID = (SELECT Max(nid) FROM dbo.service WHERE nid <> 1000)

WHILE @LoopID <= @MaxLoopID

BEGIN

INSERT INTO dbo.GlbServiceD

SELECT getdate()-1 as dDay, Min(@LoopID) as nServiceID, count(T.sOpType) as nTransaction, sum(T.nValue) as nCashinFlow , sum(T.nRevenue) as nRevenue1 

FROM dbo.Transactions AS T

INNER JOIN

dbo.Product AS P ON T.nProductID = P.nID

INNER JOIN

dbo.ServProvider AS SP ON P.nServProvID = SP.nID

INNER JOIN

dbo.Service AS S ON SP.nServiceID = S.nID

WHERE T.sOptype = 'transaction' and datediff(day, T.dInsertion, getdate()-1) = 0 AND T.nErrorCodeId = 0 AND S.nID = @LoopID

SET @LoopID = @LoopID+1

END;

The select results this value if no records match -

dDay = 2009-03-30 23:08:26.700 - (sample)
nServiceID = NULL ----- (PLEASE NOTE)
nTransaction = 0
nCashinFlow = NULL
nRevenue1 = NULL

actuallu i want to set the value of nServiceID to the id of service id which is currently in loop (like if id 1 is in loop than 1, 2 than 2) and insert 8 records (bcos service table has 8 records)

So the fixed qury will do something like this -

Record 1 (where data found and not null )

dDay = 2009-03-30 23:08:26.700 - (sample)
nServiceID = 1
nTransaction = 5
nCashinFlow = 100
nRevenue1 = 1000

Record 2 (where data not found hence null)

dDay = 2009-03-30 23:08:26.700 - (sample)
nServiceID = 2 ----- (PLEASE NOTE)
nTransaction = 0
nCashinFlow = NULL
nRevenue1 = NULL

Record 3 (where data not found hence null)

dDay = 2009-03-30 23:08:26.700 - (sample)
nServiceID = 3 ----- (PLEASE NOTE)
nTransaction = 0
nCashinFlow = NULL
nRevenue1 = NULL

Record 4 (where data found and not null)

dDay = 2009-03-30 23:08:26.700 - (sample)
nServiceID = 4
nTransaction = 3
nCashinFlow = 600
nRevenue1 = 2000

and etc upto total 8 records

please help fix this qury or suggest a new one.
PS - I intend to use this query in conjunction with Scheduler and SQL Server Job so making it as stored procedure a good idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-31 : 13:52:50

seems like this is what u want


INSERT INTO dbo.GlbServiceD

SELECT getdate()-1 as dDay, S.nID as nServiceID, count(T.sOpType) as nTransaction, sum(T.nValue) as nCashinFlow , sum(T.nRevenue) as nRevenue1 

FROM dbo.Transactions AS T

INNER JOIN

dbo.Product AS P ON T.nProductID = P.nID

INNER JOIN

dbo.ServProvider AS SP ON P.nServProvID = SP.nID

INNER JOIN

dbo.Service AS S ON SP.nServiceID = S.nID

WHERE T.sOptype = 'transaction' and datediff(day, T.dInsertion, getdate()-1) = 0 AND T.nErrorCodeId = 0 
group by S.nID

Sunny155
Starting Member

15 Posts

Posted - 2009-03-31 : 14:03:09

when i added group by S.nID and replaced Min(@LoopID) with S.nid the query didnt displayed a thing i..e empty row with no data. and i dont want that.

Sunny155
Starting Member

15 Posts

Posted - 2009-03-31 : 14:09:02

A picture says thousand words -

Before making changes using your code -

http://img90.imageshack.us/my.php?image=captureaoq.jpg

After using your code -

http://img90.imageshack.us/my.php?image=capture2g.jpg

Please not i have removed INSERT INTO statement just to show u output of select.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-01 : 12:51:30

quote:
Originally posted by Sunny155

A picture says thousand words -

Before making changes using your code -

http://img90.imageshack.us/my.php?image=captureaoq.jpg

After using your code -

http://img90.imageshack.us/my.php?image=capture2g.jpg

Please not i have removed INSERT INTO statement just to show u output of select.

you're not using the query i gave you. i can still see
DECLARE @LoopID....

see what i posted, it just contains only a single insert...select
what does below return?


SELECT getdate()-1 as dDay, S.nID as nServiceID, count(T.sOpType) as nTransaction, sum(T.nValue) as nCashinFlow , sum(T.nRevenue) as nRevenue1 

FROM dbo.Transactions AS T

INNER JOIN

dbo.Product AS P ON T.nProductID = P.nID

INNER JOIN

dbo.ServProvider AS SP ON P.nServProvID = SP.nID

INNER JOIN

dbo.Service AS S ON SP.nServiceID = S.nID

WHERE T.sOptype = 'transaction' and datediff(day, T.dInsertion, getdate()-1) = 0 AND T.nErrorCodeId = 0 
group by S.nID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources