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.
| Author |
Topic |
|
cwildeman
Starting Member
40 Posts |
 Posted - 2011-06-23 : 11:34:07
|
| I have a stored procedure called rpt_Falls_NDNQI with a two parameters which are @StartDate and @EndDate. The query has three tables/views which are vEncounter,Falls_Input and tblUnits. VEncounter has AcctNum and DischDate and is joined to Falls_Input by AcctNum. Falls_Input has other fields I want in my query including Gender, FallObsv and Injury. tblUnits is jointed to Falls_Input by UnitID and has a field called UnitName. I am creating the stored procedure in order to create an SSRS report where a user selects a start and end date and obtains a report that grouped by UnitName and has fields such as AcctNum,Gender, FallObsv and Injury. I need to include an additional field called MultipleFalls which will need to do a count of AcctNum and return a value of Yes if the AcctNum appears more than once in the parameter date range the user selects. I think I would create a subquery but am unsure how to do it. I have a where clause that create the parameters based on DischDate:WHERE [DischDate] >= @StartDate and [DischDate] < @EndDate; Can someone help?Thanks,Chuck W |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-06-23 : 11:47:31
|
If you are on SQL 2005 or higher, you can add an additional column using a windowing funtion:case when count(*) over (partition by AcctNum) > 1 then 'Yes' else 'No' End as MultipleFalls |
 |
|
|
cwildeman
Starting Member
40 Posts |
Posted - 2011-06-23 : 12:18:47
|
| Thanks. That worked. ChuckChuck W |
|
|
|
|
|
|
Find Count of AcctNum in Stored Procedure