Solved

SSRS - MIN and MAX Project Phases

Posted on 2011-03-21
5
727 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
  • 3
  • 2
5 Comments
 
LVL 40

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 40

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now