November 18, 2011 at 7:46 pm
I am *way* so liking this. After however many months, I'm actually to the point of needing to play with this and was quite surprised at just how easy it was to get four separate records one for each date between two dates. I used a function that returns a table for simplicity since it was more of a play around with it thing. The immediate application will be a stored procedure that inserts records into a table.
The tally table starts at 0 and goes up to 100.
[Code]
CREATE TABLE [dbo].[TallyTable](
[Value] [int] NOT NULL,
CONSTRAINT [PK_TallyTable] PRIMARY KEY CLUSTERED
(
[Value] 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
[/code]
[Code]
Create FUNCTION [dbo].[TallyTableTest]
(
@StartDate date,
@EndDate date
)
RETURNS TABLE
AS
RETURN
(
Select Value, DATEADD("d", Value, @StartDate) As CalendarDate From TallyTable Where Value <= DateDiff("d", @StartDate, @EndDate)
)
GO
[/code]
SELECT * FROM TallyTableTest ('11/22/2011', '11/26/2011')
November 19, 2011 at 12:56 pm
shannonjk (7/21/2011)
Eh no worries! Being a DBA rarely people actually see my face so I get it all the time with the companies I work with :-D. Ironically enough I just did the same thing with a new employee named Jamie who also happens to be male :-D. The age of non-physical presence communication has some flaws apparently!
I know a Kim, Stacey, and a Shannon. I want to put together a male bowling team and call it "Girls Night Out" and the reaction of the other teams. Haven't done yet.
November 19, 2011 at 1:03 pm
Very cool feedback. Thanks, David.
As a side bar, if you meant for both dates in your example to be inclusive, you'll need to add "1" to the DATEDIFF.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2011 at 1:22 pm
Best Idea ever Lynn 😀
Link to my blog http://notyelf.com/
November 21, 2011 at 6:15 am
Jeff Moden (11/19/2011)
Very cool feedback. Thanks, David.As a side bar, if you meant for both dates in your example to be inclusive, you'll need to add "1" to the DATEDIFF.
I circumvented that by starting the Tally Table with a '0'.
November 24, 2011 at 9:43 am
That would do it! Thanks for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2012 at 2:16 pm
I just dropped it into production and it is looking very, very sweet. Not to mention that it saves me an immense amount of time. My specific need is the ability to create individual records for each date falling in between two given dates.
Now about building the ASP.NET front end...
CREATE TABLE [dbo].[sysNumbers](
[Value] [int] NOT NULL,
CONSTRAINT [PK_sysNumbers] PRIMARY KEY CLUSTERED
(
[Value] 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
INSERT INTO sysNumbers(Value) SELECT '0'
INSERT INTO sysNumbers(Value) SELECT '1'
INSERT INTO sysNumbers(Value) SELECT '2'
INSERT INTO sysNumbers(Value) SELECT '3'
INSERT INTO sysNumbers(Value) SELECT '4'
INSERT INTO sysNumbers(Value) SELECT '5'
INSERT INTO sysNumbers(Value) SELECT '6'
INSERT INTO sysNumbers(Value) SELECT '7'
INSERT INTO sysNumbers(Value) SELECT '8'
INSERT INTO sysNumbers(Value) SELECT '9'
INSERT INTO sysNumbers(Value) SELECT '0'
INSERT INTO sysNumbers(Value) SELECT '11'
INSERT INTO sysNumbers(Value) SELECT '12'
INSERT INTO sysNumbers(Value) SELECT '13'
INSERT INTO sysNumbers(Value) SELECT '14'
INSERT INTO sysNumbers(Value) SELECT '15'
INSERT INTO sysNumbers(Value) SELECT '16'
INSERT INTO sysNumbers(Value) SELECT '17'
INSERT INTO sysNumbers(Value) SELECT '18'
INSERT INTO sysNumbers(Value) SELECT '19'
INSERT INTO sysNumbers(Value) SELECT '20'
INSERT INTO sysNumbers(Value) SELECT '21'
INSERT INTO sysNumbers(Value) SELECT '22'
INSERT INTO sysNumbers(Value) SELECT '23'
INSERT INTO sysNumbers(Value) SELECT '24'
INSERT INTO sysNumbers(Value) SELECT '25'
INSERT INTO sysNumbers(Value) SELECT '26'
INSERT INTO sysNumbers(Value) SELECT '27'
INSERT INTO sysNumbers(Value) SELECT '28'
INSERT INTO sysNumbers(Value) SELECT '29'
INSERT INTO sysNumbers(Value) SELECT '30'
INSERT INTO sysNumbers(Value) SELECT '31'
GO
--Note that sysNumbers starts at 0 allowing the Start & End Dates to be used as-is without having to add 1 to get the correct number of days.
Create FUNCTION [dbo].[NumbersTable]
(@StartDate date,@EndDate date)
RETURNS TABLE AS
RETURN (Select Value, DATEADD("d", Value, @StartDate) As CalendarDate From sysNumbers Where Value <= DateDiff("d", @StartDate, @EndDate))
GO
CREATE PROCEDURE [dbo].[sp_ShowSetup_ShowDate_InsertBatch]
(
@ShowNumber varchar(8),
@DateCategoryId integer,
@StartDate date,
@EndDate date,
@MilestoneTimeStart time,
@MilestoneTimeEnd time,
@ConfirmationStatus varchar(4),
@Area varchar(30),
@Comment varchar(30)
)
AS
BEGIN
INSERT INTO ShowDates
([ShowNumber],[DateCategoryId],[MilestoneDate],[ConfirmationStatus],[MilestoneTimeStart],[MilestoneTimeEnd],[EntryUserId],[EntryDateTime])
SELECT @ShowNumber, @DateCategoryId, CalendarDate, @ConfirmationStatus, @MilestoneTimeStart, @MilestoneTimeEnd, SYSTEM_USER, SYSDATETIME()
FROM NumbersTable (@StartDate, @EndDate)
RETURN @@Error
END
CREATE TABLE [dbo].[ShowDates](
[ShowNumber] [varchar](8) NOT NULL,
[DateCategoryId] [smallint] NOT NULL,
[MilestoneDate] [date] NULL,
[ConfirmationStatus] [varchar](4) NOT NULL,
[MilestoneTimeStart] [time](0) NULL,
[MilestoneTimeEnd] [time](0) NULL,
[EntryUserId] [nvarchar](128) NULL,
[EntryDateTime] [datetime] NULL,
[ModifiedUserId] [nvarchar](128) NULL,
[ModifiedDateTime] [datetime] NULL,
[Id] [int] IDENTITY(1000,1) NOT NULL,
[Area] [varchar](30) NOT NULL,
[Comment] [varchar](30) NOT NULL)
GO
exec sp_ShowSetup_ShowDate_InsertBatch '000001', '5', '2/14/2012', '2/18/2012', '8:00 AM', '4:30 PM', '', '', ''
GO
January 6, 2012 at 7:03 pm
david.holley (1/6/2012)
I just dropped it into production and it is looking very, very sweet. Not to mention that it saves me an immense amount of time. My specific need is the ability to create individual records for each date falling in between two given dates.
Very cool. Thank you for the feedback.
I do have a question, though (I'm just curious). Why is it that you need to essentially duplicate rows to create a row for each date between the dates? I'm looking for the business or logical reason.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2012 at 12:16 pm
Its for a scheduling module. Each date can have different start/end times. Since the majority of the start/end times will be the same, the idea is to speed entry by adding them in bulk and then allowing the user to go back and then tweek as neccessary. As opposed to the user having to enter each and every date.
From a coolness factor, it would be a nice challenge to use a single record that represents the same start/end times for a range of dates which is then broken up as needed for the exceptions, but having to maintain the code would be hell if any changes needed to be made, not to mention the testing.
For the record, the date and times are stored in separate columns using the DATE or TIME datatypes as appropriate since not every date will have an associated start/end time nor will every date that has a 'start' time have and end time.
Examples:
50% Deposit Due 5/1/2012
Deposit Paid In Full 6/1/2012
On Site Pre Con 7/6/2012 4:00 PM
Welcome Reception 7/21/2012 6:00 PM - 8:00 PM
January 8, 2012 at 12:00 am
david.holley (1/7/2012)
Its for a scheduling module. Each date can have different start/end times. Since the majority of the start/end times will be the same, the idea is to speed entry by adding them in bulk and then allowing the user to go back and then tweek as neccessary. As opposed to the user having to enter each and every date.
Thanks for the feedback, David, but that's what I want to really know. Why does the scheduling module need to have the individual dates for each StartDate/EndDate pair? Calculating overlapping dates for scheduling purposes is pretty easy and a whole lot more effecient (although, admittedly, not as obvious), IMHO.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2012 at 11:37 am
It isn't staff scheduling/availablity oriented. Its more similar to the example of the various dates that I gave where overlap is entirely possible.
April 5, 2013 at 12:00 pm
Tally Tables are the bomb, and confirm my own intuitions regarding the power of set-based operations. Many thanks, Jeff!
Now I have another poser for you or anyone else - is there / would there be a way to somehow employ TT's to do the reverse? Meaning roll-up child records into a column of concatenated/delimited values for each parent record?
Disclaimer: I have an Access2010 project I'm trying to sort out, so I don't have recourse to SQL Server CTE or FOR XML Path options...
April 5, 2013 at 12:08 pm
jjturner (4/5/2013)
Tally Tables are the bomb, and confirm my own intuitions regarding the power of set-based operations. Many thanks, Jeff!Now I have another poser for you or anyone else - is there / would there be a way to somehow employ TT's to do the reverse? Meaning roll-up child records into a column of concatenated/delimited values for each parent record?
Disclaimer: I have an Access2010 project I'm trying to sort out, so I don't have recourse to SQL Server CTE or FOR XML Path options...
I don't know if you can do that with a TallyTable or if a TT is neccessary. This is a function that should accomplish it. It simply adds the next selected value to the previous. And no, I didn't figure this out myself.
USE [Mercury]
GO
/****** Object: UserDefinedFunction [dbo].[getShowContactsForDepartment_AsString] Script Date: 04/05/2013 14:03:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[getShowContactsForDepartment_AsString]
(
@ShowNumber varchar(8), @ProductionDepartmentId integer
)
RETURNS varchar(500)
AS
BEGIN
DECLARE @names VARCHAR(500)
SET @names = ''
SELECT
@names = @names + ', ' + NameInitials + Case WHEN ScheduleCommentShort Is Null THEN '' ELSE ' ' + ScheduleCommentShort End
FROM
vw_ShowContacts
WHERE
ShowNumber = @ShowNumber AND ProductionDepartmentId = @ProductionDepartmentId
ORDER BY
Id
--Contingent if @names is a zero-length string
IF Len(@names) > 0
BEGIN
SET @names = Right(@names,LEN(@names)-1)
End
RETURN @names
END
GO
April 5, 2013 at 12:17 pm
Thanks David - that was quick! I'll have a go at translating this into VBA and see what happens.
Although it does look like there's no way around firing a bunch of SELECT statements with a cursor (unless I'm misreading it)...
Cheers,
John
April 5, 2013 at 12:21 pm
Thanks David - that was quick! I'll have a go at translating this into VBA and see what happens.
Although it does look like there's no way around firing a bunch of SELECT statements with a cursor (unless I'm misreading it)...
Cheers,
John
Viewing 15 posts - 361 through 375 (of 511 total)
You must be logged in to reply to this topic. Login to reply