Day of week expression for parameter

I have a sales quotes report and want the report to run for quotes
entered 2 working days ago. I have used a default date-time parameter
in the past to do something similar for another report. I will be
running the report daily and elivering by subscrition, so the parameter
needs to be part of the report. In this case, I want the expression to
evaluate what day of the week it is today and calculate what the date
was 2 working days ago. For example:
If today is Monday, then get last Thursday's date = today -4 days.
If today is Tuesday, then use the range Friday to Sunday (in case
anything is entered over the weekend) = -4 to -2 days.
If today is Wednesday, then get Monday's date = today -2 days.
If today is Thursday, then get Tuesday's date = today -2 days.
If today is Friday, then get Wednesday's date = today -2 days.
Can anybody help me - I'm struggling with how to use the date functions
like this?
Thanks!agenda9533 had a similar question and the answer was writing a user function
Search using DATEDIFF and look for agenda9533 's posting

David Portas on Any one? Query Help Please

Thanks for your response.
I got that query working,
SELECT P.productname, COUNT(S.productname)
FROM Products AS P
LEFT JOIN Sales AS S
ON P.productname = S.productname
GROUP BY P.productname ;
but what if there was another column CustomerId, which exists in both tables
and I want to just see products just for a particular customer?
So if a product is offered to a customer , but he doesn't order it then in
result I want to show that product with count as 0...Try this:
SELECT P.productname, COUNT(S.productname)
FROM Products AS P
LEFT JOIN Sales AS S
ON P.productname = S.productname
AND P.customerid = S.customerid
WHERE P.customerid = /* specify the required customer */
GROUP BY P.productname ;
The best way to get help with a problem such as this is to post DDL and
sample data. The following artcile explains how:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Thnx David,
It worked for me.
"David Portas" wrote:

> Try this:
> SELECT P.productname, COUNT(S.productname)
> FROM Products AS P
> LEFT JOIN Sales AS S
> ON P.productname = S.productname
> AND P.customerid = S.customerid
> WHERE P.customerid = /* specify the required customer */
> GROUP BY P.productname ;
> The best way to get help with a problem such as this is to post DDL and
> sample data. The following artcile explains how:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>sql

Datediff / computed column / indexing in data warehouse

Hi
I've picked up a data warehouse where i have a sales FACT table, and i
need to run a report that shows sales by shop for this week this year
vs. sales for this week last year.
ie.
WEEKLY SALES, WEEK 10
SHOP--THIS YEAR--LAST YEAR
a--100,000--85,000
b--120,100--103,000
etc
The fact table has a DATE field for each sales transaction
grain-record. There are about 25million records in the table.
My resultant select statement will do something like (trying to
remember):
SELECT
store_name,
SUM(a.sales_amount) AS this_year,
SUM(b.sales_amount) AS last_year
FROM sales a
INNER JOIN sales.b
ON b.sales_date = DATEADD(yy, -1, a.sales_date)
WHERE sales_week = 10
GROUP BY store_name
The trouble is, this is taking forever to query. I am at a loss as to
how to improve the query / table design to improve this performance.
Can anyone suggest anything? Should I have another computed column on
my table to help the query know which records it must look up for one
year ago's sales, or is there some index that i am missing'
Thanks
SeanI assume that the join is the problem (since it uses a function on a column
that is hopefully indexed, causing the plan to avoid the index). Does the
following pseudo-code query help?
select year, store,
sum(case when year = 2003 then sales else 0 end) as this_year,
sum(case when year = 2002 then sales else 0 end) as last_year
from (
select 2003 as year, store, sum(sales) as sales
where week = 10 and sales_date between 20030101 and 20031231 group by ...
union all
select 2002, store, sum(sales)
where week = 10 and sales_date between 20020101 and 20021231 group by ...
) as both_years
group by ...
Note - I've made certain assumptions about your use of the terms "this year"
and "last year". Without knowing anything else about your table (including
the DDL and the indexes you currently have), I'm hoping that it has an index
(preferably clustered) involving sales_date. It would be even better if it
also included sales_week. Lastly, since your subject included the term
"computed column", it may be beneficial to tell us what the computed column
is and why you believe that it is pertinent to the problem.
"Sean Walsh" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0309180314.429a4f53@.posting.google.com...
> Hi
> I've picked up a data warehouse where i have a sales FACT table, and i
> need to run a report that shows sales by shop for this week this year
> vs. sales for this week last year.
> ie.
> WEEKLY SALES, WEEK 10
> SHOP--THIS YEAR--LAST YEAR
> a--100,000--85,000
> b--120,100--103,000
> etc
> The fact table has a DATE field for each sales transaction
> grain-record. There are about 25million records in the table.
> My resultant select statement will do something like (trying to
> remember):
> SELECT
> store_name,
> SUM(a.sales_amount) AS this_year,
> SUM(b.sales_amount) AS last_year
> FROM sales a
> INNER JOIN sales.b
> ON b.sales_date = DATEADD(yy, -1, a.sales_date)
> WHERE sales_week = 10
> GROUP BY store_name
> The trouble is, this is taking forever to query. I am at a loss as to
> how to improve the query / table design to improve this performance.
> Can anyone suggest anything? Should I have another computed column on
> my table to help the query know which records it must look up for one
> year ago's sales, or is there some index that i am missing'
> Thanks
> Sean