Grouping dates

[#1]

Can anyone please help me. I am trying to group dates (ordheadh_0."o-date") by the month and year from data in a Progress database.

SQL SELECT ordheadh_0."cust-code", ordheadh_0."o-value", ordheadh_0."o-vatam", ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE (ordheadh_0."o-date">={d '2014-01-01'})

GROUP BY MONTH(ordheadh_0."o-date")+"-"+YEAR(ordheadh_0."o-date");

Thanks

[buzzy996]

try tis,

SQL SELECT

ordheadh_0."cust-code",

ordheadh_0."o-value",

ordheadh_0."o-vatam",

ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE Date#(ordheadh_0."o-date",'YYYY-MM-DD')>='2014-01-01'

GROUP BY MONTH(DATE#(ordheadh_0."o-date",'YYYY-MM-DD'))

       AND YEAR(DATE#(ordheadh_0."o-date",'YYYY-MM-DD'));

[Anonymous]

hi  try

SQL SELECT ordheadh_0."cust-code",

           ordheadh_0."o-value",

           ordheadh_0."o-vatam",

           ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE Date(date#(ordheadh_0."o-date¨,'YYYY-MM-DD')>= '2014-01-01'

GROUP BY MONTH(date(date#(ordheadh_0."o-date",'YYYY-MM-DD'), YEAR(date(date#(ordheadh_0."o-date",'YYYY-MM-DD');

[#4]

Hi, I've tried both scripts and they return errors I do not understand.

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "Date#(ordheadh_0."o-date",'YYYY-MM-DD')>" (10713)

SQL SELECT    ordheadh_0."cust-code",

            ordheadh_0."o-value",

            ordheadh_0."o-vatam",

            ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE Date#(ordheadh_0."o-date",'YYYY-MM-DD')>='2014-01-01'

GROUP BY MONTH(DATE#(ordheadh_0."o-date",'YYYY-MM-DD'))

AND YEAR(DATE#(ordheadh_0."o-date",'YYYY-MM-DD'))

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "Date(date#(ordheadh_0."o-date",'YYYY-MM-" (10713)

SQL SELECT ordheadh_0."cust-code",

           ordheadh_0."o-value",

           ordheadh_0."o-vatam",

           ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE Date(date#(ordheadh_0."o-date",'YYYY-MM-DD')>= '2014-01-01'

GROUP BY MONTH(date(date#(ordheadh_0."o-date",'YYYY-MM-DD'), YEAR(date(date#(ordheadh_0."o-date",'YYYY-MM-DD')

[#5]

Hi Jannet, I tried your script and get this error. Can you help any further ? Appreciate your assistance.

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "Date(date#(ordheadh_0."o-date",'YYYY-MM-" (10713)

SQL SELECT ordheadh_0."cust-code",

           ordheadh_0."o-value",

           ordheadh_0."o-vatam",

           ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE Date(date#(ordheadh_0."o-date",'YYYY-MM-DD')>= '2014-01-01'

GROUP BY MONTH(date(date#(ordheadh_0."o-date",'YYYY-MM-DD'), YEAR(date(date#(ordheadh_0."o-date",'YYYY-MM-DD')

[#6]

Hi Shiva, I tried your script and get this error. Can you help any further ? Appreciate your assistance also.

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "Date#(ordheadh_0."o-date",'YYYY-MM-DD')>" (10713)

SQL SELECT    ordheadh_0."cust-code",

            ordheadh_0."o-value",

            ordheadh_0."o-vatam",

            ordheadh_0."o-date"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE Date#(ordheadh_0."o-date",'YYYY-MM-DD')>='2014-01-01'

GROUP BY MONTH(DATE#(ordheadh_0."o-date",'YYYY-MM-DD'))

AND YEAR(DATE#(ordheadh_0."o-date",'YYYY-MM-DD'))

[buzzy996]

seems to be here the problem with ODBC drives &bridge!

try to create simply query and fetch data...see?whether ur getting any issue or not?

[#8]

Hi, thanks for help.

This was my original query and brings through the data without any errors.

I was just trying to group the information when I then started with the problem.

SQL SELECT     ordheadh_0."order",

            ordheadh_0."cust-code",

            ordheadh_0."o-date",

            ordheadh_0."o-value",

            ordheadh_0."o-vatam"

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE (ordheadh_0."o-date">= '2014-01-01');

[#9 · Accepted solution]

What are you trying to aggregate? Whatever isn't included in the group by part of a sql query must be aggregated in some way. for example... Do you understnad SQL?

SQL SELECT    ordheadh_0."cust-code",

            sum(ordheadh_0."o-value"),

            sum(ordheadh_0."o-vatam"),

            cast(datepart(month,ordheadh_0."o-date") as varchar(2)) + ' - '  + cast(datepart(year,ordheadh_0."o-date") as varchar(4)) as mmyyyy,

FROM PACKAGE.PUB.ordheadh ordheadh_0

WHERE ordheadh_0."o-date" >= '2014-01-01'

GROUP BY ordheadh_0."cust-code", cast(datepart(month,ordheadh_0."o-date") as varchar(2)) + ' - ' + cast(datepart(year,ordheadh_0."o-date") as varchar(4))