Select records for current week to date

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.
Vitamin1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

select
*
from
<insert your tablename here>
where
<insert your datefield here>
between
cast(convert(varchar,getdate() - (6 - datepart(dw, getdate())), 101) as datetime)
and
getdate()
0
grant300Commented:
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

Regards,
Bill

DECLARE @MONDAY DATETIME
SET @MONDAY = CONVERT(VARCHAR,DATEADD(DAY,1-DATEPART(CDW,GETDATE()),GETDATE()),102)
 
SELECT ....
 ...
WHERE SalesDate <= @MONDAY

Open in new window

0
Vitamin1Author Commented:
grant300,
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??
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

grant300Commented:
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.

Regards,
Bill
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
grant300Commented:
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.

Regards,
Bill
0
Vitamin1Author Commented:
Bill,
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.