Link to home
Start Free TrialLog in
Avatar of Mayank Tripathi
Mayank Tripathi

asked on

Need T-SQL Help for SSRS and .net code.

CREATE TABLE Report
(
    YearMonth        INT,
    ReportDateKey    INT,
    Admints          INT,
    AdmisionType     VARCHAR(200)
)
GO

INSERT INTO Report VALUES
(201701,20170131,10,'Acute Care')
 ,(201703,20170330,10,'Acute Care')
  ,(201704,20170430,70,'Acute Care')
,(201706,20170630,10,'Acute Care')
,(201702,20170228,80,'Acute Care')
,(201701,20170228,20,'LTE')
,(201702,20170228,30,'LTE')
,(201701,20170131,50,'RHEAB')
,(201702,20170228,100,'RHEAB')

Open in new window


EXEPECTED OUTPUT:-

AdmisionType	ADK	PreviousMonthADK	YTDADK"
Acute Care	80	10	90
LTE	30	20	50
RHEAB	100	50	150

Open in new window

Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Is Pivot fine with you??

;WITH CTE AS
(
     SELECT * , ROW_NUMBER() OVER (PARTITION BY AdmisionType ORDER BY ReportDateKey) rnk FROM Report    
)
SELECT AdmisionType , ISNULL(MAX([2]),0) [ADK], ISNULL(MAX([1]),0) [PreviousMonthADK] ,ISNULL(MAX([2]),0) + ISNULL(MAX([1]),0)  YTDADK FROM CTE
PIVOT 
(
     MAX(Admints) FOR RNK IN ([1],[2])
)t
GROUP BY AdmisionType

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vitor Montalvão
Will this solution work for you?
WITH CTE_ADK
AS (
	SELECT AdmisionType, MAX(Admints) ADK
	FROM report
	GROUP BY AdmisionType
	),
CTE_PreviousMonthADK
AS (
	SELECT r.AdmisionType, r.Admints
	FROM report r
	WHERE r.YearMonth = (
		SELECT CAST(CAST(r2.YearMonth AS INTEGER)-1 AS CHAR(6))
		FROM report r2  
			INNER JOIN CTE_ADK ca ON r2.AdmisionType = ca.AdmisionType AND r2.Admints = ca.ADK
		WHERE r2.AdmisionType = r.AdmisionType
		)
	)
SELECT a.AdmisionType, a.ADK, p.Admints as PreviousMonthADK, a.ADK + p.Admints as YTDADK
FROM CTE_ADK a
	INNER JOIN CTE_PreviousMonthADK p ON a.AdmisionType=p.AdmisionType

Open in new window

Avatar of Mayank Tripathi
Mayank Tripathi

ASKER

Thanks !! It works