locked
Calculating values in new column RRS feed

  • Question

  • Hi

    I have a script that gives the following example output;

    What I need to do is create another column e.g. VoidLossAmount with the following results

    Where the VoidCode is VLOSS then the new VoidLossAmount should stay the same ie in this example 305.25

    Where the VoidCode <> VLOSS then the value in the new VoidLossAmount column should be in this example (305.25/29)*6 and (305.25/29)*23

    Can anyone help with a solution

    Thanks

    John

    Wednesday, November 25, 2015 1:59 PM

Answers

  • create table test (PropertyCode varchar(28), void_start date, VoidCode varchar(10),VoidLoss decimal(10,2), VoidDay int)
    
    Insert into test values('ALBS_00003_02','2015-10-27','VLOSS',305.25,29),
    ('ALBS_00003_02','2015-10-27','VMANT',0,6),
    ('ALBS_00003_02','2015-11-03','VHOSS',0,23)
    
    ;with mycte as (
    Select *, row_number() Over(Partition by PropertyCode Order by Case when VoidCode='VLOSS' Then 1 else 0 END DESC) rn 
    from test)
    
    Select PropertyCode, void_start,VoidCode,VoidLoss,VoidDay,
    Cast((Max(Case WHen rn=1 then VoidLoss else null End) Over(Partition by PropertyCode) / 
    Max(Case WHen rn=1 then VoidDay else null End) Over(Partition by PropertyCode) ) * VoidDay  as decimal(10,2)) as VoidLossAmount 
    from mycte
    
    drop table test

    Wednesday, November 25, 2015 2:18 PM

All replies

  • Hi John,

    You can use computed column like this example

    CREATE TABLE dbo.Products 
    (
        ProductID int IDENTITY (1,1) NOT NULL
      , QtyAvailable smallint
      , UnitPrice money
      , InventoryValue AS QtyAvailable * UnitPrice
    );
    
    -- Insert values into the table.
    INSERT INTO dbo.Products (QtyAvailable, UnitPrice)
    VALUES (25, 2.00), (10, 1.5);
    
    -- Display the rows in the table.
    SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue
    FROM dbo.Products;


    Many Thanks & Best Regards, Hua Min

    Wednesday, November 25, 2015 2:02 PM
  • create table test (PropertyCode varchar(28), void_start date, VoidCode varchar(10),VoidLoss decimal(10,2), VoidDay int)
    
    Insert into test values('ALBS_00003_02','2015-10-27','VLOSS',305.25,29),
    ('ALBS_00003_02','2015-10-27','VMANT',0,6),
    ('ALBS_00003_02','2015-11-03','VHOSS',0,23)
    
    ;with mycte as (
    Select *, row_number() Over(Partition by PropertyCode Order by Case when VoidCode='VLOSS' Then 1 else 0 END DESC) rn 
    from test)
    
    Select PropertyCode, void_start,VoidCode,VoidLoss,VoidDay,
    Cast((Max(Case WHen rn=1 then VoidLoss else null End) Over(Partition by PropertyCode) / 
    Max(Case WHen rn=1 then VoidDay else null End) Over(Partition by PropertyCode) ) * VoidDay  as decimal(10,2)) as VoidLossAmount 
    from mycte
    
    drop table test

    Wednesday, November 25, 2015 2:18 PM
  • So your calculation is based on at least 2 rows?  And while I'm pushing the limits here, is your description hiding an assumption about the number of "related" rows and the distribution of values for VoidCode across those row?  Conveniently, you have a single VLOSS row.  Is that guaranteed?  Are you certain (and by certain, there should be a constraint to enforce this)? 

    And lastly, which column or columns "tie" related rows together?  Presumably PropertyCode - but I suspect there is more to this since there seems to be some sort of episodic aspect to this table. 

    Wednesday, November 25, 2015 2:26 PM
  • Are you looking for this?

    DECLARE @table TABLE (PropertyCode NVARCHAR(20), voidStart DATETIME, voidEnd DATETIME, voidCode NVARCHAR(6), voidDescription NVARCHAR(100), voidLoss FLOAT, voidDays INT)
    INSERT INTO @table (PropertyCode, voidStart, voidEnd, voidCode, voidDescription, voidLoss, voidDays) VALUES
    ('ALBS_00003_02', '2015-10-27', NULL, 'VLOSS', 'Void Loss Financial Tracking', 305.25, 29),
    ('ALBS_00003_02', '2015-10-28', '2015-11-02 23:59:00.000', 'VMAINT', '*Keys Rec - Void Maint (VLF)', 0, 6),
    ('ALBS_00003_02', '2015-11-03', NULL, 'VHOUS', '*Keys to Housing - RTL (VLF)', 0, 23)
    
    ;WITH base AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY propertyCode ORDER BY voidStart, voidEnd) AS seq
      FROM @table
    )
    
    SELECT b1.*, CASE WHEN b1.voidCode = 'VLOSS' THEN b1.voidLOSS
                      WHEN b1.voidCode <> 'VLOSS' THEN (b2.voidLoss/b2.voidDays) * b1.voidDays 
    				  END, *
      FROM base b1
        LEFT OUTER JOIN base b2
    	  ON b1.PropertyCode = b2.PropertyCode
    	  AND b1.voidCode <> 'VLOSS'
    	  AND b1.seq > b2.seq
    	  AND b2.voidCode = 'VLOSS'
    
    When you post example data and DDL, please do it similarly to how I have in this example. We can't write queries on images :)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles. Help me change the face of men’s health this Movember by making a donation to my moustache: http://mobro.co/reeb

    Wednesday, November 25, 2015 3:17 PM
  • Hi

    Thank you all for your input I should have explained better.

    I need to show the VoidLoss for every VoidCode against a property but in the database only the VLOSS has a monetary value so the other codes need to be worked out based on their portion of VoidDays against the total Void Loss.

    I have the following code and as you can see VoidDays is worked out using whatever date is in the VoidStartDate, VoidEndDate or if there is a NULL value.

    The Property Code ties the groups together and every Property will always have a row with VoidCode = "VLOSS"

    However in my example I have VLOSS, VMAINT and VHOUSE for this property but there could also be VMAJOR.

    So in effect every property will always have VLOSS as one row but could have any combination of one, two or all three of the other codes.


    SELECT        CompanyCode, PropertyCode, VoidStartDate , VoidEndDate , VoidCode, VoidDescription, VoidLoss,
    CASE WHEN VoidEndDate IS NOT NULL THEN  DATEDIFF(d,VoidStartDate, VoidEndDate)+1
      WHEN VoidEndDate IS NULL AND VoidCode <> 'VLOSS' THEN DATEDIFF(d,VoidStartDate, GETDATE())+1
      WHEN VoidEndDate IS NULL AND VoidCode = 'VLOSS' THEN DATEDIFF(d,VoidStartDate, GETDATE())
    END AS VoidDays
    FROM            QLHRA_VoidProperties
    WHERE        (CompanyCode = 'wha') AND (PropertyCode NOT LIKE 'play_%') AND (VoidCode <> 'NEWDEV') AND (VoidCode <> 'NOTICE') AND (VoidCode <> 'ZBLK')
    ORDER BY PropertyCode, VoidStartDate

    Example of output for two properties is:

    CompanyCode PropertyCode                   VoidStartDate           VoidEndDate             VoidCode VoidDescription                VoidLoss               VoidDays

    ----------- ------------------------------ ----------------------- ----------------------- -------- ------------------------------ ---------------------- -----------

    WHA         ALBS_00003_02                  2015-10-27 00:00:00.000 NULL                    VLOSS    Void Loss financial tracking   305.25                 29

    WHA         ALBS_00003_02                  2015-10-28 00:00:00.000 2015-11-02 23:59:00.000 VMAINT   *Keys Rec - Void Maint (VLF)   0                      6

    WHA         ALBS_00003_02                  2015-11-03 00:00:00.000 NULL                    VHOUS    *Keys to Housing - RTL (VLF)   0                      23

    WHA         CLAS_00009_32                  2015-09-14 00:00:00.000 NULL                    VLOSS    Void Loss financial tracking   834.32                 72

    WHA         CLAS_00009_32                  2015-09-15 00:00:00.000 2015-10-20 23:59:00.000 VMAJOR   *Void Major Works (VLF)        0                      36

    WHA         CLAS_00009_32                  2015-10-21 00:00:00.000 NULL                    VHOUS    *Keys to Housing - RTL (VLF)   0                      36

    (6 row(s) affected)

    Regards

    John

    Wednesday, November 25, 2015 10:35 PM
  • This is what my example attempts to do. Could you let us know if it is not performing as you expect?

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles. Help me change the face of men’s health this Movember by making a donation to my moustache: http://mobro.co/reeb

    Thursday, November 26, 2015 2:14 PM
  • Hi

    Using your code and creating the &table works as shown in image

    My VoidDays column is created from

    CASE WHEN VoidEndDate IS NOT NULL THEN  DATEDIFF(d,VoidStartDate, VoidEndDate)+1
      WHEN VoidEndDate IS NULL AND VoidCode <> 'VLOSS' THEN DATEDIFF(d,VoidStartDate, GETDATE())+1
      WHEN VoidEndDate IS NULL AND VoidCode = 'VLOSS' THEN DATEDIFF(d,VoidStartDate, GETDATE())
    END AS VoidDays

    and when I substitute this code into your script as shown it doesn't calculate.

    ;WITH base AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY propertyCode ORDER BY VoidStartDate, VoidEndDate) AS seq, CASE WHEN VoidEndDate IS NOT NULL THEN  DATEDIFF(d,VoidStartDate, VoidEndDate)+1
      WHEN VoidEndDate IS NULL AND VoidCode <> 'VLOSS' THEN DATEDIFF(d,VoidStartDate, GETDATE())+1
      WHEN VoidEndDate IS NULL AND VoidCode = 'VLOSS' THEN DATEDIFF(d,VoidStartDate, GETDATE())
    END AS VoidDays
      FROM QLHRA_VoidProperties
    )
    SELECT b1.*, CASE WHEN b1.voidCode = 'VLOSS' THEN b1.VoidLoss
                      WHEN b1.voidCode <> 'VLOSS' THEN (b2.voidLoss/b2.VoidDays) * b1.voidDays
          END, *
      FROM base b1
        LEFT OUTER JOIN base b2
       ON b1.PropertyCode = b2.PropertyCode
       AND b1.voidCode <> 'VLOSS'
       AND b1.seq > b2.seq
       AND b2.voidCode = 'VLOSS'

    Thursday, November 26, 2015 2:42 PM