June 1, 2011 at 3:02 am
Trying unsuccessfully to get a count of tasks by week, and also want to display the week period.
This doesn't return the correct results -the count is wrong (probably due to group by) but my work in progress...please help.
I want to see something like:
Tasks SubTasks Year Month Week PeriodBeg PeriodEnd
-------------------------------------------------------------------
24 12 2011 5 23 5/28/2011 6/04/2011
SELECT Year, Month, Week, PeriodBegin,PERIODEND, COUNT(subtask) AS No_of_SubIssues, COUNT(task_id) AS No_of_Issues
FROM
(
SELECT DATEPART(YEAR,create_time) Year, DATEPART(MONTH,create_time) Month,
DATEPART(DAY,create_time) Day, DATEPART(WEEK,create_time) WEEK,
create_time - DATEPART(weekday, create_time-1) PeriodBegin, create_time - DATEPART(weekday, create_time) + 7 PERIODEND,
subtask, task_id
FROM mytasktable tsk
) temp
group by Year, Month, Week,PeriodBegin,PERIODEND
June 1, 2011 at 7:13 am
sql_jr (6/1/2011)
Trying unsuccessfully to get a count of tasks by week, and also want to display the week period.This doesn't return the correct results -the count is wrong (probably due to group by) but my work in progress...please help.
I want to see something like:
Tasks SubTasks Year Month Week PeriodBeg PeriodEnd
-------------------------------------------------------------------
24 12 2011 5 23 5/28/2011 6/04/2011
SELECT Year, Month, Week, PeriodBegin,PERIODEND, COUNT(subtask) AS No_of_SubIssues, COUNT(task_id) AS No_of_Issues
FROM
(
SELECT DATEPART(YEAR,create_time) Year, DATEPART(MONTH,create_time) Month,
DATEPART(DAY,create_time) Day, DATEPART(WEEK,create_time) WEEK,
create_time - DATEPART(weekday, create_time-1) PeriodBegin, create_time - DATEPART(weekday, create_time) + 7 PERIODEND,
subtask, task_id
FROM wh_task tsk
) temp
group by Year, Month, Week,PeriodBegin,PERIODEND
What day of the week is the start of the periods in question?
At this poin, my recommendation would be to show us some test data and tables. To get the best help, please [font="Arial Black"]don't[/font] assume you know how to do that. Instead, please refer to the first link in my signature line below for how to create readily consumable data. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 1, 2011 at 8:03 am
It doesn't matter when the period starts, part of the code calculates that based on the date in the table. So, for ex, if its 6/1/2010, the period will be calculated as PeriodBegin:5/28/11 to PeriodEnd: 6/4/11.
As for row by row, I had included a sample of what i wanted it to look like. Previously, I just posted this, and folks complained to post code. So now I posted code (that I was wkg on)
I have to count tasks from the table based on the tally for a week's worth of data (in each row). I could get it to do week 1-52, but to the end user they rather see a period. Can you help? Does this make sense now? Thanks!
June 1, 2011 at 8:49 am
You have bad results but you didn't explain why they were bad or show an example of the bad results, didn't show the data that the bad results are based on, and didn't show the good results that you are hoping to get from that data.
June 1, 2011 at 9:22 am
Wow. A lot of criticism, not a lotta help :w00t: I'm sorry if I'm not explaining it right. Right now don't have access to what the results of my query are, but it was returning a 1:1 for each date in the table.
I'm looking more for the correct tsql logic, than what's actually in the table. It could apply to any table. It has x items (tasks), with each task having a datetime stamp, going back 1-2 years, let's say.
Can't anyone take a stab at this? Count the tasks, by week, and include the week's period per row.
Again:
# of Tasks(Count) Week(No?) Year Wk_Begin Wk_End
----------------------------------------------------------
240 12 2011 3/6/11 3/12/2011
175 13 2011 3/13/11 3/19/2011
Script: "Hey boss, looks like we had 175 tasks this year during the week ending March 19th"
Many thanks!
June 1, 2011 at 9:54 am
There has been no criticism in this thread except for what you are reading into it. You first have to help yourself by helping us. Look at this post http://www.sqlservercentral.com/Forums/Topic1116896-392-1.aspx and see how the poster puts scripts that anyone could copy right off the page and paste into SSMS. Read the first link in Jeff Moden's signature. You have the leading expert willing to help you if he had some DDL to work from. 😉
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 1, 2011 at 10:54 am
Ok, todd - do my best to oblige. Thx
CREATE TABLE [Tasks](
[task_name] [nvarchar](30) NULL,
[task_id] [int] NOT NULL,
[subtask_id] int,
[subtask_name] [nvarchar](150) NULL,
[create_time] [datetime] NOT NULL
)
insert into [Tasks] values ('Network',0100,8,'NIC','2011-04-04')
go
insert into [Tasks] values ('Cable',0101,5,'RJ5','2011-04-04')
go
insert into [Tasks] values ('Mouse',0102,3,'Trackball','2011-04-05')
go
insert into [Tasks] values ('Mouse',0103,3,'Optical','2011-04-05')
go
insert into [Tasks] values ('Keyboard',0104,9,'USB','2011-04-06')
go
insert into [Tasks] values ('Keyboard',0105,9,'PS2','2011-04-06')
go
insert into [Tasks] values ('Router',0106,7,'Cisco','2011-04-12')
go
insert into [Tasks] values ('Drive',0107,6,'Floppy','2011-04-12')
go
insert into [Tasks] values ('Drive',0108,6,'CD-ROM','2011-04-13')
go
So, this query will get me them group by week(no) - total task count for each week, but also want the period begin and end.
select COUNT(task_id),datepart(wk,create_time) from Tasks
group by datepart(wk,create_time)
My code above was able to get the period, but the grouping was off.
I also want to either group by or display the year - not sure if needed or how to aggregate it. THx
HTH
June 1, 2011 at 11:15 am
Something like this?
SELECT COUNT(task_id) AS cnt,DATEPART(isowk,create_time) AS ISO_wk,YEAR(create_time) AS Yr
FROM Tasks
GROUP BY YEAR(create_time),DATEPART(isowk,create_time)
Since you didn't define the start of a week I jus assume you'll follow the ISO definition.
If not, please clarify the definition of a week.
June 1, 2011 at 11:47 am
I do appreciate your reply LutzM. Close, but not exactly. The missing element is the week period PeriodBegin AND PeriodEnd. Let me try to further explain. If you use for testing the following query alone:
Select create_time, create_time - DATEPART(weekday, create_time-1) PeriodBegin, create_time - DATEPART (weekday, create_time) + 7 PERIODEND
from tasks
you get:(just showing date):
Date Period Begin Period End
2011-04-04 2011-04-03 2011-04-09
2011-04-04 2011-04-03 2011-04-09
2011-04-05 2011-04-03 2011-04-09
2011-04-05 2011-04-03 2011-04-09
2011-04-06 2011-04-03 2011-04-09
2011-04-06 2011-04-03 2011-04-09
2011-04-12 2011-04-10 2011-04-16
2011-04-12 2011-04-10 2011-04-16
2011-04-13 2011-04-10 2011-04-16
SOO, I want to somehow get the PeriodBegin and PeriodEnd columns to show up next to the count and week columns. If I put the tsql for this in the aggregate, I must group them, and it turns out to be a different result. DOes this make sense now?
Give me LutzM's output, PLUS perBegin and perEnd. Please 😀 Thx!
June 1, 2011 at 11:54 am
So, based on the sample data provided, please show what your expected results are.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 1, 2011 at 11:57 am
K, it's somewhere in this thread a few back, but here is my expected results, again :unsure:
# of Tasks(Count) Week(No?) Year Wk_Begin Wk_End
----------------------------------------------------------
240 12 2011 3/6/11 3/12/2011
175 13 2011 3/13/11 3/19/2011
THx!
June 1, 2011 at 12:10 pm
Perhaps it was, but your test data is from April and your results are for March, thus, they don't directly match, so we don't know when we've arrived at the correct results.
Modifying Lutz's query to provide the other dates you were seeking (which the test data let me write, btw, without it I'd have buzzed past), see the following. Notice how the calculation is in both the select and the group by. This seemed like what you were looking for.
SELECT
COUNT(task_id) AS cnt,
DATEPART(isowk,create_time) AS ISO_wk,
YEAR(create_time) AS Yr,
DATEADD( dd, -1 * ( datepart( dw, Create_time) - 1), Create_time) AS BeginWeek,
DATEADD( dd, -1 * ( datepart( dw, Create_time) - 1 ), Create_time) + 6 AS EndOfWeek
FROM Tasks
GROUP BY
YEAR(create_time),
DATEPART(isowk,create_time),
DATEADD( dd, -1 * ( datepart( dw, Create_time) - 1), Create_time),
DATEADD( dd, -1 * ( datepart( dw, Create_time) - 1), Create_time) + 6
EDIT: Forgot to add the -1 to the datepart(dw) component.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 1, 2011 at 12:20 pm
I don't have the source system right now, but based on our test environment, I think this might be the answer:
SELECT Year, Month, Week, PeriodBegin,PERIODEND, COUNT(task_id) AS No_of_Issues
FROM
(
SELECT DATEPART(YEAR,create_time) Year, DATEPART(MONTH,create_time) Month,
DATEPART(DAY,create_time) Day, DATEPART(WEEK,create_time) WEEK,
create_time - DATEPART(weekday, create_time-1) PeriodBegin, create_time - DATEPART(weekday, create_time) + 7 PERIODEND,
task_id
FROM Tasks tsk
) temp
group by Year, Month, Week,PeriodBegin,PERIODEND
Thoughts?
June 1, 2011 at 12:36 pm
Yep, yours is good too. It will all depend how you want to define a week.
--Lutz
SELECT COUNT(task_id) AS cnt,DATEPART(isowk,create_time) AS ISO_wk,YEAR(create_time) AS Yr
FROM Tasks
GROUP BY YEAR(create_time),DATEPART(isowk,create_time)
--Craig
SELECT Year, Month, Week, PeriodBegin,PERIODEND, COUNT(task_id) AS No_of_Issues
FROM
(
SELECT DATEPART(YEAR,create_time) Year, DATEPART(MONTH,create_time) Month,
DATEPART(DAY,create_time) Day, DATEPART(WEEK,create_time) WEEK,
create_time - DATEPART(weekday, create_time-1) PeriodBegin, create_time - DATEPART(weekday, create_time) + 7 PERIODEND,
task_id
FROM Tasks tsk
) temp
group by Year, Month, Week,PeriodBegin,PERIODEND
--sql_jr
SELECT
COUNT(task_id) AS cnt,
DATEPART(isowk,create_time) AS ISO_wk,
YEAR(create_time) AS Yr,
DATEADD( dd, -1 * ( datepart( dw, Create_time) - 1), Create_time) AS BeginWeek,
DATEADD( dd, -1 * ( datepart( dw, Create_time) - 1 ), Create_time) + 6 AS EndOfWeek
FROM Tasks
GROUP BY
YEAR(create_time),
DATEPART(isowk,create_time),
DATEADD( dd, -1 * ( datepart( dw, Create_time) - 1), Create_time),
DATEADD( dd, -1 * ( datepart( dw, Create_time) - 1), Create_time) + 6
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
June 1, 2011 at 12:42 pm
I strongly vote against using DATEPART(wk,...) or DATEPART(dw,...) since it depends on the setting of @@DATEFIRST, which in turn can be modified by a different language setting.
And all of a sudden the result will be different even though the source data are the same...
Therefore, I'd go with the following approach:
SELECT
COUNT(task_id) AS cnt,
DATEPART(isowk,create_time) AS ISO_wk,
YEAR(create_time) AS Yr,
DATEADD(wk,DATEDIFF(wk,0,create_time),0),
DATEADD(wk,DATEDIFF(wk,0,create_time),6)
FROM Tasks
GROUP BY YEAR(create_time),DATEPART(isowk,create_time),
DATEADD(wk,DATEDIFF(wk,0,create_time),0),
DATEADD(wk,DATEDIFF(wk,0,create_time),6)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply