sql - Combining a Recursive Query to a Non-Recursive Query -
i have 2 queries have created - 1 recursive , 1 not - , combine them on coherent query (using left join connect non-recursive query recursive one) - i'm not sure how go it. tried encapsulating recursive 1 giant parenthesis however, doesn't seem work.
recursive query
declare @userid bigint = 31946, @startdateofweek datetime = '8/4/2014', @enddateofweek datetime = '8/10/2014' ;with dates (d) ( select 0 union select 1 + d dates d < datediff(dd, @startdateofweek, @enddateofweek) ) select cast([date] date) [dayreport], sum([totalhours]) [timereport] ( select dd2.[date], 0 [totalhours] ( select dateadd(dd, d, @startdateofweek) [date] dates ) dd2 union select [date], sum([realminutes])/60 [totalhours] ( select cast(eventcreatedate date) [date], case when [minutes] >= 30 , cast([minutes] float)/cast([pagecount] float) >= 1 case when cast([pagecount] float) * 0.5 > 45 45 else cast([pagecount] float) * 0.5 end when [minutes] >= 60 , cast([minutes] float)/cast([pagecount] float) < 1 60 else [minutes] /*half minute*/ end [realminutes] ( select case when c.[pagecount] = 0 1 when c.[pagecount] null 1 else c.[pagecount] end [pagecount], ev.eventcreatedate, datediff(mi, ev.eventcreatedate, ev.eventcompletiondate) [minutes] tbl_papers c, tbl_paperevents ev, tbl_users u c.chartid = ev.chartid , cast(ev.eventcreatedate date) between cast(@startdateofweek date) , cast(@enddateofweek date) , ev.eventcreateuserid = @userid , ev.eventid = 201 , u.userid = ev.eventcreateuserid ) tc ) th group [date] ) tf group [date] non-recurisve query
declare @userid bigint = 31946, @startdateofweek datetime = '8/4/2014', @enddateofweek datetime = '8/10/2014' select cast ([timeentrydate] date) time ,datediff(hh, [timeentrystarttime], [timeentryendtime]) timeentrytotalhours [tbl_timeentries] te inner join tbl_users u on u.userid = te.[timeentryuserid] inner join tbl_userpermissions on up.userid = u.userid inner join tbl_permissions p on p.permissionid = up.permissionid left outer join tbl_approvalstatuses ast on ast.approvalstatusid = te.timeentrystatusid (@userid = te.[timeentryuserid] or @userid = -1) , p.permissiontype = 'coder' and((te.[timeentrystarttime] between @startdateofweek , @enddateofweek) or (@startdateofweek null)) what see result
dayreport /*from recursive query (which can matched non-recursive query)*/ | timereport /*from recursive query*/ | timeentrytotalhours /*from non-recursive query*/ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 8/14/2014 | 1 | 2
just combine them this:
declare @userid bigint = 31946, @startdateofweek datetime = '8/4/2014', @enddateofweek datetime = '8/10/2014' ;with dates (d) ( select 0 union select 1 + d dates d < datediff(dd, @startdateofweek, @enddateofweek) ), times ( select cast([date] date) [dayreport], sum([totalhours]) [timereport] ( select dd2.[date], 0 [totalhours] ( select dateadd(dd, d, @startdateofweek) [date] dates ) dd2 union select [date], sum([realminutes])/60 [totalhours] ( select cast(eventcreatedate date) [date], case when [minutes] >= 30 , cast([minutes] float)/cast([pagecount] float) >= 1 case when cast([pagecount] float) * 0.5 > 45 45 else cast([pagecount] float) * 0.5 end when [minutes] >= 60 , cast([minutes] float)/cast([pagecount] float) < 1 60 else [minutes] /*half minute*/ end [realminutes] ( select case when c.[pagecount] = 0 1 when c.[pagecount] null 1 else c.[pagecount] end [pagecount], ev.eventcreatedate, datediff(mi, ev.eventcreatedate, ev.eventcompletiondate) [minutes] tbl_papers c, tbl_paperevents ev, tbl_users u c.chartid = ev.chartid , cast(ev.eventcreatedate date) between cast(@startdateofweek date) , cast(@enddateofweek date) , ev.eventcreateuserid = @userid , ev.eventid = 201 , u.userid = ev.eventcreateuserid ) tc ) th group [date] ) tf group [date]), otherquery ( select cast ([timeentrydate] date) [time] ,datediff(hh, [timeentrystarttime], [timeentryendtime]) timeentrytotalhours [tbl_timeentries] te inner join tbl_users u on u.userid = te.[timeentryuserid] inner join tbl_userpermissions on up.userid = u.userid inner join tbl_permissions p on p.permissionid = up.permissionid left outer join tbl_approvalstatuses ast on ast.approvalstatusid = te.timeentrystatusid (@userid = te.[timeentryuserid] or @userid = -1) , p.permissiontype = 'coder' and((te.[timeentrystarttime] between @startdateofweek , @enddateofweek) or (@startdateofweek null)) ) select times.dayreport, times.timereport, otherquery.timeentrytotalhours times left join otherquery on times.dayreport = otherquery.[time] basically, can add more cte clause separating each comma
Comments
Post a Comment