Assistance Needed with Returning Records

[Michael Cole]

/****** Object: Table [dbo].[Order] Script Date: 05/28/2013
18:24:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[Order]') AND type in (N'U'))
DROP TABLE [dbo].[Order]
GO


CREATE TABLE [dbo].[Order](
[OrderNo] [varchar](50) NULL,
[OrderDate] [date] NULL,
[CancelledDate] [date] NULL,
[Amount] [money] NULL
) ON [PRIMARY]

GO

INSERT INTO [Order] ([OrderNo], [OrderDate], [CancelledDate], [Amount])
VALUES ('007', '1/01/2013 12:00:00 AM', '1/02/2013 12:00:00 AM',
300.0000);
INSERT INTO [Order] ([OrderNo], [OrderDate], [CancelledDate], [Amount])
VALUES ('063', '1/01/2013 12:00:00 AM', NULL, 6000.0000);
INSERT INTO [Order] ([OrderNo], [OrderDate], [CancelledDate], [Amount])
VALUES ('063', '1/02/2013 12:00:00 AM', '1/03/2013 12:00:00 AM',
6200.0000);
INSERT INTO [Order] ([OrderNo], [OrderDate], [CancelledDate], [Amount])
VALUES ('065', '1/01/2013 12:00:00 AM', NULL, 450.0000);
INSERT INTO [Order] ([OrderNo], [OrderDate], [CancelledDate], [Amount])
VALUES ('065', '1/02/2013 12:00:00 AM', NULL, 480.0000);

Note - this is data from another system - please no complaints about
the structure.

The way that this should work is that if an order is reissued, then a
new record is created with a new order date and a new amount, but when
providing a summation for a date range, the date of the first issue is
used regardless of the date of the reissue. The Cancellation date is
recorded on its actual date.

Some examples (with notes on reasons for each line): -

Range of 1/1/2013 to 1/4/2013
007 - [No record] [Issued and cancelled in the date range]
063 - [No record] [Issued and cancelled in the date range]
065 - 1/1/2013 - 480 [Issued in the date range, date is first issued
date, amount is last issued amount]

Range of 1/1/2013 to 1/2/2013
007 - 1/1/2013 - 300 [Issued in the date range, date is first
issued date, amount is last issued amount, not cancelled in the date
range]
063 - 1/1/2013 - 6200 [Issued in the date range, date is first
issued date, amount is last issued amount, not cancelled in the date
range]
065 - 1/1/2013 - 480 [Issued in the date range, date is first issued
date, amount is last issued amount]


Range of 1/2/2013 to 1/3/2013
007 - 1/2/2013 - -300 [Not issued in the date range but was
cancelled, date is cancelled date, amount is negative issued amount]
063 - [No record] [Issued prior - cancelled after - no changes
in this period. The reissue within this period doesn't count as it
goes to the original issue date]
065 - [No record] [Not issued in the date range (that it was
reissued in the date range is not counted)]

Range of 1/3/2013 to 1/4/2013
007 - [No record]
063 - 4/2/2013 - -6200 [Not issued in the date range but was
cancelled, date is cancelled date, amount is negative issued amount]
065 - [No record]


Note that records with a 0 amount do not need to (and probably
shouldn't) be listed in the resultset.

Does this make sense? I'm sue the solution should be simple, but it's
not clicking for me.

[Erland Sommarskog]

Post by Michael Cole
The way that this should work is that if an order is reissued, then a
new record is created with a new order date and a new amount, but when
providing a summation for a date range, the date of the first issue is
used regardless of the date of the reissue. The Cancellation date is
recorded on its actual date.
Some examples (with notes on reasons for each line): -

Your examples and your sample data is difficult to read, because the
date format is ambiguous, and the description does not seem to match

Post by Michael Cole
Range of 1/1/2013 to 1/2/2013
007 - 1/1/2013 - 300 [Issued in the date range, date is first
issued date, amount is last issued amount, not cancelled in the date
range]

No matter the range is intended to be from 2013-01-01 to 2013-02-01
or something else, I can't see that this order should be included,
as it was both issue and cancelled in the rante.

Post by Michael Cole
063 - 4/2/2013 - -6200 [Not issued in the date range but was
cancelled, date is cancelled date, amount is negative issued amount]

And whatever date that is, it does not appear in the sample data.

Anyway, from your narrative and descriptions, I arrived at this query,
but you need to test it further:

DECLARE @start date = '20130102',
@end date = '20130103'

; WITH issuedates AS (
SELECT OrderNo, MIN(OrderDate) AS IssueDate
FROM [Order]
GROUP BY OrderNo
HAVING MIN(OrderDate) BETWEEN @start AND @end
AND coalesce(MAX(CancelledDate), '99991231') > @end
), amounts AS (
SELECT i.OrderNo, i.IssueDate, O.Amount,
row_number() OVER(PARTITION BY i.OrderNo ORDER BY O.OrderDate
DESC) AS rowno
FROM [Order] O
JOIN issuedates i ON O.OrderNo = i.OrderNo
), cancellations AS (
SELECT O.OrderNo, O.CancelledDate, O.Amount
FROM [Order] O
WHERE O.CancelledDate BETWEEN @start AND @end
AND NOT EXISTS (SELECT *
FROM [Order] O2
WHERE O.OrderNo = O2.OrderNo
GROUP BY O2.OrderNo
HAVING MIN(O2.OrderDate) >= @start)
)
SELECT OrderNo, IssueDate, Amount
FROM amounts
WHERE rowno = 1
UNION ALL
SELECT OrderNo, CancelledDate, -Amount
FROM cancellations
ORDER BY OrderNo

[Michael Cole]

Post by Erland Sommarskog
Anyway, from your narrative and descriptions, I arrived at this query,

Apologies for the issues with the dates, but thank you, as your
interpretations were correct and the query did work.

Thanks.