Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Claymationator
Starting Member
6 Posts |
 Posted - 2008-09-13 : 08:07:03
|
| I am using this query to run a report that I need, however I need to make a few modifications to it, and I am not sure what the syntax would be. The code is as follows:SELECT PtAccountNumber ,YEAR(Date_DCDate) AS Date_DCDate ,PrimaryPayorName ,SUM(DirectCost_SubsidiesGrants_Total) AS DirectCost_SubsidiesGrants_Total ,SUM(IndirectCost_SubsidiesGrants_Total) AS IndirectCost_SubsidiesGrants_Total ,SUM(PmtsAllPayors) AS PmtsAllPayors ,SUM(Cases) AS Cases ,SUM(TotalChargesAllServices) AS TotalChargesAllServices ,SUM(AllowancesAllPayors) AS AllowancesAllPayorsFROM tbl_HPN_PhysCostDetailWHERE YEAR(Date_DCDate) IN ('2004','2005','2006','2007','2008') AND TotalChargesAllServices IS NOT NULL AND PmtsAllPayors IS NOT NULL AND PmtsAllPayors > TotalChargesAllServicesGROUP BY PtAccountNumber ,YEAR(Date_DCDate) ,PrimaryPayorNameIn those results there are may be multiple line items for one patient account number. What I need to do in that case where there is more than one account number listed is to only show PmtsAllPayors and AllowancesAllPayors once. What is happening is that they are shown on each line that the PtAccountNumber is showing and when I have pulled it into Excel for some analysis the numbers are completely off as it is summing all of the values and inflating both of those fields. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-13 : 09:06:00
|
1) You can drop these two linesANDTotalChargesAllServices IS NOT NULLAND PmtsAllPayors IS NOT NULL2) Rewrite WHERE YEAR(Date_DCDate) IN ('2004','2005','2006','2007','2008')to thisWHERE Date_DCDate >= '20040101'AND Date_DCDate < '20090101'
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
|
|
|
|
|
Help Modifying Query