Club PA 2.0 has arrived! If you'd like to access some extra PA content and help support the forums, check it out at patreon.com/ClubPA
The image size limit has been raised to 1mb! Anything larger than that should be linked to. This is a HARD limit, please do not abuse it.
Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

SQL consecutive days key

DeebaserDeebaser on my way to work in a suit and a tieAhhhh...come on fucking guyRegistered User regular
Alright, so Im trying to build a composite key to join to another table that is storing comments on the rows. The report feeding my table is run daily. The same row can show up multiple days and we looking to create a key based on the first consecutive instance the item appeared.

Basically Im looking to concatenate the existing composite_key and the first consecutive report_date


H/A please help and advise

YOLO. Swag. Whatever. Fuck it. Lets do this.

Posts

  • syndalissyndalis Getting Classy On the WallRegistered User, Loves Apple Products regular
    edited July 2014
    edit: misread question, disregard

    syndalis on
    SW-4158-3990-6116
    Let's play Mario Kart or something...
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    example:

    key report_date composite_key
    ABC123 7/1/2014 ABC1237/1/2014
    ABC123 7/10/2014 ABC1237/10/2014
    DEF456 7/11/2014 DEF4567/11/2014
    ABC123 7/11/2014 ABC1237/10/2014

    YOLO. Swag. Whatever. Fuck it. Lets do this.
  • schussschuss Registered User regular
    Wouldn't it just be SELECT Key, MIN(Report_Date) Group By Key ?

    syndalis
  • DiannaoChongDiannaoChong Registered User regular
    I think schuss has it, unless theres a detail were missing.

    steam_sig.png
  • BogartBogart Because I hate you Registered User, Moderator mod
    Deebaser wrote: »
    example:

    key report_date composite_key
    ABC123 7/1/2014 ABC1237/1/2014
    ABC123 7/10/2014 ABC1237/10/2014
    DEF456 7/11/2014 DEF4567/11/2014
    ABC123 7/11/2014 ABC1237/10/2014

    Shouldn't all three ABC123 rows have the same composite key made up of the row and the first date it appeared?

  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    edited July 2014
    schuss wrote: »
    Wouldn't it just be SELECT Key, MIN(Report_Date) Group By Key ?

    Unfortunately, it isn't that easy. That will return the minimum report date. I need the minimum consecutive report date.

    Like, if ABC123 showed up on one report in 2012 and started showing up again in June 2014, it's going to be because of a completely seperate issue. We want to fly all the consecutive appearances under the same banner.

    Deebaser on
    YOLO. Swag. Whatever. Fuck it. Lets do this.
  • DiannaoChongDiannaoChong Registered User regular
    edited July 2014
    So you have:
    unit
    issue
    comments

    many comments to issue
    many issues to unit

    You want output of unit with all issues, and comments per issue? But comments for the same issue but a different time to be rolled up into the first issue?

    edit: like 2 different events for 'item broke' should be rolled into the first 'item broke' event?

    DiannaoChong on
    steam_sig.png
  • syndalissyndalis Getting Classy On the WallRegistered User, Loves Apple Products regular
    Deebaser wrote: »
    schuss wrote: »
    Wouldn't it just be SELECT Key, MIN(Report_Date) Group By Key ?

    Unfortunately, it isn't that easy. That will return the minimum report date. I need the minimum consecutive report date.

    Like, if ABC123 showed up on one report in 2012 and started showing up again in June 2014, it's going to be because of a completely seperate issue. We want to fly all the consecutive appearances under the same banner.

    Okay, so you have this data:
    ABC123	5/11/2011	ABC1235/11/2011
    DEF456	3/11/2012	DEF4563/11/2012
    ABC123	7/1/2014	ABC1237/1/2014
    ABC123	7/10/2014	ABC1237/10/2014
    DEF456	7/11/2014	DEF4567/11/2014
    ABC123	7/11/2014	ABC1237/11/2014
    

    What is the exact row you would like to reference with the last row?

    SW-4158-3990-6116
    Let's play Mario Kart or something...
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    edited July 2014
    I have two tables. One is the report table. The other is the comment table.
    The composite key includes the report_date is one to one on the report table, but we need a foreign key to join to the comment table that is composite key minus the report date + the first consecutive instance of the report date.

    Deebaser on
    YOLO. Swag. Whatever. Fuck it. Lets do this.
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    Bogart wrote: »
    Deebaser wrote: »
    example:

    key report_date composite_key
    ABC123 7/1/2014 ABC1237/1/2014
    ABC123 7/10/2014 ABC1237/10/2014
    DEF456 7/11/2014 DEF4567/11/2014
    ABC123 7/11/2014 ABC1237/10/2014

    Shouldn't all three ABC123 rows have the same composite key made up of the row and the first date it appeared?

    no, we need it minimum consecutive date. That is why the 2nd and 4th row use 7/10, but the 1st uses 7/1.

    YOLO. Swag. Whatever. Fuck it. Lets do this.
  • schussschuss Registered User regular
    Deebaser wrote: »
    schuss wrote: »
    Wouldn't it just be SELECT Key, MIN(Report_Date) Group By Key ?

    Unfortunately, it isn't that easy. That will return the minimum report date. I need the minimum consecutive report date.

    Like, if ABC123 showed up on one report in 2012 and started showing up again in June 2014, it's going to be because of a completely seperate issue. We want to fly all the consecutive appearances under the same banner.

    Then you'll need to do an encapsulated sub-clause, or whatever they're called (I use GUI's for my SQL, just troubleshoot it). Something like Select Report, (Select Date where Report = Report and Date = Date+1) Group by Report. Honestly though, to do it in pure SQL will be a royal pain, while producing it in BI Tools or Excel will be easy, as you're really talking about 2 groups of report+dates to build your key from, 1-1 report/date and 1-many report/dates, so ideally you'll select as separate groups and union them.

  • BogartBogart Because I hate you Registered User, Moderator mod
    I think I get what you want, but can't immediately see a way to get it in a single query without using a function or something to get the earliest consecutive date for a key. In the function would be something that just looped backwards one day at a time through rows using the key until it came up empty, then outputted the earliest one. Ugly and, depending on the size of the table, time-consuming.

    syndalis
  • syndalissyndalis Getting Classy On the WallRegistered User, Loves Apple Products regular
    Deebaser wrote: »
    Bogart wrote: »
    Deebaser wrote: »
    example:

    key report_date composite_key
    ABC123 7/1/2014 ABC1237/1/2014
    ABC123 7/10/2014 ABC1237/10/2014
    DEF456 7/11/2014 DEF4567/11/2014
    ABC123 7/11/2014 ABC1237/10/2014

    Shouldn't all three ABC123 rows have the same composite key made up of the row and the first date it appeared?

    no, we need it minimum consecutive date. That is why the 2nd and 4th row use 7/10, but the 1st uses 7/1.

    ooooh.... shit, I think I get it.

    you want to know, day by day

    so like


    7/1 -> 7/1
    7/2 -> 7/1
    7/3 -> 7/1
    7/8 -> 7/8
    7/9 -> 7/8

    rite?

    SW-4158-3990-6116
    Let's play Mario Kart or something...
    Deebaser
  • DiannaoChongDiannaoChong Registered User regular
    edited July 2014
    edit nevermind

    DiannaoChong on
    steam_sig.png
    Deebaser
  • syndalissyndalis Getting Classy On the WallRegistered User, Loves Apple Products regular
    edited July 2014
    Then bogart is right in that in each row you will need to run a function that checks for the date before in a different row with the same key. If it returns >0 rows, that date is the new column value and then run the function again in a loop until the response is null or 0 rows depending on your db engine.

    If the database is huge, this may take a while, but if you are only generating it once a month who gives a shit?

    syndalis on
    SW-4158-3990-6116
    Let's play Mario Kart or something...
    Delmain
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    edited July 2014
    It's about ~400 rows a day, and I plan on truncating this bitch annually.

    Deebaser on
    YOLO. Swag. Whatever. Fuck it. Lets do this.
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    Bogart wrote: »
    I think I get what you want, but can't immediately see a way to get it in a single query without using a function or something to get the earliest consecutive date for a key. In the function would be something that just looped backwards one day at a time through rows using the key until it came up empty, then outputted the earliest one. Ugly and, depending on the size of the table, time-consuming.

    Just thinking out loud here, but after the initial backfill (which I dont give a warm fart about tbh), shouldn't I be able to look up if there was a report sharing the key on the most previous run of the report and if so, update the current days report to that value else start new with composite + current report date?

    YOLO. Swag. Whatever. Fuck it. Lets do this.
  • BogartBogart Because I hate you Registered User, Moderator mod
    Is there any way you can just add another column to the table to hold the earliest consecutive date? It'll mean an initial query that probably uses a function to work out what that is for all the existing rows, but after that all you need to do is check the table for a record with the same key and yesterday's date. If one exists, use the new column firstConsecutiveDate from that row to fill the same column in the new row, and if not use today's date to fill the new column.

    FirstConsecutiveDate will always be the earliest consecutive date for that key, and you only need to check for one with yesterday's date and the same key.

    Dunno if adding a new column is possible, though.

  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    All things are possible through Christ. (I am the dbo)

    I can create a new column, but the meat of the matter is I am struggling to identify that first consecutive date.

    YOLO. Swag. Whatever. Fuck it. Lets do this.
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    Maybe if I look at the last run report

    SELECT
    MAX(report_date) as previous_report_date
    from
    stupidtable
    WHERE
    CONVERT(Char(10),GETDATE(),101) > report_date

    then if exists key + previous_report_date
    use that the composite key value of that report date....

    (thinking out loud)

    YOLO. Swag. Whatever. Fuck it. Lets do this.
  • BogartBogart Because I hate you Registered User, Moderator mod
    As soon as you set up the initial load of the new column it'll be gravy to identify it in an ongoing basis. For the initial load I can't see a way around using a cursor or something to look back through the table one day at a time for the earliest consecutive date for that key. So its a pain in the arse at least once.

    After that, though, its just filling the column with (select isnull(b.firstConsecutiveDate, todaysDate) from tblA b where b.key = newRowKey and b.reportDate = todaysDate - 1).

    syndalisDeebaser
  • TehSlothTehSloth Hit Or Miss I Guess They Never Miss, HuhRegistered User regular
    edited July 2014
    It gets tricky if you start running into more than two consecutive days, a quick run through would be something like


    select a.mykey, a.report_date
    from stupidtable as a join stupidtable as b on a.mykey=b.mykey
    where DATEDIFF(dy, a.report_date, b.report_date) = 1

    edit: you could do min to just get the earliest of these but then if you had two separate consecutive occurrences you would ignore the later one

    edit2: you also need to make sure the year is the same if you have multiple years, so where datediff(dy,a.report_date,b.report_date)=1 and datediff(year,a.report_date,b.report_date)=0

    TehSloth on
    FC: 1993-7778-8872 PSN: TehSloth Xbox: SlothTeh
    twitch.tv/tehsloth
    Deebaser
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    Sloth, you sexy bastard.
    I owe u beer.

    I don't give a single solitary fuck about backfill. So this is perfect.

    YOLO. Swag. Whatever. Fuck it. Lets do this.
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    UPDATE db..stupidtable
    SET foreigncompositekey = rls2.foreigncompositekey
    FROM
    db..stupidtable rls1
    ,db..stupidtable rls2
    WHERE
    DATEDIFF(dy,CONVERT(char(10),rls1.report_date,101),CONVERT(char(10),rls2.report_date,101)) = 1
    AND rls1.compositekey = rls2.compositekey
    AND CONVERT(char(10),rls1.report_date,101) > CONVERT(char(10),rls2.report_date,101);
    

    This works perfectly. And will so long as the report is run every day.

    YOLO. Swag. Whatever. Fuck it. Lets do this.
  • DeebaserDeebaser on my way to work in a suit and a tie Ahhhh...come on fucking guyRegistered User regular
    thanks everyone!

    YOLO. Swag. Whatever. Fuck it. Lets do this.
Sign In or Register to comment.