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

.NET ProgrammingDatabasesMicrosoft SQL Server 2008Microsoft SQL ServerSSRS

Last Comment

Mayank Tripathi

8/22/2022 - Mon

Pawan Kumar

11/7/2017

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

Pawan Kumar

11/7/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Vitor Montalvão

11/7/2017

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

Mayank Tripathi

11/7/2017

ASKER

Thanks !! It works

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes