Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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  
 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.06 seconds. Powered By: Snitz Forums 2000