Group by 1-7 System date in SQL

dateadd sql
sql date format
sql group by hour and day
datepart sql
group by hour mysql
mysql group by 1 hour intervals
mysql date format
how to get quarter from date in sql

I have a table like below, and I need to group by week (system date)

 tran_date      Hours   Tech_ID Tech_Name
02/02/2019 16:30    1   310091  Nasser Ahmed Muhammad
02/02/2019 17:30    8   310094  Ajish Babu Parambath
03/02/2019 08:30    8   310380  Nishad Mulayath Mohanan
04/02/2019 08:30    8   310380  Nishad Mulayath Mohanan
04/02/2019 08:30    8   310094  Ajish Babu Parambath
05/02/2019 08:30    1   310091  Nasser Ahmed Muhammad
06/02/2019 08:30    1   310380  Nishad Mulayath Mohanan
06/02/2019 08:30    5   310094  Ajish Babu Parambath
09/02/2019 09:30    3   310091  Nasser Ahmed Muhammad
09/02/2019 09:30    3   310094  Ajish Babu Parambath
09/02/2019 11:00    1   310380  Nishad Mulayath Mohanan
09/02/2019 13:30    1   310380  Nishad Mulayath Mohanan
09/02/2019 13:30    1   310091  Nasser Ahmed Muhammad
09/02/2019 14:30    1   310094  Ajish Babu Parambath
09/02/2019 16:30    2   310094  Ajish Babu Parambath
10/02/2019 08:30    1   310094  Ajish Babu Parambath
10/02/2019 08:30    2   310380  Nishad Mulayath Mohanan
10/02/2019 09:30    1   310094  Ajish Babu Parambath
10/02/2019 10:30    2   310091  Nasser Ahmed Muhammad
10/02/2019 13:30    1   310380  Nishad Mulayath Mohanan
10/02/2019 13:30    2   310094  Ajish Babu Parambath
10/02/2019 14:30    1   310380  Nishad Mulayath Mohanan
10/02/2019 15:30    1   310094  Ajish Babu Parambath
10/02/2019 16:30    1   310094  Ajish Babu Parambath
13/02/2019 08:30    4   310380  Nishad Mulayath Mohanan
14/02/2019 13:30    1   310091  Nasser Ahmed Muhammad
14/02/2019 14:30    0.5 310091  Nasser Ahmed Muhammad

Im expecting a result like below.

tran_week              Total
2/1/2019 - 2/7/2019    40
2/8/2019 - 2/14/2019   29.5

If you generate the the string of select part of the query by your programming language of choice then the below code shall work so will be able to calculate the hours:

    select 
      sum (case when t.tran_date between  range1_1 and  range1_2 then t.hours else 0 end) as range1,
      sum (case when t.tran_date between  range2_1 and  range2_2 then t.hours else 0 end) as range2,
      sum (case when t.tran_date between  range3_1 and  range3_2 then t.hours else 0 end) as range3,
      sum (case when t.tran_date between  range3_1 and  range4_2 then t.hours else 0 end) as range4,
  ...
  from your_table as t

DateTime group by date and hour, SQL Server : SELECT [activity_dt], count(*) FROM table1 GROUP BY DATEPART (day, [activity_dt]), DATEPART(hour, [activity_dt]);. Oracle : SELECT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id; The GROUP BY clause arranged the first three rows into two groups and the next three rows into the other two groups with the unique combinations of the customer id and order year.

Just try this code

SELECT DATEPART(wk, tran_date) wkno,
Concat(SELECT  DATEADD(DAY, 2 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS 
DATE)) ,'-',
DATEADD(DAY, 8 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) )
tran_week ,SUM(Hours) TotalHours FROM   table_name
GROUP BY DATEPART(wk, tran_date)

MySQL SYSDATE() Function, Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, PHP, Python, Bootstrap, Java� Introduction to SQL GROUP BY clause. Grouping is one of the most important tasks that you have to deal with while working with the databases. To group rows into groups, you use the GROUP BY clause. The GROUP BY clause is an optional clause of the SELECT statement that combines rows into groups based on matching values in specified columns. One

You can try like following.

;with cte as 
(
 select cast(tran_date as date) tran_date, Hours
 from @mytable
)

SELECT Convert(varchar, WS, 101) + '-' + Convert(varchar, WE, 101) AS trans_week ,SUM(HOURS) Total FROM
(
    SELECT      DATEADD(dd, -(DATEPART(dw, tran_date)-1), tran_date) WS, DATEADD(dd, 7-(DATEPART(dw, tran_date)), tran_date) WE
            ,   Hours           
    FROM        cte
)
T
GROUP BY WS,WE
order by ws

Online Demo

Note: If you want to change the first day of week you need to first set it like following.

SET DATEFIRST 6

SET DATEFIRST

SQL - DATE and TIME Functions, DATE and TIME values in PostgreSQL have a whole special set of functions You can use all of the ORDERing, GROUPing and other functions we learned or functions to help bet the current DATE, TIMESTAMP or TIME. I have a bunch of product orders and I'm trying to group by the date and sum the quantity for that date. How can I group by the month/day/year without taking the time part into consideration? 3/8/2010 7:42:00 should be grouped with 3/8/2010 4:15:00

DATEPART SQL function, In the previous article, SQL Convert Date Functions and Formats, we Get current week of year values with SQL DATEPART 1-7 days of the year We can use the DATEPART SQL function with Group By clause as well to� In analytic queries, it’s very common to group things by dates. For example you may want to see new users by year, month, week or day. To do so, you’ll want to use the TO_CHAR function to convert the dates into a truncated string before you GROUP BY it.

SQL date format Overview; DateDiff SQL function, DateAdd SQL , SQL date format functions like the DateDiff SQL function and DateAdd SQL the languages that are supported by the Windows Operating System. in the SELECT, WHERE, HAVING, GROUP BY and ORDER BY clauses. The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has same values in different rows then it will arrange these rows in a group. Important Points: GROUP BY clause is used with the SELECT statement. In the query, GROUP BY clause is placed after the WHERE clause.

SQL Server GETDATE() Function Explained By Practical Examples, The GETDATE() function returns the current system timestamp as a DATETIME value without the database time zone offset. The DATETIME value� The SQL GROUP BY Statement. The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. GROUP BY Syntax

Comments
  • which db you are using?
  • Microsoft SQL Server
  • But be sure to test dates around new year's as well, since as far as I know SQL server is not able to change the rules that define which week is week 1.
  • The dates are repeating.