Solved

SSRS - MIN and MAX Project Phases

Posted on 2011-03-21
5
733 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

713 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