CODEWITHSUNDEEP
sql-serverdatetime
TangoAlee
TangoAlee

Reputation: 1380

Using datepart in query with group by

I'm trying to get a select query that is the following format using Datepart and SUM

Please see the Link to SQL Fiddle which has this all set up.

I am trying to use Datepart within the Group by but it keeps giving an error.

| Material | TaskList | Weeknum | Sum of Signoff Qty |
------------------------------------------------------
|  xx      |    aa    |   11    |     8              |
|  xx      |    bb    |   11    |     7              |
|  xx      |    aa    |   12    |     8              |
|  xx      |    bb    |   12    |     7              |
|  yy      |    aa    |   11    |     22             |
|  yy      |    bb    |   11    |     20             |
|  yy      |    aa    |   12    |     22             |
|  yy      |    bb    |   12    |     20             |

Generating test data:

CREATE TABLE tangoTable 
    (
     [Task List] varchar(50), 
     [DateandTime] varchar(50), 
     [SignoffQty] varchar(30),
     [Material] varchar(50)
    );

INSERT INTO tangoTable
([Task List]
 , [DateandTime]
 , [SignoffQty]
 , [Material])
VALUES
('aa', '3/14/16 21:41', '4', 'xx'),
('aa', '3/16/16 11:41', '4', 'xx'),
('bb', '3/18/16 20:00', '3', 'xx'),
('bb', '3/19/16 10:00', '4', 'xx'),
('aa', '3/14/16 10:00', '10', 'yy'),
('aa', '3/16/16 10:00', '12', 'yy'),
('bb', '3/18/16 11:41', '9', 'yy'),
('bb', '3/19/16 11:41', '11', 'yy'),
('aa', '3/07/16 21:41', '4', 'xx'),
('aa', '3/07/16 11:41', '4', 'xx'),
('bb', '3/07/16 20:00', '3', 'xx'),
('bb', '3/07/16 10:00', '4', 'xx'),
('aa', '3/07/16 10:00', '10', 'yy'),
('aa', '3/07/16 10:00', '12', 'yy'),
('bb', '3/07/16 11:41', '9', 'yy'),
('bb', '3/07/16 11:41', '11', 'yy')
;

Query Attempt:

Select 
[Material]
,[Task List]
,Datepart(wk, [DateandTime]) as WeekNum
,Sum(Cast(SignoffQty as Int)) as 'Sum of Signoff'
from tangoTable
Group By
[Material], [Task List], WeekNum

Upvotes: 2

Views: 878

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

You need to use date_part(wk, dateandtime) to get week number of a year.

You need to cast your signoffqty value to int datatype to be able to sum it up.

SELECT
  material, 
  [Task List], 
  datepart(wk, dateandtime) AS weeknum, 
  sum(cast(signoffqty as int)) as sum_of_signoff_qty
FROM
  tangotable
GROUP BY
  material, [Task List], datepart(wk, dateandtime)

I don't think varchar(30) for [SignoffQty] column is a good fit. It should rather be some numeric datatype.

If you happen to have a non-numeric character there your query will return an error:

Conversion failed when converting the varchar value 'value_here' to data type int.

As @Used_By_Already correctly stated:

Starting with SQL Server 2012 you could use TRY_CAST() function instead of CAST() which:

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Beware that the behaviour would change. For a given material, [Task list], weeknum pair with values in signoffqty column like:

5
3x -- varchar!
8

the sum would return: 13 instead of conversion type error.

Upvotes: 1

Related Questions