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)
 return value of stored procedure based on rules

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 order

NOTE:

- 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: BRAZIL

SAMPLE 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 flat

something like

DECLARE @RowsAffected int

UPDATE TOP(1) f1
SET OwnerName = @YourName
FROM FLATMASTER f1
JOIN FLATMASTER f2
ON f2.Floor = f1.Floor
AND f2.FlatNo = f1.FlatNo + 1
INNER OWNERMASTER o
ON o.OWNER = f1.OWNERNAME
WHERE o.NATIONALITY = @Yournationality
AND f2.IsOccupied = 'No'

SET @RowsAffected = @@ROWCOUNT

IF @RowsAffected =0
BEGIN
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 = 0
END

SET @RowsAffected = @@ROWCOUNT

IF @RowsAffected =0
BEGIN
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 = 0

END

SET @RowsAffected = @@ROWCOUNT

IF @RowsAffected =0
BEGIN

UPDATE TOP(1) f1
SET OwnerName = @YourName
FROM FLATMASTER f1
WHERE f2.IsOccupied = 'NO'

END


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -