Calculating the status duration

Question

Calculating the status duration

sql_2005_fan

SSCommitted

Points: 1871
More actions
January 2, 2016 at 6:25 am

#323692

Hi,
I have a field in my table called status and this field can have status active,inactive,hold,deleted and withdrawn and a product can have any of these statuses and there will be a timestamp associated with this status.
I need to write a query for a product to calculate how long this product is in each status.
But only for the inactive status I need to accumulate this number if this is inactive for more than 7 days then I need to add those days to the status. If this product is inactive for 6 days the count should be 0 if this is inactive for 7 days then the count should be 1 if this inactive for 8 days count should be 2.
How can I write this query?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

This sounds like a pretty complex requirement. Thus it is even more important than usual for you to give us sample table(s), sample data and expected output. You need to be sure to cover ALL of your various logic requirements with the sample data and sample output. The data should be in the form of INSERT statements into your table(s).

Help us help you!! 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

TheSQLGuru (1/2/2016)
This sounds like a pretty complex requirement. Thus it is even more important than usual for you to give us sample table(s), sample data and expected output. You need to be sure to cover ALL of your various logic requirements with the sample data and sample output. The data should be in the form of INSERT statements into your table(s).
Help us help you!! 😀

I'll echo what Kevin said about DDL, sample data and expected output. I hope you have a table that tracks the changes in status of each product.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

This could actually be a pretty simple thing to solve using either a self-joined re-enumerated table or the difference between two ROW_NUMBER()s. My recommendation is that you study the article at the first link in my signature line below (under "Helpful Links"). It might seem like a PITA to do but it will be incredibly helpful in us understanding your problem without wasting a whole lot of time going down lost-leader paths. Thanks for helping us help you on this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Jeff Moden (1/2/2016)
This could actually be a pretty simple thing to solve using either a self-joined re-enumerated table or the difference between two ROW_NUMBER()s. My recommendation is that you study the article at the first link in my signature line below (under "Helpful Links"). It might seem like a PITA to do but it will be incredibly helpful in us understanding your problem without wasting a whole lot of time going down lost-leader paths. Thanks for helping us help you on this.

My guess is that 2012 Windowing Functions will be best too, although I was thinking LAG/LEAD possibly. But that is just a guess for now until we have some sample details.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service