• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1847
  • Last Modified:

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.
0
Vitamin1
Asked:
Vitamin1
  • 3
  • 2
1 Solution
 
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now