return separate days of week from sql string

[DougP]

I need to get the total hours for each day so the user knows where they are at when keying in time. I stored the date such as Month = 7 day = 27 year = 2012. I also have the Weekend date with each record. which is the main criteria.
I want to return a SQL result that has something like
Sun Mon Tues Wed Thurs Fri Sat Total
3.5 12 11 26.5
Is there a way to get the Day name such as Monday or "Mon" from passing 7/23/12

Select [DAY],  [MONTH],[YEAR], WeekEndDate,  sum(HoursWorked) from SOWTimeReporting
Where WeekEndDate = '07/28/2012'
and ResourceLastName = 'flintstone' and
ResourceFirstName = 'fred'
Group by [DAY],  [MONTH],[YEAR], WeekEndDate

The above code returns this:
Day Month Year WeekEndDate
23 7 2012 2012-07-28 00:00:00.000 3.5
26 7 2012 2012-07-28 00:00:00.000 12
27 7 2012 2012-07-28 00:00:00.000 11


DougP

 

[gmmastros]

There is a DateName function you could use:

Select DateName(Weekday, GetDate())

Or...

Select DateName(Weekday, DateAdd(Day, [Day]-1, DateAdd(Month, [month]-1, DateAdd(Year, [Year]-1900, 0))))
From   YourTableName

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DougP]

Greate !!! so I extrapilated that into

Select DateName(Weekday, DateAdd(Day, [Day]-1, DateAdd(Month, 
[month]-1, DateAdd(Year, [Year]-1900, 0)))) as TheDay, sum (HoursWorked ) as Hours
From   SOWTimeReporting 
Where WeekEndDate = '8/4/2012' 
and ResourceLastName = 'flintstone' 
and ResourceFirstName = 'fred'
Group by DateName(Weekday, DateAdd(Day, [Day]-1, DateAdd(Month, 
[month]-1, DateAdd(Year, [Year]-1900, 0))))

which creates rows
TheDay Hours
Monday 1.5
Thursday 4
Tuesday 6.5
Wednesday 8

is there any way to create the "day name" as columns and a total?
Sun Mon Tues Wed Thurs Fri Sat Total
1.5 6.5 8 4 20

DougP

 

[gmmastros]

Try this:

; With DailyData As
(
  Select DateName(Weekday, DateAdd(Day, [Day]-1, DateAdd(Month, 
         [month]-1, DateAdd(Year, [Year]-1900, 0)))) as TheDay, 
		 sum (HoursWorked ) as Hours
  From   SOWTimeReporting 
  Where  WeekEndDate = '8/4/2012' 
         and ResourceLastName = 'flintstone' 
         and ResourceFirstName = 'fred'
  Group by DateName(Weekday, DateAdd(Day, [Day]-1, DateAdd(Month, 
         [month]-1, DateAdd(Year, [Year]-1900, 0))))
)
Select Max(Case When TheDay = 'Sunday' Then Hours End) As Sun,
       Max(Case When TheDay = 'Monday' Then Hours End) As Mon,
       Max(Case When TheDay = 'Tuesday' Then Hours End) As Tue,
       Max(Case When TheDay = 'Wednesday' Then Hours End) As Wed,
       Max(Case When TheDay = 'Thursday' Then Hours End) As Thur,
       Max(Case When TheDay = 'Friday' Then Hours End) As Fri,
       Max(Case When TheDay = 'Saturday' Then Hours End) As Sat,
	   Sum(Hours) As Total
From   DailyData

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom