Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Group two related into one

Posted on 2007-12-06
4
Medium Priority
?
182 Views
Last Modified: 2010-03-19
I have the following query which looks for programs that users are enrolled in:
SELECT tmp.user_id,
service.program_name, appi.enroll_date, service_level.description
FROM #tmp_dual_enrolled tmp
INNER JOIN user
      ON tmp.user_id = user..user_id
INNER JOIN userprogram up
      ON tmp.user_id = up.user_id
INNER JOIN alg_user_program_info aupi
      ON up.user_program_id = aupi.user_program_id
INNER JOIN alg_lk_service als
      ON aupi.program_service_id = als.service_id
INNER JOIN alg_lk_episode_status ales
      ON aupi.episode_status_id = ales.episode_status_id
LEFT JOIN alg_lk_service_level alsl
      ON aupi.service_level_id = alsl.service_level_id
WHERE aupi.episode_status_id = 3
AND aupi.enroll_date IS NOT NULL
AND aupi.disenroll_date IS NULL
AND als.service_id IN ('1','2','3','4','5')
ORDER BY tmp.user_id
Currently, I want to look for only 5 programs that are identified by a key of 1, 2, 3, 4 or 5. What I would like to do, is create a SQL statement which will group all records for one patient into one line. For example, when I run the query I get the following information for one user:

user_id program_name enroll_date   description
1001        C                      2006-01-01  Level 1
1001        P                      2006-07-01  Level 2

I would like to get the following:
user_id enrolled in C? C enroll date C Description enrolled in P? P enroll date P Description enrolled in A?
1001     Yes                2006-01-01   Level 1         Yes                   2006-07-01   Level 2       No

How can accomplish this? My end purpose is creating a stored procedure that can be read from Crystal Enterprise and be able to schedule a report.
A co-worker suggested that I save all results in a temporary table, then loop through the temporary table and create a new temporary table where I could store the information from the first one. This is my first time working with MSSQL, and I have certainly never create a SProc with this level of difficulty. Any help will be greatly appreciated. Thanks!
0
Comment
Question by:horalia
  • 2
4 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 20421787
This is what I suggest:

Get all the data into one table and agroup by user_id and program_name. Something like this:
Select userd_id, program_name, MIN(enroll_Date), Min(description)
into #temp
From  tables
Group by user_id, program name.

Then assuming that you have three programs C, P, and A, craeate a temporary table. Something like this:

CREATE TABLE #Summary(
user_id datatype,
Enrolled_in_c datatype,
c_enroll_Date datetype,
c_description datatype,
Enrolled_in_p datatype,
p_enroll_Date datetype,
p_description datatype,
Enrolled_in_a datatype,
a_enroll_Date datetype,
a_description datatype
)

Then insert the user_ids in the above Summary table:
INSERT INTO #Summary(user_id)
Select user_id from tablename group by user_id

Note: Make sure you group it so that same user id does not repeat.

Then all you have to do is insert the values one by one for each program. Somthing like this:

UPDATE #Summary
SET  c_enroll_Date  = t.enroll_Date,
       c_description = t.Description,
FROM #Summary s INNER JOIN #temp t
          ON s.User_id = t.User_id
Where  t.Program_name = 'C'

UPDATE #Summary
SET  p_enroll_Date  = t.enroll_Date,
       p_description = t.Description,
FROM #Summary s INNER JOIN #temp t
          ON s.User_id = t.User_id
Where  t.Program_name = 'P'

UPDATE #Summary
SET  a_enroll_Date  = t.enroll_Date,
       a_description = t.Description,
FROM #Summary s INNER JOIN #temp t
          ON s.User_id = t.User_id
Where  t.Program_name = 'A'

UPDATE #Summary
SET  enrolled_in_c = CASE WHEN c_enroll_Date IS NULL and c_description is NULL Then 'NO' ELSE 'Yes' END

UPDATE #Summary
SET  enrolled_in_p = CASE WHEN p_enroll_Date IS NULL and p_description is NULL Then 'NO' ELSE 'Yes' END

UPDATE #Summary
SET  enrolled_in_a = CASE WHEN a_enroll_Date IS NULL and a_description is NULL Then 'NO' ELSE 'Yes' END


Now you select from the Summary table

Select * From #Summary

See the Code Snippet of how it will look like in a procedure.

Hope this helps.

P.
CREATE PROCEDURE spGetDetails
AS
 
Select userd_id, program_name, MIN(enroll_Date), Min(description)
into #temp
From  tables
Group by user_id, program name.
 
CREATE TABLE #Summary(
user_id datatype,
Enrolled_in_c datatype,
c_enroll_Date datetype,
c_description datatype,
Enrolled_in_p datatype,
p_enroll_Date datetype,
p_description datatype,
Enrolled_in_a datatype,
a_enroll_Date datetype,
a_description datatype
)
 
INSERT INTO #Summary(user_id)
Select user_id from tablename group by user_id
 
UPDATE #Summary
SET  c_enroll_Date  = t.enroll_Date,
       c_description = t.Description,
FROM #Summary s INNER JOIN #temp t
          ON s.User_id = t.User_id
Where  t.Program_name = 'C'
 
UPDATE #Summary
SET  p_enroll_Date  = t.enroll_Date,
       p_description = t.Description,
FROM #Summary s INNER JOIN #temp t
          ON s.User_id = t.User_id
Where  t.Program_name = 'P'
 
UPDATE #Summary
SET  a_enroll_Date  = t.enroll_Date,
       a_description = t.Description,
FROM #Summary s INNER JOIN #temp t
          ON s.User_id = t.User_id
Where  t.Program_name = 'A'
 
UPDATE #Summary
SET  enrolled_in_c = CASE WHEN c_enroll_Date IS NULL and c_description is NULL Then 'NO' ELSE 'Yes' END
 
UPDATE #Summary
SET  enrolled_in_p = CASE WHEN p_enroll_Date IS NULL and p_description is NULL Then 'NO' ELSE 'Yes' END
 
UPDATE #Summary
SET  enrolled_in_a = CASE WHEN a_enroll_Date IS NULL and a_description is NULL Then 'NO' ELSE 'Yes' END
 
Select * From #Summary
 
DROP TABLE #temp
DROP TABLE #Summary

Open in new window

0
 
LVL 25

Accepted Solution

by:
imitchie earned 2000 total points
ID: 20422365
This should do it nicely..
SELECT tmp.user_id,
  max(case when service.program_name = 'C' then 'Yes' else 'No' end) [Enrolled in C], 
  max(case when service.program_name = 'C' then appi.enroll_date end) [Enroll Date C],
  max(case when service.program_name = 'C' then service_level.description end) [C Description],
  max(case when service.program_name = 'P' then 'Yes' else 'No' end) [Enrolled in P], 
  max(case when service.program_name = 'P' then appi.enroll_date end) [Enroll Date P],
  max(case when service.program_name = 'P' then service_level.description end) [P Description],
  max(case when service.program_name = 'A' then 'Yes' else 'No' end) [Enrolled in A], 
  max(case when service.program_name = 'A' then appi.enroll_date end) [Enroll Date A],
  max(case when service.program_name = 'A' then service_level.description end) [A Description]
-- etc
FROM #tmp_dual_enrolled tmp
INNER JOIN [user] ON tmp.user_id = user.user_id
INNER JOIN userprogram up ON tmp.user_id = up.user_id
INNER JOIN alg_user_program_info aupi ON up.user_program_id = aupi.user_program_id
INNER JOIN alg_lk_service als ON aupi.program_service_id = als.service_id
INNER JOIN alg_lk_episode_status ales ON aupi.episode_status_id = ales.episode_status_id
LEFT JOIN alg_lk_service_level alsl ON aupi.service_level_id = alsl.service_level_id
WHERE aupi.episode_status_id = 3
  AND aupi.enroll_date IS NOT NULL
  AND aupi.disenroll_date IS NULL
  AND als.service_id IN ('1','2','3','4','5')
GROUP by tmp.user_id
ORDER BY tmp.user_id

Open in new window

0
 

Author Comment

by:horalia
ID: 20422454
Like I said in my posting, I'm very new to MSSQL Server, and even newer to SPROC. A co-worker also suggested using a loop, but I'm more inclined on using the suggested solutions. Would using temp tables make a difference vs. using a loop? Not that I have a concrete idea on how to create the procedure using the loop, I only want to know if it would make a difference in the performance. Thanks!
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 20422521
Even in your loop I see you are using temp tables. So I am not sure if that going to make a whole lot difference in performance.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Loops Section Overview

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question