[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

day of week

Posted on 2009-12-19
4
Medium Priority
?
195 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:Svlss
4 Comments
 
LVL 71

Expert Comment

by:Qlemo
ID: 26088771
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?


0
 
LVL 13

Accepted Solution

by:
sameer2010 earned 1000 total points
ID: 26089690
Try this
Select CASE LCASE(DateName(weekday, sales_date))
	WHEN  'monday' THEN sum(Net_Sales) ELSE 0 END AS Mon,
       CASE LCASE(DateName(weekday, sales_date))	
       	WHEN  'tuesday' THEN sum(Net_Sales) ELSE 0 END AS Tue,
       CASE LCASE(DateName(weekday, sales_date))	     
       	WHEN  'wednesday' THEN sum(Net_Sales) ELSE 0 END AS Wed,
       CASE LCASE(DateName(weekday, sales_date))	     
       	WHEN  'thursday' THEN sum(Net_Sales) ELSE 0 END AS Thu,
       CASE LCASE(DateName(weekday, sales_date))	     
       	WHEN  'friday' THEN sum(Net_Sales) ELSE 0 END AS Fri,
       CASE LCASE(DateName(weekday, sales_date))	     
       	WHEN  'saturday' THEN sum(Net_Sales) ELSE 0 END AS Sat,
       CASE LCASE(DateName(weekday, sales_date))	     
       	WHEN  'sunday' THEN sum(Net_Sales) ELSE 0 END AS Sun
from sales_summary 
where STARTDATE >= @STARTDATE AND DATE <= @ENDDATE

Open in new window

0
 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 1000 total points
ID: 26090278
this would give the results as you the results as you wanted
SELECT [Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun]
FROM
	(Select DateName(weekday, sales_date) 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

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 26099159
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.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 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