cte recursion, or multiple select statements

Now that I can get my data correctly with two cte's, I need to be able to combine the data into one row.  I've been looking at recursive cte information online, but can't quite get what I need.  Ultimately, my query should return the following:

Name, BillRate, BilledHours, BilledAmt, UnbilledHours, UnbilledAmt

As an added bonus, I'd like to get total hours and total amount at the end, but I haven't even started that part yet... :)

Thanks for any assistance.
----- Billed ------------------

;with LDcte(name,billrate, Billedhours , BilledAmt  ) as (
   select name, (billext/reghrs), reghrs, billext
   from LD
   -- just to ensure we do not have any divide by zero errors
   where reghrs > 0 and WBS1 = '0042009.10'
   and BillStatus = 'F'
)
select name, billrate, sum(Billedhours) [BilledHours], sum(BilledAmt) [BilledAmt]
from LDcte
group by name, billrate
;

----  Works  ----------------------------------------
--------------------------------------------------------
---------------   Unbilled   -----------------------------


;with LDcte(name,billrate, UnBilledhours , UnBilledAmt  ) as (
   select name, (billext/reghrs), reghrs, billext
   from LD
   -- just to ensure we do not have any divide by zero errors
   where reghrs > 0 and WBS1 = '0042009.10'
   and BillStatus in ('B','H')
)
select name, billrate, sum(UnBilledhours) [UnBilledHours], sum(UnBilledAmt) [UnBilledAmt]
from LDcte
group by name, billrate
;

Open in new window

charkerr6111Asked:
Who is Participating?
 
dqmqCommented:
TRy this:
;with LDcte(name, billrate, Billedhours , BilledAmt, UnbilledHours, UnbilledAmt  ) as (
   select name, (billext/reghrs), reghrs, billext, Null, Null
   from LD
   -- just to ensure we do not have any divide by zero errors
   where reghrs > 0 
   and BillStatus = 'F'
   union all
   select name, (billext/reghrs), null, null, reghrs, billext
   from LD
   -- just to ensure we do not have any divide by zero errors
   where reghrs > 0 
   and BillStatus in ('B','H')
 )
select name, billrate, sum(Billedhours) [BilledHours], sum(BilledAmt) [BilledAmt], sum(Unbilledhours) unbilledhours, sum(unbilledAmt) unbilledamt
from LDcte
where WBS1 = '0042009.10' 
group by name, billrate

Open in new window

0
 
charkerr6111Author Commented:
Never occurred to me to use null values, tried all kinds of things. Thanks again!
0
 
dqmqCommented:
;with LDcte(name, billrate, Billedhours , BilledAmt, UnbilledHours, UnbilledAmt  ) as (
   select name, (billext/reghrs), reghrs, billext, Null, Null
   from LD
   -- just to ensure we do not have any divide by zero errors
   where reghrs > 0
   and WBS1 = '0042009.10'
   and BillStatus = 'F'
   union all
   select name, (billext/reghrs), null, null, reghrs, billext
   from LD
   -- just to ensure we do not have any divide by zero errors
   where reghrs > 0
   and WBS1 = '0042009.10'
   and BillStatus in ('B','H')
 )
select  '' as Line, name, billrate, sum(Billedhours) [BilledHours], sum(BilledAmt) [BilledAmt], sum(Unbilledhours) unbilledhours, sum(unbilledAmt) unbilledamt
from LDcte
group by name, billrate
union all
select 'Total', as Line, Null, avg(billrate), sum(billedhours) billedhours, sum(billedamt) billedamt,
sum(unbilledhours) unbilledhours, sum(unbilledamt) unbilledamt
order by linetype, name, billrate


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.