SQL Loop - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

SQL Loop

Author

Topic

richardwaugh
Starting Member

36 Posts

Posted - 2011-11-21 : 14:43:51

Hello. I would like to attempt some kind of loop in SQL but unsure of how to go about this. Basically I have a table that contains placement informtation. Each record contains an id of a person that is in charge of that placement. So a person can have multiple placements. What I would like to do is the following:

Pull all of the most current placements (placements that don't have an end date yet). I would then like to check to see if there has been a gap between the Start Date of the most recent placement and the End Date of the previous placement. If there hasn't been a gap and the name of the placement (i.e. House Name) hasn't changed, then treat this as one placement (i.e. do not use the start date of the most recent placement but instead use the start date of the previous placement).

I'd basically like to do this for all of the people until they either a) don't have previous placements or b) there is a gap between the start and end dates or c) if the name of the house has changed.

Any ideas?

Thanks in advance,

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2011-11-21 : 14:55:40

Do a self join to the table you are looking at. If there is an ID and placement order field it would be easier. A layout of your table would be easier to work with. The final solution is not too difficult from there.

-Derek

richardwaugh
Starting Member

36 Posts

Posted - 2011-11-21 : 15:09:24

Hello Derek,

The table structure is as follows:

PlacementID (PK, int, not null)
AdmissionID (int, not null)
FamilyID (int, not null)
PersonID (int, not null)
StartDate (datetime, not null)
EndDate (datetime, null)

To clarify the criteria I want to do the loop until either
a) No more placements for that person
b) There is a gap between the dates of the placement
c) The FamilyID changes

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 01:29:41

so what should be your output? Can you show output format for some sample data from your tables

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

richardwaugh
Starting Member

36 Posts

Posted - 2011-11-24 : 11:06:27

Hello visakh,

Sample output could be the following:

PersonID-FamilyID-StartDate-EndDate
102-1234-12/9/2004-NULL

The table itself may contain the following:

PlacementID-AdmissionID-FamilyID-PersonID-StartDate-EndDate
1-123-1234-102-12/09/2004-4/25/2007
2-123-1234-102-4/25/2007-NULL

Hope this helps.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources