Solved

Group two related into one

Posted on 2007-12-06
4
175 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
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.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now