display values mutliping with 1 instead of 0 if 0 is present

• Shanmuga Raj

  Ten Centuries

  Points: 1178

  More actions

  December 21, 2013 at 3:02 pm

  #280619

  In the below table, quantity can be 0 for the primary record.

  In case of value 0 , multiply child value with primary assigning 1 for primary value only for calculation

  CREATE TABLE [dbo].[Travel_Occurs](

  [Mode_Sno] [int] NULL,

  [Mode_Id] [nchar](2) NULL,

  [Mode_Parent_Sno] [int] NULL,

  [Quantity] [numeric](18, 0) NULL

  ) ON [PRIMARY]

  INSERT INTO [dbo].[Travel_Occurs] ([Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (1,'AP',-1,2)

  INSERT INTO [dbo].[Travel_Occurs] ([Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (2,'SE',-1,0)

  INSERT INTO [dbo].[Travel_Occurs] ([Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (3,'SP',1,3)

  INSERT INTO [dbo].[Travel_Occurs] ([Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (4,'BT',1,5)

  INSERT INTO [dbo].[Travel_Occurs] ([Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (5,'RD',-1,5)

  INSERT INTO [dbo].[Travel_Occurs] ([Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (6,'BU',5,10)

  INSERT INTO [dbo].[Travel_Occurs] ([Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (7,'CA',5,0)

  INSERT INTO [dbo].[Travel_Occurs] ([Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (8,'BI',5,15)

  INSERT INTO [dbo].[Travel_Occurs] ([Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (9,'CY',5,2)

  INSERT INTO [dbo].[Travel_Occurs] ([Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (10,'TR',5,5)

  INSERT INTO [dbo].[Travel_Occurs] ([Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (11,'BM',7,6)

  INSERT INTO [dbo].[Travel_Occurs] ([Mode_Sno] ,[Mode_Id], [Mode_Parent_Sno] ,[Quantity]) VALUES (12,'AI',7,14)

  Expected Result

  Code | Qty | Logic to be applied

  =================================================

  AP | 2 | AP direcly since ParentId = -1

  SE | 0 | SE direcly since ParentId = 0

  SP | 3 | SP *1 since SE = 0

  BT | 5 | BT *1 since SE = 0

  RD | 5 | RD direcly since ParentId = -1

  BU | 50| BU * RD since RD <> 0

  CA | 0 | CA direcly since ParentId = -1

  BI | 15| BI * RD since RD <> 0

  CY | 10| BY * RD since RD <> 0

  TR | 25| TR * RD since RD <> 0

  BM | 6 | BM *1 since SE = 0

  AI | 14| AI *1 since SE = 0

  I tried below syntax which didnot workout

  ,cast(b.quantity * a.quantity as numeric(18,0)) as quantity

  -- ,cast( case when b.quantity =0 then 1 else b.Quantity end as b_quantity * a.quantity as numeric(18,0)) as quantity

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  December 21, 2013 at 6:00 pm

  #1675415

  Try this.

  cast( case when b.quantity =0 then 1 else b.Quantity end * a.quantity as numeric(18,0)) as quantity

  Not being one that believes in the myth of portability, the following is a visual shortcut.

  CAST(ISNULL(NULLIF(b.quantity,0),1) * a.quantity AS NUMERIC(18,0)) AS quantity

  I'd also recommend looking into that nasty NUMERIC(18,0). That's probably the most wasteful size a column could ever be defined as. Lookup NUMERIC and DECIMIAL datatypes in "Books Online" at the number of bytes used for different sizes and see why I say that. It's a damned shame that many table designer softwares default to that.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)