Post reply

Need help with Workweek Day setup to THursday midnight of each week.

  • December 21, 2005 at 1:40 pm

    #91054

    Hi,

    I am new to SQL Server and I need some help in setting up work week DAY function. I am working for the health industry and they have 7 days work week but they generate their reports on every Friday. So all the transaction occured prior to Friday will count in the report. I have one field called Transaction Date. Based on Max value of Transaction Date, I have to fix this date field to reflect cutoff date on Thursday at midnight so I can generate reports off of historical data.

    Can some one help me out with code as to how the Day function should be written.

    Insert into tmp_LenderQuery1

    (ResidentSys, PatientName, PlanSys, PlanDesc, trandate, transys, stmtbegdate, stmtenddate, orgentsys, billed, InvoiceBalance, TotalCharges, TotalAdjustments, TotalPayments)

    Select distinct a.ResidentSys, b.FirstName + ' ' + b.Lastname As PatientName,

    a.PlanSys, c.PlanDesc, a.trandate, a.transys, a.stmtbegdate, a.stmtenddate, a.orgentsys, a.billed,

    Sum(Case When AccountType = 'AR' then Amount

    End) as InvoiceBalance,

    Sum(Case When AccountType = 'RV' then Amount

    End) as TotalCharges,

    Sum(Case When (AccountType = 'DS' Or AccountType='D1' Or AccountType='C2'

    Or AccountType='C4' Or AccountType='C3' Or AccountType='C1' Or AccountType='C5'

    Or AccountType='C6' Or AccountType='C8' Or AccountType='C7' Or AccountType='CH'

    Or AccountType='DI' Or AccountType='C9' Or AccountType='FD' Or AccountType='DM'

    Or AccountType='ME' Or AccountType='MF' Or AccountType='CO' Or AccountType='AA'

    Or AccountType='BD' Or AccountType='CA' Or AccountType='MC' Or AccountType='XR'

    OR AccountType='AC' Or AccountType='OA' OR AccountType='CB' or AccountType='M2'

    or AccountType = 'AD' OR AccountType='DI' OR AccountType='NS')

    then Amount

    End) as TotalAdjustments,

    Sum(Case When AccountType = 'PM' then Amount

    End) as TotalPayments

    from tranardetail a inner join Entities b

    on a.ResidentSys = b.EntitySys

    inner join Plans c

    on a.Plansys = c.PlanSys

    --where trandate < @ENDDATE - Changes made on 12/13/05 to pull all records by entry date rather than Trandate.
    --Data were being dropped due to TransDate <> or < EntryDate.
    -- 12/19/05 - VS - added "trandate <= @enddate in the line below
    --added entrydate <=@enddate
    where trandate <= @Enddate
    group by a.ResidentSys, a.transys,a.trandate, a.stmtbegdate, a.stmtenddate, b.Lastname, b.FirstName, a.PlanSys, c.PlanDesc, a.orgentsys, a.billed
    order by a.ResidentSys

    Thanks
    Manoj

  • December 22, 2005 at 2:56 am

    #611058

    Not sure what you are asking for but

    WHERE trandate < @ENDDATE

    seems right as long as @ENDDATE is set correctly

    So, today is Thursday 22 Dec, midnight tonight would be

    '2005-12-23 00:00:00.000'

    Therefore this would work

    DECLARE @ENDDATE datetime

    SET @ENDDATE = '2005-12-23 00:00:00.000'

    SELECT ...

    WHERE trandate < @ENDDATE

    So if your job is scheduled to run on a Friday then you could do this to get all transactions upto midnight the previous day (Thursday)

    DECLARE @ENDDATE datetime

    SET @ENDDATE = DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

    SELECT ...

    WHERE trandate < @ENDDATE

    The following thread discusses the various options of removing the time portion, albeit with smalldatetime

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=23&messageid=243877#bm244062

    Or are you wanting to adjust a date to the next nearest Thursday Midnight date?

    Far away is close at hand in the images of elsewhere.
    Anon.

  • December 23, 2005 at 1:27 am

    #611258

    Thanks David, it did the trick.

Viewing 3 posts - 1 through 3 (of 3 total)

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