Adding all the values for each day of the week

  • Thread starter Thread starter Chumley Walrus
  • Start date Start date
C

Chumley Walrus

IN a c-sharp program that displays results in a datagrid, I have daily
sales records that i need to grab out of a database, but I
only need to grab those from the present week (starting on Monday). I
will need to add those daily sales amounts and lump them in one sum.
I'm trying to use sql strings similar to this:
select sum(salestoday) from transactions where thedate = " +
date.now(thisweek) + "
but having tough time grabbing each salestoday amount from each day of
the present week.
Any help greatly appreciated
chumley
 
Chumley,

Assuming that the value in the date doesn't include a time value, you
could do this:

select
thedate, sum(salestoday)
from
transactions
where
thedate >= (getdate() - datepart("dw", getdate()) + 2)
group by
thedate

This will give you two columns, the first being the date of the week
that the sales occured on, and the second being the total for that date. It
will also only select out transactions which occured later than Monday of
this week.

A few things to be aware of here. If you run this query around
midnight, you might get some inaccurate results. The reason for this is
that the getdate() function is called multiple times, and since time passes
in between the calls, it can lead to inaccurate results being passed back.
To get around this, you should place this in a stored procedure which takes
the date as a parameter, or where you can set the date to a variable before
you do the select. You would then replace the calls to getdate() with that
parameter/variable.

The second thing to be aware of is the value DATEFIRST (you can get it
by issuing a "select @@datefirst"). By default, it should be set to 7,
which is Sunday (this is why 2 is added to the expression that is compared
against the date). If it isn't, then you will have to change the 2 to
another value (or, you can write a more tolerant version which will figure
out how much to add/subtract depending on the first day of the week).

Hope this helps.
 
Back
Top