Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Changing a User Defined Function into a View Expand / Collapse
Author
Message
Posted Monday, August 19, 2013 7:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 13,126, Visits: 11,965
Rick44 (8/16/2013)
Hi. That was just a small example. Yes, there could be years worth of counts. many more that 1 enrolled. Do you want me to send you larger sample data.


The small sample is fine. What I need it to understand the business rules. What would you expect as output based on the sample data you provided and how do you come up with the counts and such?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1485820
Posted Monday, August 19, 2013 10:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
Points: 30, Visits: 107
Hi. Thank you for getting back to me.

I need to pull a count of clients enrolled in a program by month. CLT_NBR is a unique ID for each client.

•I want the total count of clients that were enrolled at the end of the month.
•So include all clients enrolled prior to the period end date. I am using there Max(EFFECT_DT) during the month to determine the count at the end of the month. The EFFECT_DT gives the status change dates. SO the Max(Effect_DT) is the last status change the client had during the month.
•I need exclude all clients disenrolled or transferred out prior to the start date and Exclude all clients who transferred in after the prior end date
•The B2H_Status variable holds the codes for the actually different types of status changes.
o 4=Enrolled
o 7= Disenrolled
o 8= Transfer In
o 9= Transfer Out

If the Period date range selected is 1/01/2013 to 3/01/2013

Total_Clients_Enrolled_Count   Month    Year 
252 Jan 2013
247 Feb 2013
303 March 2013

Post #1485907
Posted Monday, August 19, 2013 11:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 16, 2013 7:55 PM
Points: 30, Visits: 107


What I need it to understand the business rules. What would you expect as output based on the sample data you provided and how do you come up with the counts and such?


I hope my response is what you are looking for. Thanks again,
Post #1485922
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse