CODEWITHSUNDEEP
sqlfunctiondate
Overseer10
Overseer10

Reputation: 361

SQL Date Function

I may be using the wrong term (hence why I can't find it on google).

"Are there any functions or common code for Accounting Months Deliminations?"

For Example, this month started on a friday but on most accounting journals the weeks are measured by the first monday of the month so instead of having the 1st of July it would be the 4th of July. Same thing with the month end (29th instead of the 31st)

Again, I'm sure someone has created this 'wheel' before, and I can't seem to find it for the life of me.

Upvotes: 2

Views: 517

Answers (2)

Michael Ames
Michael Ames

Reputation: 2617

The following query assumes a table, SalesTable, has a field called Amount (the value you want to sum) and a field called SaleDate (the date on which the sale occured.) It also assumes that accounting months begin the first Monday of the month and end on the Sunday prior to the beginning of the next accounting month.

Again, I highly recommend a table-based approach to this, but if you can't modify the schema, this should do the trick in T-SQL:

SELECT 
    CASE WHEN s.SaleDate < DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6 - DATEPART(DAY, s.SaleDate ),s.SaleDate )), 0)
       THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6 - DATEPART(DAY, DATEADD(day,-7,s.SaleDate) ),DATEADD(day,-7,s.SaleDate) )), 0)
       ELSE DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6 - DATEPART(DAY, s.SaleDate ),s.SaleDate )), 0) 
    END AccountingMonth,

 SUM(s.Amount) TotalSales

FROM SalesTable s

GROUP BY 
    CASE WHEN s.SaleDate < DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6 - DATEPART(DAY, s.SaleDate ),s.SaleDate )), 0)
       THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6 - DATEPART(DAY, DATEADD(day,-7,s.SaleDate) ),DATEADD(day,-7,s.SaleDate) )), 0)
       ELSE DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6 - DATEPART(DAY, s.SaleDate ),s.SaleDate )), 0) 
    END

Note that the AccountingMonth return field actually contains the date of the first Monday of the month. In actual practice, you probably want to wrap this entire query in another query that reformats AccountingMonth to whatever you like... "2011-07", "2011-08", etc.

Here's how it works: This bit of code is the important part:

DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(DAY, 6 - DATEPART(DAY, s.SaleDate ),s.SaleDate )), 0)

It takes any date and returns the first Monday of the month in which that date occurred. In your case, however, you have to do a little more work because a sale might have occurred in the window between the first of the month and the first Monday of the month. The CASE statement detects that scenario and, if it's true, subtracts a week off of the date before calculating the first Monday.

Good luck!

-Michael

Upvotes: 1

Narnian
Narnian

Reputation: 3908

I have some code that takes in a year and month and returns the fiscal start and end dates. Perhaps this will give you something to go by:

DECLARE @yr int;
DECLARE @mo int;
SELECT @yr = 2011
SELECT @mo = 7

DECLARE @FiscalMonthStartDate datetime
DECLARE @FiscalMonthEndDate datetime

DECLARE @startOfMonth datetime
DECLARE @startOfNextMonth datetime

select @startOfMonth = CAST((CAST(@yr AS VARCHAR(4)) + '-' + CAST(@mo AS VARCHAR(2)) + '-' + '01') as DATE)
select @startOfNextMonth = CAST((CAST(@yr AS VARCHAR(4)) + '-' + CAST((@mo + 1) AS VARCHAR(2)) + '-' + '01') as DATE)

SELECT @FiscalMonthStartDate =
        CASE 
            WHEN DATEPART(DW,@startOfMonth) = 0
                THEN DATEADD(DD, 1, @startOfMonth)
            ELSE
                DATEADD(DD, 8 - DATEPART(DW,@startOfMonth), @startOfMonth)
        END

SELECT @FiscalMonthEndDate =
        CASE 
            WHEN DATEPART(DW,@startOfNextMonth) = 0
                THEN DATEADD(DD, 1, @startOfNextMonth)
            ELSE
                DATEADD(DD, 8 - DATEPART(DW,@startOfNextMonth), @startOfNextMonth)
        END

-- subtract one day to get end of fiscal month (not start of next fiscal month)
SELECT @FiscalMonthEndDate = DATEADD(DD, -1, @FiscalMonthEndDate)

SELECT @FiscalMonthStartDate, @FiscalMonthEndDate

Upvotes: 0

Related Questions