CODEWITHSUNDEEP
sql-serverdate
Taryn
Taryn

Reputation: 247880

TSQL Determine Correct Day

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

Answers (2)

Solomon Rutzky
Solomon Rutzky

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

n8wrl
n8wrl

Reputation: 19765

Rather than store & manage DayCount, can you compute the number of days between @BusinessDate and PostDate in your query?

Upvotes: 1

Related Questions