none
problem with write a query

    Question

  • Hi,

    i have to do a view but i have problem with query its all about asset.

    i have table with data as below, table name is assetaable ofc i`ve got about 10k rows in table

    ASSETID           NAMEALIAS    ACQUISITIONDATE_W           ACQUISITIONPRICE_W
    GR4-0008184    Zamknięty    2008-03-06 00:00:00.000    2873.920000000000
    GR4-0009001    Zamknięty    2008-12-31 00:00:00.000    2963.000000000000
    GR4-0010100    Zamknięty    2005-08-18 00:00:00.000    835.250000000000
    GR4-0008184    Otwarte       2008-03-06 00:00:00.000    2873.920000000000
    GR4-0009001    Otwarte       2008-12-31 00:00:00.000    2963.000000000000

    What i need to do is a view with this data and new column as "currently value".Depreciation is 40 month so if i good think take ACQUISITIONPRICE_w /40= "xxxxxx" its price that should be deduct every month. Then take ACQUISITIONDATE_w and every new month deduct (ACQUISITIONPRICE_w /40= "xxxxxx") and "namealiast" shoul be only 'otwarte'... so easy to say but i have no clue how i can do this



    • Edited by Ghotti Tuesday, November 19, 2013 2:07 PM
    Tuesday, November 19, 2013 2:03 PM

All replies

  • Hi,

    Not very clear with your requirement but try the below and see if you get the desired result:

    select ASSETID, NAMEALIAS, ACQUISITIONDATE_W, ACQUISITIONPRICE_W-(ACQUISITIONPRICE_w /40) [Deducted Price] from assetaable


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Tuesday, November 19, 2013 2:16 PM
  • Hi,

    Something like this would work?

    SELECT ASSETID, NAMEALIAS, ACQUISITIONDATE_W, ACQUISITIONPRICE_W, 
    GETDATE() AS TODAY, 
    CASE WHEN DATEDIFF(m, ACQUISITIONDATE_W, GETDATE()) BETWEEN 0 AND 40 
    	THEN (1E0 - DATEDIFF(m, ACQUISITIONDATE_W, GETDATE())*1E0/40)*ACQUISITIONPRICE_W 
    	ELSE 0 
    END AS DEPRECIATEDPRICE_W 
    FROM MYTABLE
    


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, November 19, 2013 2:18 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Here is my guess from your picture. We have a DATE data type and only need to carry currency to two decimals. A table must have a key. Większość ludzi nie zna polskiego (Most people do not speak Polish).

    CREATE TABLE Assets
    (asset_id CHAR(12) NOT NULL, 
     asset_status VARCHAR(6) DEFAULT 'Open' NOT NULL
       CHECK (asset_status IN ('Open', 'Closed')),
     PRIMARY KEY (asset_id, asset_status),
     acquisition_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
     acquisition_price DECIMAL (8,2) NOT NULL
     CHECK (acquisition_price > = 0.00)
    );

    INSERT INTO Assets
    VALUES
    ('GR4-0008184', 'Open', '2008-03-06', 2873.92), 
    ('GR4-0008184', 'Closed', '2008-03-06', 2873.92), 
    ('GR4-0009001', 'Open', '2008-12-31', 2963.00),
    ('GR4-0009001', 'Closed', '2008-12-31', 2963.00), 
    ('GR4-0010100', 'Closed', '2005-08-18', 835.25);

    How can 'GR4-0010100' be closed when it was never opened? 

    >> What I need to do is a view with this data and new column as "current_value". Depreciation is 40 month so if I take acquisition_price, '40 = "xxxxxx" its price that should be deduct every month. <<

    Build a special Calendar table 

    CREATE TABLE Month_Periods
    (month_name CHAR(10) NOT NULL PRIMARY KEY
       CHECK (month_name 
     LIKE '[21][0-9][0-9][0-9]-[01][0-9]-00'),
     month_start_date DATE NOT NULL,
     month_end_date DATE NOT NULL,
      CONSTRAINT date_ordering
        CHECK (month_start_date <= month_end_date),
     julian_month INTEGER NOT NULL UNIQUE):

    I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with ISO-8601 dates. Fill the table with a century of months (1200 rows). 

    The Julian month is a sequential number from 1 to 1200.  Here is a skeleton to get you started: 


    WITH Asset_Depreciation
    AS
    (SELECT A1.asset_id, M1.month_name AS acquisition_month_name,
            M1.julian_month AS acquisition_julian_month,
           A1.acquisition_price,
           (A1.acquisition_price/40.00) AS depreciation_amt
      FROM Assets AS A1, Month_Periods AS M1
     WHERE A1.acquisition_month
           BETWEEN M1.month_start_date AND M1.month_end_date),

    Asset_Lifetime
    AS
    (SELECT A2.asset_id, M.julian_month, 
            (depreciation_amt * << 1 to 40 computed from julian months>>) AS current_depreciation_amt_tot
       FROM Asset_Depreciation AS A, Month_Periods AS M2
      WHERE M2.julian_month 
            BETWEEN A2.acquisition_julian_month AND
                    A2.acquisition_julian_month +39

    If you cannot get it from this, we will try again. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, November 19, 2013 6:12 PM