Display all the days in a months in a matrix

  • Hi,

    Can someone please suggest, how this is can be done.

    I need to produce all the days in the month, to show along the columns. Each user therefore can see what appointments they have on which day.

    Is a cross-join the best way to get all the days against user regardless of them having an appointment.

    But how would I make this dynamic? So they could potentially flick from month to month maybe.

    how can I produce all the days in the month in a query for the cross-join?

  • Use a parametrized query.

  • mysorian (6/10/2013)


    Use a parametrized query.

    Yeah... and all you have to do to get to the Moon is build a rocket ship that will support human life.

    Seriously... post some code. Yes, a parameterized query will be necessary. WHAT is the query?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ryan Duclos wrote an article for Redgate's technical solutions site, simple-talk.com, that may be of value for you. He discusses creating a calendar report in SSRS 2005:

    https://www.simple-talk.com/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/

    [/url]

    He also wrote a follow-up posting on his blog that updates the instructions for SSRS 2008:

    http://rduclos.wordpress.com/2010/02/13/ssrs-2008-generate-calendars-based-on-a-date-range/[/url]

    I imagine that his approach could be modified to integrate a parameter to limit data as needed.

    --Pete

  • Suggest that master dates table is best.

    But if it's not available, here's a query that will give you the dates for a given year and month.

    Still working on how to get the cross-tab/pivot/matrix working to have these in columns, but this is a start:

    with daylist as

    (

    select 1 as daynum

    union all

    select dl.daynum + 1 as daynum

    from daylist dl

    where dl.daynum + 1 <=

    day( DATEADD( s, -1, DATEADD( mm, DATEDIFF( m, 0,

    CAST(@yearnum + '-' + @monthnum + '-01' AS datetime))+1,0)))

    )

    select daynum from daylist order by daynum;

  • AlanLSmith (6/11/2013)


    Suggest that master dates table is best.

    But if it's not available, here's a query that will give you the dates for a given year and month.

    Still working on how to get the cross-tab/pivot/matrix working to have these in columns, but this is a start:

    with daylist as

    (

    select 1 as daynum

    union all

    select dl.daynum + 1 as daynum

    from daylist dl

    where dl.daynum + 1 <=

    day( DATEADD( s, -1, DATEADD( mm, DATEDIFF( m, 0,

    CAST(@yearnum + '-' + @monthnum + '-01' AS datetime))+1,0)))

    )

    select daynum from daylist order by daynum;

    Alan,

    Please see the article at the following link. You might change your mind about using rCTE's (even small ones) that count in the future.

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks for tip, Jeff.

    OK, here are 2 additional (and hopefully lest costly) queries to the get the days for a specific month.

    I don't have any experience with the SQL Profiler, so I'm still trying to figure that one out so I can compare like you did in the article.

    SELECT --===== Classic Cross-Join

    TOP (day( DATEADD( s, -1, DATEADD( mm, DATEDIFF( m, 0,

    CAST(@yearnum + '-' + @monthnum + '-01' AS datetime))+1,0))))

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as daynum2

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_Columns ac2

    ;

    WITH --===== Itzik-style Cross-Join

    E1(N) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ), -- 1*10^1 or 10 rows

    E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows

    SELECT TOP (

    day( DATEADD( s, -1, DATEADD( mm, DATEDIFF( m, 0,

    CAST(@yearnum + '-' + @monthnum + '-01' AS datetime))+1,0)))

    ) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as daynum3 FROM E2

    ;

  • An alternative to all of that, especially if you have to work with dates a lot, would be to build a nice, narrow, Calendar Table that contains only the columns that you need to keep it narrow (narrow usually = faster just because you don't have to read so many pages as a wider table for the same job)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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