Get last date in result set

8 Sep 2006 10:50 AM

kishor

Hi,

I need to get the last date of when stock levels hit zero so I can then
do a datediff to calcualte days out of stock. Here is a table with
sample data

productid,stocklevel,date
1,10,1/1/2006
1,0,2/1/2006
1,0,3/1/2006
1,100,3/1/2006
2,0,1/1/2006
2,0,2/1/2006
2,10,3/1/2006

Desired result

productid,stocklevel,date,lastdateoutofstock
1,10,1/1/2006,null
1,0,2/1/2006,2/1/2006
1,0,3/1/2006 ,2/1/2006
1,100,3/1/2006,NULL
1,0,4/1/2006,4/1/2006
2,0,1/1/2006 ,1/1/2006
2,0,2/1/2006 ,1/1/2006
2,10,3/1/2006,NULL

Currently I use a cursor to do this but it is taking too long. Hoping
it can be done using Set based T-SQL.

Thanks

8 Sep 2006 11:10 AM

Sreejith G

Requirement not clear boss///

Seems like you need to take previous out of stock date whenever a out of
stock record comes. But ur example is not that helpful.

But guessing I think you can rely only looping and take it. How much is the
record size. If its not much try in table variable and try cutting cursor and
optimize it.

--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]

Show quote

"kis***@microlytics.co.uk" wrote:

> Hi,
>
> I need to get the last date of when stock levels hit zero so I can then
> do a datediff to calcualte days out of stock. Here is a table with
> sample data
>
> productid,stocklevel,date
> 1,10,1/1/2006
> 1,0,2/1/2006
> 1,0,3/1/2006
> 1,100,3/1/2006
> 2,0,1/1/2006
> 2,0,2/1/2006
> 2,10,3/1/2006
>
> Desired result
>
> productid,stocklevel,date,lastdateoutofstock
> 1,10,1/1/2006,null
> 1,0,2/1/2006,2/1/2006
> 1,0,3/1/2006 ,2/1/2006
> 1,100,3/1/2006,NULL
> 1,0,4/1/2006,4/1/2006
> 2,0,1/1/2006 ,1/1/2006
> 2,0,2/1/2006 ,1/1/2006
> 2,10,3/1/2006,NULL
>
> Currently I use a cursor to do this but it is taking too long. Hoping
> it can be done using Set based T-SQL.
>
> Thanks
>
>

8 Sep 2006 11:36 AM

kishor

Thanks for the response

To clarify, based on the sample data when stocklevel is zero then we
take the date of that record if it has not been out of stock the day
before. This would become the lastdateoutofstock. If that same
product has further stocklevels of zero in the subsequant records then
we take the first date out of stock.

Looking at the sample output data product 1 goes out of stock on
2/1/2006 and is out of stock until 3/1/2006 so the lastdateoutofstock
should be 2/1/2006. It then goes out of stock again on 4/1/2006 so we
set lastdateoutofstock to 4/1/2006 since this is the first date of when
it went out of stock after stock was replenished.

We have around 55 million records that need to be loaded historically
and the cursor compares first record to previous record and is
currently taking around 12 hours to run.

Could this be done using set based SQL . Using SQL 2000

Thanks

productid,stocklevel,date,lastdateoutofstock
1,10,1/1/2006,null
1,0,2/1/2006,2/1/2006
1,0,3/1/2006 ,2/1/2006
1,100,4/1/2006,NULL
1,0,4/1/2006,4/1/2006
2,0,1/1/2006 ,1/1/2006
2,0,2/1/2006 ,1/1/2006
2,10,3/1/2006,NULL

Sreejith G wrote:
Show quote

> Requirement not clear boss///
>
> Seems like you need to take previous out of stock date whenever a out of
> stock record comes. But ur example is not that helpful.
>
> But guessing I think you can rely only looping and take it. How much is the
> record size. If its not much try in table variable and try cutting cursor and
> optimize it.
>
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
>
>
> "kis***@microlytics.co.uk" wrote:
>
> > Hi,
> >
> > I need to get the last date of when stock levels hit zero so I can then
> > do a datediff to calcualte days out of stock. Here is a table with
> > sample data
> >
> > productid,stocklevel,date
> > 1,10,1/1/2006
> > 1,0,2/1/2006
> > 1,0,3/1/2006
> > 1,100,3/1/2006
> > 2,0,1/1/2006
> > 2,0,2/1/2006
> > 2,10,3/1/2006
> >
> > Desired result
> >
> > productid,stocklevel,date,lastdateoutofstock
> > 1,10,1/1/2006,null
> > 1,0,2/1/2006,2/1/2006
> > 1,0,3/1/2006 ,2/1/2006
> > 1,100,3/1/2006,NULL
> > 1,0,4/1/2006,4/1/2006
> > 2,0,1/1/2006 ,1/1/2006
> > 2,0,2/1/2006 ,1/1/2006
> > 2,10,3/1/2006,NULL
> >
> > Currently I use a cursor to do this but it is taking too long. Hoping
> > it can be done using Set based T-SQL.
> >
> > Thanks
> >
> >

8 Sep 2006 11:25 AM

ML

Try this (untested, since you haven't posted any DDL):

select currStock.productid
         ,currStock.stocklevel
         ,currStock.date
         ,lastdateoutofstock
                = case
                     when exists(
                                      select *
                                                from <table> prevStock
                                                where (prevStock.stocklevel
= 0)
                                                          and
(prevStock.date <= currStock.date)
                                      )
                              then (
                                      select top 1 prevStock.date
                                                from <table> prevStock
                                                where (prevStock.stocklevel
= 0)
                                                          and
(prevStock.date <= currStock.date)
                                                order by prevStock.date desc
                                     )
                              end
         from <table> currStock

ML

---
http://milambda.blogspot.com/

8 Sep 2006 11:35 AM

ML

On second thought that DESC ought to be ASC. Well, you'll be the judge of
that.

ML

---
http://milambda.blogspot.com/

8 Sep 2006 12:30 PM

kishor

Hi ML,

What would you suggest indexing on the table mentioned based on your
query? Hopefully I can get some performance improvements on indexing
as well.

Thanks

ML wrote:
Show quote

> On second thought that DESC ought to be ASC. Well, you'll be the judge of
> that.
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

8 Sep 2006 12:41 PM

ML

Perhaps a covering index on (date, productid, stocklevel). Just judging from
this one query.

But - does the query work as it's supposed to?

ML

---
http://milambda.blogspot.com/

8 Sep 2006 1:05 PM

kishor

Hi ML,

I've just tested it on the large table but got back incorrect results.
I then tested the SQL on a sample data (DDL below for your information)

It appears that lastdayoutofstock is defaulting on the first date in
the table rather than getting the true last date out of stock for a
particuar product.

Thanks for your help by the way. Much appreciated.

----------------------------------------------------------------------
CREATE TABLE [stock] (
    [productid] [int] NULL ,
    [stocklevel] [int] NULL ,
    [date] [datetime] NULL
) ON [PRIMARY]
GO

insert into stock(productid ,stocklevel ,date ) values
(1,10,'1/1/2006')
insert into stock(productid ,stocklevel ,date ) values
(1,0,'2/1/2006')
insert into stock(productid ,stocklevel ,date ) values
(1,0,'3/1/2006')
insert into stock(productid ,stocklevel ,date ) values
(1,100,'4/1/2006')
insert into stock(productid ,stocklevel ,date ) values
(2,0,'1/1/2006')
insert into stock(productid ,stocklevel ,date ) values
(2,0,'2/1/2006')
insert into stock(productid ,stocklevel ,date ) values
(2,10,'3/1/2006')

ML wrote:
Show quote

> Perhaps a covering index on (date, productid, stocklevel). Just judging from
> this one query.
>
> But - does the query work as it's supposed to?
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

8 Sep 2006 1:22 PM

kishor

Hi ML.

Just modified your original sql by adding the productid constraint in
the where clause of the select statement. SQL below does the trick.

Thanks again for valuable time and input.

select currStock.productid
         ,currStock.stocklevel
         ,currStock.[date]
         ,lastdateoutofstock
                = case
                     when exists(
                                      select *
                                                from stock prevStock
                                                where
(prevStock.stocklevel
= 0)
                                                          and
(prevStock.[date] <= currStock.[date]) and (prevstock.productid =
currstock.productid)and
(currstock.stocklevel =0)
                                      )
                              then (
                                      select top 1 prevStock.[date]
                                                from stock prevStock
                                                where
(prevStock.stocklevel
= 0)
                                                          and
(prevStock.[date] <= currStock.[date])
and (prevstock.productid = currstock.productid)
                                                order by
prevStock.[date] Asc
                                     )
                              end
         from stock currStock

kis***@microlytics.co.uk wrote:
Show quote

> Hi ML,
>
> I've just tested it on the large table but got back incorrect results.
> I then tested the SQL on a sample data (DDL below for your information)
>
> It appears that lastdayoutofstock is defaulting on the first date in
> the table rather than getting the true last date out of stock for a
> particuar product.
>
> Thanks for your help by the way. Much appreciated.
>
> ----------------------------------------------------------------------
> CREATE TABLE [stock] (
>     [productid] [int] NULL ,
>     [stocklevel] [int] NULL ,
>     [date] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
>
> insert into stock(productid ,stocklevel ,date ) values
> (1,10,'1/1/2006')
> insert into stock(productid ,stocklevel ,date ) values
> (1,0,'2/1/2006')
> insert into stock(productid ,stocklevel ,date ) values
> (1,0,'3/1/2006')
> insert into stock(productid ,stocklevel ,date ) values
> (1,100,'4/1/2006')
> insert into stock(productid ,stocklevel ,date ) values
> (2,0,'1/1/2006')
> insert into stock(productid ,stocklevel ,date ) values
> (2,0,'2/1/2006')
> insert into stock(productid ,stocklevel ,date ) values
> (2,10,'3/1/2006')
>
> ML wrote:
> > Perhaps a covering index on (date, productid, stocklevel). Just judging from
> > this one query.
> >
> > But - does the query work as it's supposed to?
> >
> >
> > ML
> >
> > ---
> > http://milambda.blogspot.com/

8 Sep 2006 1:32 PM

Sha Anand

You can try this.....

select s1.ProductId,s1.stocklevel,s1.date1,
(select min(s2.date1) from stock s2
where
    s1.ProductId     = s2.ProductId and
    s2.date1     <= s1.date1 and
    s2.stocklevel     =0 and s1.stocklevel = 0 and
    s2.date1 >=
    (select max(s3.date1) from stock s3 where s3.date1 <= s1.date1 and
s3.stocklevel <> 0 )
)
from stock s1
order by 1,3

- Sha Anand

Show quote

"kis***@microlytics.co.uk" wrote:

> Hi ML.
>
> Just modified your original sql by adding the productid constraint in
> the where clause of the select statement. SQL below does the trick.
>
> Thanks again for valuable time and input.
>
>
> select currStock.productid
>          ,currStock.stocklevel
>          ,currStock.[date]
>          ,lastdateoutofstock
>                 = case
>                      when exists(
>                                       select *
>                                                 from stock prevStock
>                                                 where
> (prevStock.stocklevel
> = 0)
>                                                           and
> (prevStock.[date] <= currStock.[date]) and (prevstock.productid =
> currstock.productid)and
> (currstock.stocklevel =0)
>                                       )
>                               then (
>                                       select top 1 prevStock.[date]
>                                                 from stock prevStock
>                                                 where
> (prevStock.stocklevel
> = 0)
>                                                           and
> (prevStock.[date] <= currStock.[date])
> and (prevstock.productid = currstock.productid)
>                                                 order by
> prevStock.[date] Asc
>                                      )
>                               end
>          from stock currStock
>
>
>
> kis***@microlytics.co.uk wrote:
> > Hi ML,
> >
> > I've just tested it on the large table but got back incorrect results.
> > I then tested the SQL on a sample data (DDL below for your information)
> >
> > It appears that lastdayoutofstock is defaulting on the first date in
> > the table rather than getting the true last date out of stock for a
> > particuar product.
> >
> > Thanks for your help by the way. Much appreciated.
> >
> > ----------------------------------------------------------------------
> > CREATE TABLE [stock] (
> >     [productid] [int] NULL ,
> >     [stocklevel] [int] NULL ,
> >     [date] [datetime] NULL
> > ) ON [PRIMARY]
> > GO
> >
> >
> > insert into stock(productid ,stocklevel ,date ) values
> > (1,10,'1/1/2006')
> > insert into stock(productid ,stocklevel ,date ) values
> > (1,0,'2/1/2006')
> > insert into stock(productid ,stocklevel ,date ) values
> > (1,0,'3/1/2006')
> > insert into stock(productid ,stocklevel ,date ) values
> > (1,100,'4/1/2006')
> > insert into stock(productid ,stocklevel ,date ) values
> > (2,0,'1/1/2006')
> > insert into stock(productid ,stocklevel ,date ) values
> > (2,0,'2/1/2006')
> > insert into stock(productid ,stocklevel ,date ) values
> > (2,10,'3/1/2006')
> >
> > ML wrote:
> > > Perhaps a covering index on (date, productid, stocklevel). Just judging from
> > > this one query.
> > >
> > > But - does the query work as it's supposed to?
> > >
> > >
> > > ML
> > >
> > > ---
> > > http://milambda.blogspot.com/
>
>

8 Sep 2006 1:46 PM

Sha Anand

small correction... use the following Query

select s1.ProductId,s1.stocklevel,s1.[date],
(select min(s2.[date]) from stock s2
where
    s1.ProductId     = s2.ProductId and
    s2.[date]     <= s1.[date] and
    s2.stocklevel     =0 and s1.stocklevel = 0 and
    s2.[date] >=
    ISNULL((select max(s3.[date]) from stock s3 where s3.[date] < s1.[date] and
s3.stocklevel <> 0 ),s1.[date])
)
from stock s1
order by 1,3

- Sha Anand

Show quote

"Sha Anand" wrote:

> You can try this.....
>
> select s1.ProductId,s1.stocklevel,s1.date1,
> (select min(s2.date1) from stock s2
> where
>     s1.ProductId     = s2.ProductId and
>     s2.date1     <= s1.date1 and
>     s2.stocklevel     =0 and s1.stocklevel = 0 and
>     s2.date1 >=
>     (select max(s3.date1) from stock s3 where s3.date1 <= s1.date1 and
> s3.stocklevel <> 0 )
> )
> from stock s1
> order by 1,3
>
> - Sha Anand
>
> "kis***@microlytics.co.uk" wrote:
>
> > Hi ML.
> >
> > Just modified your original sql by adding the productid constraint in
> > the where clause of the select statement. SQL below does the trick.
> >
> > Thanks again for valuable time and input.
> >
> >
> > select currStock.productid
> >          ,currStock.stocklevel
> >          ,currStock.[date]
> >          ,lastdateoutofstock
> >                 = case
> >                      when exists(
> >                                       select *
> >                                                 from stock prevStock
> >                                                 where
> > (prevStock.stocklevel
> > = 0)
> >                                                           and
> > (prevStock.[date] <= currStock.[date]) and (prevstock.productid =
> > currstock.productid)and
> > (currstock.stocklevel =0)
> >                                       )
> >                               then (
> >                                       select top 1 prevStock.[date]
> >                                                 from stock prevStock
> >                                                 where
> > (prevStock.stocklevel
> > = 0)
> >                                                           and
> > (prevStock.[date] <= currStock.[date])
> > and (prevstock.productid = currstock.productid)
> >                                                 order by
> > prevStock.[date] Asc
> >                                      )
> >                               end
> >          from stock currStock
> >
> >
> >
> > kis***@microlytics.co.uk wrote:
> > > Hi ML,
> > >
> > > I've just tested it on the large table but got back incorrect results.
> > > I then tested the SQL on a sample data (DDL below for your information)
> > >
> > > It appears that lastdayoutofstock is defaulting on the first date in
> > > the table rather than getting the true last date out of stock for a
> > > particuar product.
> > >
> > > Thanks for your help by the way. Much appreciated.
> > >
> > > ----------------------------------------------------------------------
> > > CREATE TABLE [stock] (
> > >     [productid] [int] NULL ,
> > >     [stocklevel] [int] NULL ,
> > >     [date] [datetime] NULL
> > > ) ON [PRIMARY]
> > > GO
> > >
> > >
> > > insert into stock(productid ,stocklevel ,date ) values
> > > (1,10,'1/1/2006')
> > > insert into stock(productid ,stocklevel ,date ) values
> > > (1,0,'2/1/2006')
> > > insert into stock(productid ,stocklevel ,date ) values
> > > (1,0,'3/1/2006')
> > > insert into stock(productid ,stocklevel ,date ) values
> > > (1,100,'4/1/2006')
> > > insert into stock(productid ,stocklevel ,date ) values
> > > (2,0,'1/1/2006')
> > > insert into stock(productid ,stocklevel ,date ) values
> > > (2,0,'2/1/2006')
> > > insert into stock(productid ,stocklevel ,date ) values
> > > (2,10,'3/1/2006')
> > >
> > > ML wrote:
> > > > Perhaps a covering index on (date, productid, stocklevel). Just judging from
> > > > this one query.
> > > >
> > > > But - does the query work as it's supposed to?
> > > >
> > > >
> > > > ML
> > > >
> > > > ---
> > > > http://milambda.blogspot.com/
> >
> >

8 Sep 2006 2:43 PM

Hilary Cotter

Your method has 12 logical reads, mine has 2. I suspect mine offers better
performance:)

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Show quote

"Sha Anand" <ShaAn***@discussions.microsoft.com> wrote in message
news:7A96E73D-65A0-414E-9659-04AE76F5FD4C@microsoft.com...
> small correction... use the following Query
>
> select s1.ProductId,s1.stocklevel,s1.[date],
> (select min(s2.[date]) from stock s2
> where
> s1.ProductId = s2.ProductId and
> s2.[date] <= s1.[date] and
> s2.stocklevel =0 and s1.stocklevel = 0 and
> s2.[date] >=
> ISNULL((select max(s3.[date]) from stock s3 where s3.[date] < s1.[date]
> and
> s3.stocklevel <> 0 ),s1.[date])
> )
> from stock s1
> order by 1,3
>
> - Sha Anand
>
> "Sha Anand" wrote:
>
>> You can try this.....
>>
>> select s1.ProductId,s1.stocklevel,s1.date1,
>> (select min(s2.date1) from stock s2
>> where
>> s1.ProductId = s2.ProductId and
>> s2.date1 <= s1.date1 and
>> s2.stocklevel =0 and s1.stocklevel = 0 and
>> s2.date1 >=
>> (select max(s3.date1) from stock s3 where s3.date1 <= s1.date1 and
>> s3.stocklevel <> 0 )
>> )
>> from stock s1
>> order by 1,3
>>
>> - Sha Anand
>>
>> "kis***@microlytics.co.uk" wrote:
>>
>> > Hi ML.
>> >
>> > Just modified your original sql by adding the productid constraint in
>> > the where clause of the select statement. SQL below does the trick.
>> >
>> > Thanks again for valuable time and input.
>> >
>> >
>> > select currStock.productid
>> >          ,currStock.stocklevel
>> >          ,currStock.[date]
>> >          ,lastdateoutofstock
>> >                 = case
>> >                      when exists(
>> >                                       select *
>> >                                                 from stock prevStock
>> >                                                 where
>> > (prevStock.stocklevel
>> > = 0)
>> >                                                           and
>> > (prevStock.[date] <= currStock.[date]) and (prevstock.productid =
>> > currstock.productid)and
>> > (currstock.stocklevel =0)
>> >                                       )
>> >                               then (
>> >                                       select top 1 prevStock.[date]
>> >                                                 from stock prevStock
>> >                                                 where
>> > (prevStock.stocklevel
>> > = 0)
>> >                                                           and
>> > (prevStock.[date] <= currStock.[date])
>> > and (prevstock.productid = currstock.productid)
>> >                                                 order by
>> > prevStock.[date] Asc
>> >                                      )
>> >                               end
>> >          from stock currStock
>> >
>> >
>> >
>> > kis***@microlytics.co.uk wrote:
>> > > Hi ML,
>> > >
>> > > I've just tested it on the large table but got back incorrect
>> > > results.
>> > > I then tested the SQL on a sample data (DDL below for your
>> > > information)
>> > >
>> > > It appears that lastdayoutofstock is defaulting on the first date in
>> > > the table rather than getting the true last date out of stock for a
>> > > particuar product.
>> > >
>> > > Thanks for your help by the way. Much appreciated.
>> > >
>> > > ----------------------------------------------------------------------
>> > > CREATE TABLE [stock] (
>> > > [productid] [int] NULL ,
>> > > [stocklevel] [int] NULL ,
>> > > [date] [datetime] NULL
>> > > ) ON [PRIMARY]
>> > > GO
>> > >
>> > >
>> > > insert into stock(productid ,stocklevel ,date ) values
>> > > (1,10,'1/1/2006')
>> > > insert into stock(productid ,stocklevel ,date ) values
>> > > (1,0,'2/1/2006')
>> > > insert into stock(productid ,stocklevel ,date ) values
>> > > (1,0,'3/1/2006')
>> > > insert into stock(productid ,stocklevel ,date ) values
>> > > (1,100,'4/1/2006')
>> > > insert into stock(productid ,stocklevel ,date ) values
>> > > (2,0,'1/1/2006')
>> > > insert into stock(productid ,stocklevel ,date ) values
>> > > (2,0,'2/1/2006')
>> > > insert into stock(productid ,stocklevel ,date ) values
>> > > (2,10,'3/1/2006')
>> > >
>> > > ML wrote:
>> > > > Perhaps a covering index on (date, productid, stocklevel). Just
>> > > > judging from
>> > > > this one query.
>> > > >
>> > > > But - does the query work as it's supposed to?
>> > > >
>> > > >
>> > > > ML
>> > > >
>> > > > ---
>> > > > http://milambda.blogspot.com/
>> >
>> >

8 Sep 2006 4:33 PM

Sha Anand

He wants the following results

1    10    2006-01-01 00:00:00.000    NULL
1    0    2006-02-01 00:00:00.000    2006-02-01 00:00:00.000
1    0    2006-03-01 00:00:00.000    2006-02-01 00:00:00.000
1    100    2006-04-01 00:00:00.000    NULL
1    0    2006-05-01 00:00:00.000    2006-05-01 00:00:00.000
2    0    2006-01-01 00:00:00.000    2006-01-01 00:00:00.000
2    0    2006-02-01 00:00:00.000    2006-01-01 00:00:00.000
2    10    2006-03-01 00:00:00.000    NULL

But your query returns just 2 rows

1    2006-02-01 00:00:00.000    2006-05-01 00:00:00.000
2    2006-01-01 00:00:00.000    2006-02-01 00:00:00.000

- Sha Anand

Show quote

"Hilary Cotter" wrote:

> Your method has 12 logical reads, mine has 2. I suspect mine offers better
> performance:)
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
>
>
> "Sha Anand" <ShaAn***@discussions.microsoft.com> wrote in message
> news:7A96E73D-65A0-414E-9659-04AE76F5FD4C@microsoft.com...
> > small correction... use the following Query
> >
> > select s1.ProductId,s1.stocklevel,s1.[date],
> > (select min(s2.[date]) from stock s2
> > where
> > s1.ProductId = s2.ProductId and
> > s2.[date] <= s1.[date] and
> > s2.stocklevel =0 and s1.stocklevel = 0 and
> > s2.[date] >=
> > ISNULL((select max(s3.[date]) from stock s3 where s3.[date] < s1.[date]
> > and
> > s3.stocklevel <> 0 ),s1.[date])
> > )
> > from stock s1
> > order by 1,3
> >
> > - Sha Anand
> >
> > "Sha Anand" wrote:
> >
> >> You can try this.....
> >>
> >> select s1.ProductId,s1.stocklevel,s1.date1,
> >> (select min(s2.date1) from stock s2
> >> where
> >> s1.ProductId = s2.ProductId and
> >> s2.date1 <= s1.date1 and
> >> s2.stocklevel =0 and s1.stocklevel = 0 and
> >> s2.date1 >=
> >> (select max(s3.date1) from stock s3 where s3.date1 <= s1.date1 and
> >> s3.stocklevel <> 0 )
> >> )
> >> from stock s1
> >> order by 1,3
> >>
> >> - Sha Anand
> >>
> >> "kis***@microlytics.co.uk" wrote:
> >>
> >> > Hi ML.
> >> >
> >> > Just modified your original sql by adding the productid constraint in
> >> > the where clause of the select statement. SQL below does the trick.
> >> >
> >> > Thanks again for valuable time and input.
> >> >
> >> >
> >> > select currStock.productid
> >> >          ,currStock.stocklevel
> >> >          ,currStock.[date]
> >> >          ,lastdateoutofstock
> >> >                 = case
> >> >                      when exists(
> >> >                                       select *
> >> >                                                 from stock prevStock
> >> >                                                 where
> >> > (prevStock.stocklevel
> >> > = 0)
> >> >                                                           and
> >> > (prevStock.[date] <= currStock.[date]) and (prevstock.productid =
> >> > currstock.productid)and
> >> > (currstock.stocklevel =0)
> >> >                                       )
> >> >                               then (
> >> >                                       select top 1 prevStock.[date]
> >> >                                                 from stock prevStock
> >> >                                                 where
> >> > (prevStock.stocklevel
> >> > = 0)
> >> >                                                           and
> >> > (prevStock.[date] <= currStock.[date])
> >> > and (prevstock.productid = currstock.productid)
> >> >                                                 order by
> >> > prevStock.[date] Asc
> >> >                                      )
> >> >                               end
> >> >          from stock currStock
> >> >
> >> >
> >> >
> >> > kis***@microlytics.co.uk wrote:
> >> > > Hi ML,
> >> > >
> >> > > I've just tested it on the large table but got back incorrect
> >> > > results.
> >> > > I then tested the SQL on a sample data (DDL below for your
> >> > > information)
> >> > >
> >> > > It appears that lastdayoutofstock is defaulting on the first date in
> >> > > the table rather than getting the true last date out of stock for a
> >> > > particuar product.
> >> > >
> >> > > Thanks for your help by the way. Much appreciated.
> >> > >
> >> > > ----------------------------------------------------------------------
> >> > > CREATE TABLE [stock] (
> >> > > [productid] [int] NULL ,
> >> > > [stocklevel] [int] NULL ,
> >> > > [date] [datetime] NULL
> >> > > ) ON [PRIMARY]
> >> > > GO
> >> > >
> >> > >
> >> > > insert into stock(productid ,stocklevel ,date ) values
> >> > > (1,10,'1/1/2006')
> >> > > insert into stock(productid ,stocklevel ,date ) values
> >> > > (1,0,'2/1/2006')
> >> > > insert into stock(productid ,stocklevel ,date ) values
> >> > > (1,0,'3/1/2006')
> >> > > insert into stock(productid ,stocklevel ,date ) values
> >> > > (1,100,'4/1/2006')
> >> > > insert into stock(productid ,stocklevel ,date ) values
> >> > > (2,0,'1/1/2006')
> >> > > insert into stock(productid ,stocklevel ,date ) values
> >> > > (2,0,'2/1/2006')
> >> > > insert into stock(productid ,stocklevel ,date ) values
> >> > > (2,10,'3/1/2006')
> >> > >
> >> > > ML wrote:
> >> > > > Perhaps a covering index on (date, productid, stocklevel). Just
> >> > > > judging from
> >> > > > this one query.
> >> > > >
> >> > > > But - does the query work as it's supposed to?
> >> > > >
> >> > > >
> >> > > > ML
> >> > > >
> >> > > > ---
> >> > > > http://milambda.blogspot.com/
> >> >
> >> >
>
>
>

11 Sep 2006 10:07 AM

kishor

Thanks Sha,

The query returns the right results based on the test data. I am now
running the query on a large table (around 50 Mill records) and it's
taking a while. I have got one index on productid, stock and date.
Will wait and see what the execution plan says

Sha Anand wrote:
Show quote

> He wants the following results
>
> 1    10    2006-01-01 00:00:00.000    NULL
> 1    0    2006-02-01 00:00:00.000    2006-02-01 00:00:00.000
> 1    0    2006-03-01 00:00:00.000    2006-02-01 00:00:00.000
> 1    100    2006-04-01 00:00:00.000    NULL
> 1    0    2006-05-01 00:00:00.000    2006-05-01 00:00:00.000
> 2    0    2006-01-01 00:00:00.000    2006-01-01 00:00:00.000
> 2    0    2006-02-01 00:00:00.000    2006-01-01 00:00:00.000
> 2    10    2006-03-01 00:00:00.000    NULL
>
> But your query returns just 2 rows
>
> 1    2006-02-01 00:00:00.000    2006-05-01 00:00:00.000
> 2    2006-01-01 00:00:00.000    2006-02-01 00:00:00.000
>
> - Sha Anand
>
> "Hilary Cotter" wrote:
>
> > Your method has 12 logical reads, mine has 2. I suspect mine offers better
> > performance:)
> >
> > --
> > Hilary Cotter
> > Director of Text Mining and Database Strategy
> > RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> >
> > This posting is my own and doesn't necessarily represent RelevantNoise's
> > positions, strategies or opinions.
> >
> > Looking for a SQL Server replication book?
> > http://www.nwsu.com/0974973602.html
> >
> > Looking for a FAQ on Indexing Services/SQL FTS
> > http://www.indexserverfaq.com
> >
> >
> >
> > "Sha Anand" <ShaAn***@discussions.microsoft.com> wrote in message
> > news:7A96E73D-65A0-414E-9659-04AE76F5FD4C@microsoft.com...
> > > small correction... use the following Query
> > >
> > > select s1.ProductId,s1.stocklevel,s1.[date],
> > > (select min(s2.[date]) from stock s2
> > > where
> > > s1.ProductId = s2.ProductId and
> > > s2.[date] <= s1.[date] and
> > > s2.stocklevel =0 and s1.stocklevel = 0 and
> > > s2.[date] >=
> > > ISNULL((select max(s3.[date]) from stock s3 where s3.[date] < s1.[date]
> > > and
> > > s3.stocklevel <> 0 ),s1.[date])
> > > )
> > > from stock s1
> > > order by 1,3
> > >
> > > - Sha Anand
> > >
> > > "Sha Anand" wrote:
> > >
> > >> You can try this.....
> > >>
> > >> select s1.ProductId,s1.stocklevel,s1.date1,
> > >> (select min(s2.date1) from stock s2
> > >> where
> > >> s1.ProductId = s2.ProductId and
> > >> s2.date1 <= s1.date1 and
> > >> s2.stocklevel =0 and s1.stocklevel = 0 and
> > >> s2.date1 >=
> > >> (select max(s3.date1) from stock s3 where s3.date1 <= s1.date1 and
> > >> s3.stocklevel <> 0 )
> > >> )
> > >> from stock s1
> > >> order by 1,3
> > >>
> > >> - Sha Anand
> > >>
> > >> "kis***@microlytics.co.uk" wrote:
> > >>
> > >> > Hi ML.
> > >> >
> > >> > Just modified your original sql by adding the productid constraint in
> > >> > the where clause of the select statement. SQL below does the trick.
> > >> >
> > >> > Thanks again for valuable time and input.
> > >> >
> > >> >
> > >> > select currStock.productid
> > >> >          ,currStock.stocklevel
> > >> >          ,currStock.[date]
> > >> >          ,lastdateoutofstock
> > >> >                 = case
> > >> >                      when exists(
> > >> >                                       select *
> > >> >                                                 from stock prevStock
> > >> >                                                 where
> > >> > (prevStock.stocklevel
> > >> > = 0)
> > >> >                                                           and
> > >> > (prevStock.[date] <= currStock.[date]) and (prevstock.productid =
> > >> > currstock.productid)and
> > >> > (currstock.stocklevel =0)
> > >> >                                       )
> > >> >                               then (
> > >> >                                       select top 1 prevStock.[date]
> > >> >                                                 from stock prevStock
> > >> >                                                 where
> > >> > (prevStock.stocklevel
> > >> > = 0)
> > >> >                                                           and
> > >> > (prevStock.[date] <= currStock.[date])
> > >> > and (prevstock.productid = currstock.productid)
> > >> >                                                 order by
> > >> > prevStock.[date] Asc
> > >> >                                      )
> > >> >                               end
> > >> >          from stock currStock
> > >> >
> > >> >
> > >> >
> > >> > kis***@microlytics.co.uk wrote:
> > >> > > Hi ML,
> > >> > >
> > >> > > I've just tested it on the large table but got back incorrect
> > >> > > results.
> > >> > > I then tested the SQL on a sample data (DDL below for your
> > >> > > information)
> > >> > >
> > >> > > It appears that lastdayoutofstock is defaulting on the first date in
> > >> > > the table rather than getting the true last date out of stock for a
> > >> > > particuar product.
> > >> > >
> > >> > > Thanks for your help by the way. Much appreciated.
> > >> > >
> > >> > > ----------------------------------------------------------------------
> > >> > > CREATE TABLE [stock] (
> > >> > > [productid] [int] NULL ,
> > >> > > [stocklevel] [int] NULL ,
> > >> > > [date] [datetime] NULL
> > >> > > ) ON [PRIMARY]
> > >> > > GO
> > >> > >
> > >> > >
> > >> > > insert into stock(productid ,stocklevel ,date ) values
> > >> > > (1,10,'1/1/2006')
> > >> > > insert into stock(productid ,stocklevel ,date ) values
> > >> > > (1,0,'2/1/2006')
> > >> > > insert into stock(productid ,stocklevel ,date ) values
> > >> > > (1,0,'3/1/2006')
> > >> > > insert into stock(productid ,stocklevel ,date ) values
> > >> > > (1,100,'4/1/2006')
> > >> > > insert into stock(productid ,stocklevel ,date ) values
> > >> > > (2,0,'1/1/2006')
> > >> > > insert into stock(productid ,stocklevel ,date ) values
> > >> > > (2,0,'2/1/2006')
> > >> > > insert into stock(productid ,stocklevel ,date ) values
> > >> > > (2,10,'3/1/2006')
> > >> > >
> > >> > > ML wrote:
> > >> > > > Perhaps a covering index on (date, productid, stocklevel). Just
> > >> > > > judging from
> > >> > > > this one query.
> > >> > > >
> > >> > > > But - does the query work as it's supposed to?
> > >> > > >
> > >> > > >
> > >> > > > ML
> > >> > > >
> > >> > > > ---
> > >> > > > http://milambda.blogspot.com/
> > >> >
> > >> >
> >
> >
> >

8 Sep 2006 1:25 PM

Hilary Cotter

what about this

select stuff.productid, lastdayoutofstock=min(date),
Firstdayoutofstock=max(date)
from stuff join
(select productid, sl=min(stocklevel) from stuff group by productid) as k
on k.productid=stuff.productid and k.sl=stuff.stocklevel
where stocklevel=0
group by stuff.productid

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

<kis***@microlytics.co.uk> wrote in message

Show quote

news:1157720744.972933.55720@m79g2000cwm.googlegroups.com...
> Hi ML,
>
> I've just tested it on the large table but got back incorrect results.
> I then tested the SQL on a sample data (DDL below for your information)
>
> It appears that lastdayoutofstock is defaulting on the first date in
> the table rather than getting the true last date out of stock for a
> particuar product.
>
> Thanks for your help by the way. Much appreciated.
>
> ----------------------------------------------------------------------
> CREATE TABLE [stock] (
> [productid] [int] NULL ,
> [stocklevel] [int] NULL ,
> [date] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
>
> insert into stock(productid ,stocklevel ,date ) values
> (1,10,'1/1/2006')
> insert into stock(productid ,stocklevel ,date ) values
> (1,0,'2/1/2006')
> insert into stock(productid ,stocklevel ,date ) values
> (1,0,'3/1/2006')
> insert into stock(productid ,stocklevel ,date ) values
> (1,100,'4/1/2006')
> insert into stock(productid ,stocklevel ,date ) values
> (2,0,'1/1/2006')
> insert into stock(productid ,stocklevel ,date ) values
> (2,0,'2/1/2006')
> insert into stock(productid ,stocklevel ,date ) values
> (2,10,'3/1/2006')
>
> ML wrote:
>> Perhaps a covering index on (date, productid, stocklevel). Just judging
>> from
>> this one query.
>>
>> But - does the query work as it's supposed to?
>>
>>
>> ML
>>
>> ---
>> http://milambda.blogspot.com/
>

8 Sep 2006 11:35 AM

Hilary Cotter

try something like this:

create table stuff(productid int,stocklevel int,date datetime)
GO
insert into stuff(productid ,stocklevel ,date ) values
(1,10,'1/1/2006')
insert into stuff(productid ,stocklevel ,date ) values
(1,0,'2/1/2006')
insert into stuff(productid ,stocklevel ,date ) values
(1,0,'3/1/2006')
insert into stuff(productid ,stocklevel ,date ) values
(1,100,'3/1/2006')
insert into stuff(productid ,stocklevel ,date ) values
(2,0,'1/1/2006')
insert into stuff(productid ,stocklevel ,date ) values
(2,0,'2/1/2006')
insert into stuff(productid ,stocklevel ,date ) values
(2,10,'3/1/2006')

select stuff.productid, min(date) from stuff join
(select productid, sl=min(stocklevel) from stuff group by productid) as k
on k.productid=stuff.productid and k.sl=stuff.stocklevel
where stocklevel=0
group by stuff.productid

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

<kis***@microlytics.co.uk> wrote in message

Show quote

news:1157712615.660772.281190@b28g2000cwb.googlegroups.com...
> Hi,
>
> I need to get the last date of when stock levels hit zero so I can then
> do a datediff to calcualte days out of stock. Here is a table with
> sample data
>
> productid,stocklevel,date
> 1,10,1/1/2006
> 1,0,2/1/2006
> 1,0,3/1/2006
> 1,100,3/1/2006
> 2,0,1/1/2006
> 2,0,2/1/2006
> 2,10,3/1/2006
>
> Desired result
>
> productid,stocklevel,date,lastdateoutofstock
> 1,10,1/1/2006,null
> 1,0,2/1/2006,2/1/2006
> 1,0,3/1/2006 ,2/1/2006
> 1,100,3/1/2006,NULL
> 1,0,4/1/2006,4/1/2006
> 2,0,1/1/2006 ,1/1/2006
> 2,0,2/1/2006 ,1/1/2006
> 2,10,3/1/2006,NULL
>
> Currently I use a cursor to do this but it is taking too long. Hoping
> it can be done using Set based T-SQL.
>
> Thanks
>

8 Sep 2006 2:37 PM

sloan

Maybe this example can help your situation.

-- START TSQL CODE

set nocount on
declare @CourseCompletions table ( EmpID int , CourseID int ,
CompletionDate datetime ,   Credits decimal(10,2) )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (101 , 1 , '1/1/2004' , 11.1    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (101 , 2 , '1/1/2004' , 22.2    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (101 , 1 , '1/1/2005' , 11.1    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (101 , 2 , '1/1/2005' , 22.2    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (101 , 1 , '1/1/2006' , 1.11    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (101 , 2 , '1/1/2006' , 2.22   )

insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (102 , 1 , '3/1/2004' , 33.3    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (102 , 2 , '3/1/2004' , 44.4    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (102 , 1 , '3/1/2005' , 33.3    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (102 , 2 , '3/1/2005' , 44.4    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (102 , 1 , '3/1/2006' , 3.33    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (102 , 2 , '3/1/2006' , 4.44    )

insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (103 , 1 , '6/1/2003' , 5.55    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (103 , 2 , '6/1/2003' , 7.77    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (103 , 1 , '6/1/2004' , 55.5    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (103 , 2 , '6/1/2004' , 77.7   )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (103 , 1 , '6/1/2005' , 55.5    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (103 , 2 , '6/1/2005' , 77.7    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (103 , 1 , '6/1/2006' , 5.55    )
insert into @CourseCompletions (EmpID , CourseID , CompletionDate ,
redits ) values (103 , 2 , '6/1/2006' , 7.77   )
--select * from @CourseCompletions

SELECT MAX(CompletionDate) FROM @CourseCompletions WHERE EmpID = 101 and
CourseID = 1
--This returns '2006-01-01', which is obviously the latest date for this
Emp. Can I use the same technique to find the Credits?

SELECT MAX(Credits) FROM @CourseCompletions WHERE EmpID = 101 and CourseID =
1
--This returns 11.10, which was the Credits in '1/1/2004', but NOT the
Credits for the Max(CompletionDate).... we can clearly see that a new, lower
Credits went into effect on 6/1/2006. We didn't get the correct Credits by
using MAX. How can we find the Credits that corresponds to that MAX date? We
have to join to a subquery, as follows:

SELECT
   CreditsList.EmpID ,
   CreditsList.CourseID,
   CreditsList.CompletionDate as MostRecentCompletionDate,
   CreditsList.Credits
FROM @CourseCompletions AS CreditsList
INNER JOIN
   (
SELECT
top 3
    EmpID ,
    CourseID,
          MAX(CompletionDate) AS LastDate
       FROM
@CourseCompletions
       GROUP BY
EmpID , CourseID
   ) AS LastDatesAsADerivedTable
   ON
CreditsList.EmpID = LastDatesAsADerivedTable.EmpID
AND
CreditsList.CourseID = LastDatesAsADerivedTable.CourseID
AND
CreditsList.CompletionDate = LastDatesAsADerivedTable.LastDate

--WHERE CreditsList.EmpID = 101
ORDER BY
CreditsList.EmpID , CreditsList.CourseID

<kis***@microlytics.co.uk> wrote in message
Show quote

news:1157712615.660772.281190@b28g2000cwb.googlegroups.com...
> Hi,
>
> I need to get the last date of when stock levels hit zero so I can then
> do a datediff to calcualte days out of stock. Here is a table with
> sample data
>
> productid,stocklevel,date
> 1,10,1/1/2006
> 1,0,2/1/2006
> 1,0,3/1/2006
> 1,100,3/1/2006
> 2,0,1/1/2006
> 2,0,2/1/2006
> 2,10,3/1/2006
>
> Desired result
>
> productid,stocklevel,date,lastdateoutofstock
> 1,10,1/1/2006,null
> 1,0,2/1/2006,2/1/2006
> 1,0,3/1/2006 ,2/1/2006
> 1,100,3/1/2006,NULL
> 1,0,4/1/2006,4/1/2006
> 2,0,1/1/2006 ,1/1/2006
> 2,0,2/1/2006 ,1/1/2006
> 2,10,3/1/2006,NULL
>
> Currently I use a cursor to do this but it is taking too long. Hoping
> it can be done using Set based T-SQL.
>
> Thanks
>