SQL Server Performance

Date Calculation Issue

Discussion in 'SQL Server 2005 General Developer Questions' started by sqlderby, Dec 4, 2010.

  1. sqlderby Member

    Hi All
    I am calculating date range using getdate () for current date and time. And that is from 10:00 pm previous day to 10:00 pm of current day and time.
    For example: get current date and time using getdate() and then
    SET @DTTo = datediff(d,0,@DT)
    SET @DTFrom = datediff(d,0,(dateadd(d,-1,@DTTo)))
    IF (@nStartHour is not null)
    BEGIN
    SET @DTFrom = dateadd(hh,@nStartHour,@DTFrom)
    SET @DTTo = dateadd(hh,@nStartHour,@DTTo)
    END
    where @nStartHour is a value come from table (e.g. 22 hours). It means that start date ( DTFrom) should be 20101203 22:00 and end date wil be 20101204 22:00 as suppose getdate() is 20101204 5:00 am. Now data should be coming for the Real Time report should be from 20101203 22:00 pm to 201012043 22:00.
    As per above scenario I am facing issue is that when midnight cross (00:01) the date become 04th dec 2010 and according to above logic date range change and become 20101204 22:00 pm to 20101205 pm rather should be 20101203 22:00 pm to 201012043 22:00. As result data is not reflecting as 20101204 22:00 is not reached yet.
    How should I solve this issue? so for the same day date always should be same.
    Pls help
    Thanks
  2. FrankKalis Moderator

    Not sure if I understand you correctly, but this might give you some ideas:
    DECLARE @TheHour int;
    DECLARE @TheDate datetime;
    DECLARE @TheBaseDate datetime;

    DECLARE @T TABLE (
    DTFrom datetime
    );

    INSERT INTO @T SELECT '20101203 22:15'
    UNION ALL SELECT '20101204 00:01'

    SELECT
    @TheDate = '20101204 5:00 am',
    @TheHour = 22;

    SELECT
    @TheBaseDate = DATEADD(DAY, DATEDIFF(DAY, 0, @TheDate), -1)

    SELECT
    *,
    DATEADD(HOUR, @TheHour, (DATEADD(DAY, DATEDIFF(DAY, 0, DTFrom), DATEDIFF(DAY, DTFrom, @TheBaseDate))))
    FROM
    @T;

    SELECT
    @TheDate, @TheBaseDate
    @TheDate in my case would be the result from GETDATE(). @TheBaseDate goes back one day, but strips off the time.
    This then
    DATEADD(HOUR, @TheHour, (DATEADD(DAY, DATEDIFF(DAY, 0, DTFrom), DATEDIFF(DAY, DTFrom, @TheBaseDate))))
    always adjusts the value in DTFrom to what whatever you get back from @TheBaseDate and then adds whatever you send in for @TheHour.
    The result is:
    DTFrom
    2010-12-03 22:15:00.000 2010-12-03 22:00:00.000
    2010-12-04 00:01:00.000 2010-12-03 22:00:00.000

    (2 row(s) affected)


    2010-12-04 05:00:00.000 2010-12-03 00:00:00.000

    (1 row(s) affected)

    Hope I understood you correctly.
  3. sqlderby Member

    OK here it is code that I am using ...I can't insert static value. getdate() will be initially value and we need to calculating from it. Calculation going correct until a next date value come. for example...
    gedate()= '06-12-2010 09:10' and
    calculation will be: '05-12-2010 22:00' to 06-12-2010 22:00' but if when 12:00 am happen on 06-12-2010 then value will change to...
    06-12-2010 22:00 to 07-12-2010 22:00 and data before 06-12-2010 22:00 never publish.
    Date range always need to be between previous day from 22:00 pm to current date (getdate()) of 22:00 PM.
    Here it is the Code:
    -------------------------------------------------------------------------------------------------------
    DECLARE @iLoop int,@iLoopTill int
    DECLARE
    @DT datetime,
    @DTFrom datetime,
    @DTTo datetime,
    @nTimeDifferencePST int,
    @nStartHour int,
    @SkillGroupID int,
    @MaxDT datetime

    DECLARE @Tbl_FH_SkillGroup TABLE (ID int identity(1,1),SkillGroupID int)

    SET NOCOUNT ON

    SET @DT = GETDATE() -- In IST

    insert into @Tbl_FH_SkillGroup(SkillGroupID) values(433)
    insert into @Tbl_FH_SkillGroup(SkillGroupID) values(682)
    insert into @Tbl_FH_SkillGroup(SkillGroupID) values(683)
    insert into @Tbl_FH_SkillGroup(SkillGroupID) values(684)
    insert into @Tbl_FH_SkillGroup(SkillGroupID) values(688)
    insert into @Tbl_FH_SkillGroup(SkillGroupID) values(692)
    insert into @Tbl_FH_SkillGroup(SkillGroupID) values(693)
    insert into @Tbl_FH_SkillGroup(SkillGroupID) values(702)


    -- @nTimeDifferencePST value is 48600 secs behind IST now
    SELECT @nTimeDifferencePST = DiffrenceValue from TimeZoneDiffrenceData where TimeZone = 'PST' and
    (@DT between TimeZoneDiffrenceData.StartDate and TimeZoneDiffrenceData.EndDate)


    SET @DTTo = datediff(d,0,(dateadd(ss,@nTimeDifferencePST,@DT)))
    SET @DTFrom = datediff(d,0,(dateadd(d,-1,@DTTo)))

    SET @DTTo = datediff(d,0,@DT)
    SET @DTFrom = datediff(d,0,(dateadd(d,-1,@DTTo)))

    SET @iLoop = 1
    SELECT @iLoopTill = count(*) from @Tbl_FH_SkillGroup


    WHILE (@iLoop <= @iLoopTill)
    BEGIN

    SELECT @SkillGroupID = SkillGroupID FROM @Tbl_FH_SkillGroup WHERE (ID = @iLoop)

    SELECT @nStartHour = StartTime FROM SkillGroupMaster WHERE SkillGroupID = @SkillGroupID


    SET @DTTo = datediff(d,0,@DT)
    SET @DTFrom = datediff(d,0,(dateadd(d,-1,@DTTo))


    IF (@nStartHour is not null)
    BEGIN
    SET @DTFrom = dateadd(hh,@nStartHour,@DTFrom)
    SET @DTTo = dateadd(hh,@nStartHour,@DTTo)
    END

    set @MaxDT = null
    SELECT @MaxDT = max(Half_Hour_Beginning) FROM Tbl_FH_CP_HH_Handled WHERE (SkillGroupID = @SkillGroupID)


    IF (@MaxDT is not null)
    BEGIN
    DELETE FROM Tbl_FH_CP_HH_Handled WHERE (SkillGroupID = @SkillGroupID) and (Half_Hour_Beginning = @MaxDT)

    -- INSERT INTO Tbl_FH_CP_HH_Handled
    -- (
    -- Half_Hour_Beginning, DNIS, Handled, BadCalls, LocalQTime, TalkTime, HoldTime, WorkTime, SLCalls, ShortCallLess20S, CallWitin1200S,
    -- ShortCall_300S, ShortCall_M_300S, ShortCall301_600S, ShortCall601_1800S, ShortCall1800S, SL_300S, SL_M_300S, SL301_600S, SL601_1800S,
    -- SL1800S, Bad_120S, Bad_300S, Bad_M_300S, Bad301_600S, Bad601_1800S, Bad1800S, SLBadCalls, MaxQTime, AvgQTime, Call_1200S,
    -- ShortCallsHandled, SortCallsBad, Bad_5S, Bad5_S, CallsIn_10, Bad_10S, Bad10_S, Handled5s_Abd, Handled_10s, Handled_20s, Handled_30s,
    -- Bad_30S_greater,DateTime,SkillGroupID
    -- )
    SELECT
    Half_Hour_Beginning, DNIS, Handled, BadCalls, LocalQTime, TalkTime, HoldTime, WorkTime, SLCalls, ShortCallLess20S, CallWitin1200S,
    ShortCall_300S, ShortCall_M_300S, ShortCall301_600S, ShortCall601_1800S, ShortCall1800S, SL_300S, SL_M_300S, SL301_600S, SL601_1800S,
    SL1800S, Bad_120S, Bad_300S, Bad_M_300S, Bad301_600S, Bad601_1800S, Bad1800S, SLBadCalls, MaxQTime, AvgQTime, Call_1200S,
    ShortCallsHandled, SortCallsBad, Bad_5S, Bad5_S, CallsIn_10, Bad_10S, Bad10_S, Handled5s_Abd, Handled_10s, Handled_20s, Handled_30s,
    Bad_30S_greater,dateadd(d,0,datediff(d,0,Half_Hour_Beginning)),@SkillGroupID
    FROM dbo.Handled_FH_CP_RealTime (@DTFrom,@DTTo,@SkillGroupID)
    WHERE Half_Hour_Beginning >= @MaxDT
    END
    ELSE
    BEGIN
    -- INSERT INTO Tbl_FH_CP_HH_Handled
    -- (
    -- Half_Hour_Beginning, DNIS, Handled, BadCalls, LocalQTime, TalkTime, HoldTime, WorkTime, SLCalls, ShortCallLess20S, CallWitin1200S,
    -- ShortCall_300S, ShortCall_M_300S, ShortCall301_600S, ShortCall601_1800S, ShortCall1800S, SL_300S, SL_M_300S, SL301_600S, SL601_1800S,
    -- SL1800S, Bad_120S, Bad_300S, Bad_M_300S, Bad301_600S, Bad601_1800S, Bad1800S, SLBadCalls, MaxQTime, AvgQTime, Call_1200S,
    -- ShortCallsHandled, SortCallsBad, Bad_5S, Bad5_S, CallsIn_10, Bad_10S, Bad10_S, Handled5s_Abd, Handled_10s, Handled_20s, Handled_30s,
    -- Bad_30S_greater,[DateTime],SkillGroupID
    -- )
    SELECT
    Half_Hour_Beginning, DNIS, Handled, BadCalls, LocalQTime, TalkTime, HoldTime, WorkTime, SLCalls, ShortCallLess20S, CallWitin1200S,
    ShortCall_300S, ShortCall_M_300S, ShortCall301_600S, ShortCall601_1800S, ShortCall1800S, SL_300S, SL_M_300S, SL301_600S, SL601_1800S,
    SL1800S, Bad_120S, Bad_300S, Bad_M_300S, Bad301_600S, Bad601_1800S, Bad1800S, SLBadCalls, MaxQTime, AvgQTime, Call_1200S,
    ShortCallsHandled, SortCallsBad, Bad_5S, Bad5_S, CallsIn_10, Bad_10S, Bad10_S, Handled5s_Abd, Handled_10s, Handled_20s, Handled_30s,
    Bad_30S_greater,dateadd(d,0,datediff(d,0,Half_Hour_Beginning)),@SkillGroupID
    FROM dbo.Handled_FH_CP_RealTime (@DTFrom,@DTTo,@SkillGroupID)
    END

    SET @iLoop = @iLoop + 1

    END

    SET NOCOUNT OFF
    ---------------------------------------------------------------------------------
    Thanks a lot for help
  4. FrankKalis Moderator

    This will give you yesterday's date at 22:00:00 and today's date at 22:00:00
    DECLARE @TheHour int;
    DECLARE @TheDate datetime;

    SELECT
    @TheDate = GETDATE(),
    @TheHour = 22;


    SELECT
    DATEADD(HOUR, @TheHour, (DATEADD(DAY, DATEDIFF(DAY, 0, @TheDate), -1))),
    DATEADD(HOUR, @TheHour, (DATEADD(DAY, DATEDIFF(DAY, 0, @TheDate), 0)))

    2010-12-06 22:00:00.000 2010-12-07 22:00:00.000

    (1 row(s) affected)


  5. sonnysingh Member

    You mean to say that when midnight come or cross, the getdate() value change to next date and date range change according for the next day and it shouldn't be happen as you always need to run the data from previous day 22:00 pm of getdate() until getdate() 22:00 pm?
    If getdate() = '20101107' -- in IST
    then date range always should be from '20101106 22:00' to '20101107 22:00' and shouldn't be change even after midnight as after midnight getdate() will be '20101108' and date range will be '20101107 22:00' to '20101108 22:00' which wrong as it will be stop publish the data until reach the time '20101107 22:00'.
    I hope that I understood the logic correctly...and if yes then you need to use temp table (or variable table) and calculate the date range from there.

  6. sqlderby Member

    yes seems to correct...I have tried with table but again end up with same issue as we are calculating reuqired datetime on the basis of IST getdate() and every time SP run will take IST datetime from getdate()...
    Pls help
  7. FrankKalis Moderator

    [quote user="sqlderby"]
    yes seems to correct...I have tried with table but again end up with same issue as we are calculating reuqired datetime on the basis of IST getdate() and every time SP run will take IST datetime from getdate()...
    Pls help
    [/quote]
    Have you seen my bit of code?
  8. sqlderby Member

    Yes... Frank I did... If you notice same thing is happening in my code...
    SELECT @nStartHour = StartTime FROM SkillGroupMaster WHERE SkillGroupID = @SkillGroupID
    @nStartHour is get 22:00 hours for the skillgroup dynamically from the table and create date range from 22:00 hours in previous day to current day (getdate()) 22:00 hours...again issue is when 12:00 midnight on current day (from getdate()) the date change and then date range become from
    considering current date 20101209' for example below....
    '20101209 22:00' to '20101210 22:00' rather should be '20101208 22:00' to '20101209 22:00'
    just remember getdate() is in IST.
    Thanks for help...
  9. FrankKalis Moderator

    So, when GETDATE() returns 2010-12-09 08:21:19.720 for example, you want the interval to be between 2010-12-08 22:00:00.000 and 2010-12-09 22:00:00.000, right?
    Then my query will do this:
    DECLARE @TheHour int;
    DECLARE @TheDate datetime;

    SELECT
    @TheDate = GETDATE(),
    @TheHour = 22;


    SELECT
    GETDATE(),
    DATEADD(HOUR, @TheHour, (DATEADD(DAY, DATEDIFF(DAY, 0, @TheDate), -1))),
    DATEADD(HOUR, @TheHour, (DATEADD(DAY, DATEDIFF(DAY, 0, @TheDate), 0)))

    2010-12-09 08:23:36.010 2010-12-08 22:00:00.000 2010-12-09 22:00:00.000

    (1 row(s) affected)


Share This Page