Nested CASE in SELECT query, Help? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2005)

Nested CASE in SELECT query, Help?

Author

Topic

no_tofu_speed
Starting Member

6 Posts

Posted - 2008-08-29 : 15:20:26

This question is about a stored procedure that is an extension on the last one I asked help about, which just displayed the current status of each item.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109707
(Thanks for the help Vinnie881)

This stored procedure is designed to:
Show the status history of each item. Displaying the changes in ‘status’ followed by the ‘status date’, for a specified item by an item identifying parameter.

Orders of status are:
Request > Authorized > Order > Delivered
Or
Request > Denied

In the case of ‘Cancelled’
A Cancel can occur after Authorized or it can occur after Order
Request > Authorized > Cancelled
Or
Request > Authorized > Order > Cancelled
This is the reason why for the nested CASE with an EXIST (not sure if this can be done).
To test if the process is cancelled after Authorized, or if it was cancelled after an Order (if an Order for the item exists), and displays the appropriate history, to include an Order status and Date if it has one, before a Cancel status and date.

[Req_status = 'Requested']
Create new field called Req_status displaying the text ‘Requested’, in that position in the code.
(I do not remember how to)

All dates want to keep their display column name as their original column name in the tables... hence.
r.Req_date AS Req_date
[Exception Ord_cancel_date AS Canc_date]

Any help would be appreciated thankyou..

no_tofu_speed
Starting Member

6 Posts

Posted - 2008-08-29 : 15:22:11

CREATE PROCEDURE usp_Read_request_status_history
@item_Ident nvarchar(12)
AS

SELECT i.Item_id, i.Title,

CASE

WHEN r.Status = 'Requested' THEN
[Req_status = 'Requested'], r.Req_date AS Req_date

WHEN r.Status = 'Authorized' THEN(
SELECT top 1 [Req_status = 'Requested'], r.Req_date AS Req_date, [Auth_status = 'Authorized'], au.Auth_date AS Auth_date
FROM
dbo.Request r, dbo.Auth_request au
WHERE r.Item_id = au.Item_id)

WHEN r.Status = 'Denied' THEN (
SELECT top 1 [Req_status = 'Requested'], r.Req_date AS Req_date, [Deny_status = 'Denied'], dr.Deny_date AS Deny_date
FROM
dbo.Request r, dbo.Deny_request dr
WHERE r.Item_id = dr.Item_id)

WHEN r.Status = 'Ordered' THEN(
SELECT top 1 [Req_status = 'Requested'], r.Req_date AS Req_date, [Auth_status = 'Authorized'], au.Auth_date AS Auth_date, [Ord_status = 'Ordered'], o.Order_date AS Order_date
FROM
dbo.Request r, dbo.Auth_request au, dbo.[Order] o
WHERE r.Item_id = o.Item_id
AND r.Item_id = au.Item_id)

WHEN r.Status = 'Cancelled' THEN(
SELECT top 1 Req_status = 'Requested'], r.Req_date AS Req_date, [Auth_status = 'Authorized'], au.Auth_date AS Auth_date
CASE
WHEN EXISTS
(SELECT o.Item_id
FROM dbo.[Order] o
WHERE o.Item_id = @item_Ident)
THEN
[Ord_status = 'Ordered'], dbo.[Order].Order_date,
FROM
dbo.[Order] o
WHERE r.Item_id = o.Item_id
END
[Can_status = 'Cancelled'], oc.Ord_cancel_date AS Canc_date
FROM
dbo.Request r, dbo.Auth_request au, dbo.Order_cancel oc
WHERE r.Item_id = au.Item_id
AND r.Item_id = oc.Item_id)

WHEN r.Status = 'Delivered' THEN(
SELECT top 1 Req_status = 'Requested'], r.Req_date AS Req_date, [Auth_status = 'Authorized'], au.Auth_date AS Auth_date, [Ord_status = 'Ordered'], o.Order_date AS Order_date, [Del_status = 'Delivered'], ar.Arrival_date AS Arrival_date
FROM
dbo.Request r, dbo.Auth_request au, dbo.[Order] o, dbo.Arrival ar
WHERE r.Item_id = ar.Item_id
AND r.Item_id = o.Item_id
AND r.Item_id = au.Item_id)

END

FROM
dbo.Item I INNER JOIN
dbo.Request r
ON i.Item_id = r.Item_id
WHERE r.Item_id = @item_Ident

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-30 : 02:27:54

give your table sample data and also sample output

no_tofu_speed
Starting Member

6 Posts

Posted - 2008-08-30 : 06:04:06

Depending on the item_id given as a paramter, the result will be either one of these.
(The top line is the column names, bottom line is displayed data. The "Status" fields before each date have to be created (I do not remember how to create a new column name)

Item_id, Title, Req_status, Req_date, Deny_status, Deny_Date
item001, Java Basics, Requested, 1/03/2008 12:00:00 AM, Denied, 2/03/2008 12:00:00 AM

Item_id, Title, Req_status, Req_date, Auth_status, Auth_date, Cancel_status, Canc_date
item002, Advanced VB.NET, Requested, 3/03/2008 12:00:00 AM, Authorized, 4/03/2008 12:00:00 AM, Cancelled, 6/03/2008 12:00:00 AM

Item_id, Title, Req_status, Req_date, Auth_status, Auth_date, Ord_status, Order_date, Cancel_status, Canc_date
item003, Studio Sound Effects, Requested, 4/03/2008 12:00:00 AM, Authorized, 5/03/2008 12:00:00 AM, Ordered, 30/03/2008 12:00:00 AM, Cancelled, 5/04/2008 12:00:00 AM

Item_id, Title, Req_status, Req_date, Auth_status, Auth_date, Ord_status Order_date
item004, Multimedia Productions, Requested, 9/03/2008 12:00:00 AM, Authorized, 10/03/2008 12:00:00 AM, Ordered, 30/03/2008 12:00:00 AM

Item_id, Title, Req_status, Req_date, Auth_status, Auth_date, Ord_status, Order_date,
item005, System Methodogies, Requested, 9/03/2008 12:00:00 AM, Authorized, 10/03/2008 12:00:00 AM, Ordered, 30/03/2008 12:00:00 AM

Item_id, Title, Req_status, Req_date, Auth_status, Auth_date, Ord_status, Order_date, Del_status, Arrival_date
item006, e-Business - a Technological View, Requested, 10/03/2008 12:00:00 AM, Authorized, 11/03/2008 12:00:00 AM, Ordered, 30/03/2008 12:00:00 AM, Delivered, 20/04/2008 12:00:00 AM

Item_id, Title, Req_status, Req_date, Auth_status, Auth_date, Ord_status, Order_date, Del_status, Arrival_date
item007, United Process Methodology - extensive guide, Requested, 12/03/2008 12:00:00 AM, Authorized, 13/03/2008 12:00:00 AM, Ordered, 30/03/2008 12:00:00 AM, Delivered, 22/04/2008 12:00:00 AM

Item_id, Title, Req_status, Req_date, Auth_status, Auth_date
item008, SQL Server 2005 - intermediate, Requested, 13/03/2008 12:00:00 AM, Authorized, 14/03/2008 12:00:00 AM

no_tofu_speed
Starting Member

6 Posts

Posted - 2008-08-30 : 06:04:33

And here is a .bak file of my database.

http://www.2shared.com/file/3847344/ff4386ea/INFT3009_ASS1_C2110574.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources