May 25, 2012 at 4:19 am
Hi all
I have the table Like this
WorkId WorkName CurrentStatus ActualStartDAte ActualEndDate ParentWorkId
100DefineProposed25-05-200726-06-2008100
200MeasureComplted26-06-200927-09-2009100
300analysecancelled2-02-200930-05-2010100
400controlnot started03-05-201030-06-2011100
The Output should be lyk this :
DefineActualStartDAte DeifneActualEndDate MeasureActualDate MeasureActualEndDate AnalyseStartdate
25-05-200726-06-200826-06-200927-09-20092-02-2009
AnalyseEndDate ControlStartDate Controlenddate
30-05-2010 03-05-2010 30-06-2011
I tried with this query
With Project_view(WorkId,Name,StatusCurrent,ActiveProcess,ActiveGateDate)
As
(
Select vw.work_id as WorkId, vw.name as Name,vw.status_current as StatusCurrent,
vp.process_name as ActiveProcess,vw.actual_start_date as ActiveGateDate
from view_work vw INNER JOIN View_Process vp ON vw.process_id = vp.process_id
join View_Work wv on vw.active_gate = wv.work_id where vw.active_gate is not null
)
select distinct WorkId,Name,StatusCurrent,ActiveProcess,ActiveGateDate from Project_view
Need a solution on this
May 25, 2012 at 4:29 am
You will need to pivot or cross tab.
There are two links in my signature to cross tabbing, also if you follow the second link in my signature on posting code, we might be able to help you more.
May 25, 2012 at 4:32 am
First, this is how you should post your sample data: -
SELECT WorkId, WorkName, CurrentStatus,
ActualStartDAte, ActualEndDate, ParentWorkId
INTO #yourTable
FROM (VALUES(100,'Define','Proposed','25-05-2007','26-06-2008',100),
(200,'Measure','Complted','26-06-2009','27-09-2009',100),
(300,'analyse','cancelled','2-02-2009','30-05-2010',100),
(400,'control','not started','03-05-2010','30-06-2011',100)
)a(WorkId, WorkName, CurrentStatus, ActualStartDAte, ActualEndDate, ParentWorkId);
That way, it's readily consumable for anyone that wants to help you.
BEGIN TRAN
--Sample data
SELECT WorkId, WorkName, CurrentStatus,
ActualStartDAte, ActualEndDate, ParentWorkId
INTO #yourTable
FROM (VALUES(100,'Define','Proposed','25-05-2007','26-06-2008',100),
(200,'Measure','Complted','26-06-2009','27-09-2009',100),
(300,'analyse','cancelled','2-02-2009','30-05-2010',100),
(400,'control','not started','03-05-2010','30-06-2011',100)
)a(WorkId, WorkName, CurrentStatus, ActualStartDAte, ActualEndDate, ParentWorkId);
--Solution
SELECT
MAX(CASE WHEN WorkName = 'Define' THEN ActualStartDAte END) AS DefineActualStartDate,
MAX(CASE WHEN WorkName = 'Define' THEN ActualEndDate END) AS DefineActualEndDate,
MAX(CASE WHEN WorkName = 'Measure' THEN ActualStartDAte END) AS MeasureActualStartDate,
MAX(CASE WHEN WorkName = 'Measure' THEN ActualEndDate END) AS MeasureActualEndDate,
MAX(CASE WHEN WorkName = 'analyse' THEN ActualStartDAte END) AS AnalyseActualStartDate,
MAX(CASE WHEN WorkName = 'analyse' THEN ActualEndDate END) AS AnalyseActualEndDate,
MAX(CASE WHEN WorkName = 'control' THEN ActualStartDAte END) AS ControlActualStartDate,
MAX(CASE WHEN WorkName = 'control' THEN ActualEndDate END) AS ControlActualEndDate
FROM #yourTable;
ROLLBACK
May 25, 2012 at 4:34 am
I didnt find the tagging to ur Signature
May 25, 2012 at 4:41 am
Cadavre (5/25/2012)
First, this is how you should post your sample data: -
SELECT WorkId, WorkName, CurrentStatus,ActualStartDAte, ActualEndDate, ParentWorkId
INTO #yourTable
FROM (VALUES(100,'Define','Proposed','25-05-2007','26-06-2008',100),
(200,'Measure','Complted','26-06-2009','27-09-2009',100),
(300,'analyse','cancelled','2-02-2009','30-05-2010',100),
(400,'control','not started','03-05-2010','30-06-2011',100)
)a(WorkId, WorkName, CurrentStatus, ActualStartDAte, ActualEndDate, ParentWorkId);
That way, it's readily consumable for anyone that wants to help you.
BEGIN TRAN--Sample data
SELECT WorkId, WorkName, CurrentStatus,
ActualStartDAte, ActualEndDate, ParentWorkId
INTO #yourTable
FROM (VALUES(100,'Define','Proposed','25-05-2007','26-06-2008',100),
(200,'Measure','Complted','26-06-2009','27-09-2009',100),
(300,'analyse','cancelled','2-02-2009','30-05-2010',100),
(400,'control','not started','03-05-2010','30-06-2011',100)
)a(WorkId, WorkName, CurrentStatus, ActualStartDAte, ActualEndDate, ParentWorkId);
--Solution
SELECT
MAX(CASE WHEN WorkName = 'Define' THEN ActualStartDAte END) AS DefineActualStartDate,
MAX(CASE WHEN WorkName = 'Define' THEN ActualEndDate END) AS DefineActualEndDate,
MAX(CASE WHEN WorkName = 'Measure' THEN ActualStartDAte END) AS MeasureActualStartDate,
MAX(CASE WHEN WorkName = 'Measure' THEN ActualEndDate END) AS MeasureActualEndDate,
MAX(CASE WHEN WorkName = 'analyse' THEN ActualStartDAte END) AS AnalyseActualStartDate,
MAX(CASE WHEN WorkName = 'analyse' THEN ActualEndDate END) AS AnalyseActualEndDate,
MAX(CASE WHEN WorkName = 'control' THEN ActualStartDAte END) AS ControlActualStartDate,
MAX(CASE WHEN WorkName = 'control' THEN ActualEndDate END) AS ControlActualEndDate
FROM #yourTable;
ROLLBACK
If there are multiple ParentWorkID's then just need to add a group by
SELECT WorkId, WorkName, CurrentStatus,
ActualStartDAte, ActualEndDate, ParentWorkId
INTO #yourTable
FROM (VALUES(100,'Define','Proposed','25-05-2007','26-06-2008',100),
(200,'Measure','Complted','26-06-2009','27-09-2009',100),
(300,'analyse','cancelled','2-02-2009','30-05-2010',100),
(400,'control','not started','03-05-2010','30-06-2011',100),
(101,'Define','Proposed','25-12-2007','26-06-2008',101),
(201,'Measure','Complted','26-12-2009','27-09-2009',101),
(301,'analyse','cancelled','2-12-2009','30-05-2010',101),
(401,'control','not started','03-12-2010','30-06-2011',101)
)a(WorkId, WorkName, CurrentStatus, ActualStartDAte, ActualEndDate, ParentWorkId);
SELECT
MAX(CASE WHEN WorkName = 'Define' THEN ActualStartDAte END) AS DefineActualStartDate,
MAX(CASE WHEN WorkName = 'Define' THEN ActualEndDate END) AS DefineActualEndDate,
MAX(CASE WHEN WorkName = 'Measure' THEN ActualStartDAte END) AS MeasureActualStartDate,
MAX(CASE WHEN WorkName = 'Measure' THEN ActualEndDate END) AS MeasureActualEndDate,
MAX(CASE WHEN WorkName = 'analyse' THEN ActualStartDAte END) AS AnalyseActualStartDate,
MAX(CASE WHEN WorkName = 'analyse' THEN ActualEndDate END) AS AnalyseActualEndDate,
MAX(CASE WHEN WorkName = 'control' THEN ActualStartDAte END) AS ControlActualStartDate,
MAX(CASE WHEN WorkName = 'control' THEN ActualEndDate END) AS ControlActualEndDate
FROM #yourTable
GROUP BY ParentWorkID
DROP TABLE #yourTable
Links from my signature
How to post code for best results http://www.sqlservercentral.com/articles/Best+Practices/61537/
CrossTabs Part 1 http://www.sqlservercentral.com/articles/T-SQL/63681/
CrossTabs Part 2 http://www.sqlservercentral.com/articles/Crosstab/65048/
May 25, 2012 at 5:04 am
With Project_view(WorkId,Name,StatusCurrent,ActiveProcess,ActiveGateDate,ActualStartDate,ActualEndDate,parent_work_id)
As
(
Select distinct vw.work_id as WorkId, vw.name as Name,vw.status_current as StatusCurrent,
vp.process_name as ActiveProcess,vw.actual_start_date as ActiveGateDate,vw.actual_start_date,
vw.actual_end_date,vw.parent_work_id
from view_work vw INNER JOIN View_Process vp ON vw.process_id = vp.process_id
join View_Work wv on vw.active_gate = wv.work_id
)
select WorkId,Name,StatusCurrent,ActiveProcess,ActiveGateDate,
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
The Output is
Column 'Project_view.WorkId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
What is tis error
May 25, 2012 at 5:06 am
your missing a GROUP BY clause
May 25, 2012 at 5:33 am
With Project_view(WorkId,Name,StatusCurrent,ActiveProcess,ActiveGateDate,ActualStartDate,ActualEndDate,parent_work_id)
As
(
Select distinct vw.work_id as WorkId, vw.name as Name,vw.status_current as StatusCurrent,
vp.process_name as ActiveProcess,vw.actual_start_date as ActiveGateDate,vw.actual_start_date,
vw.actual_end_date,vw.parent_work_id
from view_work vw INNER JOIN View_Process vp ON vw.process_id = vp.process_id
join View_Work wv on wv.active_gate = vw.work_id
)
select WorkId,Name,StatusCurrent,ActiveProcess,ActiveGateDate ,
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
Hey thanks for ur responce but still there is an error
Column 'view_work.status_current' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
May 25, 2012 at 5:41 am
You need to group by ALL columns which are not contained in an aggregate function.
May 28, 2012 at 12:06 am
Hi Guys....
I Need to retrieve Days from the Dates..Using DATEDIFF Funtion
With Project_view(WorkId,Name,StatusCurrent,ActiveProcess,ActiveGateDate,ActualStartDate,ActualEndDate)
As
(
Select distinct vw.work_id as WorkId, vw.name as Name,vw.status_current as StatusCurrent,
vp.process_name as ActiveProcess,vw.actual_start_date as ActiveGateDate,vw.actual_start_date,
vw.actual_end_date
from view_work vw INNER JOIN View_Process vp ON vw.process_id = vp.process_id
join View_Work wv on wv.active_gate = vw.work_id
)
select WorkId,Name,StatusCurrent,ActiveProcess,ActiveGateDate ,
(Case when Name = '1. Define' then ActualStartDate end) as DefineStartDate,
(Case when Name = '1. Define' then ActualEndDate end) as DefineEndDate,
(case when Name = '2. Measure' then ActualStartDate end) as MeasureStartDate,
(Case When Name = '2. Measure' then ActualEndDate end) as MeasureEndDate,
(Case When Name = '3. Analyze' then ActualStartDate end) as AnalyseStartDate,
(case When Name = '3. Analyze' then ActualEndDate end) as AnalyseEndDate,
(case When Name = '4. Improve' then ActualStartDate end) as ImproveStartDate,
(Case When Name = '4. Improve' then ActualEndDate end) as ImproveEndDate,
(Case When Name = '5. Control' then ActualStartDate end) as ControlStartDate,
(Case When Name = '5. Control' then ActualEndDate end) as ControlEndDate
from Project_view
In the Query
DAtedifff(Day, ActualStartDate ,ActualEndDate ) as duration of gate for Define.
Like this i need to calculte for the remaining Measure,Analyse,Improve, &Control Dates.
When i use lyk this
DAtediff (Day, ActualStartDate ,ActualEndDate )
it showing the same result
Any Suggestion to get the desired result
May 28, 2012 at 1:09 am
wrap the second select in another CTE then query that CTE using datediff functions on the second date columns DATEDIFF(DAY, DefineStart, DefineEnd) AS DefineDuration
May 28, 2012 at 1:36 am
Hi Anthony,
I got one more Error..
I have table lyk
WorkId
May 28, 2012 at 1:40 am
With Project_view(WorkId,Name,StatusCurrent,ActiveProcess,ActiveGateDate,ActualStartDate,ActualEndDate)
As
(
Select distinct vw.work_id as WorkId, vw.name as Name,vw.status_current as StatusCurrent,
vp.process_name as ActiveProcess,vw.actual_start_date as ActiveGateDate,vw.actual_start_date,
vw.actual_end_date
from view_work vw INNER JOIN View_Process vp ON vw.process_id = vp.process_id
join View_Work wv on wv.active_gate = vw.work_id
),
Project_View2
AS
(
select WorkId,Name,StatusCurrent,ActiveProcess,ActiveGateDate ,
(Case when Name = '1. Define' then ActualStartDate end) as DefineStartDate,
(Case when Name = '1. Define' then ActualEndDate end) as DefineEndDate,
(case when Name = '2. Measure' then ActualStartDate end) as MeasureStartDate,
(Case When Name = '2. Measure' then ActualEndDate end) as MeasureEndDate,
(Case When Name = '3. Analyze' then ActualStartDate end) as AnalyseStartDate,
(case When Name = '3. Analyze' then ActualEndDate end) as AnalyseEndDate,
(case When Name = '4. Improve' then ActualStartDate end) as ImproveStartDate,
(Case When Name = '4. Improve' then ActualEndDate end) as ImproveEndDate,
(Case When Name = '5. Control' then ActualStartDate end) as ControlStartDate,
(Case When Name = '5. Control' then ActualEndDate end) as ControlEndDate
from Project_view
)
SELECT
......... --Put what you need in here
FROM
Project_View2
May 28, 2012 at 1:46 am
HI All
I got to retrieve record within one table(Say subquerues)
I hav table lyk this
WorkId WorkName Actual_start_Date ActiveGate Status Current
10052 XYZ 25-02-1980 Null C
10025 YZX 01-01-1987 10052 N
10026 ABC 02-05-1968 Null N
10028 MNO 05-05-2001 10026 P
To get Result:
I need all the WorkName, WorkId,ActualStartDAte, & ActiveGateDAte (Actual_start_Date Based on the condition WorkId = Activegate)
Practised:
Select a.WorkId,a.WorkName,a.Actual_Start_date,a.StatusCurrent,b.Actual_start_Date as ActiveGateDAte From View Work a Join View_Work b On a.ActiveGate = b.WorkId
By Using The above Query,I'm Not able get to the result Set
I need All the rows with ActiveGateDate if present
May 28, 2012 at 2:20 am
New problem needs a new thread.
Please take a look through the link in my signature on how to post code/date for the best help, so that we can help you out better.
But that can be solved with a self inner join, to bring back the rows which have an ActiveGate value, if I have understood your requirements, but as you have not posted expected results we cannot say if it will.
set dateformat dmy
declare @t table (WorkId int, WorkName char(3), Actual_start_Date date, ActiveGate int, StatusCurrent char(1))
insert into @t values
(10052, 'XYZ', '25-02-1980', Null, 'C'),
(10025, 'YZX', '01-01-1987', 10052, 'N'),
(10026, 'ABC', '02-05-1968', Null, 'N'),
(10028, 'MNO', '05-05-2001', 10026, 'P')
select * from @t
select
t1.workid,
t1.workname,
t1.actual_start_date,
t2.actual_start_date as ActualGateDate
from
@t t1
inner join
@t t2
on
t1.activegate = t2.workid
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply