need expert help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

need expert help

Author

Topic

anaji
Starting Member

15 Posts

Posted - 2011-10-20 : 01:13:37

dear experts,

i would like to know how to get the follwing, i have for example a table that contains Name, date , status, the status may be 0 or 1 , when it's 0 that means it's off, how can i query to get the name that is 0 and show for how many days it has been 0, this table is for wells, so if the well status is 0 that means it's been down and when it goes 1 then it's up again, so how can i get only the downtime which will be the number of days ? i know i might not be so clear so please let me know if more clarification is required.

anaji
Starting Member

15 Posts

Posted - 2011-10-20 : 02:25:20

to explain more these r the colums are ( well_name, date, status) (w1, 2011-09-01, 0) then (W1,2011-09-02,1) that means well was down on 09-01 and was back on 09-02, what is needed is a query to get the total number of days that this well was down in the month of 09. hope this is more clear

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 04:20:34

do like


select well_name,sum(datediff(dd,t.date,t1.date)) as daysoff 
from Table t
cross apply (select top 1 date
             from Table
             where well_name = t,well_name
             and status=1
             and date > t.date
             order by t.date asc)t1 
where status = 0
GROUP BY wellname

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-20 : 04:52:36

quote:

Originally posted by visakh16

do like


select well_name,sum(datediff(dd,t.date,t1.date)) as daysoff 
from Table t
cross apply (select top 1 date
             from Table
             where well_name = t,.well_name
             and status=1
             and date > t.date
             order by t.date asc)t1 
where status = 0
GROUP BY wellname

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 05:36:37

quote:
Originally posted by webfred

quote:
Originally posted by visakh16

do like

select well_name,sum(datediff(dd,t.date,t1.date)) as daysoff
from Table t
cross apply (select top 1 date
from Table
where well_name = t,.well_name
and status=1
and date > t.date
order by t.date asc)t1
where status = 0
GROUP BY wellname

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anaji
Starting Member

15 Posts

Posted - 2011-10-20 : 08:56:21

thank you for your reply i will give it a try and let you know, but just for clarification the part that's in red it's t-,well_name? or what exactly thanx again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 09:21:26

it should be t.well_name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anaji
Starting Member

15 Posts

Posted - 2011-10-23 : 03:35:49

thanks it works like a charm, the output is the downtime in date firrnce, now lets say i want to show the exact date next to every well that had status 0, ( w1 down on 2011-08-03, and w3 was down on 2011-08-04) etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-23 : 10:58:50

[code]
select well_name,min(t.date) as offdate,sum(datediff(dd,t.date,t1.date)) as daysoff
from Table t
cross apply (select top 1 date
from Table
where well_name = t,.well_name
and status=1
and date > t.date
order by t.date asc)t1
where status = 0
GROUP BY wellname
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources