Solved

SSRS - MIN and MAX Project Phases

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Communications Audit 5 67
Error in query 3 52
Sql Query Datatype 2 14
Getting max record but maybe not use Group BY 2 14
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…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

914 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

17 Experts available now in Live!

Get 1:1 Help Now