topic Re: Date Difference in SQL Where Condition? in Application Development and Automation Discussions

Date Difference in SQL Where Condition?

Former Member — Sun, 23 Sep 2007 12:13:03 GMT

Dear All,

I have a problem while fetching records from two tables LIPS and VAPMA on basis of date difference. My SQL conditions/statements are as follows.

DATA myvar type p.

1. SELECT SUM( lips~lfimg )

FROM lips INNER JOIN vapma ON lips_{vgbel = vapma}vbeln

AND vapma_{matnr = lips}matnr

INTO myvar

WHERE DATEDIFF(lips_{erdat , vapma}audat) = 2.

2. SELECT SUM( lips~lfimg )

FROM lips INNER JOIN vapma ON lips_{vgbel = vapma}vbeln

AND vapma_{matnr = lips}matnr

INTO mywar

WHERE

lips_{erdat - vapma}audat = 2.

Above SQL statements are not working, kindly help me out. Thanking you in advance.

Ahsan Majeed

Re: Date Difference in SQL Where Condition?

Former Member — Sun, 23 Sep 2007 13:53:10 GMT

Do like this:

data: begin of itab occurs 0,

vbeln like lips-vbeln,

posnr like lips-posnr,

erdat like sy-datum,

lfimg like lips-lfimg,

audat like sy-datum,

d_diff type i,

end of itab.

data : v_qty like lips-lfimg.

SELECT a_{vbeln a}posnr a_{erdat a}lfimg b~audat into table itab

FROM lips as a INNER JOIN vapma as b

ON a_{vgbel = b}vbeln.

clear v_qty.

sort itab by vbeln posnr.

loop at itab.

itab-d_diff = itab-erdat - itab-audat.

if itab-d_diff = 2.

v_qty = v_qty + itab-lfimg.

endif.

endloop.

Reward if useful

regards

Anji

Re: Date Difference in SQL Where Condition?

Former Member — Mon, 24 Sep 2007 04:55:40 GMT

Dear Anji,

Thanks for your reply. Actually i want this logic in SQL statement, later i figure it out that this logic is not possible in sql where statement because this is incorrect statement.

Consider this:

field1 = 4

field2 = 2

WHERE field1 - field2 = 2

compiler interprets it as

WHERE 2 = 2 " this is illogical condition thats why its not possible

However i really appreciate your reply. Once again thank you and points for you.

Regards,

Ahsan Majeed