Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 755
  • Last Modified:

SSRS - MIN and MAX Project Phases

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
Littleghostface
Asked:
Littleghostface
  • 3
  • 2
1 Solution
 
SharathData EngineerCommented:
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
 
LittleghostfaceAuthor Commented:
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
 
LittleghostfaceAuthor Commented:
Sorry, last line should read

MAX_END should equal 2011-04-22 00.00.00.000
0
 
SharathData EngineerCommented:
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
 
LittleghostfaceAuthor Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now