May 22, 2011 at 4:17 am
I need to retrive data based on a 24/7 shift pattern. I want to look at the shift dates and get energy used by subtracting max(energy from min(energy). What I cannot do is work out how to sort out the dates. I think I need to use the modulus function as the shift pattern repeats every 4 weeks so I built a query that will give me a list of dates and times for the search period. to then break the date into shift times would be easy BUT if the shift starts on a Sunday and goes overnight into Monday morning the original query only looks at one week out of four so the Monday of the next week is not "looked at". Below is the query that return dates for the start and end:
select distinct dateadd(hh,19,dateadd(dd, datediff(dd, 0, t_stamp),0))as start, dateadd(hh,31,dateadd(dd, datediff(dd, 0, t_stamp),0)) as finish
from table_2
where datediff(wk,0,t_stamp)%4=3 and datename(dw,t_stamp)= 'Sunday'
This returns a table of start dates and end dates going from 7pm to 7am the next morning.
I dont know if this even remotely the correct way but if it is - does the result of the query above have to used as a subquery? I tried doing this but it says "a subquery returned more than one result" which of course it does.
Can anyone one help regarding how to achieve what I am after? Even if I can get the above to work how I see it in my heard then the final query will still have to carry out 4 sub queries each one having different modulus wk equation, 0,1,2,3 each with different days and shift times.
May 22, 2011 at 10:45 am
I would probably use a calendar table and a second table to describe the shift pattern.
May 24, 2011 at 10:32 am
I came up with a method that I believe works but if anyone can tell me a better or simplier way, or critisism, I would appreciate it. I have not idea how to use calendar tables but basically my method constructs a table of start and end dates relevant to the 24/7 shift rota based around the modulus of 4 of the week (datepart(wk,t_stamp)(4 week pattern) and time period requested (in the example I use getdate and -200, will be calendar driven from SCADA system). A "select from" query then uses one "start" and respective "finish" date at a time and runs as many times as there are pairs of start/stop in the temp #calendar table. The results then get put into one final table, consisting in my case of total kwh used in each period with the start and end dates the data came from. It may not be pretty but on the other hand it may, just may, help a complete NooB like me so here it is:
--24/7 Team 3
if object_id('tempdb..#Calendar') IS NOT NULL
begin
drop table #Calendar
end
if object_id('tempdb..#Results') IS NOT NULL
begin
drop table #Results
end
create table #Calendar(Rownumber int IDENTITY,"From" datetime, "Until" datetime)
create table #Results(Rownumber int IDENTITY, kwh int)
declare @current_Row int
set @current_Row = 1
declare @no_Rows int
insert into #Calendar ("From","Until") select start, finish from
(select distinct dateadd(hh,7,dateadd(dd,datediff(dd,0,t_stamp),0)) as start,dateadd(hh,19,dateadd(dd,datediff(dd,0,t_stamp),0)) as finish
from "AP7/Energy" where
datepart(wk,t_stamp)%4=0 and datename (dw,t_stamp) = 'Monday' and t_stamp between dateadd(dd,-200,getdate()) and getdate()
or datepart(wk,t_stamp)%4=0 and datename (dw,t_stamp) ='Tuesday' and t_stamp between dateadd(dd,-200,getdate()) and getdate()
or datepart(wk,t_stamp)%4=1 and datename (dw,t_stamp) ='Wednesday' and t_stamp between dateadd(dd,-200,getdate()) and getdate()
or datepart(wk,t_stamp)%4=1 and datename (dw,t_stamp) ='Thursday'and t_stamp between dateadd(dd,-200,getdate()) and getdate()
or datepart(wk,t_stamp)%4=2 and datename (dw,t_stamp) ='Friday'and t_stamp between dateadd(dd,-200,getdate()) and getdate()
or datepart(wk,t_stamp)%4=2 and datename (dw,t_stamp) ='Saturday'and t_stamp between dateadd(dd,-200,getdate()) and getdate()
or datepart(wk,t_stamp)%4=3 and datename (dw,t_stamp) ='Sunday'and t_stamp between dateadd(dd,-200,getdate()) and getdate()
)C
insert into #Calendar ("From","Until") select start, finish from
(select distinct dateadd(hh,19,dateadd(dd,datediff(dd,0,t_stamp),0)) as start,dateadd(hh,31,dateadd(dd,datediff(dd,0,t_stamp),0)) as finish
from "AP7/Energy" where
datepart(wk,t_stamp)%4=0 and datename (dw,t_stamp) = 'Friday' and t_stamp between dateadd(dd,-200,getdate()) and getdate()
or datepart(wk,t_stamp)%4=0 and datename (dw,t_stamp) ='Saturday' and t_stamp between dateadd(dd,-200,getdate()) and getdate()
or datepart(wk,t_stamp)%4=1 and datename (dw,t_stamp) ='Sunday' and t_stamp between dateadd(dd,-200,getdate()) and getdate()
or datepart(wk,t_stamp)%4=2 and datename (dw,t_stamp) ='Monday'and t_stamp between dateadd(dd,-200,getdate()) and getdate()
or datepart(wk,t_stamp)%4=2 and datename (dw,t_stamp) ='Tuesday'and t_stamp between dateadd(dd,-200,getdate()) and getdate()
or datepart(wk,t_stamp)%4=3 and datename (dw,t_stamp) ='Wednesday'and t_stamp between dateadd(dd,-200,getdate()) and getdate()
or datepart(wk,t_stamp)%4=3 and datename (dw,t_stamp) ='Thursday'and t_stamp between dateadd(dd,-200,getdate()) and getdate()
)CC
set @no_Rows = (select count("From") from #calendar)
--select * from #calendar order by "From" asc
while (@current_Row <= @no_Rows)
begin
insert into #Results (kwh) select kwh from (
select ISNULL(max(compressors_total_kwh)-min(compressors_total_kwh),0) as kwh
from "ap7/energy"
where t_stamp between
(select "From" from #Calendar where rownumber =@current_Row)
and
(select "Until" from #Calendar where rownumber =@current_Row))A
set @current_Row = @current_Row+1
end
select R.kwh,C."From", C."Until"
from #results R, #Calendar C
where R.rownumber = C.rownumber
order by "From" asc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply