Group projects by a Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group projects by a Date

Author

Topic

jcarver
Starting Member

18 Posts

Posted - 2007-07-11 : 16:47:47

I have a TON of entries (2,000,000+) and they all have a name, a project, and a date (Format is the 100). I am trying to get the SQL to return on how I would show how many projects have been done on what date by using the parameter @name to determine which name to look at. Any ideas? I don't know where to start on this one. Thanks in Advance,

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-11 : 16:50:59

quote:

I have a TON of entries (2,000,000+) and they all have a name, a project, and a date (Format is the 100)

Can you explain what you mean by this?

- Jeff
http://weblogs.sqlteam.com/JeffS

jcarver
Starting Member

18 Posts

Posted - 2007-07-11 : 16:54:04

What this means is that the date is in the format of mm/dd/yyyy hh:mm:ss

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-11 : 18:30:39

Are you using a datetime dataype for your data? If you are, there is no format -- it is just a datetime value. If you are not not,
then you really should be!

- Jeff
http://weblogs.sqlteam.com/JeffS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 21:06:15

quote:
If you are not not not

Think you need an extra not there ?

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-12 : 02:11:32

quote:
Originally posted by jcarver

What this means is that the date is in the format of mm/dd/yyyy hh:mm:ss

What is the datatype of date column?

Also, you need

Select columns from table
where namecol=@name

or give us more details

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-12 : 02:13:23

SELECT DATEADD(DAY, DATEDIFF(day, 0, theDateColumn), 0) AS theDate, COUNT(*)
FROM Table1
WHERE Name = @Name
GROUP BY DATEADD(DAY, DATEDIFF(day, 0, theDateColumn), 0)
ORDER BY DATEADD(DAY, DATEDIFF(day, 0, theDateColumn), 0)

Peter Larsson
Helsingborg, Sweden

jcarver
Starting Member

18 Posts

Posted - 2007-07-12 : 09:52:32

The Column "date" is in DateTime, and the simplified table looks something like this

MateName Date Project
MR34-2 02/14/07 1:03:35 PM \\Ribbon\Holding\12_ERP_Tester.exe
MR34-2 02/14/07 1:04:23 PM \\Ribbon\Holding\16_ERP_Tester.exe
MR34-2 02/14/07 1:06:41 PM \\Ribbon\Holding\18_ERP_Tester.exe
MR56-2 02/17/07 8:23:56 PM \\Ribbon\Holding\34_ERP_Tester.exe
MR34-2 02/18/07 3:45:41 PM \\Ribbon\Finshed\769_ERP_Tester.exe
MR73-4 02/24/07 9:19:21 PM \\Pipe\Holding\27_ERP_Tester.exe
.....(For about 1,000,000 + More records)

I need to show how many tests were done, per mate, per day. So if my parameter for name is MR34-2 output would need to be something like

Mate Day Count
MR34-2 02/14/07 3
MR34-2 02/18/07 1

I hope that this clears up any questions that you may have. Thanks in advance,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-12 : 10:02:42

Then modify Peso's query accordingly

SELECT matename,DATEADD(DAY, DATEDIFF(day, 0, theDateColumn), 0) AS theDate, COUNT(*)
FROM Table1
WHERE Name = @Name
GROUP BY matename,DATEADD(DAY, DATEDIFF(day, 0, theDateColumn), 0)
ORDER BY matename,DATEADD(DAY, DATEDIFF(day, 0, theDateColumn), 0)

Madhivanan

Failing to plan is Planning to fail

jcarver
Starting Member

18 Posts

Posted - 2007-07-12 : 10:33:52

Ok, I'm using that Query, but I can't get rid of the error
"DATEADD Function requires 3 Arguments" Any ideas about what is going wrong? I've modified the query to be:

SELECT mate_name,DATEADD(DAY, DATEDIFF(day, 0, getdate())) AS theDate, COUNT(*)
FROM Projects
WHERE mate_Name = @Name
GROUP BY matename,DATEADD(DAY, DATEDIFF(day, 0, date0))
ORDER BY matename,DATEADD(DAY, DATEDIFF(day, 0, date0))

Thanks,

/jcarver

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-12 : 10:57:44

The query is wrong. Read the query I posted and see how it differs from yours

Madhivanan

Failing to plan is Planning to fail

jcarver
Starting Member

18 Posts

Posted - 2007-07-12 : 13:08:49

They query works like a charm, thanks so much, that cleared up the problem perfectly!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources