Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

SQl Query to get all Top 5 Month Names with Values from same table

Hi all,

What I need to do is show all Month names with values from the same table:
example:
Month                             FinalD                  Value(COUNT of FinalD Value)
January                          Inpatient                          100
February                       ER                                   229
March                           Acath                             322
April                                ARAD                               444

What I need is top5 with another column showng count of FinalD.


this is what i have tried so far but I only get Month and the FinalD
SELECT 
"MONTH" = Datename(month,( DateInserted)),(FinalD)
FROM [DailyPatientAdmits]
Where status = 'Completed'
GROUP BY Datename(month,( DateInserted)),FinalD
Order By Datename(month,( DateInserted)),FinalD Desc

Open in new window


The result is:
Month                                 FinalD
January                          Inpatient
Frbruary                        ER
March                             ACATH

thanks!
0
kouts1
Asked:
kouts1
  • 2
3 Solutions
 
vastoCommented:
SELECT
Datename(month, DateInserted) as [Month] , FinalD, COUNT(*) as [Value]
FROM [DailyPatientAdmits]
Where status = 'Completed'
GROUP BY Datename(month,( DateInserted)),FinalD
Order By Datename(month,( DateInserted)),FinalD Desc
0
 
kouts1Author Commented:
Awesome, but how about Top5 FinalD grouped by Month showing value for each?

thanks
0
 
Andrei FomitchevCommented:
I've added year (just in case). The working  query itself:
;WITH cte (RoNo, t1Yr, t1Mn, FinalId, Value) AS (
	SELECT ROW_NUMBER() OVER (ORDER BY t1.Yr, t1.Mn, Value ASC) AS RoNo, t1.Yr AS t1Yr, t1.Mn AS t1Mn, FinalId, Value 
	FROM (
		SELECT DISTINCT Year(dt) AS Yr,MONTH(dt) AS Mn
		FROM @DailyPatientAdmits
	) t1
	JOIN (
		SELECT Yr AS t2Yr, Mn AS t2Mn,FinalId,COUNT(FinalId) AS Value FROM (
			SELECT Year(dt) AS Yr, Month(dt) AS [Mn], FinalId, status 
			FROM @DailyPatientAdmits
			WHERE status = 'Completed'
		) t
		GROUP BY Yr,Mn,FinalId
	) t2 ON t2.t2Mn = t1.Mn AND t2Yr = t1.Yr
)
SELECT t3Yr AS Year, t3Mn AS [Month], FinalId, Value FROM (
	SELECT tt1.t1Yr AS t3Yr, tt1.t1Mn AS t3Mn, Max(tt1.RoNo) MxRoNo FROM cte tt1 GROUP BY tt1.t1Yr, tt1.t1Mn
) t3
JOIN cte tt2 ON tt2.t1Mn = t3.t3Mn AND tt2.t1Yr = t3.t3Yr
WHERE RoNo > MxRoNo-5
ORDER BY t3Yr, t3Mn, Value DESC

Open in new window


The complete working example (you can play with source data):

DECLARE @DailyPatientAdmits TABLE (dt DateTime, FinalId VarChar(50),status VarChar(50))

INSERT INTO @DailyPatientAdmits VALUES
('2012-01-28','Inpatient','Completed'),
('2012-01-28','Inpatient','Completed'),
('2012-01-28','Inpatient','Completed'),
('2012-01-28','Inpatient','Completed'),
('2012-01-28','Inpatient','Completed'),
('2012-01-28','Inpatient','Completed'),
('2012-01-28','Inpatient','Completed'),
('2012-01-28','Inpatient','InProgress'),
('2012-01-28','Inpatient','Completed'),
('2012-01-28','Inpatient','Completed'),
('2012-01-28','Inpatient','Completed'),
('2012-01-28','Inpatient','Completed'),
('2012-01-28','ER','Completed'),
('2012-01-28','ER','Completed'),
('2012-01-28','ER','Completed'),
('2012-01-28','ER','InProgress'),
('2012-01-28','ER','Completed'),
('2012-01-28','Acath','Completed'),
('2012-01-28','Acath','Completed'),
('2012-01-28','Acath','Completed'),
('2012-01-28','Acath','Completed'),
('2012-01-28','Acath','Completed'),
('2012-01-28','ARAD','Completed'),
('2012-01-28','ARAD','Completed'),
('2012-01-28','bbbbb','Completed'),
('2012-01-28','bbbbb','Completed'),
('2012-01-28','bbbbb','Completed'),
('2012-01-28','bbbbb','Completed'),
('2012-01-28','bbbbb','Completed'),
('2012-01-28','bbbbb','Completed'),
('2012-01-28','bbbbb','Completed'),
('2012-01-28','bbbbb','Completed'),
('2012-01-28','bbbbb','Completed'),
('2012-01-28','ccccc','Completed'),
('2012-01-28','ccccc','Completed'),
('2012-01-28','fffff','Completed'),
('2012-01-28','fffff','Completed'),
('2012-01-28','fffff','Completed'),
('2012-01-28','fffff','Completed'),

('2012-02-28','Inpatient','Completed'),
('2012-02-28','Inpatient','Completed'),
('2012-02-28','Inpatient','Completed'),
('2012-02-28','Inpatient','Completed'),
('2012-02-28','Inpatient','Completed'),
('2012-02-28','Inpatient','Completed'),
('2012-02-28','ER','Completed'),
('2012-02-28','Inpatient','InProgress'),
('2012-02-28','ER','Completed'),
('2012-02-28','ER','Completed'),
('2012-02-28','ER','Completed'),
('2012-02-28','ER','Completed'),
('2012-02-28','ER','Completed'),
('2012-02-28','ER','Completed'),
('2012-02-28','ER','Completed'),
('2012-02-28','ER','InProgress'),
('2012-02-28','ER','Completed'),
('2012-02-28','ER','Completed'),
('2012-02-28','Acath','Completed'),
('2012-02-28','Acath','Completed'),
('2012-02-28','Acath','Completed'),
('2012-02-28','Acath','Completed'),
('2012-02-28','ARAD','Completed'),
('2012-02-28','ARAD','Completed'),
('2012-02-28','ARAD','Completed'),
('2012-02-28','ARAD','Completed'),
('2012-02-28','ARAD','Completed'),
('2012-02-28','ARAD','Completed'),
('2012-02-28','ARAD','Completed'),
('2012-02-28','ARAD','Completed'),
('2012-02-28','bbbbb','Completed'),
('2012-02-28','bbbbb','Completed'),
('2012-02-28','bbbbb','Completed'),
('2012-02-28','ccccc','Completed'),
('2012-02-28','ccccc','Completed'),
('2012-02-28','ccccc','Completed'),
('2012-02-28','ccccc','Completed'),
('2012-02-28','ccccc','Completed'),
('2012-02-28','fffff','Completed')

;WITH cte (RoNo, t1Yr, t1Mn, FinalId, Value) AS (
	SELECT ROW_NUMBER() OVER (ORDER BY t1.Yr, t1.Mn, Value ASC) AS RoNo, t1.Yr AS t1Yr, t1.Mn AS t1Mn, FinalId, Value 
	FROM (
		SELECT DISTINCT Year(dt) AS Yr,MONTH(dt) AS Mn
		FROM @DailyPatientAdmits
	) t1
	JOIN (
		SELECT Yr AS t2Yr, Mn AS t2Mn,FinalId,COUNT(FinalId) AS Value FROM (
			SELECT Year(dt) AS Yr, Month(dt) AS [Mn], FinalId, status 
			FROM @DailyPatientAdmits
			WHERE status = 'Completed'
		) t
		GROUP BY Yr,Mn,FinalId
	) t2 ON t2.t2Mn = t1.Mn AND t2Yr = t1.Yr
)
SELECT t3Yr AS Year, t3Mn AS [Month], FinalId, Value FROM (
	SELECT tt1.t1Yr AS t3Yr, tt1.t1Mn AS t3Mn, Max(tt1.RoNo) MxRoNo FROM cte tt1 GROUP BY tt1.t1Yr, tt1.t1Mn
) t3
JOIN cte tt2 ON tt2.t1Mn = t3.t3Mn AND tt2.t1Yr = t3.t3Yr
WHERE RoNo > MxRoNo-5
ORDER BY t3Yr, t3Mn, Value DESC

Open in new window

0
 
kouts1Author Commented:
thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now