SQL Server：从表中选择*，其中日期减去一年

How to select year-1?

如何选择第一年?

This is my code:

这是我的代码:

select 
    a.* 
from 
    (select 
         met_men, kli_kod, pre_kod, galutinis, savik_group, marza, 
         KLR_KOD, KLI_POZ1, KLI_POZ2, KLI_POZ3, KLG_KOD, PRE_RUS, 
         PRE_POZ1, PRE_POZ2, PRE_POZ3, PRE_POZ4, PRE_POZ5, PRE_POZ6, 
         did_dat, savi_suproc, marza_suproc, pre_ska dbo.SVF_View_10) AS a 
left outer join 
    (select 
         pre_kod, kli_kod, met_men, did_dat 
     from 
         dbo.SVF_View_10_sum 
     where 
         dateadd(year, -1, 'did_dat')) as b on a.kli_kod = b.kli_kod 
                                            and a.pre_kod = b.pre_kod 
                                            and a.did_dat = b.did_dat

This error occurs on the line where DATEADD(year, -1, 'did_dat')) as b:

这个错误发生在DATEADD(年,-1,'did_dat'))为b的行上:

Msg 4145, Level 15, State 1, Line 6
An expression of non-boolean type specified in a context where a condition is expected, near ')'.

消息4145,级别15,状态1,行6在期望条件的上下文中指定的非布尔类型的表达式,在')'附近。

Please help me

请帮我

the required data: order date, id, quantity, orderdate-1 year, quantity. It is necessary to compare the quantity sold for this year and for the last year

所需数据:订单日期,ID,数量,orderdate-1年,数量。有必要比较今年和去年的销售数量

3 个解决方案

#1

You give column name in single quote dateadd(year, -1, 'did_dat') which is no need here change into dateadd(year, -1, did_dat) and when you use where clause need to give comparison operation in where clause.

你在单引号dateadd(year,-1,'did_dat')中给出列名,这里不需要改为dateadd(year,-1,did_dat),当你使用where子句时需要在where子句中给出比较操作。

so changed into

所以变成了

where did_dat <= dateadd(year, -1, did_dat)

but it doesn't make any sense because query should be execute on previous year against current date like

但它没有任何意义,因为查询应该在前一年执行,如当前日期

where did_dat <= dateadd(year, -1, GETDATE())

#2

I suspect what you want is:

我怀疑你想要的是:

select . . .
from dbo.SVF_View_10 v left outer join
     dbo.SVF_View_10_sum vs
     on v.kli_kod = vs.kli_kod and a.pre_kod = b.pre_kod and
        v.did_dat = dateadd(year, -1, vs.did_dat);

Notes:

Subqueries are not necessary for this query. I think they just make the query harder to write and to read.

此查询不需要子查询。我认为他们只是让查询更难写和阅读。

Use table aliases that are abbreviations for the table names.

使用表别名缩写的表别名。

You have a date calculation in the where clause rather than a boolean expression. That is causing your error.

您在where子句中有一个日期计算而不是布尔表达式。这导致了你的错误。

The date expression has a string for the third argument. The would be your next error. Strings do not refer to column names.

日期表达式具有第三个参数的字符串。这将是你的下一个错误。字符串不引用列名称。

I am speculating that you want the tables joined on that date expression.

我推测你想要在那个日期表达式上加入表。

#3

Try This :-

试试这个 :-

SELECT a.*
    FROM
    (
        SELECT met_men,kli_kod,pre_kod,galutinis,savik_group,marza,KLR_KOD,LI_POZ1,
               KLI_POZ2,KLI_POZ3,KLG_KOD,PRE_RUS,PRE_POZ1,PRE_POZ2,PRE_POZ3,PRE_POZ4,
               PRE_POZ5,PRE_POZ6,did_dat,savi_suproc,marza_suproc,pre_ska
        FROM SVF_View_10_sum
    ) AS a
        LEFT OUTER JOIN
        (
            SELECT pre_kod,kli_kod,met_men,did_dat
            FROM dbo.SVF_View_10_sum
            WHERE did_dat = DATEADD(YEAR, -1, did_dat)
        ) AS b
            ON a.kli_kod = b.kli_kod AND a.pre_kod = b.pre_kod AND a.did_dat = b.did_dat;

#1