Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2010
    Posts
    3

    Unanswered: creating multiple records with one column changed

    Ok so its hard to put what i need exactly into a subject line so heres where i explain.

    I have a query that i wrote that is returning data based on a value called livedate. In this query there is an entry for things ill call sites. There is only one entry per site based on the live date. What i am trying to do is to eventually turn this query into a stored proc in order to feed it to a report im designing with a matrix.

    For each site in the data returned from the query there is a package associated with it (only 2 options) and these columns are filled with a 1 or 0. Now my problem is that i need this matrix to show that every month after the date listed in the entry that package is still there otherwise in the matrix there will be one entry at the month and year according to the live date and everything after that will be blank.


    Example table to better understand, this is what i currently have(for one site):

    client ID Date Package1 Package2

    Dr. Yes 563 9/09 1 0



    now according to what i've said above i want it to print out like this after im done.

    client ID Date Package1 Package2

    Dr. Yes 563 9/09 1 0

    Dr. Yes 563 10/09 1 0

    Dr. Yes 563 11/09 1 0

    Dr. Yes 563 12/09 1 0

    Dr. Yes 563 1/10 1 0

    Dr. Yes 563 2/10 1 0



    Basically i just need to create a copy of each column in every entry but change the dates. The date of the entry should continue to increase all the way until a month before the current month. So if the report ran today it would show data all the way up till November 2010 so im assuming i would need a while loop and i would need this while loop to run all the way until that date. so the last entry for that client would show.

    Dr. Yes 563 11/10 1 0

    the date column is in a datetime format, and also i need this for every entry returned in the query and the livedate can range anywhere from January of the previous year up till todays date.

    any help would be much appreciated.

    after some help with a co worker, we have decided that an extra temp table with all the possible date combinations needed would be added to the query then i can join on that table to my current table and somehow use a while loop to check through that date temp table to create the extra entries
    Last edited by themainAV; 12-21-10 at 14:07.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    themainAV, If I followed what you want, this may work:

    Code:
    WITH CNT_TAB (CNT)
      AS (SELECT 0 UNION ALL
          SELECT 1 UNION ALL
          SELECT 2 UNION ALL
          SELECT 3 UNION ALL
          SELECT 4 UNION ALL
          SELECT 5
         )
       , DATA_TAB (CLIENT, ID, LIVEDATE, PK1, PK2)
      AS (SELECT 'DR. YES', 563, GETDATE(), 1, 0
         )
    SELECT CLIENT
         , ID
         , DATEADD(M, CNT, LIVEDATE) AS LIVE_DT
         , PK1
         , PK2
    FROM DATA_TAB
         CNT_TAB
    This will return 6 rows starting with today's date and each succeeding row will be incremented by 1 month.

  3. #3
    Join Date
    Jun 2005
    Posts
    319
    A little shorthand for u:
    Code:
    WITH CTE_TAB (CNT)
      AS (SELECT 0 
          UNION ALL
          SELECT CNT + 1 FROM CTE_TAB
          WHERE HH < 5
         )

  4. #4
    Join Date
    Dec 2010
    Posts
    3
    Quote Originally Posted by Stealth_DBA View Post
    themainAV, If I followed what you want, this may work:

    Code:
    WITH CNT_TAB (CNT)
      AS (SELECT 0 UNION ALL
          SELECT 1 UNION ALL
          SELECT 2 UNION ALL
          SELECT 3 UNION ALL
          SELECT 4 UNION ALL
          SELECT 5
         )
       , DATA_TAB (CLIENT, ID, LIVEDATE, PK1, PK2)
      AS (SELECT 'DR. YES', 563, GETDATE(), 1, 0
         )
    SELECT CLIENT
         , ID
         , DATEADD(M, CNT, LIVEDATE) AS LIVE_DT
         , PK1
         , PK2
    FROM DATA_TAB
         CNT_TAB
    This will return 6 rows starting with today's date and each succeeding row will be incremented by 1 month.
    i can see where that works, but its a bit different than what im looking for, i need it to do this for EVERY record i have (which is upwards of 7900) so i wouldn't be able to select it like you have there.

    Also i need it to be able to query off the livedate so it only starts looping and making the duplicate records AFTER the date that is presently in the query results (which i have placed into another table)

    attached is 2 examples of the matrix im creating with this data. As you can see it's mostly blank with just a few records of 1 and 0 and i need that 1 and 0 to continue past that based on the date it starts. As you can see with the second picture they are scattered all about because that is where their livedate rests. All im really trying to do is to create filler data.

    i hope this further explanation helps

    Oh also a co worker of mine suggested that i created a while loop to fill a table with all the applicable months and years and then to somewhat join to that table then somehow create the duplicate rows that way. not sure if that makes any sense, but ill be talking to him further today.
    Attached Thumbnails Attached Thumbnails ex matrix.png   ex matrix2.png  
    Last edited by themainAV; 12-22-10 at 12:04.

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    themainAV, First, the query supplied would work on multiple rows. But it would generate 6 rows for each row in the table. Additionally the date would be offset by 1 month starting with whatever LiveDate the row had.

    If I read you grid correctly, you want to pivot the data and list one client per row with the months in additional columns.

    If you stick with a one year time frame form Jan to Dec (don't cross year boundaries) then this may work. It may be better to do the pivoting in you application but here is some SQL that will do it:
    Code:
    WITH DATA_TAB (CLIENT, ID, LIVEDATE, PK1, PK2)
      AS (SELECT 'DR. YES', 563, DATEADD(M,2,GETDATE()), 1, 0 UNION ALL
          SELECT 'DR. NO' , 678, DATEADD(M,5,GETDATE()), 0, 1
         )
    SELECT CLIENT, ID
         , CASE WHEN MONTH(LIVEDATE) <=  1 THEN PK1 END AS JAN_PK1
         , CASE WHEN MONTH(LIVEDATE) <=  1 THEN PK2 END AS JAN_PK2
         , CASE WHEN MONTH(LIVEDATE) <=  2 THEN PK1 END AS FEB_PK1
         , CASE WHEN MONTH(LIVEDATE) <=  2 THEN PK2 END AS FEB_PK2
         , CASE WHEN MONTH(LIVEDATE) <=  3 THEN PK1 END AS MAR_PK1
         , CASE WHEN MONTH(LIVEDATE) <=  3 THEN PK2 END AS MAR_PK2
         , CASE WHEN MONTH(LIVEDATE) <=  4 THEN PK1 END AS APR_PK1
         , CASE WHEN MONTH(LIVEDATE) <=  4 THEN PK2 END AS APR_PK2
         , CASE WHEN MONTH(LIVEDATE) <=  5 THEN PK1 END AS MAY_PK1
         , CASE WHEN MONTH(LIVEDATE) <=  5 THEN PK2 END AS MAY_PK2
         , CASE WHEN MONTH(LIVEDATE) <=  6 THEN PK1 END AS JUN_PK1
         , CASE WHEN MONTH(LIVEDATE) <=  6 THEN PK2 END AS JUN_PK2
         , CASE WHEN MONTH(LIVEDATE) <=  7 THEN PK1 END AS JUL_PK1
         , CASE WHEN MONTH(LIVEDATE) <=  7 THEN PK2 END AS JUL_PK2
         , CASE WHEN MONTH(LIVEDATE) <=  8 THEN PK1 END AS AUG_PK1
         , CASE WHEN MONTH(LIVEDATE) <=  8 THEN PK2 END AS AUG_PK2
         , CASE WHEN MONTH(LIVEDATE) <=  9 THEN PK1 END AS SEP_PK1
         , CASE WHEN MONTH(LIVEDATE) <=  9 THEN PK2 END AS SEP_PK2
         , CASE WHEN MONTH(LIVEDATE) <= 10 THEN PK1 END AS OCT_PK1
         , CASE WHEN MONTH(LIVEDATE) <= 10 THEN PK1 END AS OCT_PK2
         , CASE WHEN MONTH(LIVEDATE) <= 11 THEN PK2 END AS NOV_PK1
         , CASE WHEN MONTH(LIVEDATE) <= 11 THEN PK1 END AS NOV_PK2
         , CASE WHEN MONTH(LIVEDATE) <= 12 THEN PK2 END AS DEC_PK1
         , CASE WHEN MONTH(LIVEDATE) <= 12 THEN PK2 END AS DEC_PK2
    FROM DATA_TAB
    NOTE: In the common table expression that is creating my work 'table', I am using DATEADD to advance the dates to next year.

  6. #6
    Join Date
    Dec 2010
    Posts
    3
    i didn't use a loop to get this all done, a loop was way too complicated.

    THE ANSWER:

    I created a temp table with a date value one month apart of all dates within my begin and end period. This table also had a key of 1 for every entry as well as did my other large table with the single entries. I then joined these two tables on that key and just compared it to my begin and end period.

    Code:
    WHERE dateadd(m,+1,myt.tempdate) >= ls.thedate AND
    myt.tempdate BETWEEN @BeginPeriod AND @EndPeriod
    the only issue i have now is if there is a date in the table whose livedate is the first of the month, on my report it is going to show that it started the package the month before (i think.... checking into it)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •