Query to find out how many days on the record?
I have a ticket table, it's used as history record. Each item# has only one line, qty can be up and down.
But if no problem been found on that day it become 0. If same item# keep show up on the list every day, it means a more serious problem and I have to identify how many days it stay there.
table structure as
item# qty ID date
A001 4 qc12 20070510
A003 3 qc03 20070510
......
A001 5 qc12 20070511
A004 2 qc11 20070511
.....
A001 happened two days in a row, and will be pick up to show on a report.
I need to create a query to find out for how many days same item# been stay in the list continuously. ie
create a report to show how many items are on the list more than 2 days in a row and how many days it stay on the list. If it has become 0 then that item will will not show on the list and restart the counter again. Hope I make it clear enough. Thanks for help.
But if no problem been found on that day it become 0. If same item# keep show up on the list every day, it means a more serious problem and I have to identify how many days it stay there.
table structure as
item# qty ID date
A001 4 qc12 20070510
A003 3 qc03 20070510
......
A001 5 qc12 20070511
A004 2 qc11 20070511
.....
A001 happened two days in a row, and will be pick up to show on a report.
I need to create a query to find out for how many days same item# been stay in the list continuously. ie
create a report to show how many items are on the list more than 2 days in a row and how many days it stay on the list. If it has become 0 then that item will will not show on the list and restart the counter again. Hope I make it clear enough. Thanks for help.
ASKER
Sorry I did not make it clear, each day each item# has one line. thansk
considering you want to reset your counter if there is a "break" (i.e 0 count on a particular day), i think you would have to loop through your ticket table (in a code module) and insert into a new table which holds your counter. let me know if this is a valid solution.
if you try to do this as one massive query it would be a headache to try to debug.
if you try to do this as one massive query it would be a headache to try to debug.
ASKER
Yes, I have control over the db so create new or temp table would be no problem.
select A.*,datediff(d,min(b.date)
from Yourtable as A
Inner Join Yourtable as B
on a.[item#]=b.[item#]
and A.date > B.date
Where A.Date = convert(char(8),getdate(),
and exists (select [item#] from yourtable as x
where a.[item#]=x.[item#]
and convert(datetime,x.date) between convert(datetime,b.date) and convert(datetime,a.date)
group by x.[item#]
having count(*) = datediff(d,a.date,b.date)
)
group by a.[item#],a.qty,a.id,a.dat
order by 1
from Yourtable as A
Inner Join Yourtable as B
on a.[item#]=b.[item#]
and A.date > B.date
Where A.Date = convert(char(8),getdate(),
and exists (select [item#] from yourtable as x
where a.[item#]=x.[item#]
and convert(datetime,x.date) between convert(datetime,b.date) and convert(datetime,a.date)
group by x.[item#]
having count(*) = datediff(d,a.date,b.date)
)
group by a.[item#],a.qty,a.id,a.dat
order by 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow hth, you are just great. this is almost there, can I add a little bit more... no record for Sat and Sun, but Friday and Monday still count as contiguous. Appreciate your help.
ASKER
Hello hth, I think I can get around of it, I will make automatic copy from Friday as Sat and Sun. This way above logic can work smoothly. Do you think so?
has record
A001 5 qc12 20070511
overwritten record
A001 4 qc12 20070510
or are they both in this table?
if "Each item# has only one line, qty can be up and down" then your table is not a historical table but rather a "daily snapshot" type table. Or do you mean each itme # has one record per day (date)?