August 27, 2013 at 2:35 pm
Using SQL Server 2008 R2, but also have access to SQL Server 2012
I have this sample data.
ItemNo DueDate Cham
121117 2013-09-05
121098 2013-09-05Y
333456 2013-09-07Y
347545 2013-09-07
798665 2013-09-07
982389 2013-09-08
908465 2013-09-08Y
985551 2013-09-09Y
432568 2013-09-09
874378 2013-09-10
647849 2013-09-10
098357 2013-09-10
673467 2013-09-13
098355 2013-09-13Y
237678 2013-09-13Y
984474 2013-09-17Y
093409 2013-09-17
003434 2013-09-18
The first sort must be on "Due Date". The second sort must be on "Cham". When days are spanned, I want to have the Cham field as continuous as possible. I realize this will not be possible for every day span. It seems the Cham field must be sometimes sorted Asc and other times sorted Desc.
Thanks.
August 27, 2013 at 2:42 pm
Patrick Womack (8/27/2013)
Using SQL Server 2008 R2, but also have access to SQL Server 2012I have this sample data.
ItemNo DueDate Cham
121117 2013-09-05
121098 2013-09-05Y
333456 2013-09-07Y
347545 2013-09-07
798665 2013-09-07
982389 2013-09-08
908465 2013-09-08Y
985551 2013-09-09Y
432568 2013-09-09
874378 2013-09-10
647849 2013-09-10
098357 2013-09-10
673467 2013-09-13
098355 2013-09-13Y
237678 2013-09-13Y
984474 2013-09-17Y
093409 2013-09-17
003434 2013-09-18
The first sort must be on "Due Date". The second sort must be on "Cham". When days are spanned, I want to have the Cham field as continuous as possible. I realize this will not be possible for every day span. It seems the Cham field must be sometimes sorted Asc and other times sorted Desc.
Thanks.
Please this as a create table statement and inserts. Also, if you could post what the desired output is based on the sample data it would help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2013 at 3:00 pm
Here is the create table and inserts. The desired output is in the original post. Thanks.
If Object_ID('tempdb..#Orders')Is Not Null
Drop Table #Orders;
Create Table #Orders
( ItemNoVarchar(15)
, DueDateDatetime
, ChamChar(1) )
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('003434', '9/18/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('985551', '9/9/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('098355', '9/13/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('333456', '9/7/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('121117', '9/5/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('647849', '9/10/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('874378', '9/10/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('798665', '9/7/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('984474', '9/17/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('908465', '9/8/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('982389', '9/8/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('121098', '9/5/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('098357', '9/10/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('093409', '9/17/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('673467', '9/13/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('347545', '9/7/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('237678', '9/13/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('432568', '9/9/2013' ,'')
August 27, 2013 at 4:10 pm
Piece of cake ... you can make it a lot more concise but this explains it easier:
WITH agg AS
(SELECT
DueDate,
ROW_NUMBER() OVER (ORDER BY DueDate) AS iRow,
SUM(CASE WHEN Cham = 'Y' THEN 1 ELSE 0 END) iCham,
SUM(CASE WHEN Cham = 'Y' THEN 0 ELSE 1 END) iNoCham,
CASE WHEN SUM(CASE WHEN Cham = 'Y' THEN 1 ELSE 0 END) * SUM(CASE WHEN Cham = 'Y' THEN 0 ELSE 1 END) = 0 THEN 0 ELSE 1 END iFlip
FROM
#orders
GROUP BY
DueDate)
, qOrder AS
(SELECT
agg.DueDate,
ISNULL(SUM(agg2.iFlip),0) % 2 As iDir
FROM
agg
LEFT JOIN agg agg2 ON agg.DueDate > agg2.DueDate
GROUP BY
agg.DueDate)
SELECT
*
FROM
#orders o
INNER JOIN qOrder qo ON o.DueDate = qo.DueDate
ORDER BY
o.DueDate,
CASE qo.iDir WHEN 0 THEN 1 ELSE -1 END * CASE o.Cham WHEN 'Y' THEN 2 ELSE 1 END
August 28, 2013 at 4:17 am
Not bad at all, busraker. Here's a slight mod which I think is a little easier to figure out, but the logic I think is the same as yours:
;WITH CountedData AS (
SELECT
rn = ROW_NUMBER() OVER(ORDER BY DueDate)%2,
DueDate
FROM #orders o
WHERE o.Cham = 'Y'
GROUP BY DueDate
)
SELECT o.*
FROM #orders o
LEFT JOIN CountedData c ON c.DueDate = o.DueDate
CROSS APPLY (
SELECT OrderBy = CASE
WHEN c.rn = 1 AND o.Cham = 'Y' THEN 2
WHEN c.rn = 1 AND o.Cham <> 'Y' THEN 1
WHEN c.rn = 0 AND o.Cham = 'Y' THEN 1
WHEN c.rn = 0 AND o.Cham <> 'Y' THEN 2
ELSE 9 END
) x
ORDER BY DueDate, x.OrderBy
I've tested using a slightly extended sample data set:
If Object_ID('tempdb..#Orders')Is Not Null Drop Table #Orders;
Create Table #Orders (ItemNo Varchar(15), DueDate Datetime, Cham Char(1))
SET DATEFORMAT MDY
Insert Into #Orders (ItemNo, DueDate, Cham)
Values
('121116', '9/4/2013' ,''),
('121117', '9/5/2013' ,''),
('121098', '9/5/2013' ,'Y'),
('333456', '9/7/2013' ,'Y'),
('798665', '9/7/2013' ,''),
('347545', '9/7/2013' ,''),
('982389', '9/8/2013' ,''),
('908465', '9/8/2013' ,'Y'),
('985551', '9/9/2013' ,'Y'),
('432568', '9/9/2013' ,''),
('647849', '9/10/2013' ,''),
('874378', '9/10/2013' ,''),
('098357', '9/10/2013' ,''),
('673467', '9/13/2013' ,''),
('098355', '9/13/2013' ,'Y'),
('237678', '9/13/2013' ,'Y'),
('984474', '9/17/2013' ,'Y'),
('093409', '9/17/2013' ,''),
('003434', '9/18/2013' ,'Y'),
('003435', '9/18/2013' ,'Y'),
('003436', '9/18/2013' ,'Y');
CREATE UNIQUE CLUSTERED INDEX ucx_ItemNo ON #orders (ItemNo);
CREATE INDEX ix_DueDate ON #orders (DueDate) INCLUDE (Cham);
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 28, 2013 at 10:34 am
Your solution would run much faster (my code must test every previous day in order to determine the proper sort order, and you rely on a case statement) but I don't think your solution would work in all cases ... for example this test data:
If Object_ID('tempdb..#Orders')Is Not Null
Drop Table #Orders;
Create Table #Orders
( ItemNoVarchar(15)
, DueDateDatetime
, ChamChar(1) )
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('003434', '9/18/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('985551', '9/9/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('098355', '9/13/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('333456', '9/7/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('121117', '9/5/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('647849', '9/10/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('874378', '9/10/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('798665', '9/7/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('984474', '9/17/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('908465', '9/8/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('982389', '9/8/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('121098', '9/5/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('098357', '9/10/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('093409', '9/17/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('673467', '9/13/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('347545', '9/7/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('237678', '9/13/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('432568', '9/9/2013' ,'')
On 9/13 it fails, for the "Cham" should be sorted with "Y" first and then the blank. With all of the possibilities of days with all "Y" and no "Y", I think my method of adding up the number of times on previous days that the order must "flip" is necessary.
August 29, 2013 at 1:21 am
busraker (8/28/2013)
Your solution would run much faster (my code must test every previous day in order to determine the proper sort order, and you rely on a case statement) but I don't think your solution would work in all cases ... for example this test data:
If Object_ID('tempdb..#Orders')Is Not Null
Drop Table #Orders;
Create Table #Orders
( ItemNoVarchar(15)
, DueDateDatetime
, ChamChar(1) )
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('003434', '9/18/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('985551', '9/9/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('098355', '9/13/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('333456', '9/7/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('121117', '9/5/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('647849', '9/10/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('874378', '9/10/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('798665', '9/7/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('984474', '9/17/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('908465', '9/8/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('982389', '9/8/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('121098', '9/5/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('098357', '9/10/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('093409', '9/17/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('673467', '9/13/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('347545', '9/7/2013' ,'')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('237678', '9/13/2013' ,'Y')
Insert Into #Orders
( ItemNo, DueDate, Cham )
Values ('432568', '9/9/2013' ,'')
On 9/13 it fails, for the "Cham" should be sorted with "Y" first and then the blank. With all of the possibilities of days with all "Y" and no "Y", I think my method of adding up the number of times on previous days that the order must "flip" is necessary.
Another model I looked at counted the number of cham = 'Y' on the previous and next row and assigned the sort direction of the current group so that cham = 'Y' was aligned next to whichever had the highest count. Think about that for a moment - you also have to take into account the direction of the previous row. The code worked ok, but made it quite clear that with a larger sample set and sufficiently skewed ratios of values of cham in the groups, this could get very tricky.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 29, 2013 at 10:18 am
I definitely agree that without knowing the scope of PCC's business requirement for aligning the "Cham" field across days, we can not present an optimal solution.
I guess the way I thought of it was that, say, you had a machine that created two variations of a product. The normal version and the "With Cham" version, and it was very expensive to alter the machine to go from one product to the other, but alas, you had to produce the orders by due date. So in a given date span, you would need to alter the machine as absolutely few times as possible. That is what my code achieves.
But you are correct in that if PCC just needs to cluster the Cham field then comparing each order date with the surrounding dates would be the way to go.
It's a fun SQL brain exercise either way!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply