SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Loop over multivalue Parameter?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cant2ny
Starting Member

USA
20 Posts

Posted - 08/14/2012 :  15:24:31  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 08/14/2012 :  15:33:06  Show Profile  Reply with Quote
sorry didnt understand problem in using IN operator. can you explain with an example?

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

Go to Top of Page

cant2ny
Starting Member

USA
20 Posts

Posted - 08/14/2012 :  15:38:56  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 08/14/2012 :  15:42:26  Show Profile  Reply with Quote
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/

Go to Top of Page

cant2ny
Starting Member

USA
20 Posts

Posted - 08/14/2012 :  22:54:52  Show Profile  Reply with Quote
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).

Edited by - cant2ny on 08/15/2012 12:32:07
Go to Top of Page

cant2ny
Starting Member

USA
20 Posts

Posted - 08/15/2012 :  12:34:22  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000