October 21, 2016 at 12:29 am
Hi, suppose i have the following table, is it possible to group the totals by week nr? (from Monday to Sunday)
date/time amount1amount2
1/05/2016 12:0011
2/05/2016 12:0021
3/05/2016 12:0041
3/05/2016 12:0061
3/05/2016 12:0082
3/05/2016 12:03101
5/05/2016 12:001221
8/05/2016 12:00141
8/05/2016 12:30161
10/05/2016 12:00184
11/05/2016 14:00204
12/05/2016 12:00224
13/05/2016 12:00244
14/05/2016 12:00264
15/05/2016 12:00284
16/05/2016 12:00304
17/05/2016 12:00324
18/05/2016 12:00344
19/05/2016 12:00364
This is what i want:
week sum(Amount1) sum(Amount2)
Week1711
Week187229
Week1913824
Week2013216
see attachment
October 21, 2016 at 1:16 am
Firstly, it would help if you provided data in a readily usable format.
That said, this should do the trick.
-- Create a table to house the demo data
CREATE TABLE #Data (
DataDate DATETIME
, amount1 INT
, amount2 INT
);
-- Add the demo data
INSERT INTO #Data ( DataDate, amount1, amount2 )
VALUES
( '5/1/2016 12:00', 1, 1 )
, ( '5/2/2016 12:00', 2, 1 )
, ( '5/3/2016 12:00', 4, 1 )
, ( '5/3/2016 12:00', 6, 1 )
, ( '5/3/2016 12:00', 8, 2 )
, ( '5/3/2016 12:03', 10, 1 )
, ( '5/5/2016 12:00', 12, 21 )
, ( '5/8/2016 12:00', 14, 1 )
, ( '5/8/2016 12:30', 16, 1 )
, ( '5/10/2016 12:00', 18, 4 )
, ( '5/11/2016 14:00', 20, 4 )
, ( '5/12/2016 12:00', 22, 4 )
, ( '5/13/2016 12:00', 24, 4 )
, ( '5/14/2016 12:00', 26, 4 )
, ( '5/15/2016 12:00', 28, 4 )
, ( '5/16/2016 12:00', 30, 4 )
, ( '5/17/2016 12:00', 32, 4 )
, ( '5/18/2016 12:00', 34, 4 )
, ( '5/19/2016 12:00', 36, 4 );
-----------------------------------------------------------
DECLARE @DF INT = @@DATEFIRST; -- Determine the current start day of the week.
SET DATEFIRST 1; -- Set the week to start on Monday
SELECT [week] = 'Week' + RIGHT(100 + DATEPART(wk, DataDate), 2)
, sum(amount1), SUM(amount2)
FROM #Data
GROUP BY DATEPART(wk, DataDate);
SET DATEFIRST @DF; -- Reset the startdate of the week to what it was.
October 21, 2016 at 4:34 am
SELECT DATEADD(dd, DATEDIFF(dd, 0, [date/time])/7*7), 0) WeekStart,
, sum(amount1), SUM(amount2)
FROM #Data
GROUP BY DATEPART(wk, DataDate);
No datefirst settings involved.
Converting of week start date to week number I leave to you, as I don't know how do you number weeks at your place.
_____________
Code for TallyGenerator
October 21, 2016 at 9:43 pm
yvesql (10/21/2016)
Hi, suppose i have the following table, is it possible to group the totals by week nr? (from Monday to Sunday)date/time amount1amount2
1/05/2016 12:0011
2/05/2016 12:0021
3/05/2016 12:0041
3/05/2016 12:0061
3/05/2016 12:0082
3/05/2016 12:03101
5/05/2016 12:001221
8/05/2016 12:00141
8/05/2016 12:30161
10/05/2016 12:00184
11/05/2016 14:00204
12/05/2016 12:00224
13/05/2016 12:00244
14/05/2016 12:00264
15/05/2016 12:00284
16/05/2016 12:00304
17/05/2016 12:00324
18/05/2016 12:00344
19/05/2016 12:00364
This is what i want:
week sum(Amount1) sum(Amount2)
Week1711
Week187229
Week1913824
Week2013216
see attachment
The 1st of January, 2016, occurred on a Friday. What is your definition of what "Week 1" of any given year? If it's ISO Weeks, then piece o' cake. If it's SQL Server Weeks, then not so good because those don't always have 7 days in them. Sergiy's comes the closest to being correct, especially since he also identified that you haven't defined what "Week 1" of any given year is.
Again, what is your definition of what "Week 1" of any given year?
Also, please see the first link in my signature line below for future posts. It'll help you get responses to your posts a whole lot faster.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply