TSQL Count by Week - Show Period

Question

TSQL Count by Week - Show Period

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

sql_jr (6/1/2011)
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

sql_jr (6/1/2011)
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!

Okay, here are my questions then:

1. You show a total count of 415 tasks. Your sample data has 9 - please explain the difference. Are we to multiply the actual count by some magical number to obtain these results?

2. You show two date ranges: 3/6/11-3/12/11, and 3/13/11-3/19/11. These dates are not in your sample data - please explain where they come from. Are they a calculation of the dates that are in the sample data? If so, what is the formula used so that this can be reflected in what we work on.

3. Your sample data has dates of the range 4/4/11 - 4/13/11 that are not reflected in your expected output - what happens to this data? Just throw it away? Or, as questioned earlier, are they then recalculated to become a different date?

4. Is the first date being used the first date in the file, and just go every 7 days from that? If so, then 4/4/11 would be your first date in the sample data?

5. The sample data has zero-prefixed data going into an int column... should this be int or char?

This is why I asked

So, based on the sample data provided, please show what your expected results are.

What you have shown is NOT based upon the sample data provided, so we have no earthly idea of how to verify that we have a solution for you that meets your needs.

You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature. For extra clarification, read the second link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

sql_jr SSCommitted Points: 1612 More actions · Answer 2

Sorry for the misconfusion, Wayne! In the heat of having to finish these, and not having the source system available I had to take a leap of faith and make-up data. You are right.

But, safe to say, we can kill this thread, stick a fork in it - I'm done.

I believe I came to the answer myself, with help from all of you.

Many thanks!

LutzM SSC Guru Points: 107049 More actions · Answer 3

I kindly ask you to read my latest reply just to be aware of the trouble you might run into when using wk or dw together with DATEPART()...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

sql_jr (6/1/2011)
It doesn't matter when the period starts,...

Actually, it does and the reason I asked it is because I believed I could save you a lot of pain in the future. 😉 You're the one that asked for help to begin with. Don't be so quick to shirk off a question. 😉

sql_jr (6/1/2011)
I believe I came to the answer myself, with help from all of you.

Then my current recommendation would be to post your solution so we can take a look at it for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)