September 24, 2014 at 9:18 am
CREATE TABLE #Tracking ( ID INT IDENTITY(1,1),COrderID INT,OrderNumber VARCHAR(100),ProjectName VARCHAR(100),ProductName VARCHAR(20),Units [NUMERIC](21, 6) NULL,TotalTrigUnits FLOAT ,Haslift BIT,ClientName VARCHAR(500), )
INSERT INTO #Tracking
(
COrderID ,
OrderNumber ,
ProjectName ,
ProductName ,
Units ,
TotalTrigUnits ,
Haslift ,
ClientName
)
SELECT 2233 ,'ABCD-1234','Mary Rose','',101.33,101.33,1,'RRR' UNION
SELECT 2233 ,'ABCD-1234','Mary Rose','',1594.33,1594.33,1,'RRR' UNION
SELECT 2233 ,'ABCD-1234','Mary Rose','',131.22,131.22,1,'RRR' UNION
SELECT 2233 ,'','Mary Rose','U charge',155.33,0,1,'RRR'
SELECT * FROM #Tracking
DROP TABLE #Tracking
WHEN for the same project,Client and COrderID, when produtName = '' and Order Number <> '' then I want to populate TotalTrigUnits from Units field of the same line item.
Desired result set :
SELECT 2233 ,'','Mary Rose','U charge',155.33,155.33,1,'RRR'
Thanks,
PSB
September 24, 2014 at 10:46 am
I think you want something like this, but I'm not sure:
CREATE TABLE #Tracking
(
ID INT IDENTITY(1, 1),
COrderID INT,
OrderNumber VARCHAR(100),
ProjectName VARCHAR(100),
ProductName VARCHAR(20),
Units [NUMERIC](21, 6) NULL,
TotalTrigUnits FLOAT,
Haslift BIT,
ClientName VARCHAR(500),
)
INSERT INTO #Tracking
(
COrderID,
OrderNumber,
ProjectName,
ProductName,
Units,
TotalTrigUnits,
Haslift,
ClientName
)
SELECT
2233,
'ABCD-1234',
'Mary Rose',
'',
101.33,
101.33,
1,
'RRR'
UNION
SELECT
2233,
'ABCD-1234',
'Mary Rose',
'',
1594.33,
1594.33,
1,
'RRR'
UNION
SELECT
2233,
'ABCD-1234',
'Mary Rose',
'',
131.22,
131.22,
1,
'RRR'
UNION
SELECT
2233,
'',
'Mary Rose',
'U charge',
155.33,
0,
1,
'RRR'
SELECT
*
FROM
#Tracking AS T
UPDATE
#Tracking
SET
TotalTrigUnits = #Tracking.Units
WHERE
EXISTS ( SELECT
1
FROM
#Tracking AS T
WHERE
T.COrderID = #Tracking.COrderID AND
T.ProjectName = #Tracking.ProjectName AND
T.ClientName = #Tracking.ClientName AND
T.ProductName = '' AND
T.OrderNumber <> '' );
SELECT
*
FROM
#Tracking AS T
DROP TABLE #Tracking
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 24, 2014 at 11:16 am
Thanks that worked perfectly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply