Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Display all the days in a months in a matrix Expand / Collapse
Author
Message
Posted Monday, June 10, 2013 3:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 20, 2014 3:23 AM
Points: 50, 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?
Post #1461446
Posted Monday, June 10, 2013 5:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 5:39 PM
Points: 40, Visits: 79
Use a parametrized query.
Post #1461815
Posted Monday, June 10, 2013 8:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1461842
Posted Tuesday, June 11, 2013 9:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:41 PM
Points: 316, Visits: 1,480
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



Post #1462238
Posted Tuesday, June 11, 2013 1:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 12:01 PM
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;
Post #1462368
Posted Tuesday, June 11, 2013 4:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1462419
Posted Wednesday, June 12, 2013 9:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 2, 2013 12:01 PM
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
;
Post #1462695
Posted Sunday, June 16, 2013 10:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1463969
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse