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)

quote:

---

Originally posted by shanmugaraj

This is new post based to make my current issue specific on
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190438


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


[img]
C:\Users\IBM_ADMIN\Desktop\Desk\Result.jpg
[/img]



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



THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

---