How to group by the each week upto last six week sundays dates in sql?
I made a few assumptions about the data that you posted.
First, the values you posted all state the year as 2011 but the final end dates as column headers do not correspond to 2011, they are the Sunday values for 2012 so I altered the data. Also the final entry of Early ASN 8/15/2011 12:00, I believe is supposed to be a Late ASN entry otherwise the totals to do match up.
To get the results, you want you will want to apply the PIVOT function. This function allows you to aggregate the values and then convert them to columns.
SET DATEFIRST 1 -- set this so the start of the week is Sunday
select InstanceType,
sum([39]) as Sep_23,
sum([38]) as Sep_16,
sum([37]) as Sep_09,
sum([36]) as Sep_02,
sum([35]) as Aug_26,
sum([34]) as Aug_19
from
(
select SPGI01_INSTANCE_TYPE_C as InstanceType,
[39], [38], [37], [36], [35], [34]
from
(
select SPGI01_INSTANCE_TYPE_C,
DatePart(wk, SPGI01_CREATE_S) WeekNo,
DATEADD(DAY, 7 -DATEPART(WEEKDAY,SPGI01_CREATE_S),SPGI01_CREATE_S) WeekEnd
from table1
) x
pivot
(
count(WeekEnd)
for weekno in ([39], [38], [37], [36], [35], [34])
) p
) x1
group by InstanceType with rollup
See SQL Fiddle with Demo
How to group by week in MySQL?
Figured it out... it's a little cumbersome, but here it is.
FROM_DAYS(TO_DAYS(TIMESTAMP) -MOD(TO_DAYS(TIMESTAMP) -1, 7))
And, if your business rules say your weeks start on Mondays, change the -1 to -2.
Edit
Years have gone by and I've finally gotten around to writing this up.
https://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/
Group days by week
All right, bear with me here. We're going to build a temporary calendar table that represents this month, including the days from before and after the month that fall into your definition of a week (Monday - Sunday). I do this in a lot of steps to try to make the process clear, but I probably haven't excelled at that in this case.
We can then generate the ranges for the different weeks, and you can join against your other tables using that.
SET DATEFIRST 7;
SET NOCOUNT ON;
DECLARE @today SMALLDATETIME, @fd SMALLDATETIME, @rc INT;
SELECT @today = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0), -- today
@fd = DATEADD(DAY, 1-DAY(@today), @today), -- first day of this month
@rc = DATEPART(DAY, DATEADD(DAY, -1, DATEADD(MONTH, 1, @fd)));-- days in month
DECLARE @thismonth TABLE (
[date] SMALLDATETIME,
[weekday] TINYINT,
[weeknumber] TINYINT
);
;WITH n(d) AS (
SELECT TOP (@rc+12) DATEADD(DAY, ROW_NUMBER() OVER
(ORDER BY [object_id]) - 7, @fd) FROM sys.all_objects
)
INSERT @thismonth([date], [weekday]) SELECT d, DATEPART(WEEKDAY, d) FROM n;
DELETE @thismonth WHERE [date] < (SELECT MIN([date]) FROM @thismonth WHERE [weekday] = 2)
OR [date] > (SELECT MAX([date]) FROM @thismonth WHERE [weekday] = 1);
;WITH x AS ( SELECT [date], weeknumber, rn = ((ROW_NUMBER() OVER
(ORDER BY [date])-1) / 7) + 1 FROM @thismonth ) UPDATE x SET weeknumber = rn;
-- now, the final query given all that (I've only broken this up to get rid of the vertical scrollbars):
;WITH ranges(w,s,e) AS (
SELECT weeknumber, MIN([date]), MAX([date]) FROM @thismonth GROUP BY weeknumber
)
SELECT [week] = CONVERT(CHAR(10), r.s, 120) + ' - ' + CONVERT(CHAR(10), r.e, 120)
--, SOMETHING , other columns from STATISTICS_?
FROM ranges AS r
-- LEFT OUTER JOIN dbo.STATISTICS_ AS s
-- ON s.TIME_ >= r.s AND s.TIME_ < DATEADD(DAY, 1, r.e)
-- comment this out if you want all the weeks from this month:
WHERE w = (SELECT weeknumber FROM @thismonth WHERE [date] = @today)
GROUP BY r.s, r.e --, SOMETHING
ORDER BY [week];
Results with WHERE clause:
week
-----------------------
2012-05-14 - 2012-05-20
Results without WHERE clause:
week
-----------------------
2012-04-30 - 2012-05-06
2012-05-07 - 2012-05-13
2012-05-14 - 2012-05-20
2012-05-21 - 2012-05-27
2012-05-28 - 2012-06-03
Note that I chose YYYY-MM-DD on purpose. You should avoid regional formatting like M/D/Y especially for input but also for display. No matter how targeted you think your audience is, you're always going to have someone who thinks 05/07/2012 is July 5th, not May 7th. With YYYY-MM-DD there is no ambiguity whatsoever.
Group by week and select records from current week
SELECT DATE_FORMAT(cc.datefield, '%d-%b') AS DATE
, SUM(IFNULL(cds.quantity,0)) AS total_sales
FROM cart_calendar cc
INNER JOIN cart_daily_sales cds
ON (DATE(cds.order_date) = cc.datefield)
WHERE WEEK(cc.datefield) = WEEK(now())
GROUP BY WEEK(cc.datefield)
Or if you want it per day:
SELECT DATE_FORMAT(cc.datefield, '%d-%b') AS DATE
, SUM(IFNULL(cds.quantity,0)) AS total_sales
FROM cart_calendar cc
INNER JOIN cart_daily_sales cds
ON (DATE(cds.order_date) = cc.datefield)
WHERE WEEK(cc.datefield) = WEEK(now())
GROUP BY MOD(WEEKDAY(cc.datefield)+1,6) WITH ROLLUP
SQL Group by Week Date
This gives me Mondays..
SELECT DATEADD(Week, DATEDIFF(week, 0, created), 0) [week],
count(1) [number of orders]
FROM Orders
GROUP BY DATEADD(Week, DATEDIFF(week, 0, created), 0)
If above gives you Monday, this should give you Sunday
DATEADD(Week, DATEDIFF(week, 0, created), -1)
count grouped records by ID and show as weekly with day/week outputted
Do you mean this?
WITH
-- your input ...
indata(BOOKID,USR,DT) AS (
SELECT 1,'A',DATE '20211001'
UNION ALL SELECT 2,'A',DATE '20211002'
UNION ALL SELECT 3,'A',DATE '20211003'
UNION ALL SELECT 4,'A',DATE '20211004'
UNION ALL SELECT 5,'B',DATE '20211009'
UNION ALL SELECT 6,'C',DATE '20211008'
UNION ALL SELECT 7,'C',DATE '20211008'
)
SELECT
COUNT(*) AS week_count
, usr
, TO_CHAR(
DATE_TRUNC('WEEK',dt) + INTERVAL '6 DAYS'
, 'MM/DD'
) AS trcweek
FROM indata
GROUP BY 2,3
ORDER BY 2,3
;
week_count | usr | trcweek
------------+-----+---------
3 | A | 10/03
1 | A | 10/10
1 | B | 10/10
2 | C | 10/10
Group daily items by week from starting date
You can use the date_trunc() function to convert a date into the start of a week (Monday). If you need to group by a different day (eg Sunday), you will need to offset the date, then do a date_trunc(), then add the day back again.
Here is an example from DATE_TRUNC Function - Amazon Redshift:
select date_trunc('week', saletime), sum(pricepaid) from sales where
saletime like '2008-09%' group by date_trunc('week', saletime) order by 1;
date_trunc | sum
------------+------------
2008-09-01 | 2474899.00
2008-09-08 | 2412354.00
2008-09-15 | 2364707.00
2008-09-22 | 2359351.00
2008-09-29 | 705249.00
(5 rows)
How to get the six week sunday count using the Weekno in SQL Server 2008?
If you are looking to be able to pass in any date values to get the data that meets your criteria, then for this type of PIVOT you will need to use a dynamic SQL solution similar to this:
DECLARE @cols AS NVARCHAR(MAX),
@colsRollup AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@StartDate DateTime,
@EndDate DateTime
Set @StartDate = '10-08-2012 00:00:00.000'
Set @EndDate = '11-18-2012 23:59:59.000'
select @cols = STUFF((SELECT ',' + QUOTENAME(WeekEnd)
from
(
select DatePart(wk, I01.[SPGI01_CREATE_S]) WeekEnd
from [SUPER-G].[dbo].[CSPGI01_ASN_ACCURACY]
where I01.[SPGI01_CREATE_S] between @StartDate AND @EndDate
) src
group by WeekEnd
order by WeekEnd desc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @colsRollup = STUFF((SELECT ', Sum(' + QUOTENAME(WeekEnd) +') as WeekNo'+Cast(Weekend as varchar(2))
from
(
select DatePart(wk, I01.[SPGI01_CREATE_S]) WeekEnd
from [SUPER-G].[dbo].[CSPGI01_ASN_ACCURACY]
where I01.[SPGI01_CREATE_S] between @StartDate AND @EndDate
) src
group by WeekEnd
order by WeekEnd desc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = '
SELECT case when InstanceType is not null then InstanceType else ''Sum'' End InstanceType ,
'+@colsRollup+', max(InstanceDescription) AS InstanceDescription
FROM
(
SELECT SPGI01_INSTANCE_TYPE_C as InstanceType,
InstanceDescription, ' + @cols + '
from
(
SELECT I01.[SPGI01_INSTANCE_TYPE_C],
DatePart(wk, I01.[SPGI01_CREATE_S]) WeekNo,
DATEADD(DAY, 7 -DATEPART(WEEKDAY,I01.[SPGI01_CREATE_S]), I01.[SPGI01_CREATE_S]) WeekEnd,
J03.SPGJ03_MSG_TRANSLN_X InstanceDescription
FROM [SUPER-G].[dbo].[CSPGI01_ASN_ACCURACY] I01
INNER JOIN [SUPER-G].[dbo].[CSPGI50_VALID_INSTANCE_TYPE] I50
ON I50.[SPGI50_INSTANCE_TYPE_C] = I01.[SPGI01_INSTANCE_TYPE_C]
LEFT JOIN CSPGJ02_MSG_OBJ J02
ON I50.SPGJ02_MSG_K = J02.SPGJ02_MSG_K
LEFT JOIN CSPGJ03_MSG_TRANSLN J03
ON J02.SPGJ02_MSG_K = J03.SPGJ02_MSG_K
where I50.[SPGA04_RATING_ELEMENT_D] = 1
and I01.[SPGI01_EXCEPTIONED_F] = ''N''
and I01.[SPGI01_DISPUTED_F] != ''Y''
AND J03.[SPGJ03_LOCALE_C] = ''en_US''
and I01.[SPGA02_BUSINESS_TYPE_C] = ''PROD''
and I01.[SPGA03_REGION_C] = ''EU''
and I01.[SPGI01_SUB_BUSINESS_TYPE_C] = ''PRD''
and I01.[SPGI01_CREATE_S] between '+ convert(varchar(10), @StartDate, 120)+' AND '+ convert(varchar(10), @EndDate, 120)+'
) x
pivot
(
count(WeekEnd)
for weekno in (' + @cols + ')
) p
) x1
GROUP BY InstanceType WITH ROLLUP '
execute(@query)
Note: this is untested since I do not have any sample data, etc.
Group by weekofyear MySQL for the end of the year
You need to switch to YEARWEEK(orders.date,3) to get the ISO weeks as a single column. Using WEEK(orders.date,3) (which is exactly the same as WEEKOFYEAR) will return the correct week number, but YEAR(orders.date) will return either 2015 or 2016, splitting the week into four days in 2015 and and three days in 2016.
How to get the description from the tables?
It is difficult to tell how the tables are related, but it looks like you want to do something like this:
SET DATEFIRST 1
SELECT
case when InstanceType is not null then InstanceType else 'Sum' End InstanceType ,
sum([39]) AS FirstWeek,
sum([38]) AS SecondWeek,
sum([37]) AS ThirdWeek,
sum([36]) AS FourthWeek,
sum([35]) AS FifthWeek,
sum([34]) AS SixthWeek,
max(InstanceDescription)
FROM
(
SELECT [SPGI03_INSTANCE_TYPE_C] AS InstanceType,
[39], [38], [37], [36], [35], [34], InstanceDescription
FROM
(
SELECT I03.[SPGI03_INSTANCE_TYPE_C],
DatePart(wk, I03.[SPGI03_CREATE_S]) WeekNo,
DATEADD(DAY, 7 -DATEPART(WEEKDAY,I03.[SPGI03_CREATE_S]),
I03.[SPGI03_CREATE_S]) WeekEnd,
J03.SPGJ03_MSG_TRANSLN_X InstanceDescription
FROM [SUPER-G].[dbo].[CSPGI03_MANUAL_ASN] I03
INNER JOIN [SUPER-G].[dbo].[CSPGI50_VALID_INSTANCE_TYPE] I50
ON I50.[SPGI50_INSTANCE_TYPE_C] = I03.[SPGI03_INSTANCE_TYPE_C]
LEFT JOIN CSPGJ02_MSG_OBJ J02
ON I50.SPGJ02_MSG_K = J02.SPGJ02_MSG_K
LEFT JOIN CSPGJ03_MSG_TRANSLN J03
ON J02.SPGJ02_MSG_K = J03.SPGJ02_MSG_K
where I50.[SPGA04_RATING_ELEMENT_D] = 2
and I03.[SPGI03_EXCEPTIONED_F] = 'N'
and I03.[SPGI03_DISPUTED_F] != 'Y'
and I03.[SPGI03_CREATE_S] between '08-19-2012 12:00:00.000' AND DATEADD(day , 7, '09-30-2012 11:59:59.000'
)) x
pivot
(
count(WeekEnd)
FOR weekno IN ([39], [38], [37], [36], [35], [34])
) p
) x1
GROUP BY InstanceType WITH ROLLUP
Related Topics
Why Does SQL Server Return 0 for 1/2
Postgresql: Check If Schema Exists
Sqllite Strftime Not Reading Column Value
SQL Management Studio Won't Recognize a Table Exists After Scripted Create
Efficient Latest Record Query with Postgresql
Using Left Join and Inner Join in the Same Query
How to Take Last Four Characters from a Varchar
Database Design for Recurring Events with Exceptions
Oracle Regexp_Substr | Fetch String Between Two Delimiters
How to Generate a Random, Unique, Alphanumeric Id of Length N in Postgres 9.6+
SQL Different Between Left Join On... and Left Join On..Where
What Do Column Flags Mean in MySQL Workbench
SQL Query in Spark/Scala Size Exceeds Integer.Max_Value