query help...

3 Nov 2005 11:05 PM

Bob

Hello,
    I am kinda lost with this problem and hoping that someone here might be
able to help out!
The sample data below shows transactions for an ID - basically, for
different IDs, we keep track of when their rc (return code) changes and the
values to what it changes.
I would like to get the difference in dates from when it changed from a 'non
999' value to a '999' value. I don't need the converse (change from 999 to
non-999)

CREATE TABLE #test (
    ID_no char (10) ,
    valdate datetime ,
    rc char(3)
)
insert into #test select '1111','2005-01-05 12:00:00', '010'
insert into #test select '1111','2005-01-15 12:00:00', '999'
insert into #test select '1111','2005-01-15 12:39:00', '103'
insert into #test select '1111','2005-01-15 14:00:00', '211'
insert into #test select '1111','2005-05-05 12:00:00', '999'
insert into #test select '1111','2005-05-15 12:00:00', '918'
insert into #test select '2222','2005-02-05 12:01:00', '050'
insert into #test select '2222','2005-02-15 12:11:00', '999'
insert into #test select '2222','2005-03-05 16:00:00', '054'
insert into #test select '2222','2005-03-15 18:00:00', '999'

Expected result

1111    240 --datediff(hh, '2005-01-05 12:00:00', '2005-01-15 12:00:00')
1111    2638 --datediff(hh, '2005-01-15 14:00:00', '2005-05-05 12:00:00')
2222    240 --datediff(hh, '2005-02-05 12:01:00', '2005-02-15 12:11:00')
2222    242 --datediff(hh, '2005-03-05 16:00:00', '2005-03-15 18:00:00')

Any suggestions/pointers?
Thanks!

3 Nov 2005 11:17 PM

Anith Sen

Based on your test data:

SELECT t1.ID_no,
       DATEDIFF( hh, ( SELECT MAX( t2.valdate )
                         FROM #test t2
                        WHERE t2.ID_no = t1.ID_no
                          AND t2.valdate < t1.valdate
                          AND t2.rc <> 999 ), t1.valdate )
FROM #test t1
WHERE t1.rc = 999 ;

--
Anith

4 Nov 2005 12:41 AM

Bob

Thank you Anith! I believe this will work perfectly! As soon as I get to
work, I willl try this out and let you know!
Thanks for the really quick reply. (For some reason, I just couldn't get the
stuff between my ears to work today...)

Show quote

"Anith Sen" wrote:

> Based on your test data:
>
> SELECT t1.ID_no,
>        DATEDIFF( hh, ( SELECT MAX( t2.valdate )
>                          FROM #test t2
>                         WHERE t2.ID_no = t1.ID_no
>                           AND t2.valdate < t1.valdate
>                           AND t2.rc <> 999 ), t1.valdate )
>   FROM #test t1
> WHERE t1.rc = 999 ;
>
> --
> Anith
>
>
>

4 Nov 2005 2:56 PM

Bob

Anith, I tried this out and it worked perfectly - except for one scenario
that I had not included in my sample data - when for a given Id_no, if there
is a row with a rc=999, but no row with a 'non-999' rc whose date is less
than the date for the row with rc=999, then, the query returns a row with the
id_no and the datediff as NULL. This row shouldn't have been returned.
(I realize that I had not clarified this earlier)

Thanks for your help again!

Show quote

"Anith Sen" wrote:

> Based on your test data:
>
> SELECT t1.ID_no,
>        DATEDIFF( hh, ( SELECT MAX( t2.valdate )
>                          FROM #test t2
>                         WHERE t2.ID_no = t1.ID_no
>                           AND t2.valdate < t1.valdate
>                           AND t2.rc <> 999 ), t1.valdate )
>   FROM #test t1
> WHERE t1.rc = 999 ;
>
> --
> Anith
>
>
>

3 Nov 2005 11:25 PM

Steven Wilmot

Show quote

"Bob" <B**@discussions.microsoft.com> wrote in message
news:E761B80C-4FCE-481F-8F6A-463E272B3E73@microsoft.com...
> Hello,
> I am kinda lost with this problem and hoping that someone here might be
> able to help out!
> The sample data below shows transactions for an ID - basically, for
> different IDs, we keep track of when their rc (return code) changes and
> the
> values to what it changes.
> I would like to get the difference in dates from when it changed from a
> 'non
> 999' value to a '999' value. I don't need the converse (change from 999 to
> non-999)
>
>
> CREATE TABLE #test (
> ID_no char (10) ,
> valdate datetime ,
> rc char(3)
> )
> insert into #test select '1111','2005-01-05 12:00:00', '010'
> insert into #test select '1111','2005-01-15 12:00:00', '999'
> insert into #test select '1111','2005-01-15 12:39:00', '103'
> insert into #test select '1111','2005-01-15 14:00:00', '211'
> insert into #test select '1111','2005-05-05 12:00:00', '999'
> insert into #test select '1111','2005-05-15 12:00:00', '918'
> insert into #test select '2222','2005-02-05 12:01:00', '050'
> insert into #test select '2222','2005-02-15 12:11:00', '999'
> insert into #test select '2222','2005-03-05 16:00:00', '054'
> insert into #test select '2222','2005-03-15 18:00:00', '999'
>
>
> Expected result
>
> 1111 240 --datediff(hh, '2005-01-05 12:00:00', '2005-01-15 12:00:00')
> 1111 2638 --datediff(hh, '2005-01-15 14:00:00', '2005-05-05 12:00:00')
> 2222 240 --datediff(hh, '2005-02-05 12:01:00', '2005-02-15 12:11:00')
> 2222 242 --datediff(hh, '2005-03-05 16:00:00', '2005-03-15 18:00:00')
>
> Any suggestions/pointers?
> Thanks!

select
t999.id_no,
t2.valdate,
t999.valdate,
datediff(hh,t2.valdate,t999.valdate)
from #test t999
inner join #test t2
on t2.valdate < t999.valdate
and t2.valdate =
(
   select max(t3.valdate)
   from #test t3
   where
    t3.valdate < t999.valdate
   and t3.id_no = t999.id_no
)
and t2.rc <> '999'
and t999.rc = '999'
and t2.id_no = t999.id_no

----

Thanks for providing the DDL ... It made it much easier to create an answer
for you.

Steven

4 Nov 2005 12:42 AM

Bob

Steven, thank you for the really quick response! Took me a few minutes to
understand the query, but I believe this will work perfectly! I will try it
out first thing tomorrow at work and will update back! Thanks again:)

Show quote

"Steven Wilmot" wrote:

>
> "Bob" <B**@discussions.microsoft.com> wrote in message
> news:E761B80C-4FCE-481F-8F6A-463E272B3E73@microsoft.com...
> > Hello,
> > I am kinda lost with this problem and hoping that someone here might be
> > able to help out!
> > The sample data below shows transactions for an ID - basically, for
> > different IDs, we keep track of when their rc (return code) changes and
> > the
> > values to what it changes.
> > I would like to get the difference in dates from when it changed from a
> > 'non
> > 999' value to a '999' value. I don't need the converse (change from 999 to
> > non-999)
> >
> >
> > CREATE TABLE #test (
> > ID_no char (10) ,
> > valdate datetime ,
> > rc char(3)
> > )
> > insert into #test select '1111','2005-01-05 12:00:00', '010'
> > insert into #test select '1111','2005-01-15 12:00:00', '999'
> > insert into #test select '1111','2005-01-15 12:39:00', '103'
> > insert into #test select '1111','2005-01-15 14:00:00', '211'
> > insert into #test select '1111','2005-05-05 12:00:00', '999'
> > insert into #test select '1111','2005-05-15 12:00:00', '918'
> > insert into #test select '2222','2005-02-05 12:01:00', '050'
> > insert into #test select '2222','2005-02-15 12:11:00', '999'
> > insert into #test select '2222','2005-03-05 16:00:00', '054'
> > insert into #test select '2222','2005-03-15 18:00:00', '999'
> >
> >
> > Expected result
> >
> > 1111 240 --datediff(hh, '2005-01-05 12:00:00', '2005-01-15 12:00:00')
> > 1111 2638 --datediff(hh, '2005-01-15 14:00:00', '2005-05-05 12:00:00')
> > 2222 240 --datediff(hh, '2005-02-05 12:01:00', '2005-02-15 12:11:00')
> > 2222 242 --datediff(hh, '2005-03-05 16:00:00', '2005-03-15 18:00:00')
> >
> > Any suggestions/pointers?
> > Thanks!
>
>
> select
> t999.id_no,
> t2.valdate,
> t999.valdate,
> datediff(hh,t2.valdate,t999.valdate)
> from #test t999
> inner join #test t2
> on t2.valdate < t999.valdate
> and t2.valdate =
>   (
>    select max(t3.valdate)
>    from #test t3
>    where
>     t3.valdate < t999.valdate
>    and t3.id_no = t999.id_no
>   )
> and t2.rc <> '999'
> and t999.rc = '999'
> and t2.id_no = t999.id_no
>
> ----
>
> Thanks for providing the DDL ... It made it much easier to create an answer
> for you.
>
> Steven
>
>
>

4 Nov 2005 2:56 PM

Bob

Works perfectly! thanks again!

Show quote

"Steven Wilmot" wrote:

>
> "Bob" <B**@discussions.microsoft.com> wrote in message
> news:E761B80C-4FCE-481F-8F6A-463E272B3E73@microsoft.com...
> > Hello,
> > I am kinda lost with this problem and hoping that someone here might be
> > able to help out!
> > The sample data below shows transactions for an ID - basically, for
> > different IDs, we keep track of when their rc (return code) changes and
> > the
> > values to what it changes.
> > I would like to get the difference in dates from when it changed from a
> > 'non
> > 999' value to a '999' value. I don't need the converse (change from 999 to
> > non-999)
> >
> >
> > CREATE TABLE #test (
> > ID_no char (10) ,
> > valdate datetime ,
> > rc char(3)
> > )
> > insert into #test select '1111','2005-01-05 12:00:00', '010'
> > insert into #test select '1111','2005-01-15 12:00:00', '999'
> > insert into #test select '1111','2005-01-15 12:39:00', '103'
> > insert into #test select '1111','2005-01-15 14:00:00', '211'
> > insert into #test select '1111','2005-05-05 12:00:00', '999'
> > insert into #test select '1111','2005-05-15 12:00:00', '918'
> > insert into #test select '2222','2005-02-05 12:01:00', '050'
> > insert into #test select '2222','2005-02-15 12:11:00', '999'
> > insert into #test select '2222','2005-03-05 16:00:00', '054'
> > insert into #test select '2222','2005-03-15 18:00:00', '999'
> >
> >
> > Expected result
> >
> > 1111 240 --datediff(hh, '2005-01-05 12:00:00', '2005-01-15 12:00:00')
> > 1111 2638 --datediff(hh, '2005-01-15 14:00:00', '2005-05-05 12:00:00')
> > 2222 240 --datediff(hh, '2005-02-05 12:01:00', '2005-02-15 12:11:00')
> > 2222 242 --datediff(hh, '2005-03-05 16:00:00', '2005-03-15 18:00:00')
> >
> > Any suggestions/pointers?
> > Thanks!
>
>
> select
> t999.id_no,
> t2.valdate,
> t999.valdate,
> datediff(hh,t2.valdate,t999.valdate)
> from #test t999
> inner join #test t2
> on t2.valdate < t999.valdate
> and t2.valdate =
>   (
>    select max(t3.valdate)
>    from #test t3
>    where
>     t3.valdate < t999.valdate
>    and t3.id_no = t999.id_no
>   )
> and t2.rc <> '999'
> and t999.rc = '999'
> and t2.id_no = t999.id_no
>
> ----
>
> Thanks for providing the DDL ... It made it much easier to create an answer
> for you.
>
> Steven
>
>
>