problem with wirte query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

problem with wirte query

Author

Topic

Ghotti
Starting Member

1 Post

Posted - 2013-11-19 : 09:13:06

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 Zamkniety 2008-03-06 00:00:00.000 2873.920000000000
GR4-0009001 Zamkniety 2008-12-31 00:00:00.000 2963.000000000000
GR4-0010100 Zamkniety 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

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-11-19 : 09:24:29

How about something like

CREATE VIEW abc AS
SELECT ASSETID, NAMEALIAS, ACQUISTITIONDATE_W, ACQUISITIONPRICE_W, 
ACQUISITIONPRICE_W - ((ACQUISITIONPRICE_W/40) * DATEDIFF(month, ACQUISTITIONDATE_W, GETDATE())) AS CurrentValue

djj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-19 : 09:35:08

[code]
CREATE VIEW YourView
AS
SELECT *,ACQUISITIONPRICE_W * POWER((1 - 0.4),MonthElapsed) AS ACQUISITIONPRICE_W
FROM Table t
CROSS APPLY (SELECT COUNT(DISTINCT DATEDIFF(mm,0,ACQUISITIONDATE_W)) AS MonthElapsed
FROM Table
WHERE ACQUISITIONDATE_W < DATEADD(mm,DATEDIFF(mm,0,t.ACQUISITIONDATE_W),0)
)t1
WHERE NAMEALIAS ='Otwarte'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources