August 24, 2014 at 2:21 am
In a recent post I was trying to find a way to check that dates in a set of records were sequential. And Lynn Petis and Sean Lange came up with the code I needed - which is:
CREATE TABLE #tblTasks
(
TaskID int,
TaskDate smalldatetime,
OrderID int
)
GO
INSERT INTO #tblTasks (TaskID, TaskDate, OrderID)
SELECT 10, 'Jan 01 2014', 1 UNION ALL
SELECT 11, 'Jan 06 2014', 2 UNION ALL
SELECT 12, 'Jan 06 2014', 3 UNION ALL
SELECT 13, 'Jan 16 2014', 4
;WITH BaseData as (
SELECT TaskID
,TaskDate
,OrderID
,ROW_NUMBER() OVER (ORDER BY OrderID ASC) AS [OrderSeq]
,ROW_NUMBER() OVER(ORDER BY TaskDate ASC) AS [TrueOrderID]
FROM #tblTasks
)
SELECT * FROM BaseData
WHERE OrderSeq <> TrueOrderID
But, I've just realised that although this allows me to determine if the dates in a group of records are in order, my requirement includes making sure that no dates occur on the same day. In the data above, two dates are both Jan 06 2014 - I need to be able to catch this too.
So, I have done this (below), but I'm hoping there is a better way of doing it.
CREATE TABLE #tblTasks
(
TaskID int,
TaskDate smalldatetime,
OrderID int
)
GO
DECLARE @num int = 0
INSERT INTO #tblTasks (TaskID, TaskDate, OrderID)
SELECT 10, 'Jan 06 2014', 1 UNION ALL
SELECT 11, 'Jan 06 2014', 2 UNION ALL
SELECT 12, 'Jan 06 2014', 3 UNION ALL
SELECT 13, 'Jan 09 2014', 3 UNION ALL
SELECT 14, 'Jan 10 2014', 3 UNION ALL
SELECT 15, 'Jan 11 2014', 3 UNION ALL
SELECT 16, 'Jan 16 2014', 4
;WITH BaseData as (
SELECT TaskID
,TaskDate
,OrderID
,ROW_NUMBER() OVER (ORDER BY OrderID ASC) AS [OrderSeq]
,ROW_NUMBER() OVER(ORDER BY TaskDate ASC) AS [TrueOrderID]
FROM #tblTasks
)
--see if BaseData has any records
SELECT @num = COUNT(*) FROM BaseData
WHERE (OrderSeq <> TrueOrderID)
SELECT 'After checking sequence we have found ' + Cast(@num as varchar(10)) + ' records'
--if BaseData has no records, check again, grouping by date
IF @num = 0
BEGIN
;WITH BaseData1 as (
SELECT
Count(TaskDate) AS [numSame]
FROM #tblTasks
GROUP BY(TaskDate)
HAVING COUNT(TaskDate) > 1
)
SELECT @num = [numSame] FROM BaseData1
SELECT 'After checking same dates we have found ' + Cast(@num as varchar(10)) + ' records'
END
DROP TABLE #tblTasks:
August 24, 2014 at 3:17 am
You can use the function rank and compare its outcome to the function row_number. Here is an example based on your script:
CREATE TABLE #tblTasks
(
TaskID int,
TaskDate smalldatetime,
OrderID int
)
GO
DECLARE @num int = 0
DECLARE @Num2 int = 0
INSERT INTO #tblTasks (TaskID, TaskDate, OrderID)
SELECT 10, 'Jan 06 2014', 1 UNION ALL
SELECT 11, 'Jan 06 2014', 2 UNION ALL
SELECT 12, 'Jan 06 2014', 3 UNION ALL
SELECT 13, 'Jan 09 2014', 3 UNION ALL
SELECT 14, 'Jan 10 2014', 3 UNION ALL
SELECT 15, 'Jan 11 2014', 3 UNION ALL
SELECT 16, 'Jan 16 2014', 4
;WITH BaseData as (
SELECT TaskID
,TaskDate
,OrderID
,ROW_NUMBER() OVER (ORDER BY OrderID ASC) AS [OrderSeq]
,ROW_NUMBER() OVER(ORDER BY TaskDate ASC) AS [TrueOrderID]
,RANK() OVER (ORDER BY TaskDate ASC) as DateRank
FROM #tblTasks
)
--see if BaseData has any records
SELECT @num = SUM(CASE WHEN OrderSeq <> TrueOrderID THEN 1 ELSE 0 END),
@Num2 = SUM(CASE WHEN DateRank <> TrueOrderID THEN 1 ELSE 0 END)
FROM BaseData
select @num, @Num2
go
drop table #tblTasks
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 24, 2014 at 4:01 am
Thank you very much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply