May 28, 2012 at 2:46 am
Thanks for the reply...
But i need all the four recored (All records from the table )With ActiveGateDate
Say: If Active Gate Is Null then ActiveGateDate vl also be null
If Active Gate is Not Null then ActiveGateDate vl also be Not Null ( ActualStartDate Based on WorkId = Active Gate)
The Result Set Should Like this:
WorkId WorkName Actual_start_Date ActiveGate Status Current ActiveGateDAte
10052 XYZ 25-02-1980 Null C Null
10025 YZX 01-01-1987 10052 N 01-01-1987
10026 ABC 02-05-1968 Null N null
10028 MNO 05-05-2001 10026 P 05-05-2001
May 28, 2012 at 2:51 am
just change the join then to join t1 on workid and t2 on workid and use a case statement to get the actualgatedate.
May 28, 2012 at 4:46 am
1800m1g0000h69g9eujg000000Reduce non-value added calls stemming from complex marketing initiativesCanceled DMAICNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip1m600000001. DefineCompleted DMAICNULL2007-06-25 01:00:00.0002007-07-18 11:52:44.73723NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip1qkg0000001.01 Project Charter DevelopedCompleted NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip1uig0000001.02 Identify CTQsCompleted NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip20sg0000001.03 Map the High Level Process (SIPOC)Completed NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip23eg0000001.04 Change Readiness AssessmentNot Started NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
The values returning Null :
If Name is not equal to Define,Measure,Improve,Control,Analyse the Actual Dates are returning Null
May 28, 2012 at 4:47 am
1800m1g0000h69g9eujg000000Reduce non-value added calls stemming from complex marketing initiativesCanceled DMAICNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip1m600000001. DefineCompleted DMAICNULL2007-06-25 01:00:00.0002007-07-18 11:52:44.73723NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip1qkg0000001.01 Project Charter DevelopedCompleted NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip1uig0000001.02 Identify CTQsCompleted NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip20sg0000001.03 Map the High Level Process (SIPOC)Completed NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip23eg0000001.04 Change Readiness AssessmentNot Started NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69g9eujg000000Reduce non-value added calls stemming from complex marketing initiativesCanceled DMAICNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip1m600000001. DefineCompleted DMAICNULL2007-06-25 01:00:00.0002007-07-18 11:52:44.73723NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip1qkg0000001.01 Project Charter DevelopedCompleted NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip1uig0000001.02 Identify CTQsCompleted NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip20sg0000001.03 Map the High Level Process (SIPOC)Completed NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
1800m1g0000h69ip23eg0000001.04 Change Readiness AssessmentNot Started NULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
Returning Null Values in the actual Date
May 28, 2012 at 4:49 am
This is my Query
------------------------------------------------------
With Project_view(WorkId,Name,StatusCurrent,ActualStartDate,ActualEndDate,ActiveProcess,ActiveGate)
As
(
SELECT DISTINCT TOP 2000 vw.work_id as WorkId, vw.name as Name,vw.status_current as StatusCurrent,
vw.actual_start_date,vw.actual_end_date,vp.process_name as ActiveProcess,wv.active_gate as ActiveGate
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.work_id
),
project_actual_view
AS
(
SELECT WorkId,Name,StatusCurrent,ActiveProcess,ActiveGate,
Max(Case when Name = '1. Define' then ActualStartDate end) as DefineStartDate,
Max(Case when Name = '1. Define' then ActualEndDate end) as DefineEndDate,
Max(case when Name = '2. Measure' then ActualStartDate end) as MeasureStartDate,
Max(Case When Name = '2. Measure' then ActualEndDate end) as MeasureEndDate,
Max(Case When Name = '3. Analyze' then ActualStartDate end) as AnalyseStartDate,
Max(case When Name = '3. Analyze' then ActualEndDate end) as AnalyseEndDate,
Max(case When Name = '4. Improve' then ActualStartDate end) as ImproveStartDate,
Max(Case When Name = '4. Improve' then ActualEndDate end) as ImproveEndDate,
Max(Case When Name = '5. Control' then ActualStartDate end) as ControlStartDate,
Max(Case When Name = '5. Control' then ActualEndDate end) as ControlEndDate
FROM Project_view Group by WorkId,Name,StatusCurrent,ActiveProcess,ActiveGate
)
SELECT WorkId,Name,StatusCurrent,ActiveProcess,ActiveGate,DefineStartDate,DefineEndDate,
DATEDIFF(DAY, DefineStartDate,DefineEndDate) AS DurationGate,
MeasureStartDate,MeasureEndDate,DATEDIFF(DAY, MeasureStartDate,MeasureEndDate) AS MeasureDurationGate,
AnalyseStartDate,AnalyseEndDate,DATEDIFF(DAY, AnalyseStartDate,AnalyseEndDate) AS AnalyseDurationGate,
ImproveStartDate,ImproveEndDate,DATEDIFF(DAY, ImproveStartDate,ImproveEndDate) AS ImproveDurationGate,
ControlStartDate,ControlEndDate,DATEDIFF(DAY, ControlStartDate,ControlEndDate) AS ControlDurationGate
FROM project_actual_view
May 28, 2012 at 4:49 am
Ok now I am going to insist that you read the link to posting code and data.
I need the DDL of the table/s and views.
The data from each tables
The expected outcome of what you want from that data
What you have tried so far
May 28, 2012 at 4:55 am
Ok
the Query Used for this Is ;...All datas are from Single Views only..ViewWork
SELECT DISTINCT TOP 2000 vw.work_id as WorkId, vw.name as Name,vw.status_current as StatusCurrent,
vw.actual_start_date,vw.actual_end_date,vp.process_name as ActiveProcess,vw.active_gate as ActiveGate
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.work_id
1800m1g0000h69g9eujg000000Reduce non-value added calls stemming from complex marketing initiativesCanceled 2007-06-25 01:00:00.0002008-06-18 17:00:00.000DMAICNULL
1800m1g0000h69ip1m600000001. DefineCompleted 2007-06-25 01:00:00.0002007-07-18 11:52:44.737DMAICNULL
1800m1g0000h69ip1qkg0000001.01 Project Charter DevelopedCompleted 2007-06-26 19:40:56.4602007-07-16 15:20:33.347NULLNULL
1800m1g0000h69ip1uig0000001.02 Identify CTQsCompleted 2007-06-28 15:24:12.4572007-07-16 15:20:33.527NULLNULL
1800m1g0000h69ip20sg0000001.03 Map the High Level Process (SIPOC)Completed 2007-06-27 10:24:31.4372007-06-27 10:25:53.460NULLNULL
1800m1g0000h69ip23eg0000001.04 Change Readiness AssessmentNot Started NULLNULLNULLNULL
1800m1g0000h69ip25vg0000001.05 ARMI WorksheetCompleted 2007-06-27 09:00:00.0002007-07-11 17:00:00.000NULLNULL
1800m1g0000h69ip289g000000Define: Link to Tools & TemplatesNot Started NULLNULLNULLNULL
1800m1g0000h69ip2d6g0000001.06 Summary (Optional)Completed 2007-06-13 14:29:38.2532008-06-13 17:00:00.000NULLNULL
1800m1g0000h69ip2fv00000001.07 Project Date Change RequestNot Started NULLNULLNULLNULL
The Reslut should be like this
NameStatusActive ProcessActive Gate DateChampion(s) or VSO/MBB(s)Project Lead/SPOCStatus Comment1. Define Start Date (Actual)1. Define End Date (Actual)Duration Of Gate2. Measure Start Date (Actual)2. Measure End Date (Actual)Duration Of Gate3. Analyze Start Date (Actual)3. Analyze End Date (Actual)Duration Of Gate4. Improve Start Date (Actual)4. Improve End Date (Actual)Duration Of Gate5. Control Start Date (Actual)5. Control End Date (Actual)Duration Of Gate
"qualification criteria. Train sales reps to use CPSOS and qualify by address.
"
CEMW: Improve CBRE Vendor OversightOn Track DMAIC06/28/2010Fred Kisiday,Charlie KeaneRandy Carver06/28/201015
CEMW: Improve Construction Time ReportingCompleted DMAICFred Kisiday,David ClevengerBrad Hall"Summary Remarks
Measure deliverables complete except financial workbook. Reworking Financial Workbook. Working Analize deliveables.
"02/01/200802/26/20082502/26/200804/01/20083504/01/200805/14/20084305/14/200807/07/20085407/07/200808/25/200850
CEMW: Increase FTTP LU Sales AvailabilityCompleted DMAICJeffrey Dietrich,Patrick HamrockGreg Kneusel09/08/200909/23/20091509/23/200911/11/20094911/12/200912/07/20092512/07/200902/26/20108102/26/201003/03/20106
CEMW: PDAC Processes and CommunicationCanceled DMAICBrad Hall,Charlene LazetteDan Schaafsma"Summary Remarks
7 main items have been identified for correction. Work in progress
"09/30/200806/24/200926706/24/200902/25/201024602/25/201002/25/2010102/25/201002/25/2010102/25/201002/25/20101
I Final query which i use dis : ------------------------------------------------------
With Project_view(WorkId,Name,StatusCurrent,ActualStartDate,ActualEndDate,ActiveProcess,ActiveGate)
As
(
SELECT DISTINCT TOP 2000 vw.work_id as WorkId, vw.name as Name,vw.status_current as StatusCurrent,
vw.actual_start_date,vw.actual_end_date,vp.process_name as ActiveProcess,vw.active_gate as ActiveGate
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.work_id
),
project_actual_view
AS
(
SELECT WorkId,Name,StatusCurrent,ActiveProcess,ActiveGate,
Max(Case when Name = '1. Define' then ActualStartDate end) as DefineStartDate,
Max(Case when Name = '1. Define' then ActualEndDate end) as DefineEndDate,
Max(case when Name = '2. Measure' then ActualStartDate end) as MeasureStartDate,
Max(Case When Name = '2. Measure' then ActualEndDate end) as MeasureEndDate,
Max(Case When Name = '3. Analyze' then ActualStartDate end) as AnalyseStartDate,
Max(case When Name = '3. Analyze' then ActualEndDate end) as AnalyseEndDate,
Max(case When Name = '4. Improve' then ActualStartDate end) as ImproveStartDate,
Max(Case When Name = '4. Improve' then ActualEndDate end) as ImproveEndDate,
Max(Case When Name = '5. Control' then ActualStartDate end) as ControlStartDate,
Max(Case When Name = '5. Control' then ActualEndDate end) as ControlEndDate
FROM Project_view Group by WorkId,Name,StatusCurrent,ActiveProcess,ActiveGate
)
SELECT WorkId,Name,StatusCurrent,ActiveProcess,ActiveGate,DefineStartDate,DefineEndDate,
DATEDIFF(DAY, DefineStartDate,DefineEndDate) AS DurationGate,
MeasureStartDate,MeasureEndDate,DATEDIFF(DAY, MeasureStartDate,MeasureEndDate) AS MeasureDurationGate,
AnalyseStartDate,AnalyseEndDate,DATEDIFF(DAY, AnalyseStartDate,AnalyseEndDate) AS AnalyseDurationGate,
ImproveStartDate,ImproveEndDate,DATEDIFF(DAY, ImproveStartDate,ImproveEndDate) AS ImproveDurationGate,
ControlStartDate,ControlEndDate,DATEDIFF(DAY, ControlStartDate,ControlEndDate) AS ControlDurationGate
FROM project_actual_view
May 28, 2012 at 4:56 am
I need the DDL and data from your DB, please read the link on how to generate this information.
May 28, 2012 at 4:58 am
Where is the Link
?
May 28, 2012 at 4:59 am
at the bottom of all my posts, it looks like this
How to post data/code for the best help - Jeff Moden
May 28, 2012 at 5:16 am
Hi Green,
This is simple,,I dont want to complex... If u run tis query u vl get actual start date N end date
SELECT DISTINCT TOP 2000 vw.work_id as WorkId, vw.name as Name,vw.status_current as StatusCurrent,
vw.actual_start_date,vw.actual_end_date,vp.process_name as ActiveProcess,vw.active_gate as ActiveGate
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.work_id
If the Name != "Define or Measure or Control ) then should take the Actual date from the Previous Query..
I'm getting the ooutput as Null if the Name != "Define,Measure,Improve...Etc"
I need to get date From the Actual Start date N Actual End Date From the Above Auery itsef
So i used CTE..
SELECT WorkId,Name,StatusCurrent,ActiveProcess,ActiveGate,
Max(Case when Name = '1. Define' then ActualStartDate else vw.actual_start_date end) as DefineStartDate,
Max(Case when Name = '1. Define' then ActualEndDate end) as DefineEndDate,
Max(case when Name = '2. Measure' then ActualStartDate end) as MeasureStartDate,
Max(Case When Name = '2. Measure' then ActualEndDate end) as MeasureEndDate,
Max(Case When Name = '3. Analyze' then ActualStartDate end) as AnalyseStartDate,
Max(case When Name = '3. Analyze' then ActualEndDate end) as AnalyseEndDate,
Max(case When Name = '4. Improve' then ActualStartDate end) as ImproveStartDate,
Max(Case When Name = '4. Improve' then ActualEndDate end) as ImproveEndDate,
Max(Case When Name = '5. Control' then ActualStartDate end) as ControlStartDate,
Max(Case When Name = '5. Control' then ActualEndDate end) as ControlEndDate
FROM Project_view Group by WorkId,Name,StatusCurrent,ActiveProcess,ActiveGate
I need Output: If Name != " Define nImprove Etc then take the Actual Date
Hope u understood this tym
May 28, 2012 at 5:19 am
farooq.hbs (5/28/2012)
Hi Green,This is simple,,I dont want to complex... If u run tis query u vl get actual start date N end date
SELECT DISTINCT TOP 2000 vw.work_id as WorkId, vw.name as Name,vw.status_current as StatusCurrent,vw.actual_start_date,vw.actual_end_date,vp.process_name as ActiveProcess,vw.active_gate as ActiveGate
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.work_id
No I wont as I do not have access to your SQL server? If your SQL server is internet facing, please provide me with the external IP address and the SA password so that I can connect you your SQL server and get the information I need
If I run the select distince top 2000 query I get
Msg 208, Level 16, State 1, Line 1
Invalid object name 'view_work'.
and also I would get
Msg 208, Level 16, State 1, Line 1
Invalid object name 'view_process'.
May 28, 2012 at 5:57 am
I am not permit to Give the Ip Address N SA password
May 28, 2012 at 6:04 am
I wouldn't of thought you would be which is why we ask for this information.
From the data you have provided we can make best quess as to which bits of it fit into which columns, but in order for us to get you the ansers you need, you first need to give us what we require.
You have 2 views in the SELECT DISTINCT TOP 2000 statement, view_work and view_process. Find these objects in SSMS and check which tables they use and also copy the CREATE VIEW syntax for each. Then find these tables the views use then create the CREATE TABLE statements for the tables. Then create a script which uses INSERT INTO tablename (columnnames) VALUES (col1 value, col2 value.................................)
Then in a readable format maybe something like EXCEL or using a delimiter show is in column based result sets what your expected outcome is.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Something like this
CREATE TABLE tab1
(
Col1 INT IDENTITY(1,1) PRIMARY KEY,
Col2 NVARCHAR(2) NULL
)
GO
CREATE TABLE tab2
(
Col1 INT IDENTITY(1,1) PRIMARY KEY,
Col2 INT NOT NULL,
Col3 INT NOT NULL
)
INSERT INTO tab1 (Col2) VALUES ('AG')
GO
INSERT INTO tab2 (Col2, Col3) VALUES (1,38000)
GO
ALTER TABLE tab2 ADD CONSTRAINT FK_tab2_tab1 FOREIGN KEY tab2 (Col2) REFERENCES tab1 (Col1)
GO
CREATE VIEW view1
AS
SELECT
tab1.Col2 AS Person,
tab2.Col3 AS Salary
FROM
tab1
INNER JOIN
tab2
ON
tab1.Col1 = tab2.Col2
GO
I want my results like this
Col1 | Col2
abcd | 1234
May 28, 2012 at 6:13 am
This is View_Work /****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [work_id]
,[work_type_code]
,[name]
,[objective]
,[visibility]
,[parent_work_id]
,[owner_id]
,[delegated_owner_id]
,[sequence_id]
,[external_project_id]
,[created_by]
,[created_date]
,[last_change_date]
,[assigned_priority]
,[status_current]
,[percent_complete]
,[budgeted_cost]
,[estimated_payback]
,[actual_start_date]
,[actual_end_date]
,[planned_start_date]
,[planned_end_date]
,[system_start_date]
,[system_end_date]
,[baseline_start_date]
,[baseline_end_date]
,[system_latest_start_date]
,[system_latest_end_date]
,[effective_start_date]
,[effective_end_date]
,[planned_duration]
,[planned_duration_unit]
,[active_gate]
,[currency]
,[process_id]
,[phase_id]
,[is_track_resource]
,[is_best_practice]
,[bp_nomination_user_id]
,[bp_nomination_time]
,[bp_nomination_comments]
,[bp_approval_user_id]
,[bp_approval_time]
,[bp_approval_comments]
,[baseline_budget]
,[on_timesheet]
,[require_champion]
,[has_financial_rep]
,[require_financial_rep]
,[lock_percent_complete]
,[is_organization]
,[is_project]
,[is_idea]
,[idea_version]
,[type_name]
,[template_id]
,[sequence_within_parent]
FROM [reporting_ATT].[dbo].[View_Work]
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply