Post reply

SQL Query question (count dates for each month)

  • August 18, 2011 at 6:45 am

    #255943

    Hi there,

    Hope you can help me with the following, i have the following view availble:

    DD/MM/YYYY

    ENTITY | StartDate | EndDate | CodeA | CodeB | Revenue | Currency

    AZERT | 01/01/2011 | 02/01/2011 | SU | BOLD | 100 | EUR

    AZERT | 28/01/2011 | 02/02/2011 | SU | BOLD | 500 | EUR

    Can someone help with a query to pull the data so that I get the following summed?

    ENTITY | YYYY.MM | CodeA | CodeB | DAYS | TIMES | Revenue | Currency

    AZERT | 2011.01 | SU | BOD | 5 | 2 | 500 | EUR

    AZERT | 2011.02 | SU | BOD | 1 | 0 | 100 | EUR

    Where YYYY.MM is created depending on the difference between Sdate and EDate.

    And DAYS is the variance between the start and end day in the right month

    And TIMES is the number of times that the StartDate occurs in that month

    Revenue splitted depening how many days there are.

  • August 18, 2011 at 7:19 am

    #1370100

    Hello

    Have a read of the link in my sig for general guidelines on posting. Included in this is a suggestion for the creation of sample data for folks to test their solutions against. I've made a start for you...

    DROP TABLE #Sample

    CREATE TABLE #Sample (ENTITY VARCHAR(10), StartDate DATE, EndDate DATE, CodeA CHAR(2), CodeB CHAR(4), Revenue MONEY, Currency CHAR(3))

    INSERT INTO #Sample (ENTITY, StartDate, EndDate, CodeA, CodeB, Revenue, Currency)

    SELECT 'AZERT', '01/01/2011', '02/01/2011', 'SU', 'BOLD', 100, 'EUR' UNION ALL

    SELECT 'AZERT', '28/01/2011', '02/02/2011', 'SU', 'BOLD', 500, 'EUR'

    SELECT * FROM #Sample

    It would be helpful if you could include a few more rows in this so that it is broadly representative of your actual data and includes a few edge cases.

    Cheers

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

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