SQL consecutive days key

Deebaser · July 2014

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

syndalis · July 2014

edit: misread question, disregard

Deebaser · July 2014

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

schuss · July 2014

Wouldn't it just be SELECT Key, MIN(Report_Date) Group By Key ?

DiannaoChong · July 2014

I think schuss has it, unless theres a detail were missing.

Bogart · July 2014

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?

Deebaser · 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.

DiannaoChong · 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?

syndalis · July 2014

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?

Deebaser · 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 · July 2014

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.

schuss · July 2014

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.

Bogart · July 2014

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 · July 2014

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?

DiannaoChong · July 2014

edit nevermind

syndalis · 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?

Deebaser · July 2014

It's about ~400 rows a day, and I plan on truncating this bitch annually.

Deebaser · July 2014

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?

Bogart · July 2014

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.

Deebaser · July 2014

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.

Deebaser · July 2014

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)

Bogart · July 2014

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).

TehSloth · 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

Deebaser · July 2014

Sloth, you sexy bastard.
I owe u beer.

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

Deebaser · July 2014

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.

Deebaser · July 2014

thanks everyone!

Penny Arcade

Quick Links

SQL consecutive days key

Posts