May 30, 2012 at 1:17 am
Hi All
Is there any alternate method for this below query
With Project_view(WorkId,Name,StatusCurrent,ActualStartDate,ActualEndDate,ActiveProcess,ActiveDateGate)
As
(
SELECT DISTINCT TOP 2000 vw.work_id as WorkId, vw.name as Name,vw.status_current as StatusCurrent,
vw.actual_start_date AS ActualStartDate ,vw.actual_end_date AS ActualEndDate ,
vp.process_name as ActiveProcess,wv.actual_start_date as ActiveDateGate
FROM view_work vw
LEFT OUTER JOIN View_Process vp ON vw.process_id = vp.process_id
LEFT JOIN view_work wv on vw.work_id = wv.active_gate
WHERE vw.is_project = 'Y' and vw.type_name <> 'Folder'
),
Project_Actual_view
As
(
SELECT w1.WorkId,w1.Name,w1.StatusCurrent,w1.ActualStartDate,w1.ActualEndDate,w1.ActiveProcess,w1.ActiveDateGate,
Max(Case when w2.Name = '1. Define' then w2.actual_start_date end) as DefineStartDate,
Max(Case when w2.Name = '1. Define' then w2.actual_end_date end) as DefineEndDate,
Max(case when w2.Name = '2. Measure' then w2.actual_start_date end) as MeasureStartDate,
Max(Case When w2.Name = '2. Measure' then w2.actual_end_date end) as MeasureEndDate,
Max(Case When w2.Name = '3. Analyze' then w2.actual_start_date end) as AnalyseStartDate,
Max(case When w2.Name = '3. Analyze' then w2.actual_end_date end) as AnalyseEndDate,
Max(case When w2.Name = '4. Improve' then w2.actual_start_date end) as ImproveStartDate,
Max(Case When w2.Name = '4. Improve' then w2.actual_end_date end) as ImproveEndDate,
Max(Case When w2.Name = '5. Control' then w2.actual_start_date end) as ControlStartDate,
Max(Case When w2.Name = '5. Control' then w2.actual_end_date end) as ControlEndDate
FROM Project_view w1
INNER JOIN view_work w2 on w2.parent_work_id = w1.WorkId
GROUP BY WorkId,w1.Name,w1.StatusCurrent,w1.ActualStartDate,w1.ActualEndDate,w1.ActiveProcess,w1.ActiveDateGate
)
SELECTWorkId,Name,StatusCurrent,ActiveProcess,ActiveDateGate,
vt1.tag_name as org,
vt2.tag_name as overall,
DefineStartDate,DefineEndDate,(DATEDIFF(dd, DefineStartDate, DefineEndDate) + 1)
-(DATEDIFF(wk, DefineStartDate, DefineEndDate) * 2)
-(CASE WHEN DATENAME(dw, DefineStartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, DefineEndDate) = 'Saturday' THEN 1 ELSE 0 END) as DurationOfDefineGate,
MeasureStartDate,MeasureEndDate,(DATEDIFF(dd, MeasureStartDate, MeasureEndDate) + 1)
-(DATEDIFF(wk, MeasureStartDate, MeasureEndDate) * 2)
-(CASE WHEN DATENAME(dw, MeasureStartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, MeasureEndDate) = 'Saturday' THEN 1 ELSE 0 END) as DurationOfMeasureGate,
AnalyseStartDate,AnalyseEndDate,(DATEDIFF(dd, AnalyseStartDate, AnalyseEndDate) + 1)
-(DATEDIFF(wk, AnalyseStartDate, AnalyseEndDate) * 2)
-(CASE WHEN DATENAME(dw, AnalyseStartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, AnalyseEndDate) = 'Saturday' THEN 1 ELSE 0 END) as DurationOfAnalyseGate,
ImproveStartDate,ImproveEndDate,(DATEDIFF(dd, ImproveStartDate, ImproveEndDate) + 1)
-(DATEDIFF(wk, ImproveStartDate, ImproveEndDate) * 2)
-(CASE WHEN DATENAME(dw, ImproveStartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, ImproveEndDate) = 'Saturday' THEN 1 ELSE 0 END) as DurationOfImproveGate,
ControlStartDate,ControlEndDate,(DATEDIFF(dd, ControlStartDate, ControlEndDate) + 1)
-(DATEDIFF(wk, ControlStartDate, ControlEndDate) * 2)
-(CASE WHEN DATENAME(dw, ControlStartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, ControlEndDate) = 'Saturday' THEN 1 ELSE 0 END) as DurationOfControlGate
FROM Project_Actual_view ww
left join view_tag vt1 on vt1.object_id = ww.workid and vt1.tagset_name = 'Organization'
left join view_tag vt2 on vt2.object_id = ww.workid and vt2.tagset_name = 'Overall Project Status'
May 30, 2012 at 1:55 am
Hi looks like you are trying to work out your Six Sigma phase duration excluding weekends?
Have you considered using a calendar table for this it could save you quite a bit of the logic in the query
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 30, 2012 at 2:02 am
Andy Hyslop (5/30/2012)
Hi looks like you are trying to work out your Six Sigma phase duration excluding weekends?Have you considered using a calendar table for this it could save you quite a bit of the logic in the query
Andy
Thats exactly what I suggested on the original topic http://www.sqlservercentral.com/Forums/Topic1306422-391-4.aspx as the OP wanted to factor in holidays.
Good places to start on a calendar table are these links
May 30, 2012 at 2:09 am
Thats exactly what I suggested on the original topic http://www.sqlservercentral.com/Forums/Topic1306422-391-4.aspx as the OP wanted to factor in holidays.
Good places to start on a calendar table are these links
Thanks Anthony
Didn't spot that topic, looks like you've already done quite a bit on this already..
Yep definitely the way to go on this one!
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 30, 2012 at 2:58 am
thanks
But there are many Join in my query, I want them to replace with Subqueries Bcoz it is taking much time to execute..
May 30, 2012 at 3:05 am
This is one more Issue
User_Name UserId
XYZ 102
ABC 101
REP 102
PQR 103
MNL 102
I want the Output like this
UserId UserName
101 ABC
102 XYZ,REP,MNL
103 PQR
When i use XML PATH for this Concatenation
I'm Getting the result as
<username>XYZ,REP,MNL</username>
But i dont want the <username></username>
select username from MyTable where role = 'Championship'
FOR XML PATH('')
May 30, 2012 at 3:06 am
As you are nesting views (which isn't a good thing IMO anyway) within your query in order to script the sub queries for you we would need to see the SQL that creates the views that you are joining on..
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 30, 2012 at 3:44 am
farooq.hbs (5/30/2012)
thanksBut there are many Join in my query, I want them to replace with Subqueries Bcoz it is taking much time to execute..
Subqueries are less efficient than a join on a table, have you looked at how many reads your query does agaisnt each table?
Also as others suggest take a look a the calender tables
***The first step is always the hardest *******
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply