SQL Query…pls help!!.. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Query…pls help!!..

Hi All, i dunno if it’s right for me to post this qn here but here it goes… I have a query like this: select viewprojectprogressreport.refcode, viewprojectprogressreport.projectcode, viewprojectprogressreport.companyname,
viewprojectprogressreport.projectdesc, viewprojectprogressreport.projectstatus,
sum(case when ViewProjectProgressReport.periodmonth<‘8’ then ViewProjectProgressReport.timespent else null end) as hoursbefore,
sum(case when ViewProjectProgressReport.periodmonth=’8′ then ViewProjectProgressReport.timespent else null end) as currentmonthtime,
sum(case when ViewProjectProgressReport.periodmonth<‘8’ then ViewProjectProgressReport.timecost else null end) as costbefore,
sum(case when ViewProjectProgressReport.periodmonth=’8′ then ViewProjectProgressReport.timecost else null end) as currentmonthcost

from ViewProjectProgressReport
where ViewProjectProgressReport.projectstatus=’active’ and viewprojectprogressreport.refcode =’sfc’
group by ViewProjectProgressReport.refcode, ViewProjectProgressReport.projectcode, ViewProjectProgressReport.companyname,
ViewProjectProgressReport.projectdesc, ViewProjectProgressReport.projectstatus
order by viewprojectprogressreport.projectcode select viewprojectprogressreport.refcode, viewprojectprogressreport.projectcode, viewprojectprogressreport.companyname,
viewprojectprogressreport.projectdesc, viewprojectprogressreport.projectstatus,
sum(case when ViewProjectProgressReport.periodmonth<‘8’ then ViewProjectProgressReport.timespent else null end) as hoursbefore,
sum(case when ViewProjectProgressReport.periodmonth=’8′ then ViewProjectProgressReport.timespent else null end) as currentmonthtime,
sum(case when ViewProjectProgressReport.periodmonth<‘8’ then ViewProjectProgressReport.timecost else null end) as costbefore,
sum(case when ViewProjectProgressReport.periodmonth=’8′ then ViewProjectProgressReport.timecost else null end) as currentmonthcost
from ViewProjectProgressReport full outer join viewbatchdisbursementdetails on viewprojectprogressreport.projectcode=viewbatchdisbursementdetails.chargeto
where ViewProjectProgressReport.projectstatus=’active’ and viewprojectprogressreport.refcode =’sfc’
group by ViewProjectProgressReport.refcode, ViewProjectProgressReport.projectcode, ViewProjectProgressReport.companyname,
ViewProjectProgressReport.projectdesc, ViewProjectProgressReport.projectstatus
order by viewprojectprogressreport.projectcode The two queries are supposed to generate the same figures for the underlined columns….the first one is the basic one which i have not connected to the second table… i will get teh correct figures when i run the first query…but once i add in the viewbatchdisbursementdetails table into the query, the figures will become wrong… why is that so? PS: the viewbatchdisbursementdetails table is added cos i need to add in columns from that table. but before i even add in the columns already have problem. Pls help…

You should use alias for tables to avoid a large query and use this alias when you refer a field instead table or view name:

sum(case when vwPPR.periodmonth=’8′ then vwPPR.timespent else null end) as currentmonthtime,
sum(case when vwPPR.periodmonth<‘8’ then vwPPR.timecost else null end) as costbefore,
sum(case when vwPPR.periodmonth=’8′ then vwPPR.timecost else null end) as currentmonthcost
from ViewProjectProgressReport vwPPR
… Is join correct with fiels .projectcode and .chargeto ??? Best regards !
the join is correct with the fields .projectcode and .chargeto
but is projectcode and chargeto the primary (or an alternate unique) key for the disbursements table? if not, then that is why your sums will inflate, it would be double counting rows Cheers
Twan
]]>