Select records for current week to date

Posted on 2008-11-12
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
    LVL 3

    Expert Comment

    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

    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


    SELECT ....
    WHERE SalesDate <= @MONDAY

    Open in new window


    Author Comment

    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??
    LVL 19

    Accepted Solution

    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

    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 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

    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

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    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.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now