Display all the days in a months in a matrix

Question

Post reply

Display all the days in a months in a matrix

Sachin 80451

SSCommitted

Points: 1642
More actions
June 10, 2013 at 3:19 am

#274900

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?

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

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

mysorian Ten Centuries Points: 1048 More actions · Answer 1

mysorian

Ten Centuries

Points: 1048

June 10, 2013 at 5:02 pm

#1622663

Use a parametrized query.

Jeff Moden SSC Guru Points: 1004558 More actions · Answer 2

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)

peterzeke SSCrazy Eights Points: 9003 More actions · Answer 3

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

AlanLSmith SSC Enthusiast Points: 192 More actions · Answer 4

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;

Jeff Moden SSC Guru Points: 1004558 More actions · Answer 5

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)

AlanLSmith SSC Enthusiast Points: 192 More actions · Answer 6

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

), -- 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

;

Jeff Moden SSC Guru Points: 1004558 More actions · Answer 7

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)

Display all the days in a months in a matrix

Cookies on SQLServerCentral