CODEWITHSUNDEEP
sql-servert-sqldatedatetime
Sai
Sai

Reputation: 712

Writing an SQL function that can be accessed from a view and process user data based on a field

I have a situation where I need to process user data in a view, to be presented to the user appropriately. I have a table with the following fields:

StartDate   DateTime
EndDate     DateTime
MoneySpent  Double

The complex part is that, the start date and end date could be 15 days apart, or 1 month apart or 2 months apart and there is no specific pattern. By pattern I mean that the dates are not always in a specified format like 1st April 2012 to 1st Jun 2012. For example this could have been 1st April 2012 to 31st May 2012.

I have a view which selects the 'Month' component from the start date and performs some aggregate functions on the MoneySpent Column. But for the above mentioned dates Month Component should ideally return 2 different months. As of now it considers all the money is spent in the month in the StartDate and the aggregate function understandably returns the wrong value. How do I write a function that will tackle my situation?

StartDate          EndDate          Spend
1 May 2012        1 Jun 2012         100
1 Jun 2012        30 Jun 2012        200
1 Jul 2012        31 Aug 2012        500
31 Aug 2012       29 Sep 2012        300

Consolidated View

Month      Spend
 May        100
 Jun        200
 Jul        250
 Aug        250
 Sep        300

Upvotes: 0

Views: 100

Answers (1)

cofporation
cofporation

Reputation: 33

After reading your comments, you seem to want to acheive the following:

2013-05-01 -> 2013-05-20 = MAY

2013-05-01 -> 2013-06-01 = MAY

2013-05-01 -> 2013-06-20 = MAY and JUNE

I wrote the following example script to acheive that. just replace the variables with your DB fields.

            DECLARE @startDate AS DATETIME
            DECLARE @endDate AS DATETIME

            SELECT @startDate  = '2013-05-01'
            SELECT @endDate = '2013-06-02'

            SELECT 
                CASE WHEN MONTH(@startDate) <> MONTH(@endDate) THEN
                    CASE WHEN DAY(@endDate) > 1 THEN
                        DATENAME(MONTH, @startDate) + ' AND ' + DATENAME(MONTH, @endDate)
                    ELSE DATENAME(MONTH, @startDate)
                    END
                ELSE DATENAME(MONTH, @startDate)
                END AS theDate

Does that acheive what you were looking for?

Upvotes: 0

Related Questions