Post reply

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

  • 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

  • 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)
    Intro to Tally Tables and Functions

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply