i running sql query in mssql 2008 r2 should return consistent resultset, meaning dates within selected date range should shown, although there no rows/values in database particular date within date range. should example dates 2013-07-03 - 2013-07-04 when there values id 1 , 2.
scenario 1
date-hour, value, id 2013-07-03-1, 10, 1 2013-07-03-2, 12, 1 2013-07-03-... 2013-07-03-24, 9, 1 2013-07-04-1, 10, 1 2013-07-04-2, 10, 1 2013-07-04-... 2013-07-04-24, 10, 1 2013-07-03-1, 11, 2 2013-07-03-2, 12, 2 2013-07-03-... 2013-07-03-24, 9, 2 2013-07-04-1, 10, 2 2013-07-04-2, 12, 2 2013-07-04-... 2013-07-04-24, 10, 2 however, if id 2 missing values 2013-07-04, resultset looks this: scenario 2
date-hour, value, id 2013-07-03-1, 10, 1 2013-07-03-2, 12, 1 2013-07-03-... 2013-07-03-24, 9, 1 2013-07-04-1, 10, 1 2013-07-04-2, 10, 1 2013-07-04-... 2013-07-04-24, 10, 1 2013-07-03-1, 11, 2 2013-07-03-2, 12, 2 2013-07-03-... 2013-07-03-24, 9, 2 scenario 2 create inconsistent resultset affect output. there way make sql query return scenario 1 when there missing values, @ least return null if there no values specific date within date range. if resultset returns id 1 , 2 dates id 1 , 2 should covered. if id 1, 2 , 3 returned dates id 1, 2 , 3 should covered.
i have 2 tables this:
tbl_measurement id, date, hour1, hour2, ..., hour24 tbl_plane planeid, id, maxspeed the sql query running this:
select distinct hour00_01, hour01_02, mr.date, mr.id, maxspeed tbl_measurement mr, tbl_plane p (date >= '2013-07-03' , date <= '2013-07-04') , p.id = mr.id group mr.id, mr.date, hour00_01, hour01_02, p.maxspeed order mr.id, mr.date i have been looking around quite bit, , perhaps pivot tables way solve this? please me out? appreciate if can me out how write sql query purpose.
you can use recursive cte generate list of dates. if cross join planes, 1 row per date per plane. left join, can link in measurements if exist. left join leave row if no measurement found.
for example:
declare @startdt date = '2013-01-01' declare @enddt date = '2013-06-30' ; alldates ( select @startdt dt union select dateadd(day, 1, dt) alldates dateadd(day, 1, dt) <= @enddt ) select * alldates ad cross join tbl_plane p left join ( select row_number() on (partition id, cast([date] date) order id) rn , * tbl_measurement m.inputtype = 'forecast' ) m on p.id = m.id , m.date = ad.dt , m.rn = 1 -- 1 per day p.planetype = 3 option (maxrecursion 0) 
Comments
Post a Comment