从单个表中选择两个日期的相同值的差异

i have a select query selecting different columns and for one column i have to get a value from a table as of today and previous year end and should take the difference. Assume that this table stores values everyday suppose it to be X.

我有一个选择不同列的选择查询,对于一列,我必须从今天和上一年末的表中获取一个值,并且应该采取差异。假设这个表每天存储值,假设它是X.

select a
       b
       c
       d
       e

e= ( SELECT VALUE FROM X WHERE DATE='TODAY') MINUS ( SELECT VALUE FROM X WHERE DATE='PREVIOUS YEAR END') i.e 31-dec-2013

Can some one please tell me the logic how same value as of different dates can be selected in single select query

有人可以告诉我逻辑如何在单个选择查询中选择与不同日期相同的值

1 个解决方案

#1

UPDATE

After rereading your edit it looks like you want to use a sub select to get e. In that case here's your selects with the same logic to solve for EOY last year.

重新阅读编辑后,您似乎想要使用子选择来获取e。在这种情况下,这是你的选择,具有相同的逻辑去解决EOY去年。

( SELECT VALUE FROM X WHERE DATE=getdate()) - 
( SELECT VALUE FROM X WHERE DATE=DATEADD(yy, DATEDIFF(yy,0,getdate()), -1))

My original answer below uses one table and joins it on itself.

我在下面的原始答案使用一个表并将其自身加入。

You could do something like this. Just specify the date in there where clause. The join links the date to EOY date last year.

你可以这样做。只需在where where子句中指定日期。该联接将该日期与去年的EOY日期相关联。

select 
    a.date as 'aDate',
    a.value as 'aValue',
    b.value as 'lastEOYValue',    
    a.value - b.value as 'Diff'
  from x a
  join x b 
    on b.date = DATEADD(yy, DATEDIFF(yy,0,a.date), -1) --links EOY last year

SQL Fiddle Example HERE

这里的SQL小提琴示例

#1