Help with SQL query

• PSB

  SSChampion

  Points: 13195

  More actions

  September 29, 2014 at 9:26 am

  #291112

  Hi,

  I need help with an update query given below,

  CREATE TABLE #Tracking ( ID INT IDENTITY(1,1),COrderID INT,SNo VARCHAR(100),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 ,

  SNo,

  OrderNumber ,

  ProjectName ,

  ProductName ,

  Units ,

  TotalTrigUnits ,

  Haslift ,

  ClientName

  )

  SELECT 2233 ,'ABCD-1234','P-109_hjhjj hh bb','Mary Rose','',101.33,101.33,1,'RRR' UNION

  SELECT 2233 ,'ABCD-2234','P-109_hjhjj hh bb','Mary Rose','',1594.33,1594.33,1,'RRR' UNION

  SELECT 2233 ,'ABCD-3234','P-109_hjhjj hh bb','Mary Rose','',131.22,131.22,1,'RRR' UNION

  SELECT 2233 ,'','P-109_hjhjj hh bb','Mary Rose','U charge',1826.88,1826.883,1,'RRR' UNION

  SELECT 2244 ,'ABCD-1234','P-109_abcd yy bb','Mary Rose','',0,0,1,'RRR' UNION

  SELECT 2244 ,'','P-109_abcd yy bb','Mary Rose','U charge',0,0,1,'RRR'

  SELECT * FROM #Tracking

  DROP TABLE #Tracking

  WHEN for the same project,Client , COrderID, SNo, AND Order Number <> '' I want the sum from TotalTrigUnits field (101.33 + 1594.33 + 131.22) = 1826.883 to be populated in (Units and TotTrigUnits) where COrderID = 2244 and ProductName = 'U Charge'

  5th and 6th line items has same Client,Project and same SNo ( before the _ character) and different COrderID

  1866.88 should be updated in the below :

  Desired result set :

  SELECT 2244 ,'','P-109_abcd yy bb','Mary Rose','U charge',1866.88,1866.88,1,'RRR'

  Thanks,

  PSB

• PSB

  SSChampion

  Points: 13195

  More actions

  September 29, 2014 at 1:26 pm

  #1748920

  Any help will be greatly appreciated.

  Thanks,

  PSB

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 29, 2014 at 1:42 pm

  #1748927

  Maybe something like this will work, but your description makes it confusing.

  UPDATE a SET

  Units = x.Units,

  TotalTrigUnits = x.TotalTrigUnits

  --SELECT *

  FROM #Tracking a

  CROSS APPLY( SELECT SUM(b.Units) Units, SUM(b.TotalTrigUnits) TotalTrigUnits

  FROM #Tracking b

  WHERE a.ProjectName = b.ProjectName

  AND a.ClientName = b.ClientName

  AND a.COrderID > b.COrderID

  AND LEFT( a.OrderNumber, CHARINDEX('_', a.OrderNumber)) = LEFT( b.OrderNumber, CHARINDEX('_', b.OrderNumber))

  AND b.SNo <> ''

  GROUP BY b.ProjectName, b.ClientName, b.COrderID)x

  WHERE a.ProductName = 'U charge'

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2