| Author |
 Topic  |
|
|
fchachar
Starting Member
15 Posts |
 Posted - 11/16/2012 : 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 Total
FROM 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, DService
Order by coder, Total asc
Current Output:
Coder PT Type Service Total
Coder1 I MED 1
Coder1 O EWO 3
Coder1 O OBV 30
Coder1 O MPE 177
Coder1 O EMR 365
Coder2 I MED 65
Coder3 O PAT 3
Coder4 O OBV 27
Coder4 O END 102
Coder5 O MPE 1
Coder5 O EMR 150
Coder6 O OBV 1
Coder6 I MED 44
Coder6 O MPE 99
Coder6 O EMR 218
Any help will be highly appreciated.
Thanks. |
Edited by - fchachar on 11/16/2012 14:49:10
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/16/2012 : 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 Category
FROM 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,
DService
ORDER BY
coder,
Total ASC |
 |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/16/2012 : 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 Category
FROM 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,
DService
ORDER BY
coder,
Total ASC
|
|
|
|
fchachar
Starting Member
15 Posts |
Posted - 11/16/2012 : 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 Minutes
Category1 10 20 59
Category2 5 15 59
Category3 8 24 59
Adding to the above Query. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 11/16/2012 : 22:56:30
|
you can do something like
;With CTE
AS
(
SELECT *,
Total * CASE Category
WHEN 'Category1' THEN 2
WHEN 'Category2' THEN 3
WHEN 'Category3' THEN 4
END AS TotalWeightedMin
FROM
(
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 Category
FROM 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,
DService
)t
)
SELECT *,
SUM(TotalWeightedMin) OVER () AS GrandTotal
FROM CTE
ORDER BY
coder,
Total ASC
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
fchachar
Starting Member
15 Posts |
Posted - 11/19/2012 : 09:33:10
|
| Thanks alot Viaskh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 11/20/2012 : 02:43:32
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Need some help with Counting and merging values
