asked
How Do I Subtract?
I have a query that returns census related data. I need to write the query to perform the mathematical equations which show the difference from one period of time to another (The end of one month compared to the end of the previous month). Here is the query: USE LSV SELECT C.WARDLOCATIONNAME ,C.CENSUSDATE ,C.CUMADMISSIONS ,C.CUMLOSSES ,C.PATIENTSREMAINING ,C.CUMMONTHLYPATDAYS ,C.CUMDISCHARGES ,C.IWTGAINSCUM ,C.CUMINTERXFERS ,C.TOASIH FROM R_CENSUS.CENSUSDATE C WHERE STA3N=678 AND (C.CENSUSDATE = '2013-10-31' OR C.CENSUSDATE = '11/30/2013') AND (C.WARDLOCATIONNAME ='3E' This is the output: WARDLOCATIONNAME CENSUSDATE CUMADMISSIONS CUMLOSSES PATIENTSREMAINING CUMMONTHLYPATDAYS CUMDISCHARGES IWTGAINSCUM CUMINTERXFERS TOASIH 3E 2013-11-30 178.00 311.00 20.00 655.00 263.00 134.00 48.00 NULL 3E 2013-10-31 73.00 165.00 17.00 709.00 131.00 90.00 34.00 NULL This is what the output needs to be (I get this after running several other reports, dumping in Access, and finally into Excel): Pt Days Turnover Pts IWT IWT ASIH Ave Location Admissions Discharges Losses of Care ADC Rate Treated In out LOS Service: Medicine 3E 73 131 165 709 23 721 182 90 34 0 4.30
Hi PeteK, Broadly, you are looking for a LAG() function, which is available in 2012:
http://technet.microsoft.com/en-us/library/hh231256.aspx However, the ROW_NUMBER() function, available from 2005 on, can get you what you need. I've adapted the following sample code from Pravin Patel's solution here:
http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/ --CTE based solution ; WITH CensusDate AS ( --Setting up some sample data SELECT * FROM (VALUES ('3E','2013-10-31',50,678) ,('3E','2013-11-30',55,678) ,('3E','2013-12-31',105,678) ) AS aTable(WardLocationName, CensusDate,CUMAdmissions, STA3N) ) --Preparing our data set with a row number , cte_DemonstrationOfLagAndLead AS ( SELECT C.WARDLOCATIONNAME ,C.CENSUSDATE ,C.CUMADMISSIONS ,ROW_NUMBER() OVER (ORDER BY C.CENSUSDATE) AS myRowNumber FROM CENSUSDATE C WHERE STA3N=678 AND (C.CENSUSDATE = '2013-10-31' OR C.CENSUSDATE = '2013-11-30' OR c.CensusDate = '2013-12-31') AND C.WARDLOCATIONNAME ='3E' ) --By self joining to that common table expression on the row numbers we can simulate lag and lead. SELECT m.WardLocationName ,m.CensusDate ,m.CUMAdmissions --This is where we use the lag to subtract the previous row from the current one. ,m.CUMAdmissions - myLag.CUMAdmissions AS ChangeInCummulativeAdmissionsSnapshots ,myLead.CensusDate AS leadvalue ,myLag.CensusDate AS lagvalue FROM cte_DemonstrationOfLagAndLead AS m LEFT OUTER JOIN cte_DemonstrationOfLagAndLead AS myLead ON myLead.myRowNumber = m.myRowNumber+1 LEFT OUTER JOIN cte_DemonstrationOfLagAndLead AS myLag ON myLag.myRowNumber = m.myRowNumber-1 ORDER BY m.WardLocationName ,m.CensusDate ,m.CUMAdmissions
15 People are following this question.
