Add new calendar columns to the existing query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Add new calendar columns to the existing query

Author

Topic

lahsiv2004
Starting Member

13 Posts

Posted - 2015-04-08 : 06:49:46

Hi,

I have a requirement to add new calendar columns from Jan to Dec in an SSRS existing report based on certain conditions.

The report uses a simple query at the moment.

SELECT [PROJECT ID]
,[PROJECT]
,[STATUS]
,[ACTUAL START]
,[ESTIMATED END]
FROM Projects

The requirement to add the new calendar columns is based on the following conditions:

1. If the Status value is “2” then for each Month in the current year, shade in green for the particular current month

For example, if for a particular Project for month/year of April 2015

Year/Month(Actual Start date) is < = April 2015
and Year/Month(Estimate End date) > = April 2015, then April should be shaded green.

2. The logic needs to be repeated from each month in the current year.
3. Some Estimate and Actual dates run from previous year, or into next year, so year also needs to be taken into consideration.

Please find the DDL and sample data below.

CREATE TABLE [dbo].[Projects](
[PROJECT ID] [nvarchar](255) NULL,
[PROJECT] [nvarchar](255) NULL,
[STATUS] [nvarchar](255) NULL,
[ACTUAL START] [datetime] NULL,
[ESTIMATED END] [datetime] NULL
) ON [PRIMARY]

insert into Projects
values('1','TFD','2','2015-01-31 00:00:00.000','2015-12-31 00:00:00.000')
insert into Projects
values('2','JUH','1','2015-02-01 00:00:00.000','2015-02-27 00:00:00.000')
insert into Projects
values('3','REFFD','2','2015-01-31 00:00:00.000','2015-11-30 00:00:00.000')
insert into Projects
values('4','DFRT','2','2015-04-01 00:00:00.000','2015-05-31 00:00:00.000')
insert into Projects
values('5','DFDF','2','2015-03-20 00:00:00.000','2015-04-20 00:00:00.000')
insert into Projects
values('6','HTYU','3','2015-01-31 00:00:00.000','2015-03-31 00:00:00.000')
insert into Projects
values('7','WEW','2','2015-01-31 00:00:00.000','2015-12-31 00:00:00.000')
insert into Projects
values('8','FHF','2','2015-01-31 00:00:00.000','2015-12-31 00:00:00.000')
insert into Projects
values('9','ERT','1','2015-02-20 00:00:00.000','2015-05-31 00:00:00.000')
insert into Projects
values('10','FGH','2','2015-01-02 00:00:00.000','2015-01-31 00:00:00.000')

Please find the expected output screenshot attached.

Could somebody please advice how to rewrite the existing query to add new calendar columns in the report and then in the SSRS output shade green based on the above condition. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources