Running Date Difference 2

[mrliam69]

I have been trying to setup a running date difference (DAY) query for this table that resets on StockCode Change but with little luck as I have been trying to modify a running sum one I use...

Can anyone help

SELECT     RowNum, StockCode, ReqDate
FROM         temp6

Data example

 47	E0038	04/05/2007 10:02:00
	48	E0038	07/05/2007
	49	E0038	19/05/2007
	50	E0039	04/05/2007 10:02:00
	51	E0040	04/05/2007 10:02:00
	52	E0041	04/05/2007 10:02:00
	53	E0042	04/05/2007 10:02:00
	54	E0043	04/05/2007 10:02:00
	55	E0044	04/05/2007 10:02:00
	56	E0045	04/05/2007 10:02:00
	57	E0104	04/05/2007
	58	E0104	04/05/2007 10:02:00
	59	E0104	07/05/2007
	60	E0104	18/05/2007

 

[bborissov]

And what you want from this data?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[mrliam69]

so for instance

record 57 would return 0
58 = 0
59 = 3
60 = 11

48 = 0
49 = 12

 

[monksnake]

I wrote up a query that will get you the results you desire. I switched up the day and month in the date though. You can do that through a convert(), but by default the date object doesn't recognize, dd-mm-yyyy.

This is what I come up with:

declare @table table (idcol int, code char(5), dateChange datetime)
insert into @table 
      select    47,    'E0038',    '05/04/2007 10:02:00'
union select    48,    'E0038',    '05/07/2007'
union select    49,    'E0038',    '05/19/2007'
union select    50,    'E0039',    '05/04/2007 10:02:00'
union select    51,    'E0040',    '05/04/2007 10:02:00'
union select    52,    'E0041',    '05/04/2007 10:02:00'
union select    53,    'E0042',    '05/04/2007 10:02:00'
union select    54,    'E0043',    '05/04/2007 10:02:00'
union select    55,    'E0044',    '05/04/2007 10:02:00'
union select    56,    'E0045',    '05/04/2007 10:02:00'
union select    57,    'E0104',    '05/04/2007'
union select    58,    'E0104',    '05/04/2007 10:02:00'
union select    59,    'E0104',    '05/07/2007'
union select    60,    'E0104',    '05/18/2007'


select a.idcol, a.code, 
   case when datediff(dd, b.dateChange, a.dateChange) < 0 then 0 else datediff(dd, b.dateChange,
   a.dateChange) end daysSinceLastChange from 
   @table a cross join @table b
      where (b.idcol = (select max(idcol) from @table where idcol < a.idcol and a.code = b.code) 
      or b.idcol = (select max(idcol) from @table where idcol < a.idcol and a.code != b.code))
order by a.idcol

<.

 

[mrliam69]

Cheers for that

Gave it a go and it works but very slow

Tried it on 4200 rows and cancelled after 20 mins
tried it on the first 1000 rows and again cancelled after 20 mins

100 rows = 45 secs
200 rows = 89 secs
300 rows = 208 secs


would a cursor routine be quicker and if so any tips on cursors as I haven't done one before ??

 

[gmmastros]

I think I understand what you are trying to accomplish here, but let me make sure.

For each row, you want the difference (in days) from that row to the previous row. So, for row 59, it should be Row 59's date - Row 58's date. Is this correct?

If that's the case, then take a look at this query:

[COLOR=blue]declare[/color] @table [COLOR=blue]table[/color] (idcol [COLOR=blue]int[/color], code [COLOR=blue]char[/color](5), dateChange [COLOR=#FF00FF]datetime[/color])
[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @table 
      [COLOR=blue]select[/color]    47,    [COLOR=red]'E0038'[/color],    [COLOR=red]'05/04/2007 10:02:00'[/color]
union [COLOR=blue]select[/color]    48,    [COLOR=red]'E0038'[/color],    [COLOR=red]'05/07/2007'[/color]
union [COLOR=blue]select[/color]    49,    [COLOR=red]'E0038'[/color],    [COLOR=red]'05/19/2007'[/color]
union [COLOR=blue]select[/color]    50,    [COLOR=red]'E0039'[/color],    [COLOR=red]'05/04/2007 10:02:00'[/color]
union [COLOR=blue]select[/color]    51,    [COLOR=red]'E0040'[/color],    [COLOR=red]'05/04/2007 10:02:00'[/color]
union [COLOR=blue]select[/color]    52,    [COLOR=red]'E0041'[/color],    [COLOR=red]'05/04/2007 10:02:00'[/color]
union [COLOR=blue]select[/color]    53,    [COLOR=red]'E0042'[/color],    [COLOR=red]'05/04/2007 10:02:00'[/color]
union [COLOR=blue]select[/color]    54,    [COLOR=red]'E0043'[/color],    [COLOR=red]'05/04/2007 10:02:00'[/color]
union [COLOR=blue]select[/color]    55,    [COLOR=red]'E0044'[/color],    [COLOR=red]'05/04/2007 10:02:00'[/color]
union [COLOR=blue]select[/color]    56,    [COLOR=red]'E0045'[/color],    [COLOR=red]'05/04/2007 10:02:00'[/color]
union [COLOR=blue]select[/color]    57,    [COLOR=red]'E0104'[/color],    [COLOR=red]'05/04/2007'[/color]
union [COLOR=blue]select[/color]    58,    [COLOR=red]'E0104'[/color],    [COLOR=red]'05/04/2007 10:02:00'[/color]
union [COLOR=blue]select[/color]    59,    [COLOR=red]'E0104'[/color],    [COLOR=red]'05/07/2007'[/color]
union [COLOR=blue]select[/color]    60,    [COLOR=red]'E0104'[/color],    [COLOR=red]'05/18/2007'[/color]

[COLOR=blue]Select[/color] 	B.IdCol, [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Day[/color], A.DateChange, B.DateChange)
[COLOR=blue]From[/color] 	@Table A
		[COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] @Table B
			[COLOR=blue]On[/color] A.idcol = B.idcol-1

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

I'll take a look at writing that query more efficiently.

I'm no SQL expert, but from what I see on here (this forum) is that cursors are pretty much useless, there are always faster ways of writing queries than using cursors.

<.

 

[mrliam69]

Thats a lot faster gmmastros .. Thanks for pointing me in the right direction with that

and Monksnake I too have seen that cursors are a bad thing and your more of an expert than me but I'm learning all the time....

Thanks you guys

 

[monksnake]

Here is a better query:

select a.idcol, a.code, 
   case when datediff(dd, b.dateChange, a.dateChange) < 0 then 0 else datediff(dd, b.dateChange,
   a.dateChange) end daysSinceLastChange from 
   @table a inner join @table b on 
      (b.idcol = (select max(idcol) from @table where idcol < a.idcol and a.code = b.code) 
      or b.idcol = (select max(idcol) from @table where idcol < a.idcol and a.code != b.code))
order by a.idcol

I removed the cross join, which is a killer.


Do you have any indexes on this table??
One on the columns I call idcol and code would help tremendously if there isn't already one.

<.

 

[monksnake]

George, I like your query but I wasn't sure there was always going to be an "idcol" in numerical order by 1 every time, that's why I went with the subqueries that have min and max in them.





<.

 

[AlexCuse]

monksnake - I think that query gives some goofy results.

for this part:

48 E0038 3
49 E0038 12

Shouldn't the difference be 3, then 15?

Try this one out (with the same sample data)

I don't know if the performance will be any better

(i expect it will, because you limit what you are joining to to the first row for each code, and also replace the cross-join (huge cartesian product) with an inner join)

But what kind of machine are you running this on? Those times seem just awful to me!

[COLOR=blue]select[/color] x.idcol
, x.code
, [COLOR=#FF00FF]datediff[/color]([COLOR=blue]d[/color], z.DateChange, x.DateChange) [COLOR=blue]as[/color] daysSince
[COLOR=blue]from[/color] @table x
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]select[/color] a.dateChange, a.idcol, a.code
[COLOR=blue]from[/color] @table a
[COLOR=blue]inner[/color] [COLOR=blue]join[/color]
(
[COLOR=blue]select[/color] [COLOR=#FF00FF]min[/color](idcol) [COLOR=blue]as[/color] idcol
, code [COLOR=blue]from[/color]
@table 
[COLOR=blue]group[/color] [COLOR=blue]by[/color] code
) b
[COLOR=blue]on[/color] a.idcol = b.idcol
and a.code = b.code
) z
[COLOR=blue]on[/color] x.idcol >= z.idcol
and x.code = z.code
[COLOR=blue]order[/color] [COLOR=blue]by[/color] x.idcol

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

>> George, I like your query but I wasn't sure there was always going to be an "idcol" in numerical order by 1 every time, that's why I went with the subqueries that have min and max in them.

Monksnake, you bring up a good point about the consecutive row numbers. From the original post, there's a column called RowNum and a table called Temp6. From this, I assumed that this temporary table has an identity column (RowNum). If this assumption is incorrect, then it would still be faster to create a table variable, dump the data in to it, and then run the query on the table variable. This is likely to be faster than nested subqueries.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

monksnake - I think that query gives some goofy results.

for this part:

48 E0038 3
49 E0038 12

Shouldn't the difference be 3, then 15?

Actually no, cause I entered the data like this, which was different than the posters data. That was an incorrect copy job by me, but the results are correct.

union select 48, 'E0038', '05/07/2007'
union select 49, 'E0038', '05/19/2007'


<.

 

[AlexCuse]

Late to the party (as usual). And I think I misunderstood your requirement. Apologies.

Ignorance of certain subjects is a great part of wisdom

 

[monksnake]

If this assumption is incorrect, then it would still be faster to create a table variable, dump the data in to it, and then run the query on the table variable. This is likely to be faster than nested subqueries.

Oh I absolutely agree, I just (for once) didn't assume anything.

<.

 

[AlexCuse]

I misunderstood the OP's requirement monksnake. I thought he wanted a running date difference since the last time stock code changed, not the difference in days between rows.

If that is what he wanted, then my query is the one that gives goofy results

Ignorance of certain subjects is a great part of wisdom

 

[mrliam69]

Your quite right George I am trying to do a complicated sorting query using temp tables and I am using an idcol so all the row numbers will be consecutive and your second example runs in under 10 secs in my total query....

The machine is a 4 core Xeon with 2GB ram running W2K and SQL2K

 

[gmmastros]

10 seconds for 4200 rows? Something is wrong. This should probably be instant for such a small number of records. Do you have any indexes on the Temp6 table?

I would suggest an index on the RowNum column. If possible, make it a clustered index.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrliam69]

No thats the total run time for the script....

it takes roughly 8 - 9 secs for it to get to your routine while it builds up to the final temp table and your routine is 1 sec or less

 

[gmmastros]

Ah.... Ok. I misunderstood. Thanks for clarifying.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom