Loop over multivalue Parameter? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Loop over multivalue Parameter?

Author

Topic

cant2ny
Starting Member

20 Posts

Posted - 2012-08-14 : 15:24:31

Does anyone know if it's possible to set up a parameter loop based on parameters added through BIDS? I have a report that calculations are based on individual user ids and some issues are arising with using the IN function. The user ID is a GUID, and the user can select multiple options. Ideally I would like to cycle through each user ID doing the calculations with the individual user id before moving onto the next (instead of using the in function)

While @User_ID IS NOT NULL
Begin
select * from order_ where user_id = @User_ID
End
Next User ID

Not sure if this needs to be a cursor but any help would be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 15:33:06

sorry didnt understand problem in using IN operator. can you explain with an example?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cant2ny
Starting Member

20 Posts

Posted - 2012-08-14 : 15:38:56

Sure, I'll include the real query that I'm using. The problem with it is the same person can have multiple providers but I still need to return the MAX(enc_timestamp) for each provider. So for example, if I use the IN clause but my test patient has 2 or more encounters w/ different providers, the program is the Max timestamp to the first timestamp while I need it to look at the encounter before the first one.

INSERT INTO #tmpCountPatEnc
SELECT pe.person_id,
       pe.practice_id,
	   1,
	   MAX(enc_timestamp),
                   NULL,
                   pe.enc_id
FROM dbo.patient_encounter pe INNER JOIN patient_procedure pp
ON pe.person_id = pp.person_id
	AND pe.enc_id = pp.enc_id INNER JOIN dbo.ngkbm_custom_dbp_item_dtl_ ncd
ON pp.cpt4_code_id = ncd.txt_qualifier_2
WHERE (pe.enc_timestamp BETWEEN @Start_Date AND DATEADD(second,-1,DateAdd(day,1, @End_Date))) AND
pe.rendering_provider_id = @Provider_ID AND
pe.practice_id = '0002' AND
ncd.txt_dbpicklist_name = 'Visit Codes' AND 
ncd.txt_qualifier_1 = 'Office Visit' AND
pp.delete_ind = 'N'
GROUP BY pe.person_id,pe.practice_id,pe.enc_id

--return previous encounterdate
UPDATE #tmpCountPatEnc
SET prev_enc_timestamp = a.prev_enc_time
FROM (SELECT pe.person_id,
             MAX(pe.enc_timestamp) AS prev_enc_time
      FROM dbo.patient_encounter pe INNER JOIN #tmpCountPatEnc tpe
        ON pe.person_id = tpe.person_id 
        AND tpe.practice_id = pe.practice_id INNER JOIN patient_procedure pp
ON pe.person_id = pp.person_id
	AND pe.enc_id = pp.enc_id INNER JOIN dbo.ngkbm_custom_dbp_item_dtl_ ncd
ON pp.cpt4_code_id = ncd.txt_qualifier_2
      WHERE pe.enc_timestamp < tpe.enc_timestamp AND
ncd.txt_dbpicklist_name = 'Visit Codes' AND 
ncd.txt_qualifier_1 = 'Office Visit' AND
pp.delete_ind = 'N' 
      GROUP BY pe.person_id) a INNER JOIN #tmpCountPatEnc t
  ON a.person_id = t.person_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 15:42:26

we wont be able to make out much from your query without any idea of sample data
So please provide sample data in below format and explain issue rather than posting the query

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cant2ny
Starting Member

20 Posts

Posted - 2012-08-14 : 22:54:52

Sure, here is the query as it stands right now - I'm trying to convert this into a form usable by SSRS as it's based on an existing Crystal Report that can't be changed. So instead of using the IN operator on the @Provider_ID, I'm forced to come up with a workaround while still maintaining the = operator on it (this effects the Max(enc_timestamp) and then the WHERE clause in the final select). The situation I'm running into is this, if a patient has more then one encounter during the reporting period w/ different providers that meet the "Office Visit" criteria, the query will set the Max(enc_timestamp) to itself instead of the visit before.

The data example was taken when the cursor was removed and the @ProviderID was switched to In(@ProviderID). With it being set to the = operator, it returns only 1 row (one for each provider) with a prev_enc_timestamp of 2011-06-08 on the second record (which is correct). It's fine if the patient has more then one encounter w/ the same provider as that should only count once but the problem is arising with two or more providers, it's only being counted once while I'm looking to count it twice (based on it meeting the final select criteria).

cant2ny
Starting Member

20 Posts

Posted - 2012-08-15 : 12:34:22

I was able to solve this by changing the parameter to a cursor with a parameter in the where clause. I was then able to dump the individual sets into a temp table that was used as the final select that is pulling.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources