April 7, 2016 at 10:46 am
We have the following query that pulls all the information correctly except for our Invoice Schedule and Amounts. You can see from the attached file that the columns highlighted in yellow are repeating for each record when the invoice schedule information (from the billing_schedule) table is only stored once in that table. For each project that may have multiple employees or products, we want the invoice schedule date and amount to only appear on 1 line. All other lines can be NULL
SELECT
p.proj_no,
p.proj_status,
p.proj_bill_with_parent,
p.proj_desc,
pbt.emp_no + ' ' + rtrim(e.emp_first_name) + ' ' + e.emp_last_name as 'Emp#/Prod# Description',
p.proj_serv_code + ' ' + sr.serv_rev_desc as 'Service Area Code/Description',
io.invopt_desc,
isnull(pbt.rate1,0) as 'Consulting Fees',
'' as 'Expense Amount',
'' as 'Product Amt',
'' as 'Tax Amt',
'' as 'Prebill Amt',
'' as 'Other Fees',
'' as 'Admin Fees',
'' as 'Total Amt',
MAX(CASE WHEN bs.RowNum=1 THEN bs.sched_date END) as 'Inv Schd 1 Date',
max(CASE when bs.RowNum=1 THEN bs.sched_amt END) as 'Inv Schd 1 Amount',
MAX(CASE WHEN bs.RowNum=2 THEN bs.sched_date END) as 'Inv Schd 2 Date',
max(CASE when bs.RowNum=2 THEN bs.sched_amt END) as 'Inv Schd 2 Amount',
MAX(CASE WHEN bs.RowNum=3 THEN bs.sched_date END) as 'Inv Schd 3 Date',
max(CASE when bs.RowNum=3 THEN bs.sched_amt END) as 'Inv Schd 3 Amount',
MAX(CASE WHEN bs.RowNum=4 THEN bs.sched_date END) as 'Inv Schd Date',
max(CASE when bs.RowNum=4 THEN bs.sched_amt END) as 'Inv Schd 4 Amount',
MAX(CASE WHEN bs.RowNum=5 THEN bs.sched_date END) as 'Inv Schd 5 Date',
max(CASE when bs.RowNum=5 THEN bs.sched_amt END) as 'Inv Schd 5 Amount',
MAX(CASE WHEN bs.RowNum=6 THEN bs.sched_date END) as 'Inv Schd 6 Date',
max(CASE when bs.RowNum=6 THEN bs.sched_amt END) as 'Inv Schd 6 Amount',
MAX(CASE WHEN bs.RowNum=7 THEN bs.sched_date END) as 'Inv Schd 7 Date',
max(CASE when bs.RowNum=7 THEN bs.sched_amt END) as 'Inv Schd 7 Amount',
MAX(CASE WHEN bs.RowNum=8 THEN bs.sched_date END) as 'Inv Schd 8 Date',
max(CASE when bs.RowNum=8 THEN bs.sched_amt END) as 'Inv Schd 8 Amount',
MAX(CASE WHEN bs.RowNum=9 THEN bs.sched_date END) as 'Inv Schd 9 Date',
max(CASE when bs.RowNum=9 THEN bs.sched_amt END) as 'Inv Schd 9 Amount',
MAX(CASE WHEN bs.RowNum=10 THEN bs.sched_date END) as 'Inv Schd 10 Date',
max(CASE when bs.RowNum=10 THEN bs.sched_amt END) as 'Inv Schd 10 Amount',
MAX(CASE WHEN bs.RowNum=11 THEN bs.sched_date END) as 'Inv Schd 11 Date',
max(CASE when bs.RowNum=11 THEN bs.sched_amt END) as 'Inv Schd 11 Amount',
MAX(CASE WHEN bs.RowNum=12 THEN bs.sched_date END) as 'Inv Schd 12 Date',
max(CASE when bs.RowNum=12 THEN bs.sched_amt END) as 'Inv Schd 12 Amount'
FROM dbo.project p
JOIN dbo.proj_monthly pm ON p.proj_no = pm.proj_no
left outer join project_budget_time pbt on p.proj_no = pbt.proj_no
JOIN dbo.employee e on pbt.emp_no = e.emp_no
join service_revenue sr on p.proj_serv_code = sr.serv_rev_code
JOIN dbo.invoice_options io ON p.invopt_code = io.invopt_code
left outer JOIN (select billing_schedule.*,row_number() over (Partition by proj_no ORDER by proj_no) as RowNum
FROM billing_schedule
) as BS
ON p.proj_no = BS.proj_no
where p.proj_status = 'O' and p.proj_type NOT IN ('I','D','5','9') AND p.business_segment_code <> '98' and p.proj_no = '1101654001'
group BY p.proj_no,p.proj_status,p.proj_bill_with_parent,p.proj_desc,pbt.emp_no,e.emp_first_name,e.emp_last_name,p.proj_serv_code,sr.serv_rev_desc,io.invopt_desc,pbt.rate1
UNION
SELECT
p.proj_no,
p.proj_status,
p.proj_bill_with_parent,
p.proj_desc,
pm.prod_no + ' ' + prod.product_desc as 'Emp#/Prod# Description',
pm.serv_code + ' ' + s.serv_code_desc as 'Service Area Code/Description',
io.invopt_desc,
sum(isnull(pm.adj_fees,0)) as 'Consulting Fees',
sum(isnull(pm.tot_exp,0)) as 'Expense Amount',
sum(isnull(pm.prod_fees,0)) as 'Product Amt',
sum(isnull(pm.taxes,0)) as 'Tax Amt',
sum(isnull(pm.billed_fees,0)) as 'Prebill Amt',
sum(isnull(pm.misc_fees,0)) as 'Other Fees',
sum(isnull(pm.admin_fees,0)) as 'Admin Fees',
sum(isnull(pm.tot_fees,0) + isnull(pm.tot_exp,0)) as 'Total Amt',
MAX(CASE WHEN bs.RowNum=1 THEN bs.sched_date END) as 'Inv Schd 1 Date',
max(CASE when bs.RowNum=1 THEN bs.sched_amt END) as 'Inv Schd 1 Amount',
MAX(CASE WHEN bs.RowNum=2 THEN bs.sched_date END) as 'Inv Schd 2 Date',
max(CASE when bs.RowNum=2 THEN bs.sched_amt END) as 'Inv Schd 2 Amount',
MAX(CASE WHEN bs.RowNum=3 THEN bs.sched_date END) as 'Inv Schd 3 Date',
max(CASE when bs.RowNum=3 THEN bs.sched_amt END) as 'Inv Schd 3 Amount',
MAX(CASE WHEN bs.RowNum=4 THEN bs.sched_date END) as 'Inv Schd Date',
max(CASE when bs.RowNum=4 THEN bs.sched_amt END) as 'Inv Schd 4 Amount',
MAX(CASE WHEN bs.RowNum=5 THEN bs.sched_date END) as 'Inv Schd 5 Date',
max(CASE when bs.RowNum=5 THEN bs.sched_amt END) as 'Inv Schd 5 Amount',
MAX(CASE WHEN bs.RowNum=6 THEN bs.sched_date END) as 'Inv Schd 6 Date',
max(CASE when bs.RowNum=6 THEN bs.sched_amt END) as 'Inv Schd 6 Amount',
MAX(CASE WHEN bs.RowNum=7 THEN bs.sched_date END) as 'Inv Schd 7 Date',
max(CASE when bs.RowNum=7 THEN bs.sched_amt END) as 'Inv Schd 7 Amount',
MAX(CASE WHEN bs.RowNum=8 THEN bs.sched_date END) as 'Inv Schd 8 Date',
max(CASE when bs.RowNum=8 THEN bs.sched_amt END) as 'Inv Schd 8 Amount',
MAX(CASE WHEN bs.RowNum=9 THEN bs.sched_date END) as 'Inv Schd 9 Date',
max(CASE when bs.RowNum=9 THEN bs.sched_amt END) as 'Inv Schd 9 Amount',
MAX(CASE WHEN bs.RowNum=10 THEN bs.sched_date END) as 'Inv Schd 10 Date',
max(CASE when bs.RowNum=10 THEN bs.sched_amt END) as 'Inv Schd 10 Amount',
MAX(CASE WHEN bs.RowNum=11 THEN bs.sched_date END) as 'Inv Schd 11 Date',
max(CASE when bs.RowNum=11 THEN bs.sched_amt END) as 'Inv Schd 11 Amount',
MAX(CASE WHEN bs.RowNum=12 THEN bs.sched_date END) as 'Inv Schd 12 Date',
max(CASE when bs.RowNum=12 THEN bs.sched_amt END) as 'Inv Schd 12 Amount'
FROM dbo.project p
JOIN dbo.proj_monthly pm ON p.proj_no = pm.proj_no
join dbo.product prod ON pm.prod_no = prod.product_no
left outer JOIN dbo.service_code s ON pm.serv_code = s.serv_code
JOIN dbo.invoice_options io ON p.invopt_code = io.invopt_code
left outer JOIN (select billing_schedule.*, row_number() over (Partition by proj_no ORDER by proj_no) as RowNum
FROM billing_schedule
) as BS
ON p.proj_no = BS.proj_no
where p.proj_status = 'O' and p.proj_type NOT IN ('I','D','5','9') AND p.business_segment_code <> '98' and p.proj_no = '1101654001'
group BY p.proj_no,p.proj_status,p.proj_bill_with_parent,p.proj_desc,pm.prod_no,prod.product_desc,pm.serv_code,s.serv_code_desc,io.invopt_desc
order by p.proj_no
April 7, 2016 at 1:26 pm
This is a presentation issue and is best left to the presentation layer, SSRS for example.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply