Post reply

How-to-return-next-values-and-concatenate-with_string (SQL Server 2012)

  • June 29, 2015 at 5:30 am

    #319498

    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

    subscriptionidprodgegdate salesdate productenddate

    2000 2006-02-27 2006-02-272008-04-04

    2000 2006-02-27 2006-02-272008-04-04

    2000 2006-02-27 2006-02-272008-04-04

    3000 2009-09-03 2009-09-032010-04-01

    3000 2009-09-03 2009-09-032010-04-01

    3000 2009-09-03 2009-09-032010-04-01

    4000 2006-06-16 2006-06-162015-04-03

    4000 2006-06-16 2006-06-162015-04-03

    TABLE B WITH TWO ADDITIONAL COLUMNS (SubscriptionRowLog and SubscriptionRowDate)

    subscriptionidprodgegdatesalesdate productenddatesubscriptionirowlog subscriptionrowdate

    2000 2006-02-27 2006-02-27 2008-04-042000_0 2006-02-27

    2000 2006-02-27 2006-02-27 2008-04-042000_1 2006-03-27

    2000 2006-02-27 2006-02-27 2008-04-042000_2 2006-04-27

    3000 2009-09-03 2009-09-03 2010-04-013000_0 2009-09-03

    3000 2009-09-03 2009-09-03 2010-04-013000_1 2009-10-03

    3000 2009-09-03 2009-09-03 2010-04-013000_2 2009-11-03

    4000 2006-06-16 2006-06-16 2015-04-034000_0 2006-06-16

    4000 2006-06-16 2006-06-16 2015-04-034000_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

    #1808775

    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

    #1808776

    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

    #1808782

    Hi, i tried but the rowNum column is returning null. I can only see the inserted values.

  • June 29, 2015 at 9:38 am

    #1808808

    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:

    subIDprodgdatesalesdateprodendDateRowNumNewCol1 NewCol2

    20002006-02-272006-02-272008-04-0402000_0 2006-02-27

    20002006-02-272006-02-272008-04-0412000_1 2006-03-27

    20002006-02-272006-02-272008-04-0422000_2 2006-04-27

    30002009-09-032009-09-032010-04-0103000_0 2009-09-03

    30002009-09-032009-09-032010-04-0113000_1 2009-10-03

    30002009-09-032009-09-032010-04-0123000_2 2009-11-03

    40002006-06-162006-06-162015-04-0304000_0 2006-06-16

    40002006-06-162006-06-162015-04-0314000_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

    #1809061

    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

    #1809065

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 30, 2015 at 6:22 am

    #1809082

    I have solved the issue. My gratitude to Lowell for his time and patience.

  • July 3, 2015 at 4:55 pm

    #1810254

    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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy