[Last Call] Learn how to a build a cloud-first strategyRegister Now


Select records for current week to date

Posted on 2008-11-12
Medium Priority
Last Modified: 2013-12-07
I'm using Sybase ASA version 8.0.  I have a table with basic sales information such as sales date, sale amount, salesperson and payment method.  I'm trying to write a select query that will return the sum of sales amounts for the current week to date.  Our week starts on Mondays.  Every morning I would like to run this query to return the sales totals for the week going back to and including Monday.  How do I set Monday as the start of the week and then select the records from Monday to now.  Thanks for your help.
Question by:Vitamin1
  • 3
  • 2

Expert Comment

ID: 22945076
There's probably a more elegant way that this, but this should work

<insert your tablename here>
<insert your datefield here>
cast(convert(varchar,getdate() - (6 - datepart(dw, getdate())), 101) as datetime)
LVL 19

Expert Comment

ID: 22945494
Well, since you want everything up to the current time, you don't need a between, just a greater than or equal.

Next, we need to know what data type your SalesDate field is.  It may be in later versions but I thought ASA supported separate date and time data types as well as the traditional datetime.  For the purposes of this exercise, let's assume it is a DATETIME type.

The trick is to get a datetime value for Monday, 12:00:00.000 AM for the week given the current date.  For that, you can use the datepart trick figure out how many days to subtrack from the current date and then use the dateadd function to actually do the date math.  The code would look something like...(see snippet)

The process is:
 - get the current datetime
 - get the Calendar Day of the Week date part (values 1-7 with 1 = Monday)
 - Subtract the CDW from 1 to get the number of days in the past you need to go (values -0 to -6)
 - Use dateadd to add that many (negative) days to the current datetime (yeah, we have to get it again)
 - Convert that to a YYYY/MM/DD character string using style 102
 - Assign it to a DATETIME data type variable which will do an implicit conversion and set the time portion of the datetime to 12:00:00.000 AM


WHERE SalesDate <= @MONDAY

Open in new window


Author Comment

ID: 22963909
Thanks that worked.  Funny thing is that when I run the query using Sybase Interactive SQL it works fine, but when I put it in a batch file and schedule it to run, I get the following error:

Illegal Cursor Operation Attempt

I'm dumping the results to a text file fixed format.   The file is created just fine from ISQL but the batch file errors out.  Any ideas??
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 19

Accepted Solution

grant300 earned 2000 total points
ID: 22969235
Wrap the SQL in a BEGIN / END block and see if that fixes it.

Another approach, and the one I prefer, it to drop the SQL into a stored procedure.  That does a number of things.  It keeps the SQL out of shell scripts, allow you to tune and debug it much more easily, and simplifies the script since you need only one command on a line to exec the proc.

LVL 19

Expert Comment

ID: 22969257
BTW, what you say it bombs out in the "batch file", what do you mean?  Are you running this stuff on Windows?

Windows is a really crappy scripting environment, particularly after you have used Linux or Unix shell scripts.

One way around those limitations is to use Cygwin.  It is a free, Unix/Linux shell environment and a whole bunch more.  You can pretty much just load the minimum stuff you need.  In this case just the shell and bash.  If you need to, you can load an entire X-windows environment and just about every Linux tool and utility every created.

You can also use Win-Bash (look on sourceforge.net) if you want a standalone shell or, if you have really drunk the Microshaft Koolaid, you can load up PowerShell.  I don't know if that one is free or not.


Author Closing Comment

ID: 31516164
Wrapping it into a BEGIN / END did not work but the stored procedure did the trick.  I am using a windows batch file.  I'll consider your OS suggestions going forward.
Thanks Again

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question