Solved

Group two related into one

Posted on 2007-12-06
4
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Caste datetime 2 73
How to write a sql query returning a result  with my date time template? 8 52
convert null in sql server 12 67
tempdb log keep growing 7 56
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 …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 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