October 1, 2015 at 10:11 am
Hi
I'm sure there's a really simple answer to this but I'm really struggling, similar questions have been solved before but I'm getting desperate now so hoping someone can help. I've mocked up a table with example data, the real-life scenario is not quite like this, I'm just trying to get a working example to explain what I'm struggling with.
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data. I've even tried cursors thinking I could at least achieve what I need but that failed too so I'm having a really bad day!
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[CustID] [float] NULL,
[OrderDate] [date] NULL,
[FeeDate] [date] NULL,
[CancelledDate] [date] NULL,
[FeeInvalid] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[MockData] ([OrderID], [CustID], [OrderDate], [FeeDate], [CancelledDate], [FeeInvalid]) VALUES (2835692, 24643, CAST(0x87380B00 AS Date), CAST(0xBF380B00 AS Date), CAST(0xFB380B00 AS Date), N'N')
GO
INSERT [dbo].[MockData] ([OrderID], [CustID], [OrderDate], [FeeDate], [CancelledDate], [FeeInvalid]) VALUES (2874041, 24643, CAST(0xBF380B00 AS Date), CAST(0xD9380B00 AS Date), CAST(0xFD380B00 AS Date), N'N')
GO
INSERT [dbo].[MockData] ([OrderID], [CustID], [OrderDate], [FeeDate], [CancelledDate], [FeeInvalid]) VALUES (2886912, 24643, CAST(0xCC380B00 AS Date), CAST(0xE3380B00 AS Date), CAST(0x03390B00 AS Date), N'N')
GO
INSERT [dbo].[MockData] ([OrderID], [CustID], [OrderDate], [FeeDate], [CancelledDate], [FeeInvalid]) VALUES (2907787, 24643, CAST(0xE0380B00 AS Date), CAST(0xF7380B00 AS Date), CAST(0x64390B00 AS Date), N'N')
GO
INSERT [dbo].[MockData] ([OrderID], [CustID], [OrderDate], [FeeDate], [CancelledDate], [FeeInvalid]) VALUES (3030021, 62439, CAST(0x74390B00 AS Date), CAST(0x84390B00 AS Date), NULL, N'N')
GO
INSERT [dbo].[MockData] ([OrderID], [CustID], [OrderDate], [FeeDate], [CancelledDate], [FeeInvalid]) VALUES (3091595, 62439, CAST(0xC0390B00 AS Date), CAST(0xCF390B00 AS Date), NULL, N'N')
GO
INSERT [dbo].[MockData] ([OrderID], [CustID], [OrderDate], [FeeDate], [CancelledDate], [FeeInvalid]) VALUES (2933028, 72581, CAST(0xFE380B00 AS Date), CAST(0x12390B00 AS Date), CAST(0x1F390B00 AS Date), N'N')
GO
INSERT [dbo].[MockData] ([OrderID], [CustID], [OrderDate], [FeeDate], [CancelledDate], [FeeInvalid]) VALUES (3168608, 72581, CAST(0x313A0B00 AS Date), CAST(0x5A3A0B00 AS Date), CAST(0x5E3A0B00 AS Date), N'N')
GO
Thanks in advance
October 1, 2015 at 4:08 pm
For the given data in MockTable, what are the expected results?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 1, 2015 at 4:29 pm
If I understand your requirements correctly, I think that this will do the job.
Since you posted in a SQL 2012 forum, I used features available in 2012.
DECLARE @MockData TABLE (
[OrderID] [float] NULL,
[CustID] [float] NULL,
[OrderDate] [date] NULL,
[FeeDate] [date] NULL,
[CancelledDate] [date] NULL,
[FeeInvalid] [char](1) NULL
)
INSERT @MockData ([OrderID], [CustID], [OrderDate], [FeeDate], [CancelledDate], [FeeInvalid])
VALUES (2835692, 24643, CAST(0x87380B00 AS Date), CAST(0xBF380B00 AS Date), CAST(0xFB380B00 AS Date), 'N'),
(2874041, 24643, CAST(0xBF380B00 AS Date), CAST(0xD9380B00 AS Date), CAST(0xFD380B00 AS Date), 'N'),
(2886912, 24643, CAST(0xCC380B00 AS Date), CAST(0xE3380B00 AS Date), CAST(0x03390B00 AS Date), 'N'),
(2907787, 24643, CAST(0xE0380B00 AS Date), CAST(0xF7380B00 AS Date), CAST(0x64390B00 AS Date), 'N'),
(3030021, 62439, CAST(0x74390B00 AS Date), CAST(0x84390B00 AS Date), NULL, 'N'),
(3091595, 62439, CAST(0xC0390B00 AS Date), CAST(0xCF390B00 AS Date), NULL, 'N'),
(2933028, 72581, CAST(0xFE380B00 AS Date), CAST(0x12390B00 AS Date), CAST(0x1F390B00 AS Date), 'N'),
(3168608, 72581, CAST(0x313A0B00 AS Date), CAST(0x5A3A0B00 AS Date), CAST(0x5E3A0B00 AS Date), 'N');
WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY OrderID) AS OrderNum,
LAG(CancelledDate, 1) OVER (PARTITION BY CustID ORDER BY OrderID) AS PriorCancelledDate,
LAG(OrderDate, 1) OVER (PARTITION BY CustID ORDER BY OrderID) AS PriorOrderDate
FROM @MockData
)
UPDATE cte
SET FeeInvalid =
CASE WHEN OrderNum = 1 THEN 'N'
WHEN FeeDate < PriorCancelledDate THEN 'Y'
WHEN OrderDate > PriorOrderDate THEN 'Y'
WHEN PriorCancelledDate IS NULL THEN 'Y'
ELSE 'N'
END;
SELECT *
FROM @MockData;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 2, 2015 at 3:44 am
Hi Wayne
That's beautiful, Thank you!
I've also learnt something as I had no idea the LAG function existed. I've replicated the concept into my actual scenario and am achieving exactly what I wanted.
Thank you very much for the solution.
Regards
Adrian
October 2, 2015 at 8:25 am
WADRIAN68 (10/2/2015)
Hi WayneThat's beautiful, Thank you!
I've also learnt something as I had no idea the LAG function existed. I've replicated the concept into my actual scenario and am achieving exactly what I wanted.
Thank you very much for the solution.
Regards
Adrian
Adrian,
Glad it's working for you.
If you have questions about how LAG works, check out Books Online or grab a copy of my book (see link below in my signature).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply