asked on

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

ASKER CERTIFIED SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

charkerr6111

ASKER

Never occurred to me to use null values, tried all kinds of things. Thanks again!

dqmq

;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