Trying to get the output of this code down to 1 line vs 1886 it currently displays. Can anyone help?
-
Trying to get the output of this code down to 1 line vs 1886 it currently displays. When I remove the ship date it gives me an error. Can anyone help? /* This template is an aging report for private pay open balances*/ USE AR SELECT SUM(ARO.TOTALAMOUNT) AS SUM_OF_TOTALAMOUNT,ARO.INS_CO_AR, --CONVERT(VARCHAR, CLM.SHIP_DT_CH, 101) AS SHIP_DATE, --CONVERT(VARCHAR, GETDATE(), 102) AS CUR_DATE, --DATEDIFF(dd, CLM.SHIP_DT_CH, GETDATE()) AS AGE, CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=30 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [0-30 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=31 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=60 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [31-60 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=61 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=90 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [61-90 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=91 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=180 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [91-180 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=181 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=360 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [181-360 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=361 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [OVER 360 DAYS] FROM dbo.tblAROPEN AS ARO INNER JOIN dbo.tblCLMHDR AS CLM ON ARO.CUS_NUM_AR = CLM.CUSTNUM_CH AND ARO.APPLY_TO_AR = CLM.fINVNUM_CH WHERE ARO.INS_CO_AR IN ('0','000') AND ARO.TOTALAMOUNT >0 GROUP BY ARO.INS_CO_AR, CLM.SHIP_DT_CH --ORDER BY ARO.TOTALAMOUNT DESC
-
Trying to get the output of this code down to 1 line vs 1886 it currently displays. When I remove the ship date it gives me an error. Can anyone help? /* This template is an aging report for private pay open balances*/ USE AR SELECT SUM(ARO.TOTALAMOUNT) AS SUM_OF_TOTALAMOUNT,ARO.INS_CO_AR, --CONVERT(VARCHAR, CLM.SHIP_DT_CH, 101) AS SHIP_DATE, --CONVERT(VARCHAR, GETDATE(), 102) AS CUR_DATE, --DATEDIFF(dd, CLM.SHIP_DT_CH, GETDATE()) AS AGE, CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=30 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [0-30 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=31 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=60 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [31-60 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=61 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=90 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [61-90 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=91 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=180 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [91-180 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=181 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=360 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [181-360 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=361 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [OVER 360 DAYS] FROM dbo.tblAROPEN AS ARO INNER JOIN dbo.tblCLMHDR AS CLM ON ARO.CUS_NUM_AR = CLM.CUSTNUM_CH AND ARO.APPLY_TO_AR = CLM.fINVNUM_CH WHERE ARO.INS_CO_AR IN ('0','000') AND ARO.TOTALAMOUNT >0 GROUP BY ARO.INS_CO_AR, CLM.SHIP_DT_CH --ORDER BY ARO.TOTALAMOUNT DESC
Assuming the fields you ultimately want are: SUM_OF_TOTALAMOUNT INS_CO_AR [0-30 DAYS] [31-60 DAYS] [61-90 DAYS] [91-180 DAYS] [181-360 DAYS] [OVER 360 DAYS]... Then, use your query as a sub-select and do a select from there. SELECT sum(SUM_OF_TOTALAMOUNT) as TotalAmount, sum([0-30 DAYS]), sum([31-60 DAYS]), sum([61-90 DAYS]), sum([91-180 DAYS]), sum([181-360 DAYS]), sum([OVER 360 DAYS]), INS_CO_AR from ( your query ) as DT group by INS_CO_AR Or something to that effect...
-
Trying to get the output of this code down to 1 line vs 1886 it currently displays. When I remove the ship date it gives me an error. Can anyone help? /* This template is an aging report for private pay open balances*/ USE AR SELECT SUM(ARO.TOTALAMOUNT) AS SUM_OF_TOTALAMOUNT,ARO.INS_CO_AR, --CONVERT(VARCHAR, CLM.SHIP_DT_CH, 101) AS SHIP_DATE, --CONVERT(VARCHAR, GETDATE(), 102) AS CUR_DATE, --DATEDIFF(dd, CLM.SHIP_DT_CH, GETDATE()) AS AGE, CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=30 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [0-30 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=31 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=60 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [31-60 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=61 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=90 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [61-90 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=91 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=180 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [91-180 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=181 AND DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) <=360 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [181-360 DAYS], CASE WHEN DATEDIFF(dd,CLM.SHIP_DT_CH, GETDATE()) >=361 THEN SUM(ARO.TOTALAMOUNT) ELSE 0 END AS [OVER 360 DAYS] FROM dbo.tblAROPEN AS ARO INNER JOIN dbo.tblCLMHDR AS CLM ON ARO.CUS_NUM_AR = CLM.CUSTNUM_CH AND ARO.APPLY_TO_AR = CLM.fINVNUM_CH WHERE ARO.INS_CO_AR IN ('0','000') AND ARO.TOTALAMOUNT >0 GROUP BY ARO.INS_CO_AR, CLM.SHIP_DT_CH --ORDER BY ARO.TOTALAMOUNT DESC
-
I'd probably do it like this. The main difference is making each [xx-yy] field a subquery, and using BETWEEN rather than doubling up the datediff's. select SUM(ARO.TOTALAMOUNT) AS SUM_OF_TOTALAMOUNT, [0-30 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) <= 30), [31-60 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 31 and 60), [61-90 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 61 and 90), [91-180 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 91 and 180), [181-360 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) between 181 and 360), [over 360 days] = (select sum(SUM_OF_TOTALAMOUNT) from <your table joins> where datediff(dd, CLM.SHIP_DT_CH,getdate()) >= 361) from <your table joins> group by <your grouping> order by <your ordering> desc
modified on Tuesday, June 21, 2011 3:23 PM