Solved

SSRS - MIN and MAX Project Phases

Posted on 2011-03-21
5
745 Views
Last Modified: 2012-05-11
I have a table that holds Project Phases. Each Phase has a start and end date. I want to use the MIN(StartDate) and the MAX(Enddate) for all phases in a project to set the range for a bar chart. The bar chart is a horizontal stacked bar where each Phase is part of the stack in order of phase start date. I can't figure out how to repeat the MIN(Startdate) and Max(Enddate) for each phase in a project to represent the entire project time line.

I have attached a simple example of the data as well as an image of the bar chart. I would ideally like the X axis to show dates starting with the MIN(Startdate) and ending with the MAX(Enddate), and if possible have a line representing TODAY. (Note: Phases will never overlap dates, but one may end and another start on the same day).

Can anyone help with this?


 Phases SQLData
 Phase Bar Chart
0
Comment
Question by:Littleghostface
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35183773
Do you want MIN/MAX over all the phases? You can try like this.
SELECT MIN(EST_START) AS Min_EST_START,
       GETDATE() AS Today,
       MAX(EST_START) AS Max_EST_START 
  FROM ASRNDVPA 
 WHERE PJ_KEY = '_00000002K'

Open in new window

0
 

Author Comment

by:Littleghostface
ID: 35184636
I need one start date and one end date for the project, not for each phase. Your code above would basically give me what I already have. In the PhaseSQL.jpg example above, I would like to have two additional columns named MIN_START and MAX_END. These columns would have the following values for all of the 6 records in the example.

MIN_START would equal 2010-12-13 00.00.00.000
MIN_END would equal 2011-04-22 00.00.00.000
0
 

Author Comment

by:Littleghostface
ID: 35184643
Sorry, last line should read

MAX_END should equal 2011-04-22 00.00.00.000
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 35184688
ok. try this.
SELECT PHASE, 
       CODE, 
       PCNT_COMP, 
       EST_START, 
       EST_END, 
       MIN(EST_START) OVER (PARTITION BY (SELECT 1 )) AS Min_EST_START, 
       GETDATE()                                      AS Today, 
       MAX(EST_START) OVER (PARTITION BY(SELECT 1 ))  AS Max_EST_START 
  FROM ASRNDVPA 
 WHERE PJ_KEY = '_00000002K' 

Open in new window

0
 

Author Comment

by:Littleghostface
ID: 35201772
Beautiful, worked like a charm. I'm going to have to read up on OVER and (PARTITION BY (SELECT 1)), seems quite useful.

BTW, for others reading this, the MAX line in the solution should read MAX(EST_END) not MAX(EST_START)

Many Thanks!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question