Reputation: 247880
I found an issue yesterday with either my table design or a query that I pull data from and I need some guidance/suggestions on how to proceed and fix my problem.
My table is as follows:
CREATE TABLE
[DT].[CountTotals](
[Acct] [varchar](8) NOT NULL,
[TCount] [smallint] NOT NULL ,
[TDate] [smalldatetime] NOT NULL,
[PostDate] [smalldatetime] NOT NULL ,
[DayCount] [tinyint] NULL ,
[AggCount] [smallint] NULL ,
[AggNumber] [numeric](10, 0) NULL,
[IsReadyToArchive] [bit] NOT NULL
)
I pull this data using the following view:
CREATE VIEW [DT].[vwGetCountHistory]
AS
SELECT
C.Acct, C.TCount, C.TDate, C.PostDate
, C.DayCount, C.AggCount, C.AggNumber
FROM DT.CountTotals C
UNION
SELECT
AC.Acct, AC.TCount, AC.TDate, AC.PostDate
, AC.DayCount, AC.AggCount, AC.AggNumber
FROM ARCHIVE.DTA.CountTotals AC
All of that works fine. The table has about 300k records total records including about 5000 new records added to it each day, and the PostDate is the date the records are added. My issue is coming from the field DayCount - this field gets increased by 1 each day it is in the table, until the PostDate is < GetDate()-90
. My problem is in the following query:
SELECT
Acct
, SUM(AggCount) AS SumofAggCount
FROM DT.vwGetCountHistory
WHERE (DayCount <= 5)
AND
(
PostDate >= @BusinessDate - 90
AND PostDate <= @BusinessDate
)
GROUP BY Acct
HAVING (SUM(AggCount) >= 4)
This query works wonderfully if I am searching for the current date because the DayCount would be correct. But stupid me forgot that they can search for the data from previous days so my DayCount will have increased each day since the PostDate.
I am looking for suggestions about how to remedy this problem. Is there a way to determine the correct daycount?
Upvotes: 1
Views: 103
Reputation: 48934
I generally agree with @n8wrl that DayCount should most likely not be stored and should be calculated on the fly, but if that is not an option, then at the very least make it a Computed Column where it is a DATEDIFF between the PostDate and GETDATE() and get rid of that nightly process. Of course, that would still be, in effect, processing it on the fly but I don't think that the performance hit would be bad given the potential risk of the nightly job not running or running twice, etc.
Now, for the immediate need, can you just make the DayCount test more dynamic, such as:
WHERE (DayCount <= (5 + DATEDIFF(DAY, @BusinessDate, PostDate)))
Doing this we add 1 to the condition so that it would be DayCount <= 6 as yesterday's qualifying records now have a DayCount of 6.
Upvotes: 0
Reputation: 19765
Rather than store & manage DayCount, can you compute the number of days between @BusinessDate and PostDate in your query?
Upvotes: 1