Problem in calculating missing periods

Question

Problem in calculating missing periods

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

lmu92 (2/14/2010)
Looks like I'm getting too addicted to CTEs... 😛

No... don't think that for a minute. The CTE's you wrote are a great exhibition of "Divide'n'Conquer". They run nasty fast, are relatively easy to convert to 2k derived tables (if needed), break the problem down into manageable, easy to troubleshoot "modules", and are easy to modify and test separately if the requirements for the code ever changes.

I was just showing a different way. Chris had talked about "periods" not necessarily being a given date. Rather, periods are defined by date ranges. I just showed a hybrid between what you had done and what Chris suggested.

As a sidebar, we all missed something, as well... the table uses a SMALLDATETIME and none of us converted the results of the date calculations to SMALLDATETIME so the criteria would actually be sargeable.

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

As a side bar, I probably wouldn't ever have a period table based on the last whole day of the month... if "times" ever creep into the statement table, then a whole day's worth of information can easily be missed. For the given problem, I'd probably anticipate the future and have a table like the following...

--===== Declare some control variables for building the mini-calendar table

DECLARE @StartMonthDate DATETIME,

@EndMonthDate DATETIME

;

SELECT @StartMonthDate = 'Jan 2000',

@EndMonthDate = 'Dec 2050'

;

--===== Ensure the control dates start at the beginning of their respective month's

SELECT @StartMonthDate = DATEADD(mm,DATEDIFF(mm,0,@StartMonthDate),0),

@EndMonthDate = DATEADD(mm,DATEDIFF(mm,0,@EndMonthDate),0)

;

WITH

cteTally AS

(

SELECT TOP (DATEDIFF(mm,@StartMonthDate,@EndMonthDate)+1)

ROW_NUMBER() OVER (ORDER BY GETDATE())-1 AS N

FROM Master.sys.All_Columns ac1

CROSS JOIN Master.sys.All_Columns ac2

)

SELECT ISNULL(DATEADD(mm,t.N ,@StartMonthDate) ,0) AS PeriodStart,

ISNULL(DATEADD(mm,t.N+1,@StartMonthDate) ,0) AS NextPeriodStart,

ISNULL(DATEADD(mm,t.N+1,@StartMonthDate)-1,0) AS DisplayPeriodDate

INTO dbo.Period

FROM cteTally t

;

--===== Because the table is so narrow, it won't cost much to turn the whole

-- thing into a nice clustered index like a Tally table would be.

ALTER TABLE dbo.Period

ADD CONSTRAINT PK_Period_Composite01

PRIMARY KEY CLUSTERED (PeriodStart, NextPeriodStart, DisplayPeriodDate)

WITH FILLFACTOR = 100

;

--===== Display the content of the new table

SELECT PeriodStart, NextPeriodStart, DisplayPeriodDate

FROM dbo.Period

ORDER BY PeriodStart

;

That would allow classic date joins that would never miss a thing even if times were introduced into the statment table...

ON s.Date >= p.PeriodStart

AND s.Date < p.NextPeriodStart

Think of it as a "guarantee" of correct joining to cover the improbable but still possible of times entering the statement table.

Just in case you're wondering, the ISNULL's in the formulas above make NOT NULL columns on the fly so they can become a part of the PK without the NULL column error popping up.

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

surya-434952 SSC Eights! Points: 952 More actions · Answer 3

Hi,

I got my problem solved by both Jeff and Chris solutions. I followed Jeff solution.

Thanks to all of you who spend their valuable time to me. I am very much thankful to Jeff and Chris

Surya

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Thanks for the feedback, Surya. I do recommend that you reconsider the form of your Periods table as I demonstrated. It will likely make your life a lot easier in the future.

--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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

surya-434952 SSC Eights! Points: 952 More actions · Answer 5

HI Jeff,

Thank you for your kind suggestion and help. Actually i am not using periods table its TIME Dimension and in IncomeStatement table i have no date i will stored TIMEID. I am storing monthdatekey as TIMEID in the IncomeStatement. Based upon your solution i tried i got solution.

Thanks,

Surya