SQL Timecard question

I have the following Sql database table:

TEDate    EmployeeID   ProjectID      TEDescription   Hours
1/1/15    BJD                   99-999:001   Stuff                    2
1/2/15    BJD                   99-999:001   Stuff                    3
1/3/15    BJD                   99-999:001   More Stuff         1
1/4/15    BJD                   99-999:002   Stuff                    1

And want a SQL query for this:

EmployeeID   ProjectID      TEDescription  Mon Tue Wed Thu Fri Sat Sun
BJD                  99-999:001   Stuff                     2      3
BJD                  99-999:001  More Stuff                           1
BJD                  99-999:002  Stuff                                               1

Timecard query if you were wondering.  Pivot table?  Might go into an asp web-based query so formatting for that application would be appreciated.  

Thanks!
MrBeedgeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
See this example
USE AdventureWorks2008R2;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;

Open in new window

NalinkumarbalajiCommented:
Hi,

Below is a sample for your query. I haven't tested in a query analyser.  Replace the "tablename" with your respective table name...

SELECT TEDate, EmployeeID, ProjectID, TEDescription, Sum(Monday) as Monday, Sum(Tuesday) as Tuesday, Sum(Wednesday) as Wednesday, Sum(Thursday) as Thursday, Sum(Friday) as Friday, Sum(Saturday) as Saturday, Sum(Sunday) as Sunday
FROM (
--Monday
SELECT TEDate, EmployeeID, ProjectID, TEDescription, Hours as Monday, 0 as Tuesday, 0 as Wednesday, 0 as Thursday, 0 as Friday, 0 as Saturday, 0 as Sunday
from table
where DATENAME(WEEKDAY, [TEDate]) = 'Monday'
Group by TEDate, EmployeeID, ProjectID, TEDescription

UNION
--Tuesday
SELECT TEDate, EmployeeID, ProjectID, TEDescription, 0 as Monday, Hours as Tuesday, 0 as Wednesday, 0 as Thursday, 0 as Friday, 0 as Saturday, 0 as Sunday
from table
where DATENAME(WEEKDAY, [TEDate]) = 'Tuesday'
Group by TEDate, EmployeeID, ProjectID, TEDescription

UNION
--wednesday
SELECT TEDate, EmployeeID, ProjectID, TEDescription, 0 as Monday, 0 as Tuesday, Hours as Wednesday, 0 as Thursday, 0 as Friday, 0 as Saturday, 0 as Sunday
from tablename
where DATENAME(WEEKDAY, [TEDate]) = 'Wednesday'
Group by TEDate, EmployeeID, ProjectID, TEDescription

UNION
--thursday
SELECT TEDate, EmployeeID, ProjectID, TEDescription, 0 as Monday, 0 as Tuesday, 0 as Wednesday, Hours as Thursday, 0 as Friday, 0 as Saturday, 0 as Sunday
from tablename
where DATENAME(WEEKDAY, [TEDate]) = 'Thursday'
Group by TEDate, EmployeeID, ProjectID, TEDescription

UNION
-- Friday
SELECT TEDate, EmployeeID, ProjectID, TEDescription, 0 as Monday, 0 as Tuesday, 0 as Wednesday, 0 as Thursday, Hours as Friday, 0 as Saturday, 0 as Sunday
from tablename
where DATENAME(WEEKDAY, [TEDate]) = 'Friday'
Group by TEDate, EmployeeID, ProjectID, TEDescription

UNION
--saturday
SELECT TEDate, EmployeeID, ProjectID, TEDescription, 0 as Monday, 0 as Tuesday, 0 as Wednesday, 0 as Thursday, 0 as Friday, Hours as Saturday, 0 as Sunday
from tablename
where DATENAME(WEEKDAY, [TEDate]) = 'Saturday'
Group by TEDate, EmployeeID, ProjectID, TEDescription

UNION
--sunday
SELECT TEDate, EmployeeID, ProjectID, TEDescription, 0 as Monday, 0 as Tuesday, 0 as Wednesday, 0 as Thursday, 0 as Friday, 0 as Saturday, Hours as Sunday
from tablename
where DATENAME(WEEKDAY, [TEDate]) = 'Sunday'
Group by TEDate, EmployeeID, ProjectID, TEDescription

) AS HOURS
GROUP BY HOURS.TEDate, HOURS.EmployeeID, HOURS.ProjectID, HOURS.TEDescription
ORDER BY HOURS.TEDate, HOURS.EmployeeID, HOURS.ProjectID, HOURS.TEDescription

Open in new window


--Balaji
PortletPaulEE Topic AdvisorCommented:
Here is an example using PIVOT
| EmployeeID |  ProjectID | TEDescription |    Mon |    Tue |    Wed |    Thu |    Fri |    Sat |    Sun |
|------------|------------|---------------|--------|--------|--------|--------|--------|--------|--------|
|        BJD | 99-999:001 |    More Stuff | (null) | (null) | (null) | (null) | (null) |      1 | (null) |
|        BJD | 99-999:001 |         Stuff | (null) | (null) | (null) |      2 | (null) | (null) | (null) |
|        BJD | 99-999:001 |         Stuff | (null) | (null) | (null) | (null) |      3 | (null) | (null) |
|        BJD | 99-999:002 |         Stuff | (null) | (null) | (null) | (null) | (null) | (null) |      1 |

Open in new window

select
        EmployeeID
      , ProjectID
      , TEDescription
      , Mon, Tue, Wed, Thu, Fri, Sat, Sun
from (
      select
          * , left(datename(dw,TEDate),3) as dw
      from Timecards
     ) as data
pivot (
      sum(Hours)
      for dw IN ([Mon], [Tue], [Wed], [Thu], [Fri], [Sat], [Sun])
      ) as pvt
order by
        EmployeeID
      , ProjectID
      , TEDescription
;

Open in new window

And here is another technique using GROUP BY.
| EmployeeID |  ProjectID | TEDescription |    Mon |    Tue |    Wed |    Thu |    Fri |    Sat |    Sun |
|------------|------------|---------------|--------|--------|--------|--------|--------|--------|--------|
|        BJD | 99-999:001 |    More Stuff | (null) | (null) | (null) | (null) | (null) |      1 | (null) |
|        BJD | 99-999:001 |         Stuff | (null) | (null) | (null) |      2 |      3 | (null) | (null) |
|        BJD | 99-999:002 |         Stuff | (null) | (null) | (null) | (null) | (null) | (null) |      1 |

Open in new window

select
        EmployeeID
      , ProjectID
      , TEDescription
      , SUM( case when datediff(day,0,TEDate) % 7 = 0 then Hours end ) as [Mon]
      , SUM( case when datediff(day,0,TEDate) % 7 = 1 then Hours end ) as [Tue]
      , SUM( case when datediff(day,0,TEDate) % 7 = 2 then Hours end ) as [Wed]
      , SUM( case when datediff(day,0,TEDate) % 7 = 3 then Hours end ) as [Thu]
      , SUM( case when datediff(day,0,TEDate) % 7 = 4 then Hours end ) as [Fri]
      , SUM( case when datediff(day,0,TEDate) % 7 = 5 then Hours end ) as [Sat]
      , SUM( case when datediff(day,0,TEDate) % 7 = 6 then Hours end ) as [Sun]
from Timecards
group by
        EmployeeID
      , ProjectID
      , TEDescription
order by
        EmployeeID
      , ProjectID
      , TEDescription
;

Open in new window

Note in this query I have used datediff(day,0,TEDate) % 7  to determine the day of week. This technique is independent of all server settings including language (i.e. it works reliably)

see http://sqlfiddle.com/#!6/c90ab/5 
data:
CREATE TABLE Timecards
    ([TEDate] datetime, [EmployeeID] varchar(3), [ProjectID] varchar(10), [TEDescription] varchar(10), [Hours] int)
;
    
INSERT INTO Timecards
    ([TEDate], [EmployeeID], [ProjectID], [TEDescription], [Hours])
VALUES
    ('2015-01-01 00:00:00', 'BJD', '99-999:001', 'Stuff', 2),
    ('2015-01-02 00:00:00', 'BJD', '99-999:001', 'Stuff', 3),
    ('2015-01-03 00:00:00', 'BJD', '99-999:001', 'More Stuff', 1),
    ('2015-01-04 00:00:00', 'BJD', '99-999:002', 'Stuff', 1)
;

Open in new window

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

NalinkumarbalajiCommented:
Thank you. Will use it next time onwards. :)
MlandaTCommented:
It's better the DESCRIPTION is not part of your PIVOT ,otherwise you get a row per description. I have commented it out, but you can put it back to see what the impact is.
--CREATE TABLE Table2
--    (TEDate datetime, EmployeeID varchar(3), ProjectID varchar(10), TEDescription varchar(10), Hours int)
--;
    
--INSERT INTO Table2
--    (TEDate, EmployeeID, ProjectID, TEDescription, Hours)
--VALUES
--    ('1915-01-01 02:00:00', 'BJD', '99-999:001', 'Stuff', 2),
--    ('1915-01-02 02:00:00', 'BJD', '99-999:001', 'Stuff', 3),
--    ('1915-01-03 02:00:00', 'BJD', '99-999:001', 'More Stuff', 1),
--    ('1915-01-04 02:00:00', 'BJD', '99-999:002', 'Stuff', 1)
--;

SELECT EmployeeID, ProjectID, /*TEDescription,*/ [Mon], [Tue], [Wed], [Thu], [Fri], [Sat], [Sun]
FROM (
	SELECT EmployeeID, ProjectID, /*TEDescription,*/ [Hours], LEFT(DATENAME(WEEKDAY, TEDate), 3) WeekDayName FROM Table2
) data
PIVOT (
  SUM(Hours)
  FOR WeekDayName IN ([Mon], [Tue], [Wed], [Thu], [Fri], [Sat], [Sun])
) p
ORDER BY EmployeeID

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MrBeedgeAuthor Commented:
This worked great.  Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.