1

I need help, I write the code and have to use The Last Date from user parameter >> [%1] in statement but when I execute ..the filled window force me fill from the last date to first date. It should be fill from the first date to last date.

when I execute... the "User window" popup and need to input date from the first date to last date (ex: 01/08/20 - 30/09/20) but from the fact from above coding,

user have to input the last date to the first date (ex: 30/09/20 - 01/08/20) << this will make user confuse
I attached the pic for you can see it clear>>1

Select 
    T0.docnum , T0.docdate , Sum(T1.Quantity), 
    Sum(case when T0.docdate between dateadd(month,0,DATEADD(DAY,1-DATEPART(day,'[%1]'),'[%1]')) and eomonth(dateadd(month,0,DATEADD(DAY,1-DATEPART(day,'[%1]'),'[%1]'))) then T1.Quantity*T2.U_hmc_vollts else 0 end) as 'm12_Lts'  ,
    sum(case when T0.docdate between dateadd(month,-1,DATEADD(DAY,1- 
    DATEPART(day,'[%1]'),'[%1]')) and eomonth(dateadd(month,-1,DATEADD(DAY,1- 
    DATEPART(day,'[%1]'),'[%1]'))) then T1.Quantity*T2.U_hmc_vollts else 0 end) as 'm11_Lts'
From    OINV T0 
    left join inv1 T1 on T0.DocEntry = T1.DocEntry 
    left join oitm T2 on T1.ItemCode = T2.ItemCode 
Where 
    T0.docdate >= '[%0]' and T0.Docdate <= '[%1]' 
Group By 
    T0.docnum, T0.Docdate
Dale K
  • 25,246
  • 15
  • 42
  • 71
Kieng
  • 17
  • 6
  • I'm not clear what the question is, but do you need an `order by` clause? – Dale K Oct 15 '20 at 04:40
  • I want ..when I execute the "User window" popup and need to input date from first date to last date (ex: 01/08/20 - 30/09/20) but from the fact from above coding , user have to input last date to first date (ex : 30/09/20 - 01/08/20) << this will make user confuse (I attached the pic for you can see it clear ) and I need to use the last date [%1] in code statement Thank you for your help in advance. – Kieng Oct 15 '20 at 05:01
  • 'Order by' clause is not what I want – Kieng Oct 15 '20 at 05:10
  • If you are talking about the order that the user has to input the parameters, thats a function of the SQL Client Interface you are using - so you need to tell us what it is. – Dale K Oct 15 '20 at 05:13
  • not 'order by' I am talking ...Order is just sort the data but I need User can input from first date to last date and use the last date to be the Variable in calculate code – Kieng Oct 15 '20 at 05:19
  • Yes, thats what my last comment addressed. Thats why we need to know the user interface you are using. – Dale K Oct 15 '20 at 05:19
  • but from the code user have to input from the last date to first date on user interface (they input like this >> 30/09/20 - 01/08/20) this is not naturally inputting the date , should be >> 01/08/20 - 30/09/20 this is sample pic to show you https://i.stack.imgur.com/AEZOO.png – Kieng Oct 15 '20 at 05:24
  • i write the code in SAP B1 by using Query manager – Kieng Oct 15 '20 at 05:26
  • This is a question for someone who knows about the SAP B1 Query Manager then - I would suggest consulting the official documentation. – Dale K Oct 15 '20 at 05:50

2 Answers2

0

Try Inserting

/*SELECT FROM [dbo].[OWTR] p1*/

declare @FROM as Datetime
declare @TO as datetime
/* WHERE */
set @FROM = /* p1.docdate */ '[%0]'
set @TO = /* p1.docdate */  '[%1]'

at the top of your query (the table doesn't really matter) then replace the [%0] with @FROM and [%1] with @TO in the rest of your query.

Full Code:

/*SELECT FROM [dbo].[OWTR] p1*/

 declare @FROM as Datetime
 declare @TO as datetime
 /* WHERE */
 set @FROM = /* p1.docdate */ '[%0]'
 set @TO = /* p1.docdate */  '[%1]'



Select 
T0.docnum , T0.docdate , Sum(T1.Quantity), 
Sum(case when T0.docdate between dateadd(month,0,DATEADD(DAY,1- 
DATEPART(day,@TO),@TO)) and eomonth(dateadd(month,0,DATEADD(DAY,1- 
 DATEPART(day,@TO),@TO))) then T1.Quantity*T2.U_hmc_vollts else 0 end) as 
'm12_Lts'  ,
sum(case when T0.docdate between dateadd(month,-1,DATEADD(DAY,1- 
DATEPART(day,@TO),@TO)) and eomonth(dateadd(month,-1,DATEADD(DAY,1- 
DATEPART(day,@TO),@TO))) then T1.Quantity*T2.U_hmc_vollts else 0 end) as 
'm11_Lts'
From    OINV T0 
left join inv1 T1 on T0.DocEntry = T1.DocEntry 
left join oitm T2 on T1.ItemCode = T2.ItemCode 
Where 
T0.docdate >= @FROM and T0.Docdate <= @TO 
Group By 
T0.docnum, T0.Docdate

let me know how you get on.

Praxiom
  • 578
  • 1
  • 8
  • 21
  • 1
    It's work!! thank you so muchhhh. I don't know how to post image to show you the result. but it's work!! ^o^ – Kieng Oct 16 '20 at 05:25
-1

Add order by to your SQL statement.

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15

4b0
  • 21,981
  • 30
  • 95
  • 142
Underhill Julian
  • 19
  • 1
  • Thank you for your comment but it's not exact what I want T_T, May be my question is not clear ... hen I execute the "User window" popup and need to input date from first date to last date (ex: 01/08/20 - 30/09/20) but from the fact from above coding , user have to input last date to first date (ex : 30/09/20 - 01/08/20) << this will make user confuse (I attached the pic for you can see it clear ) and I need to use the last date [%1] in code statement Thank you for your help in advance – Kieng Oct 15 '20 at 05:05