Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
fchachar
Starting Member
15 Posts |
Posted - 2012-11-16 : 14:46:51
|
Need some help with the Query listed below.What I want to do is, I want to Create a Column called 'Categories'. It will combine a list of Services that I choose, for ex: I want to combine EWO, and OBV together and Call it Category1, and Category2 will contain EMR and MPE and so on but I don't want to include PT Type 'I' that can be in a Category regardless of any Service.SELECT Coder_id as [Coder], PTType as [PT Type], DService as [Service], count(DService) as TotalFROM VISIT_VIEW LEFT JOIN ENCODERRESULTS ON VISIT_VIEW.visit_id=ENCODERRESULTS._fk_visit Where (Coder_dt_tm BETWEEN '2012-11-04 00:00:00.000' AND '2012-11-10 23:59:59.997') AND (Coder_id IS NOT NULL) Group by Coder_id, PTType, DServiceOrder by coder, Total asc Current Output:Coder PT Type Service TotalCoder1 I MED 1Coder1 O EWO 3Coder1 O OBV 30Coder1 O MPE 177Coder1 O EMR 365Coder2 I MED 65Coder3 O PAT 3Coder4 O OBV 27Coder4 O END 102Coder5 O MPE 1Coder5 O EMR 150Coder6 O OBV 1Coder6 I MED 44Coder6 O MPE 99Coder6 O EMR 218 Any help will be highly appreciated.Thanks. |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-16 : 15:05:50
|
You can use a case expression like shown below, but if you have more values in the Service types (or even if you don't), a better option would be to create a reference table with the Service types and corresponding categories and join to that table.SELECT Coder_id AS [Coder], PTType AS [PT Type], DService AS [Service], COUNT(DService) AS Total, CASE WHEN [PTType] = 'I' THEN 'No Category' WHEN [Service] IN ('EWO', 'OBV') THEN 'Category1' WHEN [Service] IN ('EMR', 'MPE') THEN 'Category2' END AS CategoryFROM VISIT_VIEW LEFT JOIN ENCODERRESULTS ON VISIT_VIEW.visit_id = ENCODERRESULTS._fk_visitWHERE ( Coder_dt_tm BETWEEN '2012-11-04 00:00:00.000' AND '2012-11-10 23:59:59.997' ) AND (Coder_id IS NOT NULL)GROUP BY Coder_id, PTType, DServiceORDER BY coder, Total ASC |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-16 : 15:11:07
|
quote: Originally posted by sunitabeck You can use a case expression like shown below, but if you have more values in the Service types (or even if you don't), a better option would be to create a reference table with the Service types and corresponding categories and join to that table.SELECT Coder_id AS [Coder], PTType AS [PT Type], DService AS [Service], COUNT(DService) AS Total, MAX(CASE WHEN [PTType] = 'I' THEN 'No Category' WHEN [Service] IN ('EWO', 'OBV') THEN 'Category1' WHEN [Service] IN ('EMR', 'MPE') THEN 'Category2' END) AS CategoryFROM VISIT_VIEW LEFT JOIN ENCODERRESULTS ON VISIT_VIEW.visit_id = ENCODERRESULTS._fk_visitWHERE ( Coder_dt_tm BETWEEN '2012-11-04 00:00:00.000' AND '2012-11-10 23:59:59.997' ) AND (Coder_id IS NOT NULL)GROUP BY Coder_id, PTType, DServiceORDER BY coder, Total ASC
|
|
|
fchachar
Starting Member
15 Posts |
Posted - 2012-11-16 : 16:05:31
|
Thanks Sunita for your help, the results are coming fine. Just one question, if I want to create two new column called Minutes and Total Minutes and I define a number for each category is it possible to mutiple that number to each Category and return the value in the new column. Something like.For Category1 its 2, Category2 is 3 and Category3 is 4 and so on... Category Total Minutes Total MinutesCategory1 10 20 59Category2 5 15 59Category3 8 24 59Adding to the above Query. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-16 : 22:56:30
|
you can do something like;With CTEAS(SELECT *,Total * CASE Category WHEN 'Category1' THEN 2 WHEN 'Category2' THEN 3 WHEN 'Category3' THEN 4 END AS TotalWeightedMinFROM (SELECT Coder_id AS [Coder], PTType AS [PT Type], DService AS [Service], COUNT(DService) AS Total, MAX(CASE WHEN [PTType] = 'I' THEN 'No Category' WHEN [Service] IN ('EWO', 'OBV') THEN 'Category1' WHEN [Service] IN ('EMR', 'MPE') THEN 'Category2' END) AS CategoryFROM VISIT_VIEW LEFT JOIN ENCODERRESULTS ON VISIT_VIEW.visit_id = ENCODERRESULTS._fk_visitWHERE ( Coder_dt_tm BETWEEN '2012-11-04 00:00:00.000' AND '2012-11-10 23:59:59.997' ) AND (Coder_id IS NOT NULL)GROUP BY Coder_id, PTType, DService)t)SELECT *,SUM(TotalWeightedMin) OVER () AS GrandTotalFROM CTEORDER BY coder, Total ASC ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
fchachar
Starting Member
15 Posts |
Posted - 2012-11-19 : 09:33:10
|
Thanks alot Viaskh |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-20 : 02:43:32
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|