Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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  
 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.12 seconds. Powered By: Snitz Forums 2000