Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««3536373839»»»

The "Numbers" or "Tally" Table: What it is and how it replaces a loop. Expand / Collapse
Author
Message
Posted Friday, November 18, 2011 7:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 13, 2014 11:51 AM
Points: 115, Visits: 223
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.


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


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



SELECT * FROM TallyTableTest ('11/22/2011', '11/26/2011')


Post #1208755
Posted Saturday, November 19, 2011 12:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:11 PM
Points: 23,003, Visits: 31,494
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 . Ironically enough I just did the same thing with a new employee named Jamie who also happens to be male . 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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1208845
Posted Saturday, November 19, 2011 1:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 36,735, Visits: 31,185
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1208846
Posted Saturday, November 19, 2011 1:21 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:36 PM
Points: 586, Visits: 822
Best Idea ever Lynn

Link to my blog http://notyelf.com/
Post #1208849
Posted Monday, November 21, 2011 6:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 13, 2014 11:51 AM
Points: 115, Visits: 223
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'.
Post #1209208
Posted Thursday, November 24, 2011 9:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 36,735, Visits: 31,185
That would do it! Thanks for the feedback.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1211701
Posted Friday, January 6, 2012 2:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 13, 2014 11:51 AM
Points: 115, Visits: 223
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



Post #1231743
Posted Friday, January 6, 2012 7:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 36,735, Visits: 31,185
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1231799
Posted Saturday, January 7, 2012 12:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 13, 2014 11:51 AM
Points: 115, Visits: 223
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




Post #1231959
Posted Sunday, January 8, 2012 12:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:07 PM
Points: 36,735, Visits: 31,185
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1232017
« Prev Topic | Next Topic »

Add to briefcase «««3536373839»»»

Permissions Expand / Collapse