Doing a Loop - 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

Doing a Loop

Author

Topic

cmrrive
Starting Member

6 Posts

Posted - 2012-03-01 : 14:24:00

I have a table which contains 3 field
Bus_structure,Date,Amount
1,2012-01-01,758014
1,2012-02-01,850214
1,2012-03-01,950247
1,2012-04-01,100325

Basically I have one entry per month I need to create a loop that take bus_structure 1 and first month and replicate bus_structure,and amount*day for each day on the month.

Also i have 4 differentes structures code(1,2,3,4), can someone help me please

miguel

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-01 : 14:31:28

I probably could, but I won't

This is a very bad idea

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-01 : 16:07:38

You probably don't want to use a loop.

However I don't really understand what you *do* want to do.

can you give some example? maybe show use what the final result should be?

I think you want a running total of some kind but I'm not sure.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-01 : 21:59:11

NO Charlie..they want to "Seed" a table

TELL me if I'm wrong

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-02 : 04:16:31

ah I see. they want to expand the table to include a row for every day in the month and have a multiple of the first day * <n> where n is the day in the month.

You don't need a loop for that.
You need a calendar table. Then a simple insert and then an update.

However, whether it's a good idea or not..............

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-02 : 04:57:30

[code]DECLARE @Sample TABLE
(
Bus INT,
oldDate DATE,
Amount INT
)

INSERT @Sample
VALUES (1, '20120101', 758014),
(1, '20120201', 850214),
(1, '20120301', 950247),
(1, '20120401', 100325)

;WITH cteSource(Bus, oldDate, newDate, theDay, Amount)
AS (
SELECT s.Bus,
oldDate,
DATEADD(DAY, v.Number, s.oldDate) AS newDate,
v.Number + 1 AS theDay,
s.Amount
FROM @Sample AS s
INNER JOIN master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 0 AND 30
)
SELECT Bus,
newDate,
theDay,
Amount,
theDay * Amount AS DayAmount
FROM cteSource
WHERE DATEDIFF(MONTH, oldDate, newDate) = 0
ORDER BY Bus,
newDate[/code]

N 56°04'39.26"
E 12°55'05.63"

cmrrive
Starting Member

6 Posts

Posted - 2012-03-02 : 08:07:59

Thanks for all the replies. Here is the scenario for the final results I'm looking for

Bus_structure, Date, Amount
1,2012-01-01,758014
1,2012-02-01,758014*2 (2ND DAY)
1,2012-02-01,758014*3 (3RD DAY)
AND SO ON UNTIL 2012-31-01,758014*31
NOW TAKE FEB DATA
1,2012-01-02,850214
1,2012-02-02,850214*2
1,2012-02-03,850214*3
AND SO ON UNTIL 2012-02-29,850214*29
SAME THING FOR THE REST OF THE MONTH

miguel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-02 : 08:10:59

?

Did you even try my suggestion?

N 56°04'39.26"
E 12°55'05.63"

cmrrive
Starting Member

6 Posts

Posted - 2012-03-05 : 10:10:53

quote:
Originally posted by SwePeso

?

Did you even try my suggestion?

N 56°04'39.26"
E 12°55'05.63"

Thanks a lot for you help this works perfect!!!!

miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources