CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Dzmitry Sevkovich
Dzmitry Sevkovich

Reputation: 901

How to select a specific row from the table with one column as a sum of values of other rows?

Let's say I have a table called EmployeeInfo like following:

Name               Hours   StartTime           Date
John Smith           8          8:00                2013-12-11
John Smith           7          7:00                2013-12-10
John Smith           9          6:00                2013-12-09
Tom Smith            6          9:00                2013-12-11
Tom Smith            8          7:00                2013-12-10
Tom Smith            7          5:00                2013-12-05
Alex Smith           8          8:00                2013-12-10

I want query to return the following table:

Name               HoursToday   HoursWeekly    StartTime    Date
John Smith           8          24              8:00        2013-12-11
Tom Smith            6          14              9:00        2013-12-11

Where all info is taken from today's date except HoursWeekly, which is the sum of Hours from the given date (lets say 2013-12-9) till today. And the info should pop up only if employee has a record as of today (2013-12-11). Any help would be appreciated.

Upvotes: 1

Views: 109

Answers (5)

Tom
Tom

Reputation: 2230

A cleaner solution than the accepted answer

SELECT e1.Name, e1.Hours HoursToday, e2.HoursWeekly, e1.StartTime, e2.Date
FROM EmployeeInfo e1
JOIN (
    SELECT Name, MAX(Date) Date, SUM(Hours) HoursWeekly
    FROM EmployeeInfo
    WHERE Date >= CONVERT(DATE, GETDATE() - 7)
    GROUP BY Name
    HAVING MAX(Date) >= CONVERT(DATE, GETDATE())
) e2 ON e1.name = e2.Name AND e1.Date = e2.Date

Upvotes: 2

KumarHarsh
KumarHarsh

Reputation: 5094

DECLARE @t TABLE
(
  Name VARCHAR(50),
  Hours INT,
  StartTime TIME,
  Date1 DATE
)

INSERT  INTO @t
        SELECT  'John Smith', 8, '8:00', '2013-12-11' UNION ALL
        SELECT  'John Smith', 7, '7:00', '2013-12-10' UNION ALL
        SELECT  'John SMITH', 9, '6:00', '2013-12-09' UNION ALL
        SELECT  'Tom Smith', 6, '9:00', '2013-12-11' UNION ALL
        SELECT  'Tom SMITH', 8, '7:00', '2013-12-10' UNION ALL
        SELECT  'Tom SMITH', 7, '5:00', '2013-12-05' UNION ALL
        SELECT  'Alex SMITH', 8, '8:00', '2013-12-10'

DECLARE @input DATE= '2013-12-9';

WITH cte1 AS 
(
   SELECT name,
          hours HoursToday,
          StartTime,
          Date1
   FROM @t
   WHERE DATEDIFF(DAY, date1, GETDATE()) = 0
),
CTE AS 
(
   SELECT name,
          SUM(hours) HoursWeekly
   FROM @t
   WHERE date1 BETWEEN @input AND GETDATE() 
   AND name IN (SELECT name FROM cte1)
   GROUP BY name
)

SELECT a.Name,
       a.HoursToday,
       b.HoursWeekly,
       a.StartTime,
       a.Date1
FROM cte1 A
INNER JOIN cte B ON a.Name = b.Name

Upvotes: 3

Kyle B.
Kyle B.

Reputation: 5787

I believe this would work.

SELECT Name, Hours, 
(SELECT SUM(Hours) FROM EmployeeInfo WHERE Name = ei.Name 
    AND [Date] BETWEEN '12/4/2011' AND '12/11/2011') As HoursWeekly, 
 StartTime, [Date]
 FROM EmployeeInfo ei WHERE ei.[Date] = '12/11/2011'

Upvotes: 0

Shoaib Shaikh
Shoaib Shaikh

Reputation: 4585

Try this, haven't tested as don't have similar table but logic should work

select Name, 
Sum(case when date = CONVERT(VARCHAR(10),GETDATE(),111) then Hours else 0 end) as HoursToday,
Sum(case when date between dateadd(dd,-7,getdate()) and getdate() then Hours else 0 end) as HoursWeekly,
Min(case when date = CONVERT(VARCHAR(10),GETDATE(),111) then StartTime else '' end) as StartTime,
Date as Date

where date = '2013-12-11'
group by name,date

Upvotes: 0

Shooorf
Shooorf

Reputation: 136

select * from
(select name, hours, StartTime, Date,
SUM(hours) over (partition by name) as totalHours
from mytable) m1
where Date = GETDATE()

Upvotes: 0

Related Questions