Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Group two related into one

Posted on 2007-12-06
4
Medium Priority
?
180 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 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 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