December 19, 2016 at 2:02 pm
Dear NG
I have a table "Beginn" with a datefield (Datum), a Price field (Wert) and a counter field (CNT). The problem which I have is I need to create a loop which sets new values to datum, Wert while the CNT field is bigger then 1
following steps should be taken:
1. Price / Wert only the first time this value should be the new Wert
2. Add every new recordset a new month to the date field
3. decrease the countfield -1
So the Beginn table is what I have and the Ziel table is what I need.
Can anybody tell me how to create a loop in SQL? In VBA it would be a Do while CNT > 0
Many thanks for your help
Best regards Chris
create table dbo.Beginn(
Bold_ID int,
Wert int,
Datum datetime,
CNT int);
create table dbo.Ziel(
Bold_ID int,
Wert int,
Datum datetime);
Insert Into Beginn
Values
(1,300,'2016-01-01 00:00:00',3),
(2,100,'2016-01-01 00:00:00',2);
Insert Into Ziel
Values
(1,100,'2016-01-01 00:00:00'),
(1,100,'2016-02-01 00:00:00'),
(1,100,'2016-03-01 00:00:00'),
(2,50,'2016-01-01 00:00:00'),
(2,50,'2016-02-01 00:00:00');
December 19, 2016 at 2:54 pm
Use a tally table[/url]. I've created a small tally table using a CTE to demonstrate.
;
WITH E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n)
)
, cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 n
FROM E a
)
SELECT b.Bold_ID, b.Wert/b.CNT, DATEADD(MONTH, n, b.Datum) AS Datum
FROM Beginn b
INNER JOIN cteTally t
ON b.CNT > t.n
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply