W.E.B
asked on
SQL 2008
Hello,
I'm using this Stored Procedure for one of my clients.
Select report from (
Select
'DETAIL,' + case when ao.Instructions like 'Return%' then 'Y' else 'N' end
+ ao.DeliveryCompanyName + ',' + ao.POD +
+ Case ao.StatusID
WHEN 4 THEN 'X6'
WHEN 5 THEN 'X4'
else ''
end
+ 'NS'+ ',' + case WHEN (coalesce(CONVERT(char(8), si.ShipmentDate, 112),'')) Like '' then coalesce(CONVERT(char(8), ao.OrderDate, 112),'') else coalesce(CONVERT(char(8), si.ShipmentDate, 112),'') end
+ case WHEN (COALESCE(RIGHT (si.BillingTimeSTamp ,6), ' ')) Like '' then (Select replace(convert(varchar, getdate(),108),':','')) else COALESCE(RIGHT (si.BillingTimeSTamp ,6), ' ') end
+ coalesce(CONVERT(char(20), (Select Name From Drivers where DriverNumber = ao.PickupDriver)),'')
AS [Report]
,ROW_NUMBER() over (partition by
si.BSN , ao.Reference order by si.BillingTimeSTamp) as rn
From Database01.[dbo].[ActiveOr ders] as ao
LEFT join Database02.[dbo].[ScanItem ] as si
on si.CCOrderNo = ao.Orderno
where ao.Accountnumber = 12345 AND ao.Instructions like 'Return%'
) as x
where rn=1
I need to add another case / if :into
+ Case ao.StatusID
WHEN 4 THEN 'X6'
WHEN 5 THEN 'X4'
else ''
end
CASE ao.Instructions like '%NO ONE HOME%' then ao.StatusID = 'AP'
Any help is appreciated.
I'm using this Stored Procedure for one of my clients.
Select report from (
Select
'DETAIL,' + case when ao.Instructions like 'Return%' then 'Y' else 'N' end
+ ao.DeliveryCompanyName + ',' + ao.POD +
+ Case ao.StatusID
WHEN 4 THEN 'X6'
WHEN 5 THEN 'X4'
else ''
end
+ 'NS'+ ',' + case WHEN (coalesce(CONVERT(char(8),
+ case WHEN (COALESCE(RIGHT (si.BillingTimeSTamp ,6), ' ')) Like '' then (Select replace(convert(varchar, getdate(),108),':','')) else COALESCE(RIGHT (si.BillingTimeSTamp ,6), ' ') end
+ coalesce(CONVERT(char(20),
AS [Report]
,ROW_NUMBER() over (partition by
si.BSN , ao.Reference order by si.BillingTimeSTamp) as rn
From Database01.[dbo].[ActiveOr
LEFT join Database02.[dbo].[ScanItem
on si.CCOrderNo = ao.Orderno
where ao.Accountnumber = 12345 AND ao.Instructions like 'Return%'
) as x
where rn=1
I need to add another case / if :into
+ Case ao.StatusID
WHEN 4 THEN 'X6'
WHEN 5 THEN 'X4'
else ''
end
CASE ao.Instructions like '%NO ONE HOME%' then ao.StatusID = 'AP'
Any help is appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that was easy.