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 |
|
Lemmy44
Starting Member
20 Posts |
 Posted - 2005-06-21 : 14:49:57
|
| OK, we are trying to write this query and while we have the logic down, we aren't quite sure how the syntax should go together.What we need, is a count of the number of days that a client occupied a bed during the fiscal year (Apr. 1, 2004 to Mar. 31, 2005). If a client was already in a bed, we need to count from April 1, but if they weren't in a bed, then we need to count from the date they became active.Can anyone suggest the best way that this can be accomplished? Here is our query so far:SELECT person.person_id, response_date.response_date, cases.close_date/*We want to sum the datediff once the dates are adjusted as noted below*/FROM personINNER JOIN collection_date ON person.person_id = collection_date.parent_id AND collection_date.child_id = 'clientrole'INNER JOIN cases ON cases.parent_id = person.person_id AND cases.case_type_id = 'QFJD0000OL' /*residential*/ AND (cases.close_date >= 'apr 01 2003' /*If this date is greater than mar 31, 2004 we want to use mar 31, 2004*/ OR cases.close_date IS NULL)INNER JOIN response_date ON response_date.parent_id = cases.case_id AND response_date.question_id = 'QFJD0000VA' /* Date Active */ AND response_date.response_date <= 'mar 31, 2004' /*If this date is less than apr 01, 2003 we want to use apr 01, 2003*/Thanks.Lem |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2005-06-21 : 17:13:55
|
| Please post following these guidelines. This will make it much easier for us to understand your issue :)Thanks!http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
|
|
|
|
|
Counting Dates