TSQL Duration question 2

[kermitforney]

I have a query I have been working on that I am trying to code to calculate duration of time. Need a bit of help and any and all wil be appreciated.

Ex.

UID|Date|Toy|Out of Stock

001|1/1/2009|Balls|Yes
002|1/3/2009|Balls|Yes
003|1/5/2009|Balls|Yes
004|1/2/2009|Straws|Yes
005|1/3/2009|Straws|No
006|1/4/2009|Straws|Yes

So, what I need is for the query to filter via out of stock (which I already understand), but return only sets of data with consecutive dates of out of stock items.

Ex.

001|1/1/2009|Balls|Yes
002|1/3/2009|Balls|Yes
003|1/5/2009|Balls|Yes

Balls| Out of stock from 1/1/2009 through 1/5/2009.

Ex.

004|1/2/2009|Straws|Yes
005|1/3/2009|Straws|No
006|1/4/2009|Straws|Yes

Straws do not qualify because their out of stock status was not consecutive.


I hope I explained this thouroughly.

Thanks in advance for any/all responses.

 

[bborissov]

You want this for some period, right?

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '20090101'
SET @EndDate   = '20090201'

SELECT YourTable.*
FROM YourTable
LEFT JOIN YourTable Tbl1 ON YourTable.Toy = Tbl1.Toy AND
                            Tbl1.OutofStock = 'No'   AND
                            Tbl1.Date >= @StartDate AND
                            Tbl1.Date <  @EndDate
WHERE YourTable.Date >= @StartDate AND
      YourTable.Date <  @EndDate   AND
      Tbl1.Uid IS NULL

NOT TESTED!!!!



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Usually, in a case like this, my advice is to use a "self join". This really is no different than any other type of join, but instead of joining two (or more) tables, you join the table back to itself. There are a couple of rules that you must follow, but really, it's nothing magical.

Now, for your particular example, I see that you have a UID column that appears to be a varchar, but contains a sequential number, AND the Date appears to be in order too. If this holds true for your data, then you can use a self join.

Basically, you join back to the same table on toy AND uid of on row is 1 less than uid of the next row.

When you use a self join, you MUST use table aliases. Everything else is pretty self explanatory. Take a look at the following example. It's not exactly what you are looking for, but it should be a good start.

Declare @Temp Table(Uid Int, Date DateTime, Toy VarChar(20), OutOfStock VarChar(20))

Insert Into @Temp Values(001,'1/1/2009','Balls','Yes')
Insert Into @Temp Values(002,'1/3/2009','Balls','Yes')
Insert Into @Temp Values(003,'1/5/2009','Balls','Yes')
Insert Into @Temp Values(004,'1/2/2009','Straws','Yes')
Insert Into @Temp Values(005,'1/3/2009','Straws','No')
Insert Into @Temp Values(006,'1/4/2009','Straws','Yes')

Select * 
From   @Temp As A
       Inner Join @Temp As B
         On A.Toy = B.Toy
         And A.UID = B.UID - 1
Where	A.OutOfStock = 'Yes'
        And B.OutOfStock = 'Yes'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom