asked on

day of week

I am selecting values for some dates in sql between 2 date parameters.

Select DateName(weekday, sales_date), sum(Net_Sales)
from sales_summary
where STARTDATE >= @STARTDATE AND DATE <= @ENDDATE
group by DateName(weekday, sales_date)

it gives me values between which fall in between these 2 parameters

now in report i show like this

Mon      Tue      WED

12 15      14

Now i want to show in my report all the 7 days even if i select only 3 days it has to show all seven days
some thing like this

Mon      Tue      WED      Thu      Fri      Sat       Sun

12 15      14

Qlemo

I have several issues with/questions about your question.
First, the above SQL will generate a RECORD for each weekday, not a COLUMN. Hence it is more like

Mon 12
Tue 15
Wed 14

Second, you date range check is not related to sales_date. Is that correct?
Third, does the list always have to start at Monday, or at the @startdate?

ASKER CERTIFIED SOLUTION

sameer2010

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

The last post by tigin44 is how I would approach this using SQL Server 2005. The conditional aggregate approach isn't bad either, I just would do a little different given you have system set as most do with collation as case insensitive.

Before I go down that path, for PIVOT remember that DATENAME will return Monday (full name) of the day of the week, so you have to either use that or take the left 3 characters.

SELECT [Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun]
FROM
(Select Left(DateName(weekday, sales_date), 3) AS day_Name, Net_Sales
from sales_summary
where STARTDATE >= @STARTDATE AND DATE <= @ENDDATE) P
PIVOT
(SUM(Net_Sales) FOR day_Name IN ([Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun]) ) AS PIVOTED

For the conditional, tend to do like this:

SUM( CASE DateName(weekday, sales_date) WHEN 'Monday' THEN Net_Sales END ) AS [Mon]

A little more straight-forward.