June 29, 2015 at 5:30 am
Hi there 🙂
I have a source table called Table A (see image) and i want to read all the data into Table B which contains two additional columns that needs to be filled in via sql script.
TABBEL A
subscriptionid prodgegdate salesdate productenddate
2000 2006-02-27 2006-02-27 2008-04-04
2000 2006-02-27 2006-02-27 2008-04-04
2000 2006-02-27 2006-02-27 2008-04-04
3000 2009-09-03 2009-09-03 2010-04-01
3000 2009-09-03 2009-09-03 2010-04-01
3000 2009-09-03 2009-09-03 2010-04-01
4000 2006-06-16 2006-06-16 2015-04-03
4000 2006-06-16 2006-06-16 2015-04-03
TABLE B WITH TWO ADDITIONAL COLUMNS (SubscriptionRowLog and SubscriptionRowDate)
subscriptionid prodgegdate salesdate productenddate subscriptionirowlog subscriptionrowdate
2000 2006-02-27 2006-02-27 2008-04-04 2000_0 2006-02-27
2000 2006-02-27 2006-02-27 2008-04-04 2000_1 2006-03-27
2000 2006-02-27 2006-02-27 2008-04-04 2000_2 2006-04-27
3000 2009-09-03 2009-09-03 2010-04-01 3000_0 2009-09-03
3000 2009-09-03 2009-09-03 2010-04-01 3000_1 2009-10-03
3000 2009-09-03 2009-09-03 2010-04-01 3000_2 2009-11-03
4000 2006-06-16 2006-06-16 2015-04-03 4000_0 2006-06-16
4000 2006-06-16 2006-06-16 2015-04-03 4000_1 2006-07-16
I have tried to do the following but i am not getting it right 🙁
DECLARE @LOGDATE1 AS DATETIME = NULL
DECLARE @ROWCOUNT AS nvarchar = 0
DECLARE @BASEENDATE AS DATE= NULL
/*the first time the row 1 i will have default value so i assign prodgegdate to the new column SubscriptionRowDate and SubscriptionRowLog_0 */
SELECT
SubscriptionRowDate = CAST(prodgegdate AS DATE) ,
SubscriptionRowLog = ViasatSubscriptionID + '_'+@ROWCOUNT ,
CASE WHEN baseenddate IS NULL
THEN CONVERT(date, getdate())
ELSE baseenddate
END AS baseenddate
FROM dbo.stage_viasatsubscription
/*next row 2 i insert the following */
@ROWCOUNT = @ROWCOUNT +1
SubscriptionRowDate =salesdate
/*next row 3 i insert the following */
@ROWCOUNT = @ROWCOUNT +1
@ LOGDATE1 =DATEADD(Month,1,DATETIME(subscriptionid))
WHILE (DATEDIFF(day,LOGDATE1 ,DATETIME(productenddate)) > 0)
--INSERT INTO TABLE B
SubscriptionRowLog = subscriptionid + '_' +@ROWCOUNT
SubscriptionRowDate =FORMAT((LOGDATE1),'yyyy-MM-dd')
--WRITERECORD
@ROWCOUNT= @ROWCOUNT+ 1
LOGDATE1 = DATEADD(Month,1,DATETIME([LOGDATE1))
END WHILE
LOGDATE1 = NULL
@ROWCOUNT = 0
Basically i want to get the same result as in Table B.
Any help will highly appreciate as i am stuck on this for past few days.
thanks 🙂
June 29, 2015 at 8:30 am
This gives the output you specified:
declare @t table
(
subID int,
prodgdate date,
salesdate date,
prodendDate date
)
insert @t (subID, prodgdate, salesdate, prodendDate) values
(2000, '2006-02-27', '2006-02-27', '2008-04-04')
,(2000, '2006-02-27', '2006-02-27', '2008-04-04')
,(2000, '2006-02-27', '2006-02-27', '2008-04-04')
,(3000, '2009-09-03', '2009-09-03', '2010-04-01')
,(3000, '2009-09-03', '2009-09-03', '2010-04-01')
,(3000, '2009-09-03', '2009-09-03', '2010-04-01')
,(4000, '2006-06-16', '2006-06-16', '2015-04-03')
,(4000, '2006-06-16', '2006-06-16', '2015-04-03')
;with cte as
(
select subID, prodgdate, salesdate, prodendDate, ROW_NUMBER() over(Partition by subID order by prodgdate)-1 RowNum
from @t
)
select *, CAST(subID as CHAR(4)) + '_' + CAST(RowNum as char(2)), DATEADD(mm, RowNum, prodgdate)
from cte
This should work WAY faster than a while loop.
For future posts, note how DDL and data have been entered. It makes it easier for anyone who would like to answer your questions.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 29, 2015 at 8:36 am
Your help is much appreciated 🙂 i will try this out and thanks for the feedback regarding my post 🙂
June 29, 2015 at 8:55 am
Hi, i tried but the rowNum column is returning null. I can only see the inserted values.
June 29, 2015 at 9:38 am
mandania (6/29/2015)
Hi, i tried but the rowNum column is returning null. I can only see the inserted values.
I just checked the docs and they state that cte's and Row_Number are valid for SQL 2005.
This is the output that I get when I run the query I posted:
subID prodgdate salesdate prodendDate RowNum NewCol1 NewCol2
2000 2006-02-27 2006-02-27 2008-04-04 0 2000_0 2006-02-27
2000 2006-02-27 2006-02-27 2008-04-04 1 2000_1 2006-03-27
2000 2006-02-27 2006-02-27 2008-04-04 2 2000_2 2006-04-27
3000 2009-09-03 2009-09-03 2010-04-01 0 3000_0 2009-09-03
3000 2009-09-03 2009-09-03 2010-04-01 1 3000_1 2009-10-03
3000 2009-09-03 2009-09-03 2010-04-01 2 3000_2 2009-11-03
4000 2006-06-16 2006-06-16 2015-04-03 0 4000_0 2006-06-16
4000 2006-06-16 2006-06-16 2015-04-03 1 4000_1 2006-07-16
You could try moving the cte into the body of the query:
select *, CAST(subID as CHAR(4)) + '_' + CAST(RowNum as char(2)) NewCol1, DATEADD(mm, RowNum, prodgdate) NewCol2
from (
select subID, prodgdate, salesdate, prodendDate,
ROW_NUMBER() over(Partition by subID order by prodgdate)-1 RowNum
from @t
) a
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 30, 2015 at 5:25 am
Thanks mate, it worked!!
The next thing i want to do is an insert these select into a table
This is what i tried to do
INSERT INTO subs (
subID
, prodgdate
, salesdate
, prodendDate,sub
,CAST(subIDas CHAR(8))+'_'+CAST(RowNum as char(4)) subscriptionrowlog
,DATEADD(mm, RowNum, prodgdate) subscriptionrowdate
)
but its throwing syntax error.
June 30, 2015 at 5:30 am
that error would be pure syntax. you don't have a it's
INSERT INTO TableName(ColumnList) VALUES ...
or INSERT INTO TableName(ColumnList) SELECT ...
you've mixed values with columns, so ti fails.
INSERT INTO subs (subID,prodgdate,salesdate,prodendDate,sub,subscriptionrowlog,subscriptionrowdate)
VALUES(
subID
, prodgdate
, salesdate
, prodendDate,sub
,CAST(subIDas CHAR(8))+'_'+CAST(RowNum as char(4)))
,DATEADD(mm, RowNum, prodgdate))
INSERT INTO subs (subID,prodgdate,salesdate,prodendDate,sub,subscriptionrowlog,subscriptionrowdate)
SELECT
subID
, prodgdate
, salesdate
, prodendDate,sub
,CAST(subIDas CHAR(8))+'_'+CAST(RowNum as char(4)) AS subscriptionrowlog
,DATEADD(mm, RowNum, prodgdate) AS subscriptionrowdate
Lowell
June 30, 2015 at 6:22 am
I have solved the issue. My gratitude to Lowell for his time and patience.
July 3, 2015 at 4:55 pm
The table below is the result of the modifed script. This works fine for one a row with one or more subid.
subID prodgdate salesdate prodendDate RowNum Subrowlog Subrowdate
2000 2006-02-09 2006-02-09 2006-02-04 0 2000_0 2006-02-27
3000 2007-04-07 2007-04-07 2007-04-09 1 2000_1 2006-03-27
4000 2008-06-02 2008-06-02 2008-06-06 2 2000_2 2006-04-27
but i had made a mistake, i forgot to mention that i want to iterate for number of days
Datediff(days,prodgdate,prodenddate) so for subid i have 5 days of difference and the result should look like this in the end
subID prodgdate salesdate prodendDate RowNum Subrowlog Subrowdate
2000 2006-02-09 2006-02-09 2006-02-04 0 0 2000_0 2006-02-04
2000 2006-02-09 2006-02-09 2006-02-04 1 1 2000_1 2006-03-04
2000 2006-02-09 2006-02-09 2006-02-04 2 2 2000_2 2006-04-04
2000 2006-02-09 2006-02-09 2006-02-04 3 3 2000_3 2006-05-04
2000 2006-02-04 2006-02-09 2006-02-04 5 4 2000_4 2006-06-04
3000 2007-04-07 2007-04-07 2007-04-09 0 0 2000_0 2006-04-07
3000 2007-02-27 2007-04-07 2007-04-09 1 1 2000_1 2006-05-07
This is the script:
SELECT*, CAST(subID as CHAR(4)) + '_' + CAST(RowNum as char(2)) subscriptionrowlog
,CASE WHEN RowNum<= DATEDIFF(DAY, salesdate, prodendDate) THEN
DATEADD(mm, RowNum, salesdate) --DATEADD(mm, RowNum,
CONVERT(VARCHAR(10),salesdate,112)) --DATE OVERFLOW???
ELSE '1900-01-01'
END AS subscriptionrowdate
FROM(
SELECT subID, prodgdate, salesdate, prodendDate,
ROW_NUMBER() over(Partition by subID order by prodgdate)-1 RowNum
FROM @t
) a
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply