Solved

Group two related into one

Posted on 2007-12-06
4
177 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 500 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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 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