Hi guys,

I have to create a report that comes from a SQL Database. I have the following fields:

AuditID
Name
Objective
Goal
Scope
Note
Planned Start Date
Planned End Date
Actual Start Date
Actual End Date
Reference Number
Planned Effort
Actuall Effort

This is my SP that I'm using:

SELECT dbo.IAAudit.AuditID, dbo.IAAudit.Name, MAX(CASE WHEN IAAuditDetailType.Code = 'OBJECTIVE' THEN CONVERT(varchar, LTextValue) ELSE NULL END) AS Objective,
MAX(CASE WHEN IAAuditDetailType.Code = 'GOAL' THEN CONVERT(varchar, LTextValue) ELSE NULL END) AS Goal,
MAX(CASE WHEN IAAuditDetailType.Code = 'SCOPE' THEN CONVERT(varchar, LTextValue) ELSE NULL END) AS Scope,
MAX(CASE WHEN IAAuditDetailType.Code = 'NOTE' THEN CONVERT(varchar, LTextValue) ELSE NULL END) AS Note,
MAX(CASE WHEN IAAuditDetailType.Code = 'PLANNED_START_DATE' THEN DateValue ELSE NULL END) AS [Planned Start Date],
MAX(CASE WHEN IAAuditDetailType.Code = 'PLANNED_END_DATE' THEN DateValue ELSE NULL END) AS [Planned End Date],
MAX(CASE WHEN IAAuditDetailType.Code = 'ACTUAL_START_DATE' THEN DateValue ELSE NULL END) AS [Actual Start Date],
MAX(CASE WHEN IAAuditDetailType.Code = 'ACTUAL_END_DATE' THEN DateValue ELSE NULL END) AS [Actual End Date],
MAX(CASE WHEN IAAuditDetailType.Code = 'REFERENCE_NUMBER' THEN TextValue ELSE NULL END) AS [Reference Number],
MAX(CASE WHEN IAAuditDetailType.Code = 'PLANNED_EFFORT' THEN IntegerValue ELSE NULL END) AS [Planned Effort],
MAX(CASE WHEN IAAuditDetailType.Code = 'ACTUAL_EFFORT' THEN IntegerValue ELSE NULL END) AS [Actual Effort]
FROM dbo.IAAudit INNER JOIN
dbo.IAAuditDetail ON dbo.IAAuditDetail.AuditID = dbo.IAAudit.AuditID INNER JOIN
dbo.IAAuditDetailType ON dbo.IAAuditDetailType.AuditDetailTypeID = dbo.IAAuditDetail.AuditDetailTypeID
GROUP BY dbo.IAAudit.AuditID, dbo.IAAudit.Name

The design of the database wasn't me. I just use it

But there's 3 tables. IAAudit, IAAuditDetail and IAAuditDetailType.

That's why I use the Max commands. Works 100%.

Now the report part:


They want a report that will show the Audit Name (and sub Audit's) with a month to month timeframe. So let's say there is "Test Audit" it will be on the left of the report and next to it it will state the "Planned start date" and "Planned end date" as well as the "Actual Start Date" and "Actual End Date"

That part I can do.

The part I need help with is:

1. They want it to be month to month (so they have to select a month and I have to pull the info till the end of that month)

2. It should show progress lines on the report. Say a blue thick line showing the planned start date to the planned end date and then a thinner yellow line stating the actually start date and actual end date.

how would I do this?

 

 

Edit:

 

Ok best that I could do so far is to use a Gantt Chart but I have 2 issues with it.

1. It shows the entire time period. Not just for the certain month

2. It gives me the "Planned Start Date" and "Planned End Date" but I need to add the "Actually Start and End date" on top of that. Is this possible