If I run this sql statement:
SELECT order_numb, stat = case
when status = 'N/S' then 'zN/S'
when status = 'ready' then '0Ready'
else cast('a' + status as varchar(13))
end
FROM rptCS_Sched
I get a result like this:
04017.0023 zN/S
04023.0559 0Ready
04127.0311 aJan 21 2005
etc
So far so good...
Now I want to remove the left hand letter in that status column,
and I try nesting the query, like this:
select order_numb, right(max(stat),len(max(stat))-1) as Status from (
SELECT order_numb, stat = case
when status = 'N/S' then 'zN/S'
when status = 'ready' then '0Ready'
else cast('a' + status as varchar(13))
end
FROM rptCS_Sched
) s
group by order_numb
Now I get:
04017.0023 N/S
04023.0559 Ready
04127.0311 an 21 2005
For some reason, it removes 2 characters when there is a date contained in the string. Any body have any ideas?Post the Table DDL|||CREATE TABLE [dbo].[rptCS_Sched] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Order_Numb] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[product] [varchar] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tons] [real] NOT NULL ,
[status] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[bill_to_numb] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cust_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[request_date] [smalldatetime] NULL ,
[LoadNum] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ItemStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchedID] [bigint] NULL ,
[Sched_Ord_Status] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO|||Not for me...the whole thing doesn't make sense though...
Do you have a real world example?
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE [dbo].[myrptCS_Sched] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Order_Numb] [varchar] (10) ,
[status] [varchar] (13)
)
GO
INSERT INTO myrptCS_Sched (Order_Numb, status)
SELECT '04017.0023', 'N/S' UNION ALL
SELECT '04023.0559', 'Ready' UNION ALL
SELECT '04127.0311', 'Jan 21 2005'
GO
SELECT order_numb
, RIGHT(MAX(stat),LEN(MAX(stat))-1) as Status
FROM (
SELECT order_numb
, stat = CASE WHEN status = 'N/S' THEN 'zN/S'
WHEN status = 'ready' THEN '0Ready'
ELSE cast('a' + status as varchar(13))
END
FROM myrptCS_Sched
) s
GROUP BY order_numb
GO
SET NOCOUNT OFF
DROP TABLE myrptCS_Sched
GO|||The table is a base recordset for a report. It gets truncated and repopulated hourly. The table shows the production status of each item on an order, so an order number can occur multiple times on the report (once for each item).
For each item, there is one of three statuses(?):
N/S = Not Scheduled
Ready = Product is already in inventory
Date = The date that production is scheduled for the item.
Now I've been asked to show an order summary on the report, and let the users drill into the detail only if they need to. I decided to add a column to the table (Sched_Ord_Status), and go back and populate that column after the rest of the table has been populated. I need it to show the "worst case" status for the order. N/S being worst, and Ready being best. If everything on the order is Ready, except for one item, which is N/S, then the order status is N/S.|||If it helps at all, the initial sql I posted is part of this statement:
update rptCs_Sched set sched_ord_status = d.Status
from rptcs_sched inner join (
select order_numb, right(max(stat),len(max(stat))-1) as Status from (
SELECT order_numb, stat = case
when status = 'N/S' then 'zN/S'
when status = 'ready' then '0Ready'
else cast('a' + status as varchar(13))
end
FROM rptCS_Sched
) s
group by order_numb) d on rptcs_sched.order_numb = d.order_numb|||Well did you cut and paste my code to see that the sympton you describe is not happening with the code I posted?
Noe how about the DDL for the other table and some DML to populate it with some sample data like I did...
