December 22, 2015 at 2:25 am
I have the following issue (using SQL Server 2008)...
Table is as follows tblVisit - VisitID int, ExpectedStartDate DateTime, VisitNumber varchar(20)
Script to set up table is as follows...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblVisit](
[VisitID] [int] IDENTITY(1,1) NOT NULL,
[ExpectedStartDate] [datetime] NULL,
[VisitNumber] [varchar](20) NULL,
CONSTRAINT [PK_tblVisit] PRIMARY KEY CLUSTERED
(
[VisitID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [FK_tblVisit_ExpectedStartDate] ON [dbo].[tblVisit]
(
[ExpectedStartDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
As part of a trigger on a Visit table I need to keep the VisitNumber in sequence when an ExpectedStartDate is changed. The VisitNumber is constructed of the Year from the ExpectedStartDate following by a ' - ' then a padded with a zero two digit sequence number. For example:-
2015 - 01
2015 - 02
2015 - 03
2016 - 01
2016 - 02
...
2016 - 12
2016 - 13
2016 - 14...
Note that the end 2 digit number does not come from the month of the date since there may be multiple visits on the same month or even same day.
At the moment I am using a cursor (I know, not ideal) and this is slow to execute especially if there are multiple changes to visit's ExpectedStartDate.
What the cursor does is order the data ascending by Expected Start Date. Each record increments the two digit number part by 1 until the year changes at which point the 2 digit numeric part goes back to 1 and starts incrementing again.
Cursor is as follows...
DECLARE @TempVisitID INT
DECLARE @TempExpectedStartDate DATETIME
DECLARE @TempFriendlyVisitNumber VARCHAR(20)
DECLARE @Counter INT = 0
DECLARE @LastYear INT = 0
DECLARE ID_Cursor_Visit_Inner1 CURSOR FOR SELECT A.[VisitID],
ISNULL(A.[ExpectedStartDate], '1900-01-01')
FROM [tblVisit] A
WHERE ISNULL(A.[ExpectedStartDate], '1900-01-01') > '1900-01-01'
ORDER BY A.[ExpectedStartDate]
OPEN ID_Cursor_Visit_Inner1
FETCH NEXT FROM ID_Cursor_Visit_Inner1 INTO @TempVisitID, @TempExpectedStartDate
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Counter = @Counter + 1
IF @LastYear = 0
BEGIN
SET @LastYear = DATEPART(YEAR, @TempExpectedStartDate)
END
ELSE IF DATEPART(YEAR, @TempExpectedStartDate) != @LastYear -- Year changed so set counter back to 1 --
BEGIN
SET @Counter = 1
SET @LastYear = DATEPART(YEAR, @TempExpectedStartDate)
END
SET @TempFriendlyVisitNumber = CAST(DATEPART(YEAR, @TempExpectedStartDate) AS VARCHAR(10)) + ' - ' + REPLICATE('0', 2 - LEN(@Counter)) + CAST(@Counter AS VARCHAR(10))
UPDATE A
SET A.[VisitNumber] = @TempFriendlyVisitNumber
FROM [tblVisit] A
WHERE A.[VisitID] = @TempVisitID
AND ISNULL(A.[VisitNumber], '') != @TempFriendlyVisitNumber
FETCH NEXT FROM ID_Cursor_Visit_Inner1 INTO @TempVisitID, @TempExpectedStartDate
END
CLOSE ID_Cursor_Visit_Inner1
DEALLOCATE ID_Cursor_Visit_Inner1
Can anyone help me change this from a cursor to a regular update statement.
Thanks for reading. 🙂
December 22, 2015 at 2:58 am
So if the ExpectedDate changes, for example, from 2015-12-01 to 2016-01-11 then you could find yourselves renumbering the visits for two years - do I understand that correctly? What happens if there are more than 99 visits in a year? You can use the ROW_NUMBER function to build a sequence of numbers partitioned by year. You might also consider creating a view over this table instead of going to the expense of making physical updates every time a date changes. If you provide some sample data (INSERT statements, please), we should be able to help out with a tested solution.
John
December 22, 2015 at 3:06 am
Something along these lines should do the trick, however I'd recommend you don't do it. If the number you are storing with the date of the visit is of any real use to you, then each time you need to use it, you will have to extract it out from the string. I'd recommend you store only the visit number, as a number, and forget about the year because you can get that from the ExpectedStartDate.
WITH OrderedData AS (
SELECT *,
rn = ROW_NUMBER() OVER (PARTITION BY x.YearVisit ORDER BY ExpectedStartDate)
FROM tblVisit
CROSS APPLY (SELECT YearVisit = YEAR(ExpectedStartDate)) x
WHERE ExpectedStartDate IS NOT NULL AND ExpectedStartDate > '19000101'
)
SELECT *,
NewValue = CAST(YearVisit AS VARCHAR(10)) + ' - ' + RIGHT('00' + rn,2)
FROM OrderedData
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
December 22, 2015 at 3:41 am
John Mitchell-245523 (12/22/2015)
So if the ExpectedDate changes, for example, from 2015-12-01 to 2016-01-11 then you could find yourselves renumbering the visits for two years - do I understand that correctly? What happens if there are more than 99 visits in a year? You can use the ROW_NUMBER function to build a sequence of numbers partitioned by year. You might also consider creating a view over this table instead of going to the expense of making physical updates every time a date changes. If you provide some sample data (INSERT statements, please), we should be able to help out with a tested solution.John
Yes, if the year changes then I would be re-ordering 2 years. At the moment, we only have a maximum of 99 visits in a year - in reality, we are only likely to have a max of about 60 but I'll probably extend it to take a max of 999 per year.
December 22, 2015 at 3:54 am
ChrisM@Work (12/22/2015)
Something along these lines should do the trick, however I'd recommend you don't do it. If the number you are storing with the date of the visit is of any real use to you, then each time you need to use it, you will have to extract it out from the string. I'd recommend you store only the visit number, as a number, and forget about the year because you can get that from the ExpectedStartDate.
WITH OrderedData AS (SELECT *,
rn = ROW_NUMBER() OVER (PARTITION BY x.YearVisit ORDER BY ExpectedStartDate)
FROM tblVisit
CROSS APPLY (SELECT YearVisit = YEAR(ExpectedStartDate)) x
WHERE ExpectedStartDate IS NOT NULL AND ExpectedStartDate > '19000101'
)
SELECT *,
NewValue = CAST(YearVisit AS VARCHAR(10)) + ' - ' + RIGHT('00' + rn,2)
FROM OrderedData
Many thanks Chris :-), I will give this a go. Regards your concern about using the value for any real use, it is only a friendly number used for the contractors so it doesn't matter too much.
December 22, 2015 at 4:15 am
That works well Chris,
The only bit which doesn't work is the padding of the number to two digits with zero. I replaced your RIGHT('00' + rn, 2)
With
REPLICATE('0', 2 - LEN(rn)) + CAST(rn AS VARCHAR(10))
and it works. Not too sure why the RIGHT clause doesn't do it...
Many thanks for taking the time to post the code.
December 22, 2015 at 9:10 am
Charlottecb (12/22/2015)
Regards your concern about using the value for any real use, it is only a friendly number used for the contractors so it doesn't matter too much.
If that's true, then why store the number at all? Just generate it during reporting. If you store it, the contractors will use it in a dependent fashion even if you tell them not to. It's the nature of the beast.
--Jeff Moden
December 22, 2015 at 10:45 am
Piling on Chris's and Jeff's good points, no need to persist that number, easily done in a query.
😎
Some sample data
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.tblVisit') IS NOT NULL DROP TABLE dbo.tblVisit;
CREATE TABLE dbo.tblVisit(
VisitID INT IDENTITY(1,1) NOT NULL
,ExpectedStartDate DATETIME NULL
,VisitNumber VARCHAR(20) NULL
,CONSTRAINT PK_tblVisit PRIMARY KEY CLUSTERED ( VisitID ASC )
);
DECLARE @MINDATE DATETIME = '2015-01-01';
DECLARE @SAMPLE_SIZE INT = 1000;
INSERT INTO dbo.tblVisit(ExpectedStartDate)
SELECT TOP(@SAMPLE_SIZE)
DATEADD(HOUR,CHECKSUM(NEWID()) % 87660,@MINDATE) -- 87660 = 10 YEARS
FROM sys.all_columns SAC1
CROSS JOIN sys.all_columns SAC2
CROSS JOIN sys.all_columns SAC3
;
Quick query suggestion
USE TEEST;
GO
SET NOCOUNT ON;
;WITH BASE_DATA AS
(
SELECT
TV.VisitID
,TV.ExpectedStartDate
,CONVERT(VARCHAR(4),YEAR(TV.ExpectedStartDate),0) + CHAR(32) + CHAR(45) + CHAR(32) + CHAR(48) + CHAR(48) AS BASE_YEAR
,CONVERT(VARCHAR(4),ROW_NUMBER() OVER
(
PARTITION BY YEAR(TV.ExpectedStartDate)
ORDER BY TV.ExpectedStartDate
),0) AS COUNT_VISIT
FROM dbo.tblVisit TV
)
SELECT
BD.VisitID
,BD.ExpectedStartDate
,STUFF(BD.BASE_YEAR
,1 + (LEN(BD.BASE_YEAR) - LEN(BD.COUNT_VISIT))
,LEN(BD.COUNT_VISIT)
,BD.COUNT_VISIT
) AS VisitNumber
FROM BASE_DATA BD
;
Sample output
VisitID ExpectedStartDate VisitNumber
----------- ----------------------- -----------
197 2005-01-01 17:00:00.000 2005 - 01
950 2005-01-10 01:00:00.000 2005 - 02
763 2005-01-15 06:00:00.000 2005 - 03
14 2005-01-28 16:00:00.000 2005 - 04
121 2005-01-30 03:00:00.000 2005 - 05
738 2005-01-30 05:00:00.000 2005 - 06
532 2005-02-06 15:00:00.000 2005 - 07
200 2005-02-06 23:00:00.000 2005 - 08
515 2005-02-11 10:00:00.000 2005 - 09
876 2005-03-12 04:00:00.000 2005 - 10
383 2005-03-25 02:00:00.000 2005 - 11
155 2005-03-30 05:00:00.000 2005 - 12
960 2005-04-03 21:00:00.000 2005 - 13
965 2005-04-04 07:00:00.000 2005 - 14
497 2005-04-12 23:00:00.000 2005 - 15
213 2005-04-14 19:00:00.000 2005 - 16
920 2005-04-16 19:00:00.000 2005 - 17
141 2005-05-11 20:00:00.000 2005 - 18
169 2005-05-13 04:00:00.000 2005 - 19
417 2005-05-27 07:00:00.000 2005 - 20
December 22, 2015 at 1:23 pm
ChrisM@Work (12/22/2015)
Something along these lines should do the trick, however I'd recommend you don't do it. If the number you are storing with the date of the visit is of any real use to you, then each time you need to use it, you will have to extract it out from the string. I'd recommend you store only the visit number, as a number, and forget about the year because you can get that from the ExpectedStartDate.
WITH OrderedData AS (SELECT *,
rn = ROW_NUMBER() OVER (PARTITION BY x.YearVisit ORDER BY ExpectedStartDate)
FROM tblVisit
CROSS APPLY (SELECT YearVisit = YEAR(ExpectedStartDate)) x
WHERE ExpectedStartDate IS NOT NULL AND ExpectedStartDate > '19000101'
)
SELECT *,
NewValue = CAST(YearVisit AS VARCHAR(10)) + ' - ' + RIGHT('00' + rn,2)
FROM OrderedData
You need to CAST the rn to a string before trying to concatenate it to the string '00', otherwise it's going to convert the string '00' to the integer 0.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 22, 2015 at 2:06 pm
That's great guys - thanks for all your help on this.
Many many thanks. 😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply