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 |
meetarun007
Starting Member
1 Post |
Posted - 2013-05-04 : 04:40:28
|
I need to write a stored procedure to get value based on the following tables. I am filling a apartment flats according to the nationality of the buyiers. The Stored procedure has to return next Non-Occupied flat based on the following rules: - If a new entry comes, I need to suggest the non-occupied flat which is next to the occupied-flat having the same nationality of new person - If no match found for above condition, then allocate first flat in a floor where no flats were occupied - If no match found for above condition, then allocate a flat which is having at-least two empty flats on both sides - If no match found for above condition, then allocate the first flat which is empty according to the sort orderNOTE: - Each flat is identified by combination of Floor & Flat No - Sort order for flat and floors while searching Should be from 1 To n SAMPLE INPUT: NAME: RANDY NATIONALITY: BRAZILSAMPLE OUTPUT: FLOOR:1 FLAT NO: 4 (w.r.t. to the attached image)please help to write the procedure. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-06 : 02:35:33
|
what you need is a set of update statements based on your logic to allocate the flatsomething likeDECLARE @RowsAffected intUPDATE TOP(1) f1SET OwnerName = @YourNameFROM FLATMASTER f1JOIN FLATMASTER f2ON f2.Floor = f1.FloorAND f2.FlatNo = f1.FlatNo + 1INNER OWNERMASTER oON o.OWNER = f1.OWNERNAMEWHERE o.NATIONALITY = @YournationalityAND f2.IsOccupied = 'No'SET @RowsAffected = @@ROWCOUNTIF @RowsAffected =0BEGIN UPDATE TOP(1) f1 SET OwnerName = @YourName FROM FLATMASTER f1 JOIN (SELECT Floor,COUNT(CASE WHEN IsOccupied='YES' THEN FlatNo END) AS Cnt FROM FLATMASTER GROUP BY Floor)f2 ON f2.Floor = f1.Floor WHERE f2.Cnt = 0ENDSET @RowsAffected = @@ROWCOUNTIF @RowsAffected =0BEGIN UPDATE TOP(1) f1 SET OwnerName = @YourName FROM FLATMASTER f1 CROSS APPLY(SELECT COUNT(CASE WHEN IsOccupied='YES' THEN FlatNo END) AS Cnt FROM FLATMASTER WHERE Floor = f1.Floor AND FlatNo >= f1.FlatNo - 2 AND FlatNo <= f1.FlatNo + 2 AND FlatNo <> f1.FlatNo)f2 WHERE f2.Cnt = 0ENDSET @RowsAffected = @@ROWCOUNTIF @RowsAffected =0BEGIN UPDATE TOP(1) f1 SET OwnerName = @YourName FROM FLATMASTER f1 WHERE f2.IsOccupied = 'NO'END ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|