Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

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.
0
tomcattyy
Asked:
tomcattyy
  • 4
  • 2
  • 2
1 Solution
 
frankyteeCommented:
i'm bit confused,
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)?
0
 
tomcattyyAuthor Commented:
Sorry I did not make it clear, each day each item# has one line. thansk
0
 
frankyteeCommented:
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.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
tomcattyyAuthor Commented:
Yes, I have control over the db so create new or temp table would be no problem.
0
 
LowfatspreadCommented:
select A.*,datediff(d,min(b.date)a.date) as nodays
  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(),113)
   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.date            
 order by 1      
0
 
LowfatspreadCommented:
i assume your date are char... (bad idea!)

convert(char(8),getdate(),113) gets today as YYYYMMDD

self join table
look for an item on today...
joined to all prior rows for item...

ensure that the join rows are contiguous with today...

hth
0
 
tomcattyyAuthor Commented:
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.
0
 
tomcattyyAuthor Commented:
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?
 
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now