SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 return value of stored procedure based on rules
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

meetarun007
Starting Member

1 Posts

Posted - 05/04/2013 :  04:40:28  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 05/06/2013 :  02:35:33  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000