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
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
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
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
('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
month_start_date DATE NOT NULL,
month_end_date DATE NOT NULL,
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:
(SELECT A1.asset_id, M1.month_name AS acquisition_month_name,
M1.julian_month AS acquisition_julian_month,
(A1.acquisition_price/40.00) AS depreciation_amt
FROM Assets AS A1, Month_Periods AS M1
BETWEEN M1.month_start_date AND M1.month_end_date),
(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
BETWEEN A2.acquisition_julian_month AND
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