Dynamic Dates in Columns on Matrix Report?

I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.

For example a report that would run on today's MM, yy (5/2007) would look something like this:

MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08

(Col 1 - Col 12 separated by commas)

How do I do this in a matrix report column?

1- Filter your datasource to return only those rows that meet the date criteria you describe

If SQL Server...

Code Snippet

Select Item, SalesDate, Sales from YourTable Where SalesDate >= DateAdd(dd, -3, GetDate()) AND SalesDate <= DateAdd(dd, 9, GetDate())

For example:

Item Date Sales

Bicycle Mar-07 $5000

Bicycle Apr-07 $4500

etc...

Then set your date column as the Column Group in the matrix report

Then select that column header in SSRS Layout view, and change the format property to the MMM-yy

(If the granularity of the dates in your source data is not month, if it's by day instead for instance, then you'll need to aggregate that data in your query with the appropriate Group By calls)

Hope this helps,

Geof

Dynamic Dates in Columns on Matrix Report

I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.

For example a report that would run on today's MM, yy (5/2007) would look something like this:

MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08

(Col 1 - Col 12 separated by commas)

How do I do this in a matrix report column?

I would do this by created an enumerated type for months and then iterate through them based on current month value(5). when you reach 12(or 13 depending on starting value) then reset to 0 or 1 respectively and increase the year by one.

Eric

|||

I'm still not sure how to actually do this. Can you send me an example of how to do this?

|||

Can you post your code please so I understand what you need.

|||

I ended up declaring a begindate and enddate, based on the current month in the stored procedure and passed that in.

Dynamic Dates and Stored Procedures

I was wondering if anyone could help me past a problem that I am having using dynamic dates and stored procedures. I am new to this and have the following procedure written, This particular query will only return data if the variables @.sDateFrom and @.sDateTo are surrounded by single quotes however, in the stored procedure the single quotes cause the query to fail giving the error "Syntax error converting datetime from character string"

Any insights would be greatly appreciated.

Thanks,
Brent

ALTER PROC prBuildSelector (
@.sNRW varchar(50),
@.sDateFrom datetime,
@.sDateTo datetime
)
AS

SELECT name,start_date,end_date, sku FROM product
INNER JOIN section ON product.pfid = section.pfid
WHERE product.name LIKE '%@.sNRW%' AND
start_date > @.sDateFrom
AND start_date < @.sDateTo
ORDER BY vcfeb10_product.name
ReturnWhich database are you using ?|||Originally posted by brentb
I was wondering if anyone could help me past a problem that I am having using dynamic dates and stored procedures. I am new to this and have the following procedure written, This particular query will only return data if the variables @.sDateFrom and @.sDateTo are surrounded by single quotes however, in the stored procedure the single quotes cause the query to fail giving the error "Syntax error converting datetime from character string"

Any insights would be greatly appreciated.

Thanks,
Brent

ALTER PROC prBuildSelector (
@.sNRW varchar(50),
@.sDateFrom datetime,
@.sDateTo datetime
)
AS

SELECT name,start_date,end_date, sku FROM product
INNER JOIN section ON product.pfid = section.pfid
WHERE product.name LIKE '%@.sNRW%' AND
start_date > @.sDateFrom
AND start_date < @.sDateTo
ORDER BY vcfeb10_product.name
Return

EXEC('SELECT name,start_date,end_date, sku FROM product
INNER JOIN section ON product.pfid = section.pfid
WHERE product.name LIKE ''%' + @.sNRW + '%'' AND
start_date > @.sDateFrom
AND start_date < @.sDateTo
ORDER BY vcfeb10_product.name')

Dynamic date setting in loops

Hello everybody,

I've been trying to write a script to populate a table.
One filed is of 'date' type and I would like to insert dates different from record to record.
I thought about creating a loop and then try to increment the day (or the hour, I don'care) by using the loop index.
There comes of course a problem of casting from integers to strings (or date).

I tried to do something like:

DECLARE @.K INT
WHILE(@.K<10)
BEGIN
INSERT INTO TABLE1 (DATETIME) VALUES ('20071026 11:'||CAST(@.K AS VARCHAR)||'00')
SET @.K=@.K+1
END

but it didn't work ...

Would you please suggest a method of performing this action?

Thanks in advance,

Stefano.What is the data that you are trying to insert. I dont think the initial condition of the loop satisfies at all.|||The data type is 'datetime' and I was trying to build the string by a cat operation.
For instance, to build '20071025 12:28:40' I coded:

'20071025 12:'+cast(@.j+28, varchar)+':40'

where @.j is the loop variable.

The aim is to obtain strings with dates like:

..............................
'20071025 12:29:40'
'20071025 12:30:40'
'20071025 12:31:40'
'20071025 12:32:40'
'20071025 12:33:40'

and so on ...|||If you have the table like below:
TABLE1 ([DATETIME] DATETIME)

The modification your script to correct one as follows:

DECLARE @.K INT
SET @.K = 0
WHILE(@.K<10)
BEGIN
INSERT INTO TABLE1 (DATETIME) VALUES (convert(datetime,'2007-10-26 11:'+CAST(@.K AS VARCHAR)+':00'))
SET @.K=@.K+1
END|||I implemented the suggested modification, the parser says ok, but the run.time execution got the following error:

Server: Msg 242, Level 16, State 3, Line 12
The conversion of a char data to a datetime data type resulted in
an out-of-range datetime value.
The statement has been terminated

The troubles keep going ...

Quote:

Originally Posted by sayedul

If you have the table like below:
TABLE1 ([DATETIME] DATETIME)

The modification your script to correct one as follows:

DECLARE @.K INT
SET @.K = 0
WHILE(@.K<10)
BEGIN
INSERT INTO TABLE1 (DATETIME) VALUES (convert(datetime,'2007-10-26 11:'+CAST(@.K AS VARCHAR)+':00'))
SET @.K=@.K+1
END

|||I already found my error! I wrote incorrect datetime format!
Now everything works!

Sorry, I am stupid ...

Thanks a lot anyway for the helpful suggetsions!!!

Stefano.

Dynamic CASE statement based on list of dates

I have the following table of data. I need to take a date from a large table and do the following case:
CASE
When date < date(0)
Then '0'
When date between date(0) and date(1)
Then '1'
When date between date(1) and date(2)
Then '2'
When date >= date(3)
Then '3'
What I need is to be able to read all the dates the the Date table, sort then chronologically, and build the dynamic CASE statement so that the first When statement is < Date(0) and the last When statement is >= Date(Last)
I hope I am making sense. Dates will be added to the table about once a year or so and I don't want to keep going back into the sql function and rewrite it with the latest date. Any ideas how to manipulate these dates into a case statement? Don't worry about the second table below. I just wanted you to see why I need to return an int from the Case function.
thanks
Milton

Dates TableDate4/1/20031/1/20064/2/2007
Fee Table

DatePeriodClassFee1DailyTrue3291Half DayTrue1781OTTrue491HourlyTrue411DailyFalse1561Half DayFalse861OTFalse271HourlyFalse192DailyTrue3552Half DayTrue1922OTTrue502HourlyTrue442DailyFalse1712Half DayFalse922OTFalse282HourlyFalse213DailyTrue3643Half DayTrue1973OTTrue513HourlyTrue453DailyFalse1753Half DayFalse943OTFalse293HourlyFalse21

If you're using SQL Server 2005, you could write something like this. It combines two of the new features of SQL Server 2005: ROW_NUMBER T-SQL Function, and COMMON TABLE EXPRESSIONS (CTE).

--------------------------------------
USE NORTHWIND
GO

WITH CTE_ALL_DATES (ID, DATE)
AS (
SELECT ROW_NUMBER()OVER (ORDER BY DATEASC)AS ID
, DATE
FROM (
SELECTCAST('1995-01-01'AS DATETIME) DATE
UNION
SELECTCAST('1996-08-01'AS DATETIME)
UNION
SELECTCAST('1997-04-01'AS DATETIME)
UNION
SELECTCAST('1998-03-01'AS DATETIME)
UNION
SELECTCAST('2000-01-01'AS DATETIME)
) T1
)
SELECT D1.ID
, D1.DATE
, O.*
FROM ORDERS O
JOIN CTE_ALL_DATES D1
ON D1.DATE <= O.[ORDERDATE]
--------------------------------------

You would just have to replacee all the UNION, UNION, UNION with your Dates table. You can test out this code against Northwind database.

Hope this helps,

|||

Ok, thanks. I am using sql server 2000.

|||

SQL Server 2000 Version, it is not as simple as the 2005 version but it works, you should probably encapsulate it in a stored procedure.

--

USE NORTHWINDGO-- CREATE TEMP, FOR DATES NUMBERINGCREATE TABLE #INDEX_ALL_DATES(IDINT IDENTITY(1,1) ,DATESMALLDATETIME )CREATE CLUSTERED INDEX IX_INDEX_ALL_DATES_DATE_IDON #INDEX_ALL_DATES(DATE)INSERT #INDEX_ALL_DATES(DATE)SELECT DATEFROM (-- REPLACE THIS WITH YOUR SELECT * FROM DATES TABLESELECTDATEADD(M, -3,MIN([ORDERDATE])) DATEFROM ORDERSUNIONSELECTDATEADD(M, -3,DATEADD(DAY, (DATEDIFF(DAY,MAX([ORDERDATE]),MIN([ORDERDATE])))/2,MIN([ORDERDATE])))FROM ORDERSUNIONSELECTDATEADD(M, -3,MAX([ORDERDATE]))FROM ORDERS-- REPLACE THIS WITH YOUR SELECT * FROM DATES TABLE)AS T1ORDER BY DATEASC SELECT D1.ID, D1.DATE, O.*FROM ORDERS OJOIN #INDEX_ALL_DATES D1ON D1.DATE <= O.[ORDERDATE]-- CLEAN UPDROP TABLE #INDEX_ALL_DATES--

Hope this helps,