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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy