[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • Last Modified:

Need columns for each day of week and total

Hi Guys, been on the road for weeks and I am really brain dead....


so guy who can do this very simple query

I need

Mon,     tues,   wed etc
total      total    total etc

for SELECT     DATENAME(dw, datetime_to) AS Expr1, DATEDIFF(mi, datetime_from, DATEADD(mi, 1, datetime_to))
                      AS plan_minutes
FROM         timesheet_details AS td
where  td.the_date between GETDATE()-131 and getdate()
0
James Murrell
Asked:
James Murrell
2 Solutions
 
KatieAndEmilCommented:
You can do that using SSRS or Excel Pivot Table.... Doing it in T-SQL would rather be very unusual.

Below is link to SSRS Tutorial
http://www.katieandemil.com/ssrs-tutorial-for-beginners-2008-r2-step-by-step-pdf

Please clarify your objective

Hope that helps
Emil
0
 
James MurrellProduct SpecialistAuthor Commented:
thanks but need as sql query
0
 
vastoCommented:
You can use PIVOT operator to create a pivoted view. Check this link for help : http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
0
 
LIONKINGCommented:
Give this a try... Just use your table/column names accordingly

DECLARE @t TABLE(
      the_date DATETIME,
      datetime_from DATETIME,
      datetime_to DATETIME
)

INSERT INTO @t
SELECT GETDATE(), '2012-09-19 08:00', '2012-09-19 08:15'
UNION ALL
SELECT GETDATE(), '2012-09-19 08:40', '2012-09-19 08:52'
UNION ALL
SELECT GETDATE(), '2012-09-19 10:36', '2012-09-19 12:51'
UNION ALL
SELECT GETDATE(), '2012-09-19 13:06', '2012-09-19 13:18'

SELECT * FROM @t

SELECT *
FROM (SELECT DATENAME(dw,DATEPART(dw,the_date)) [weekday], DATEDIFF(mi,datetime_from, datetime_to) [difference]
      FROM @t) AS SourceTable
PIVOT
(SUM([difference]) FOR [weekday] IN([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])) AS PivotTable
0
 
James MurrellProduct SpecialistAuthor Commented:
Doh Cheers guys
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now