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

?? on creating a temp table for a date range Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 6:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 7:34 AM
Points: 299, Visits: 498
Hi,

Can someone pint me in the direction of where I can see some examples on creating a temp table for a date range?

What I need to do is use SSRS with parameters for a month and year o create report. I then need to choose data within that month and create a matrix report.

Problem I find is some dates don't have data but I still want to show the date as null so I show all dates in the month. I assume I have to create a temp table using a dimdate table?

Thanks
Joe
Post #1467120
Posted Tuesday, June 25, 2013 7:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 1,917, Visits: 19,583
search this site for "calendar table"

lots of articles that may help


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1467130
Posted Tuesday, June 25, 2013 7:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 13,078, Visits: 12,528
And please don't post multiple threads with the same question.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1467158
Posted Wednesday, June 26, 2013 4:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 5:04 PM
Points: 102, Visits: 96
I use a recursive CTE for this usually. In this example it will give you all the days in June. Had to use this when I created a calendar application with recurring entries. Sort of like outlook and needed to match the day of week based on its recurrence schedule. Like every week, 2 weeks, month... etc.

DECLARE @StartDate DATE = '6/1/2013';
WITH dt(MDate) AS (
SELECT @StartDate AS MDate
UNION ALL
SELECT DATEADD(dd, 1, MDate)
FROM dt
WHERE DATEADD(dd, 1, MDate) < DATEADD(mm, 1, @StartDate)
)
SELECT MDate FROM dt

Post #1467897
Posted Wednesday, June 26, 2013 4:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:20 PM
Points: 3,745, Visits: 8,404
r.mitchell (6/26/2013)
I use a recursive CTE for this usually. In this example it will give you all the days in June. Had to use this when I created a calendar application with recurring entries. Sort of like outlook and needed to match the day of week based on its recurrence schedule. Like every week, 2 weeks, month... etc.

DECLARE @StartDate DATE = '6/1/2013';
WITH dt(MDate) AS (
SELECT @StartDate AS MDate
UNION ALL
SELECT DATEADD(dd, 1, MDate)
FROM dt
WHERE DATEADD(dd, 1, MDate) < DATEADD(mm, 1, @StartDate)
)
SELECT MDate FROM dt



Please, avoid this and read the following article:
Hidden RBAR: Counting with Recursive CTE's



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1467903
Posted Thursday, June 27, 2013 7:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 13,078, Visits: 12,528
And here is an example of doing this without using a recursive cte for counting. This code also will only return data for the specified month even if the @StartDate is not the first of the month. To see what I mean change the start date to June 2nd and run the recursive version. It will return dates in July because it assumes the start date is the first of the month. Now run the one below with the same start date and it will only return 28 rows now.

DECLARE @StartDate DATE = '6/1/2013';

WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
)

select DATEADD(day, N - 1, @StartDate)
from cteTally
where N <= DATEDIFF(day, @StartDate, dateadd(month, datediff(month, 0, @StartDate) + 1, 0))



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1468158
Posted Thursday, June 27, 2013 11:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 5:04 PM
Points: 102, Visits: 96
Hmm. thanks for the good read. Time to change some code around
Post #1468273
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse