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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need some help with Counting and merging values

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 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.

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 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
Go to Top of Page

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 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


Go to Top of Page

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 Minutes
Category1 10 20 59
Category2 5 15 59
Category3 8 24 59


Adding to the above Query.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-16 : 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/

Go to Top of Page

fchachar
Starting Member

15 Posts

Posted - 2012-11-19 : 09:33:10
Thanks alot Viaskh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-20 : 02:43:32
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -