Problem with creating query for allocating amount

  • good day i came a cross of a problem

    I have a query that takes one line in table and if it math the condition in my situation is if

    Date and Tracking_Category_1 in table OLAP.VW_CUBE_Finance

    equal Date and Tracking_Category_1 in OLAP.test2

    then it take line that have both condition and braek it in 3 more and divide amount that was in original line between new ones

    its successfully done what it mus tmut now i need to put in view that will replace original data with new one and leave rest in peace

    so that what i get in example of my test data base i dont need red but need to keep yellow and white

    here is data for base

    use sqlteam
    go

    declare @sampledata1 table
    (
    [Date] date ,TC1 varchar(100),Amount_New decimal(10,2)
    )
    --Step 2

    INSERT INTO @sampledata1
    ([Date]
    ,[TC1]
    ,[Amount_New])
    VALUES ('2020-11-01','Houses:N-Home',70),
    ('2020-11-01','Houses:N-KL',80),
    ('2020-11-01','Default Category: Default Option',90),
    ('2020-11-01','Houses:NS-Home',22.5),
    ('2020-11-01','Houses:N-KL',45),
    ('2020-11-01','Houses:N-GR',22.5),
    ('2020-12-01','Houses:N-Home',100),
    ('2020-12-01','Default Category: Default Option',250),
    ('2020-12-01','Houses:N-Kl',110),
    ('2020-12-01','Houses:N-Home',24),
    ('2020-12-01','Houses:N-KL',12),
    ('2020-12-01','Houses:N-GR',60),
    ('2020-10-01','Houses:N-BR',24),
    ('2020-10-01','Houses:N-KL',12),
    ('2020-10-01','Houses:N-GR',60),
    ('2020-10-01','Houses:N-BR',24),
    ('2020-10-01','Default Category: Default Option',70)

    declare @test2 TABLE (
    [Name] nvarchar(150) NULL,
    [Source] nvarchar(150) NULL,
    [AllocateToTc] nvarchar(150) NULL,
    [Percent] nvarchar(150) NULL,
    [date] nvarchar(150) NULL,
    [AllocateFromTc] nvarchar(150) NULL
    )

    --Step 4

    INSERT INTO @test2
    ([Name]
    ,[Source]
    ,[AllocateToTc]
    ,[Percent]
    ,[date]
    ,[AllocateFromTc])
    VALUES
    ('1','User','Houses: NS-Home','25','2020-11-01','Default Category: Default Option'),
    ('2','User','Houses: N-KL','50', '2020-11-01','Default Category: Default Option'),
    ('3','User','Houses: N-GR','25', '2020-11-01','Default Category: Default Option'),
    ('4','User','Houses: N-Home','20', '2020-12-01','Default Category: Default Option'),
    ('5','User','Houses: N-KL','10', '2020-12-01','Default Category: Default Option'),
    ('6','User','Houses: N-GR','50', '2020-12-01','Default Category: Default Option'),
    ('7','User','Houses: N-BR','20', '2020-12-01','Default Category: Default Option')

    --CREATE VIEW [VW_Deimos_view]
    --AS
    select distinct
    A1.Date,
    A1.TC1,
    A1.Amount_New,
    case
    when A1.Date = B2.Date
    and a1.TC1 = b2.AllocateFromTc
    then AllocateToTc
    else a1.TC1
    end as Tracking_Category_d,
    case
    when A1.TC1 = b2.AllocateFromTc
    and A1.Date = B2.Date
    then A1.Amount_New*[Percent]/100
    else A1.Amount_New
    end as Amountd

    from @sampledata1 A1 , @test2 B2?

     

    Attachments:
    You must be logged in to view attached files.
  • I think the following might give you the data rows you need?:

    declare @sampledata1 table

    (

    [Date] date ,TC1 varchar(100),Amount_New decimal(10,2)

    )
    INSERT INTO @sampledata1

    ([Date]

    ,[TC1]

    ,[Amount_New])

    VALUES ('2020-11-01','Houses:N-Home',70),

    ('2020-11-01','Houses:N-KL',80),

    ('2020-11-01','Default Category: Default Option',90),

    ('2020-11-01','Houses:NS-Home',22.5),

    ('2020-11-01','Houses:N-KL',45),

    ('2020-11-01','Houses:N-GR',22.5),

    ('2020-12-01','Houses:N-Home',100),

    ('2020-12-01','Default Category: Default Option',250),

    ('2020-12-01','Houses:N-Kl',110),

    ('2020-12-01','Houses:N-Home',24),

    ('2020-12-01','Houses:N-KL',12),

    ('2020-12-01','Houses:N-GR',60),

    ('2020-10-01','Houses:N-BR',24),

    ('2020-10-01','Houses:N-KL',12),

    ('2020-10-01','Houses:N-GR',60),

    ('2020-10-01','Houses:N-BR',24),

    ('2020-10-01','Default Category: Default Option',70)

    declare @test2 TABLE (

    [Name] nvarchar(150) NULL,

    [Source] nvarchar(150) NULL,

    [AllocateToTc] nvarchar(150) NULL,

    [Percent] nvarchar(150) NULL,

    [date] nvarchar(150) NULL,

    [AllocateFromTc] nvarchar(150) NULL)

    INSERT INTO @test2

    ([Name]

    ,[Source]

    ,[AllocateToTc]

    ,[Percent]

    ,[date]

    ,[AllocateFromTc])

    VALUES

    ('1','User','Houses: NS-Home','25','2020-11-01','Default Category: Default Option'),

    ('2','User','Houses: N-KL','50', '2020-11-01','Default Category: Default Option'),

    ('3','User','Houses: N-GR','25', '2020-11-01','Default Category: Default Option'),

    ('4','User','Houses: N-Home','20', '2020-12-01','Default Category: Default Option'),

    ('5','User','Houses: N-KL','10', '2020-12-01','Default Category: Default Option'),

    ('6','User','Houses: N-GR','50', '2020-12-01','Default Category: Default Option'),

    ('7','User','Houses: N-BR','20', '2020-12-01','Default Category: Default Option')

    --CREATE VIEW [VW_Deimos_view]

    --AS

    select distinct

    A1.Date,

    A1.TC1,

    A1.Amount_New,

    case

    when A1.Date = B2.Date and a1.TC1 = b2.AllocateFromTc

    then b2.AllocateToTc

    else a1.TC1

    end as Tracking_Category_d,

    case

    when A1.TC1 = b2.AllocateFromTc and A1.Date = B2.Date

    then A1.Amount_New*[Percent]/100

    else A1.Amount_New

    end as Amountd

    from @sampledata1 A1 , @test2 B2


    where

    --Tracking_category_d is displaying a1.tc1 and a1.tc1 != 'Default Category: Default Option'

    (not (A1.Date = B2.Date and a1.TC1= b2.AllocateFromTc)

    and a1.TC1!='Default Category: Default Option')


    or
    --Tracking_category_d is displaying b2.AllocateToTc so include it in the results
    (A1.Date = B2.Date and a1.TC1= b2.AllocateFromTc)

    If it is correct then I will try to improve the query to give the correct order.

    I put some comments in the where clause so you can see, and check, my logic.

     

  • it does the right thing but it also remove Default Category: Default Option on 2020-10-01 that i need to keep test12

  • Sorry for my mistake. I am having another look at your question and I noticed something:

    You said that you need to keep the items marked yellow in your attachment. On line 6 column tracking_category_d is marked in yellow and has the value 'houses:nsw001-@Home'.

    However 'houses:nsw001-@Home' is not in your test data?

    Did I misunderstand something or is there a difference between your test data and the attachment?

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

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