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

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.  It is very hard to debug code when you do not let us
see it.

You usually build a Calendar which holds all the temporal information
for your enterprise.  you can simply number the weeks in one column of
this table.

SELECT @my_week AS week_nbr, SUM(S.sales_amt)
  FROM Sales AS S, Calendar AS C
WHERE C.week_nbr = @my_week
  AND  C.cal_date = S.sales_date;

you can do something like this:

select thedate, sum(salestoday)
from tb
where datepart(wk,thedate)=datepart(wk,getdate())
group by thedate

Show quoteHide quote

You can try setting a begining and ending variable and using the between
function to pass the two dates. Please look at the example below

Create table #transactions
(
ID          int,
thedate     datetime,
Salestoday  int
)

Insert #transactions
Values (1,getdate()-7,100)


Insert #transactions
Values (2,getdate()-6,100)


Insert #transactions
Values (3,getdate()-5,100)


Insert #transactions
Values (4,getdate()-4,100)


Insert #transactions
Values (5,getdate()-3,100)


Insert #transactions
Values (6,getdate()-2,100)

Insert #transactions
Values (7,getdate()-1,100)


Declare @thedate1 datetime,
        @thedate2 datetime

set @thedate1 = getdate()-7 -- gives one week ago
set @thedate2 = getdate()-1 -- gives the end of one week ago


select sum(salestoday) from #transactions where thedate between @thedate1
and @thedate2

Which gives the sum of 700





Show quoteHide quote

Scope of temp table
Why is Count(*) slow?
Bad datetime values stored by SQL Server 2000
Bitwise operations on BigInt
Upsized SQLServer app 100x slower than MsAccess