10-20-2016 4:36 PM
Hello all,
I would like to select database table entries with a calculated condition.
An content example:
SITE DATE QUANTITY UNIT_OF_MEASURE
1000 12.10.10216 3 PCE
1010 13.10.10216 5 PCE
1010 16.10.10216 8 PCE
1020 18.10.10216 4 PCE
1420 19.10.10216 1 PCE
---
The where-condition should be defined in that way, that only entries will be selected with weekday eq <weekday>, e.g weekday is sunday
Is it possible to formulate a where-condition using EXEC SQL in that way?
Can it be implemented by a stored procedure?
SELECT * FROM MY_TABLE
WHERE calculate_weekdey(DATE) EQ 1. (1 = Sunday, 2 = Monday, etc.)
Thank you for your reply.
Best regards,
Kurt
10-21-2016 1:24 PM
hi Kurt,
Do you have access to CDS and or AMDP in your environment?
The following link has some good examples of relevant sqlscript functions for AMDP usage.
https://blogs.sap.com/2016/06/23/valuable-sqlscript-functions-for-use-in-amdps/
Select posting_date, day_number, sum(day_number)
From ( Select map(dayname(posting_date),'MONDAY',1,
'TUESDAY',2,
'WEDNESDAY',3,
'THURSDAY',4,
'FRIDAY',5,
'SATURDAY',6,
'7'
) as day_number,
posting_date
From crmd_orderadm_h
Where created_at >= to_dats(add_days(current_date, -7))||replace(to_time(current_timestamp),':','')
)
Group by posting_date, day_number
order by 2
Above example of using a converting the columns during select, you can then apply a filter outside of Select if you so wished.
You can also create defined functions that could do the same thing in SQLScript, but it's hard to know what implementation would suit unless you tell us what env you are working in.
Thanks,
Sean.
10-21-2016 1:27 PM
10-21-2016 1:50 PM
If you are connecting to MS SQL server using native SQL:
EXEC SQL
SELECT QUANTITY FROM MY_TABLE WHERE DATEPART(weekday,DATE)=1
ENDEXEC