Progress ODBC WEEK() function

Yahoo Archive
1
Thanks!

There were some syntax changes necessary to get it to work with the Progress ODBC driver:
weekday() = DAYOFWEEK()
IF, THEN, ELSE = CASE WHEN, THEN, ELSE, END

Here it is translated to accessing via ODBC:

In the query define a field as:

(CASE WHEN DAYOFWEEK(MFGSYS.PUB.LaborDtl.ClockInDate) = '1)' THEN (MFGSYS.PUB.LaborDtl.ClockInDate - 6) ELSE (MFGSYS.PUB.LaborDtl.ClockInDate - DAYOFWEEK(MFGSYS.PUB.LaborDtl.ClockInDate) + 2) END)

Set the Criteria to:

= (CASE WHEN DAYOFWEEK(CURDATE()) = '1)' THEN (CURDATE() - 6) ELSE (CURDATE() - DAYOFWEEK(CURDATE()) + 2) END)

Full SQL:

SELECT MFGSYS.PUB.EmpBasic.Name AS Team, SUM(MFGSYS.PUB.LaborDtl.LaborQty) AS QTY, SUM(MFGSYS.PUB.LaborDtl.LaborQty * MFGSYS.PUB.Part.Number02)
AS Score, 'THIS WEEK:' AS txt
FROM MFGSYS.PUB.LaborDtl, MFGSYS.PUB.EmpBasic, { oj MFGSYS.PUB.JobProd LEFT OUTER JOIN
MFGSYS.PUB.Part ON MFGSYS.PUB.JobProd.PartNum = MFGSYS.PUB.Part.PartNum AND MFGSYS.PUB.JobProd.Company = MFGSYS.PUB.Part.Company }
WHERE MFGSYS.PUB.LaborDtl.Company = MFGSYS.PUB.EmpBasic.Company AND MFGSYS.PUB.LaborDtl.EmployeeNum = MFGSYS.PUB.EmpBasic.EmpID AND
MFGSYS.PUB.LaborDtl.Company = MFGSYS.PUB.JobProd.Company AND MFGSYS.PUB.LaborDtl.JobNum = MFGSYS.PUB.JobProd.JobNum AND
(MFGSYS.PUB.LaborDtl.ClockOutTime < 24) AND ((CASE WHEN DAYOFWEEK(MFGSYS.PUB.LaborDtl.ClockInDate)
= '1)' THEN (MFGSYS.PUB.LaborDtl.ClockInDate - 6) ELSE (MFGSYS.PUB.LaborDtl.ClockInDate - DAYOFWEEK(MFGSYS.PUB.LaborDtl.ClockInDate) + 2) END)
= (CASE WHEN DAYOFWEEK(CURDATE()) = '1)' THEN (CURDATE() - 6) ELSE (CURDATE() - DAYOFWEEK(CURDATE()) + 2) END))
GROUP BY MFGSYS.PUB.EmpBasic.Name
HAVING (MFGSYS.PUB.EmpBasic.Name = 'Wood Shop QC')
ORDER BY Team

--- In vantage@yahoogroups.com, "jckinneman" wrote:
>
> I have used this to calculate the Monday of the current week when I needed to group jobs by their due date
>
> "Create a calculated field call StartOfWeek
>
> (if weekday(Date(JobOper.DueDate)) = 1 then (JobOper.DueDate - 6) else (JobOper.DueDate - weekday(JobOper.DueDate) + 2))
>
> Then I grouped on this field, all jobs that are scheduled to run that week will be grouped together. Add a sort on the due date so that they appear in Monday to Sunday order in the grouping."
>
> Jim Kinneman
> Encompass Solutions, Inc
>
> --- In vantage@yahoogroups.com, "sbraudrick@" wrote:
> >
> > I have a report that groups data by week and have learned today that it appears the week number assigned is set at 7 day intervals from the first day of the year, regardless of the day of week.
> >
> > For example, this year started on Tuesday 1/1. So when I group using the WEEK() function it returns values based on a Tues-Mon week instead of the desired Mon-Sun.
> >
> > In MS SQL Server you can use the DatePart() function and stipulate what the first day of the week is, does anyone know how to do this with the ODBC driver for Progress?
> >
> > Thanks,
> > Sean
> >
>
2
I have a report that groups data by week and have learned today that it appears the week number assigned is set at 7 day intervals from the first day of the year, regardless of the day of week.

For example, this year started on Tuesday 1/1. So when I group using the WEEK() function it returns values based on a Tues-Mon week instead of the desired Mon-Sun.

In MS SQL Server you can use the DatePart() function and stipulate what the first day of the week is, does anyone know how to do this with the ODBC driver for Progress?

Thanks,
Sean
3
What is your end goal?



Progress itself has no Week() function AFAIK.



If you are writing queries in SQuirreL or something similar, you can use the
WEEK() function (same syntax as SQL) and the ODBC engine will/should
translate.



Why I ask of your end goal because if you're trying to do a cool report, do
all of this in Crystal as it's a bit easier and more robust for this
scenario.







From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
sbraudrick@...
Sent: Monday, February 11, 2013 10:50 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Progress ODBC WEEK() function





I have a report that groups data by week and have learned today that it
appears the week number assigned is set at 7 day intervals from the first
day of the year, regardless of the day of week.

For example, this year started on Tuesday 1/1. So when I group using the
WEEK() function it returns values based on a Tues-Mon week instead of the
desired Mon-Sun.

In MS SQL Server you can use the DatePart() function and stipulate what the
first day of the week is, does anyone know how to do this with the ODBC
driver for Progress?

Thanks,
Sean



No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13

[Non-text portions of this message have been removed]
4
The OpenEdge SQL Reference lists an ODBC scalar date function WEEK() on page 315 that accepts only one parameter: the date that you want the week number for. Unfortunately, unlike MS SQL, it does not define what day of week a week starts and ends on, it just returns the number of 7 day intervals between the beginning of the year and now + 1.

This is for a visual queuing status board. For example, this board (TV) is in one of my production areas. On a preset interval it queries the DB and shows how many pieces have been completed for the day and week, along with the queue of jobs coming down the line.

It is just an ASP .NET web application using a SQL Datasource hitting the Progress DB using the ODBC driver. I'm thinking I'll have to determine the day of week for the current date and then pass a date range based on CURDATE()-x and CURDATE()+y where x is the number of days ago Monday fell on and y is the number of days ahead Sunday falls on. It just seems a bit sloppy.

--- In vantage@yahoogroups.com, "Vic Drecchio" wrote:
>
> What is your end goal?
>
>
>
> Progress itself has no Week() function AFAIK.
>
>
>
> If you are writing queries in SQuirreL or something similar, you can use the
> WEEK() function (same syntax as SQL) and the ODBC engine will/should
> translate.
>
>
>
> Why I ask of your end goal because if you're trying to do a cool report, do
> all of this in Crystal as it's a bit easier and more robust for this
> scenario.
>
>
>
>
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
> sbraudrick@...
> Sent: Monday, February 11, 2013 10:50 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Progress ODBC WEEK() function
>
>
>
>
>
> I have a report that groups data by week and have learned today that it
> appears the week number assigned is set at 7 day intervals from the first
> day of the year, regardless of the day of week.
>
> For example, this year started on Tuesday 1/1. So when I group using the
> WEEK() function it returns values based on a Tues-Mon week instead of the
> desired Mon-Sun.
>
> In MS SQL Server you can use the DatePart() function and stipulate what the
> first day of the week is, does anyone know how to do this with the ODBC
> driver for Progress?
>
> Thanks,
> Sean
>
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
>
> [Non-text portions of this message have been removed]
>
5
Ahh, yes. I have two ASP web dashboards doing something similar. I
understand "sloppy" :-)



Here's how sloppy mine is: I installed MSSQL Express and basicially I have
an hourly scheduled query that hits Progress and refreshes my SQL table. It
is a fairly complex query with nested SubSelects, so it would take two
minutes for results to show up anyway. So I use MSSQL as my "data cube" and
let the back-end deal with the heavy lifting. Once the query's results are
in MSSQL, it's just a simple query to that new table via ASP and THEN you
can use your MSSQL Week() function. :-)







From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
sbraudrick@...
Sent: Monday, February 11, 2013 12:43 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Progress ODBC WEEK() function





The OpenEdge SQL Reference lists an ODBC scalar date function WEEK() on page
315 that accepts only one parameter: the date that you want the week number
for. Unfortunately, unlike MS SQL, it does not define what day of week a
week starts and ends on, it just returns the number of 7 day intervals
between the beginning of the year and now + 1.

This is for a visual queuing status board. For example, this board (TV) is
in one of my production areas. On a preset interval it queries the DB and
shows how many pieces have been completed for the day and week, along with
the queue of jobs coming down the line.

It is just an ASP .NET web application using a SQL Datasource hitting the
Progress DB using the ODBC driver. I'm thinking I'll have to determine the
day of week for the current date and then pass a date range based on
CURDATE()-x and CURDATE()+y where x is the number of days ago Monday fell on
and y is the number of days ahead Sunday falls on. It just seems a bit
sloppy.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Vic
Drecchio" wrote:
>
> What is your end goal?
>
>
>
> Progress itself has no Week() function AFAIK.
>
>
>
> If you are writing queries in SQuirreL or something similar, you can use
the
> WEEK() function (same syntax as SQL) and the ODBC engine will/should
> translate.
>
>
>
> Why I ask of your end goal because if you're trying to do a cool report,
do
> all of this in Crystal as it's a bit easier and more robust for this
> scenario.
>
>
>
>
>
>
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of
> sbraudrick@...
> Sent: Monday, February 11, 2013 10:50 AM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Progress ODBC WEEK() function
>
>
>
>
>
> I have a report that groups data by week and have learned today that it
> appears the week number assigned is set at 7 day intervals from the first
> day of the year, regardless of the day of week.
>
> For example, this year started on Tuesday 1/1. So when I group using the
> WEEK() function it returns values based on a Tues-Mon week instead of the
> desired Mon-Sun.
>
> In MS SQL Server you can use the DatePart() function and stipulate what
the
> first day of the week is, does anyone know how to do this with the ODBC
> driver for Progress?
>
> Thanks,
> Sean
>
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
>
> [Non-text portions of this message have been removed]
>



No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13

[Non-text portions of this message have been removed]
6
I am going to take the whole day, I think. I have a doctor appt at 8:30 and then I can finish up our taxes. So, I'm thinking I will take the whole day. Love you.

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of sbraudrick@...
Sent: Monday, February 11, 2013 12:43 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Progress ODBC WEEK() function



The OpenEdge SQL Reference lists an ODBC scalar date function WEEK() on page 315 that accepts only one parameter: the date that you want the week number for. Unfortunately, unlike MS SQL, it does not define what day of week a week starts and ends on, it just returns the number of 7 day intervals between the beginning of the year and now + 1.

This is for a visual queuing status board. For example, this board (TV) is in one of my production areas. On a preset interval it queries the DB and shows how many pieces have been completed for the day and week, along with the queue of jobs coming down the line.

It is just an ASP .NET web application using a SQL Datasource hitting the Progress DB using the ODBC driver. I'm thinking I'll have to determine the day of week for the current date and then pass a date range based on CURDATE()-x and CURDATE()+y where x is the number of days ago Monday fell on and y is the number of days ahead Sunday falls on. It just seems a bit sloppy.

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "Vic Drecchio" wrote:
>
> What is your end goal?
>
>
>
> Progress itself has no Week() function AFAIK.
>
>
>
> If you are writing queries in SQuirreL or something similar, you can use the
> WEEK() function (same syntax as SQL) and the ODBC engine will/should
> translate.
>
>
>
> Why I ask of your end goal because if you're trying to do a cool report, do
> all of this in Crystal as it's a bit easier and more robust for this
> scenario.
>
>
>
>
>
>
>
> From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of
> sbraudrick@...
> Sent: Monday, February 11, 2013 10:50 AM
> To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Progress ODBC WEEK() function
>
>
>
>
>
> I have a report that groups data by week and have learned today that it
> appears the week number assigned is set at 7 day intervals from the first
> day of the year, regardless of the day of week.
>
> For example, this year started on Tuesday 1/1. So when I group using the
> WEEK() function it returns values based on a Tues-Mon week instead of the
> desired Mon-Sun.
>
> In MS SQL Server you can use the DatePart() function and stipulate what the
> first day of the week is, does anyone know how to do this with the ODBC
> driver for Progress?
>
> Thanks,
> Sean
>
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com<http://www.avg.com>
> Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com<http://www.avg.com>
> Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
>
> [Non-text portions of this message have been removed]
>



[Non-text portions of this message have been removed]
7
Thanks for confirming my fears. I do something similar on information that only gets updated and published to the web once a day. This thing updates every several minutes, oh joy. :-)

SB

--- In vantage@yahoogroups.com, "Vic Drecchio" wrote:
>
> Ahh, yes. I have two ASP web dashboards doing something similar. I
> understand "sloppy" :-)
>
>
>
> Here's how sloppy mine is: I installed MSSQL Express and basicially I have
> an hourly scheduled query that hits Progress and refreshes my SQL table. It
> is a fairly complex query with nested SubSelects, so it would take two
> minutes for results to show up anyway. So I use MSSQL as my "data cube" and
> let the back-end deal with the heavy lifting. Once the query's results are
> in MSSQL, it's just a simple query to that new table via ASP and THEN you
> can use your MSSQL Week() function. :-)
>
>
>
>
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
> sbraudrick@...
> Sent: Monday, February 11, 2013 12:43 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Progress ODBC WEEK() function
>
>
>
>
>
> The OpenEdge SQL Reference lists an ODBC scalar date function WEEK() on page
> 315 that accepts only one parameter: the date that you want the week number
> for. Unfortunately, unlike MS SQL, it does not define what day of week a
> week starts and ends on, it just returns the number of 7 day intervals
> between the beginning of the year and now + 1.
>
> This is for a visual queuing status board. For example, this board (TV) is
> in one of my production areas. On a preset interval it queries the DB and
> shows how many pieces have been completed for the day and week, along with
> the queue of jobs coming down the line.
>
> It is just an ASP .NET web application using a SQL Datasource hitting the
> Progress DB using the ODBC driver. I'm thinking I'll have to determine the
> day of week for the current date and then pass a date range based on
> CURDATE()-x and CURDATE()+y where x is the number of days ago Monday fell on
> and y is the number of days ahead Sunday falls on. It just seems a bit
> sloppy.
>
> --- In vantage@yahoogroups.com , "Vic
> Drecchio" wrote:
> >
> > What is your end goal?
> >
> >
> >
> > Progress itself has no Week() function AFAIK.
> >
> >
> >
> > If you are writing queries in SQuirreL or something similar, you can use
> the
> > WEEK() function (same syntax as SQL) and the ODBC engine will/should
> > translate.
> >
> >
> >
> > Why I ask of your end goal because if you're trying to do a cool report,
> do
> > all of this in Crystal as it's a bit easier and more robust for this
> > scenario.
> >
> >
> >
> >
> >
> >
> >
> > From: vantage@yahoogroups.com
> [mailto:vantage@yahoogroups.com ] On
> Behalf Of
> > sbraudrick@
> > Sent: Monday, February 11, 2013 10:50 AM
> > To: vantage@yahoogroups.com
> > Subject: [Vantage] Progress ODBC WEEK() function
> >
> >
> >
> >
> >
> > I have a report that groups data by week and have learned today that it
> > appears the week number assigned is set at 7 day intervals from the first
> > day of the year, regardless of the day of week.
> >
> > For example, this year started on Tuesday 1/1. So when I group using the
> > WEEK() function it returns values based on a Tues-Mon week instead of the
> > desired Mon-Sun.
> >
> > In MS SQL Server you can use the DatePart() function and stipulate what
> the
> > first day of the week is, does anyone know how to do this with the ODBC
> > driver for Progress?
> >
> > Thanks,
> > Sean
> >
> >
> >
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
> >
> >
> >
> >
> > -----
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
>
> [Non-text portions of this message have been removed]
>
8
I used this in a BAQ for Week Ending date - (we only have progress DB not
SQL).



OrderRel.ReqDate + (7 - WEEKDAY (OrderRel.ReqDate))



My Weekending is Saturday.



This is a datatype date with format of 99/99/9999.



Not sure if this helps.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Vic Drecchio
Sent: Monday, February 11, 2013 10:53 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: Progress ODBC WEEK() function





Ahh, yes. I have two ASP web dashboards doing something similar. I
understand "sloppy" :-)

Here's how sloppy mine is: I installed MSSQL Express and basicially I have
an hourly scheduled query that hits Progress and refreshes my SQL table. It
is a fairly complex query with nested SubSelects, so it would take two
minutes for results to show up anyway. So I use MSSQL as my "data cube" and
let the back-end deal with the heavy lifting. Once the query's results are
in MSSQL, it's just a simple query to that new table via ASP and THEN you
can use your MSSQL Week() function. :-)

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of
sbraudrick@... <mailto:sbraudrick%40ymail.com>
Sent: Monday, February 11, 2013 12:43 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Re: Progress ODBC WEEK() function

The OpenEdge SQL Reference lists an ODBC scalar date function WEEK() on page
315 that accepts only one parameter: the date that you want the week number
for. Unfortunately, unlike MS SQL, it does not define what day of week a
week starts and ends on, it just returns the number of 7 day intervals
between the beginning of the year and now + 1.

This is for a visual queuing status board. For example, this board (TV) is
in one of my production areas. On a preset interval it queries the DB and
shows how many pieces have been completed for the day and week, along with
the queue of jobs coming down the line.

It is just an ASP .NET web application using a SQL Datasource hitting the
Progress DB using the ODBC driver. I'm thinking I'll have to determine the
day of week for the current date and then pass a date range based on
CURDATE()-x and CURDATE()+y where x is the number of days ago Monday fell on
and y is the number of days ahead Sunday falls on. It just seems a bit
sloppy.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Vic
Drecchio" wrote:
>
> What is your end goal?
>
>
>
> Progress itself has no Week() function AFAIK.
>
>
>
> If you are writing queries in SQuirreL or something similar, you can use
the
> WEEK() function (same syntax as SQL) and the ODBC engine will/should
> translate.
>
>
>
> Why I ask of your end goal because if you're trying to do a cool report,
do
> all of this in Crystal as it's a bit easier and more robust for this
> scenario.
>
>
>
>
>
>
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of
> sbraudrick@...
> Sent: Monday, February 11, 2013 10:50 AM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Progress ODBC WEEK() function
>
>
>
>
>
> I have a report that groups data by week and have learned today that it
> appears the week number assigned is set at 7 day intervals from the first
> day of the year, regardless of the day of week.
>
> For example, this year started on Tuesday 1/1. So when I group using the
> WEEK() function it returns values based on a Tues-Mon week instead of the
> desired Mon-Sun.
>
> In MS SQL Server you can use the DatePart() function and stipulate what
the
> first day of the week is, does anyone know how to do this with the ODBC
> driver for Progress?
>
> Thanks,
> Sean
>
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
>
> [Non-text portions of this message have been removed]
>

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
9
Hi Sean,

Another option, since you're comfortable in the .Net world, is to use the
business objects to open a session and run a BAQ to get a result set and
close the session.

Here's a thread on how to add a criteria to a BAQ on the fly:

http://tech.groups.yahoo.com/group/vantage/message/115367

And you can use one of the C# programs in the Files section to see how to
create a session

http://tech.groups.yahoo.com/group/vantage/files/C%23/

There is a Weekday function in the BAQ where Sunday = 1. All you would have
to do is set your criteria and run it and use the DataSet for your
page/report.

Mark W.


On Mon, Feb 11, 2013 at 12:43 PM, sbraudrick@... <sbraudrick@...
> wrote:

> **
>
>
> The OpenEdge SQL Reference lists an ODBC scalar date function WEEK() on
> page 315 that accepts only one parameter: the date that you want the week
> number for. Unfortunately, unlike MS SQL, it does not define what day of
> week a week starts and ends on, it just returns the number of 7 day
> intervals between the beginning of the year and now + 1.
>
> This is for a visual queuing status board. For example, this board (TV) is
> in one of my production areas. On a preset interval it queries the DB and
> shows how many pieces have been completed for the day and week, along with
> the queue of jobs coming down the line.
>
> It is just an ASP .NET web application using a SQL Datasource hitting the
> Progress DB using the ODBC driver. I'm thinking I'll have to determine the
> day of week for the current date and then pass a date range based on
> CURDATE()-x and CURDATE()+y where x is the number of days ago Monday fell
> on and y is the number of days ahead Sunday falls on. It just seems a bit
> sloppy.
>
>
> --- In vantage@yahoogroups.com, "Vic Drecchio" wrote:
> >
> > What is your end goal?
> >
> >
> >
> > Progress itself has no Week() function AFAIK.
> >
> >
> >
> > If you are writing queries in SQuirreL or something similar, you can use
> the
> > WEEK() function (same syntax as SQL) and the ODBC engine will/should
> > translate.
> >
> >
> >
> > Why I ask of your end goal because if you're trying to do a cool report,
> do
> > all of this in Crystal as it's a bit easier and more robust for this
> > scenario.
> >
> >
> >
> >
> >
> >
> >
> > From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
> Behalf Of
> > sbraudrick@...
>
> > Sent: Monday, February 11, 2013 10:50 AM
> > To: vantage@yahoogroups.com
> > Subject: [Vantage] Progress ODBC WEEK() function
> >
> >
> >
> >
> >
> > I have a report that groups data by week and have learned today that it
> > appears the week number assigned is set at 7 day intervals from the first
> > day of the year, regardless of the day of week.
> >
> > For example, this year started on Tuesday 1/1. So when I group using the
> > WEEK() function it returns values based on a Tues-Mon week instead of the
> > desired Mon-Sun.
> >
> > In MS SQL Server you can use the DatePart() function and stipulate what
> the
> > first day of the week is, does anyone know how to do this with the ODBC
> > driver for Progress?
> >
> > Thanks,
> > Sean
> >
> >
> >
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
> >
> >
> >
> >
> > -----
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.2238 / Virus Database: 2639/5596 - Release Date: 02/11/13
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>


[Non-text portions of this message have been removed]
10
I have used this to calculate the Monday of the current week when I needed to group jobs by their due date

"Create a calculated field call StartOfWeek

(if weekday(Date(JobOper.DueDate)) = 1 then (JobOper.DueDate - 6) else (JobOper.DueDate - weekday(JobOper.DueDate) + 2))

Then I grouped on this field, all jobs that are scheduled to run that week will be grouped together. Add a sort on the due date so that they appear in Monday to Sunday order in the grouping."

Jim Kinneman
Encompass Solutions, Inc

--- In vantage@yahoogroups.com, "sbraudrick@..." wrote:
>
> I have a report that groups data by week and have learned today that it appears the week number assigned is set at 7 day intervals from the first day of the year, regardless of the day of week.
>
> For example, this year started on Tuesday 1/1. So when I group using the WEEK() function it returns values based on a Tues-Mon week instead of the desired Mon-Sun.
>
> In MS SQL Server you can use the DatePart() function and stipulate what the first day of the week is, does anyone know how to do this with the ODBC driver for Progress?
>
> Thanks,
> Sean
>