problem with new query ...

• Ghotti

  Grasshopper

  Points: 11

  More actions

  November 19, 2013 at 7:08 am

  #286436

  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

• Lowell

  SSC Guru

  Points: 323504

  More actions

  November 19, 2013 at 7:34 am

  #1667394

  with the data you posted, everything is older than 40 months, so i assume the item has no official value any more; i added one example to the sample data.

  so i'm calculating 1/40th of the value of the original item, times the # of months left in the 40 month depreciation period.

  this is how i would do it:

  ;WITH MyCTE([ASSETID],[NAMEALIAS],[ACQUISITIONDATE_W],[ACQUISITIONPRICE_W])

  AS

  (

  SELECT 'GR4-0008184','Zamkniety',CONVERT(date,'2008-03-06 00:00:00.000'),2873.920000000000 UNION ALL

  SELECT 'GR4-0009001','Zamkniety','2008-12-31 00:00:00.000',2963.000000000000 UNION ALL

  SELECT 'GR4-0010100','Zamkniety','2005-08-18 00:00:00.000',835.250000000000 UNION ALL

  SELECT 'GR4-0008184','Otwarte','2008-03-06 00:00:00.000',2873.920000000000 UNION ALL

  SELECT 'GR4-0009001','Otwarte','2008-12-31 00:00:00.000',2963.000000000000 UNION ALL

  SELECT 'Lowell''s Kindle','Lowell','2011-12-11 00:00:00.000',199.000000000000

  )

  SELECT

  DATEDIFF(month,[ACQUISITIONDATE_W],getdate()) As MonthsAge,

  CASE

  WHEN DATEDIFF(month,[ACQUISITIONDATE_W],getdate()) < 40

  THEN ([ACQUISITIONPRICE_W] / 40.0) * (40 - DATEDIFF(month,[ACQUISITIONDATE_W],getdate()))

  ELSE 0

  END AS CurrentValue,

  * FROM MyCTE;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!