Group by 1-7 System date in SQL
sql date format
sql group by hour and day
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)
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
Note: If you want to change the first day of week you need to first set it like following.
SET DATEFIRST 6
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
- 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.