May 28, 2012 at 6:16 am
Thats selecting from the view, I want the create view syntax
CREATE VIEW view_work
AS
SELECT
a
b
c
d
e
f
g
FROM
sometable
May 28, 2012 at 6:20 am
Hi Anthony This is my table
Work_Id Name Current_Status Actual_Start_Date Actual End_Date ActiveGate
1258282 AXF Complted 20-05-1987 20-05-1988 Null
1259874 1.Define On Track 21-06-1989 18-06-1992 Null
15697445 2.Measure Off track 2-06-1987 06-09-1996 1258282
1589746 XYZ Promoted 5-05-06 25-05-1968 15967445
I need ouptu Lyk 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
May 28, 2012 at 6:25 am
I need output Lyk this
Work Id Work Name Define_Start_Date DefineEndDate Measure StartDAte Measure End Date
1258282 AXF 20-05-1987 20-05-1988 Null Null
1259874 1.DEFine 21-06-1989 18-06-1992 Null Null
15697445 2.Measure Null Null 2-06-1987 06-09-1996
thi s
May 28, 2012 at 6:30 am
What are the actual start dates for rows 3 and 4 as they are incorrectly formatted.
Based on that information you have provided this is how you would create the sample data when we ask for it
CREATE TABLE #temp
(
Work_ID INT,
Name NVARCHAR(20),
Current_Status NVARCHAR(20),
Actual_Start_Date DATE,
Actual_End_Date DATE,
ActiveGate INT
)
INSERT INTO #temp VALUES
(1258282,'AXF','Completed','1987-05-20','1988-05-20',Null),
(1259874,'1.Define','On Track','1989-06-21','1992-06-18',Null),
(15697445,'2.Measure','Off Track','','1996-09-06',1258282),
(1589746,'XYZ','Promoted','','1968-05-25',15967445)
May 28, 2012 at 6:35 am
Select work_id,name,(select process_name From view_process Where Process_Id In ( Select Process_id from View_work))as Process,
Status_current,active_gate FRom View_Work
----------------
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
-------
I'm trying Use SubQueries To fetch Result from two Table Instead of Joins...
May 28, 2012 at 6:37 am
you have multiple process id and you can only link 1 row to 1 row, you cant link 1 row to many rows in a result set
CREATE TABLE #temp
(
Work_ID INT,
Name NVARCHAR(20),
Current_Status NVARCHAR(20),
Actual_Start_Date DATE,
Actual_End_Date DATE,
ActiveGate INT
)
INSERT INTO #temp VALUES
(1258282,'AXF','Completed','1987-05-20','1988-05-20',Null),
(1259874,'1.Define','On Track','1989-06-21','1992-06-18',Null),
(15697445,'2.Measure','Off Track','1987-06-02','1996-09-06',1258282),
(1589746,'XYZ','Promoted','2006-05-05','1968-05-25',15967445)
SELECT
Work_ID,
Name,
MAX(CASE WHEN Name <> '2.Measure' THEN Actual_Start_Date END) AS Define_Start_Date,
MAX(CASE WHEN Name <> '2.Measure' THEN Actual_End_Date END) AS Define_End_Date,
MAX(CASE WHEN Name = '2.Measure' THEN Actual_Start_Date END) AS Measure_Start_Date,
MAX(CASE WHEN Name = '2.Measure' THEN Actual_End_Date END) AS Measure_End_Date
FROM
#temp
GROUP BY
Work_ID,
Name
May 28, 2012 at 11:37 pm
Hi anthony,
I have two date Define_Start_date and Define_End_Date
Say:
Define_Start_date = 28-04-2012
Define_End_date = 28- 05-2012
I need to get days between these two dates Excluding holidays(i.e Sturday & Sunday)
Using DateDiff(day,Define_Start_date ,Define_End_date )
The output should be total number of days between these two dates excluding Holidays(saturday & sunday) coming between these dates
May 29, 2012 at 12:43 am
Sounds to me like you need a calendar table which you need to lookup through to determin if the day is a holiday
If you only want to exclude sat n sun then do a date diff in days of the two dates, then minus the date diff in weeks multiplied by two to get the amount of sat n suns in that period.
But if your have other dates that need excluding you will need to do that in your own logic somehow like a calendar table.
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply