SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Display all the days in a months in a matrix


Display all the days in a months in a matrix

Author
Message
Sachin 80451
Sachin 80451
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 126
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?
mysorian
mysorian
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 101
Use a parametrized query.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85214 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
peterzeke
peterzeke
SSChasing Mays
SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)SSChasing Mays (658 reputation)

Group: General Forum Members
Points: 658 Visits: 1766
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/


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/

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

--Pete



AlanLSmith
AlanLSmith
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 39
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
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85214 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
AlanLSmith
AlanLSmith
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 39
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
;
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85214 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search