Home SQL 13 months transaction and quantity data extract broken down per month
Reply: 1

SQL 13 months transaction and quantity data extract broken down per month

Griphon
1#
Griphon Published in 2018-01-09 11:37:44Z

I was reading through a couple of older posts and tried to apply the same logic to my question, I need to extract 13 months of data broken down per month, I would also like to apply the data to relevant headers... any suggestions. Please see code below and error received.

SELECT ST.TXDATE, ST.CODE, ST.QUANTITY    
FROM StocTran ST
WHERE ST.TXDATE >= DATEADD(MONTH, -13, CAST(GETDATE() AS DATE))
ORDER BY ST.TXDATE

ERROR: [Elevate Software][DBISAM] DBISAM Engine Error # 11949 SQL parsing error - Expected end of statement but instead found ( in SELECT SQL statement at line 3, column 27 Error Code: 11949

MartynA
2#
MartynA Reply to 2018-01-09 12:28:55Z

DATEADD is a function in MS's TransactSQL for Sql Server. I do not know that DBIsam supports it, and it is not listed in DBIsam's list of supported functions here:

https://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=delphi&version=7&topic=functions

Generally, date functions are not portable across different SQL engines, and from that list, one possibility might be to use the EXTRACT function instead:

The EXTRACT function returns a specific value from a date, time, or timestamp value. The syntax is as follows:

EXTRACT(extract_value
        FROM column_reference or expression)
EXTRACT(extract_value,
        column_reference or expression)

Use EXTRACT to return the year, month, week, day of week, day, hours, minutes, seconds, or milliseconds from a date, time, or timestamp column. EXTRACT returns the value for the specified element as an integer.

The extract_value parameter may contain any one of the specifiers: YEAR MONTH WEEK DAYOFWEEK DAYOFYEAR DAY HOUR MINUTE SECOND MSECOND

Even if you are in a hurry, I strngly recommend that you study that page carefully.

UPDATE: From googling dbisam dateadd it looks like Elevate don't have a good answer for an equivalent to DATEADD. One of the hits is this thread:

https://www.sqlservercentral.com/Forums/Topic173627-169-1.aspx

which suggested an alternative way to do it using Delphi's built-in date functions (like IncMonth which I suggested you use in an answer to another q. Basically, you would calculate the start- and end-dates of a range of dates, then convert them to strings to construct a WHERE clause with a column date (from your db) which is equal to or greater than the start date and less or equal to the end date.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.358182 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO