June 13, 2011 at 11:50 am
I have the following table (OFF_WELL_DATE is DATETIME datatype; OFF_WELL is int and has the number of hours a well has been turned off):
WELL_IDOFF_WELL_DATEOFF_WELL
w111-05-01 5
w111-05-02 2
w111-05-10 4
w211-05-01 7
w211-05-05 5
w311-05-01 9
w311-05-07 2
w311-05-08 4
I need a query which displays the total time a well has been turned off every day, including the dates that the well has been turned on along the month (in which case OFF_WELL would be 0, because the well worked all day long), like this:
WELL_IDDATEOFF_WELL
w111-05-015
w111-05-022
w111-05-030
w1…0
w111-05-104
w1…0
w111-05-310
w211-05-017
w2…0
w211-05-055
w2…0
w211-05-310
w311-05-019
w3…0
w311-05-072
w311-05-084
w3…0
w311-05-310
Any help will be highly appreciated,
Marco
June 13, 2011 at 1:00 pm
The best way to deal with these types of problems is with a calendar table. I would advise you to create a permanent one for future use, but the example below creates one dynamically using CTEs. It cross joins the set of all dates to the set of all well_ids in your table, then left joins to the actual data using the well_ID/date combination. Where no match is found, the ISNULL() function returns a value of zero. Let me know if you have any questions.
declare @wells table (well_ID char(2), off_well_date date, off_well int)
insert into @wells
select 'w1',' 2011-05-01', 5 union all
select 'w1',' 2011-05-02', 2 union all
select 'w1',' 2011-05-10', 4 union all
select 'w2',' 2011-05-01', 7 union all
select 'w2',' 2011-05-05', 5 union all
select 'w3',' 2011-05-01', 9 union all
select 'w3',' 2011-05-07', 2 union all
select 'w3','2011-05-08', 4
;with tally (N) as (select row_number() over (order by id) from sysobjects)
,calendar (calendarDate) as (select dateadd(day,N-1,cast('2011-01-01' as date)) from tally )
,well_ranges (well_ID,mindate,maxdate)as (select well_ID, min(off_well_date), max(off_well_date) from @wells group by well_ID)
select w1.well_ID,calendarDate as date_off, ISNULL(off_well,0) as off_well
from calendar c
cross join well_ranges w1
left join @wells w2 on w2.off_well_date = c.calendarDate and w2.well_ID = w1.well_ID
where calendarDate between mindate and maxdate
order by well_ID,date_off
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply