SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need some help with Counting and merging values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fchachar
Starting Member

15 Posts

Posted - 11/16/2012 :  14:46:51  Show Profile  Reply with Quote
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

5155 Posts

Posted - 11/16/2012 :  15:05:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/16/2012 :  15:11:07  Show Profile  Reply with Quote
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 - 11/16/2012 :  16:05:31  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/16/2012 :  22:56:30  Show Profile  Reply with Quote
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 - 11/19/2012 :  09:33:10  Show Profile  Reply with Quote
Thanks alot Viaskh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/20/2012 :  02:43:32  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000