|
Try this
Sample Data
declare @t table(dt date, tm int)
insert into @t
select '2010-01-05',15 union all
select '2010-01-05',16 union all
select '2010-01-05',18 union all
select '2010-01-05',36 union all
select '2010-01-05',59 union all
select '2010-01-06',01 union all
select '2010-01-06',20 union all
select '2010-01-07',30 union all
select '2009-12-21',34 union all
select '2009-12-22',12
Query
select dt,CntMins = COUNT(tm),TotalMins = SUM(tm) from @t
group by dt
--order by SUM(tm) desc --[Incase you want to see the result in Descending Order]
Output
dt CntMins TotalMins
2009-12-21 1 34
2009-12-22 1 12
2010-01-05 5 144
2010-01-06 2 21
2010-01-07 1 30
Niladri Biswas
|
|
|
|
|
Group by only the date part of the datetime, modified being the datetime field
GROUP BY CONVERT(DATETIME,Modified,103)
[edit] the above does not work, if you are usiong 2008 the following will work using the new DATE data type.
SELECT COUNT(*)
FROM EquityCounter
GROUP BY CONVERT(DATE,Modified)
If you are using 2005 I would suggest chopping up the string daettime like so:
GROUP BY CONVERT(DATETIME,LEFT(CONVERT(VARCHAR(50),Modified),11))
[/edit]
Still gotta be better than a temp table!
Never underestimate the power of human stupidity
RAH
modified on Tuesday, January 5, 2010 1:16 AM
|
|
|
|
|
Mycroft Holmes wrote: Still gotta be better than a temp table!
The temp table in the answer above was only to provide example data methinks!
|
|
|
|
|
Me didn't look at the details of the answer, just looked at the complexity and shook the head. I don't think Nilandri answered the question which was to group by the date only in a datetime field that has the time component in it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How bad would a stored proc have to be that could not return more than 3000 records before timing out, regardless of how big your timeout value was? We're talking SQL Server 2005 running on an Itanium server...
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
John Simmons / outlaw programmer wrote: regardless of how big your timeout value was?
Even with a demanding query you'd have a hard time with timing out "regardless how big the value". I'd go for an endless loop in a cursor.
Can you count the cursors, temporary tables and the udf's? 
I are Troll 
|
|
|
|
|
Well, it does eventually come back (that's how they know about the 3000 record limit). An endless loop wouldn't come back at all...
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
The latest SQL Management Studio has a cool query-analyzer that even proposes indexes. It might help in tracking down the worst offenders - that way you don't have to plow through all of them.
What's it doing? "Just" some TSQL, or is it calling a webservice and writing the results to a networkdrive?
--edit--
What MyCroft said, and I'm still curious to what it's doing
I are Troll
|
|
|
|
|
I dunno - the boss says it times out, shrugs his shoulders, and we all laugh a little at the problem.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
John Simmons / outlaw programmer wrote: How bad would a stored proc have to be
Really crappy, I've seen this where SSMS performs the query in seconds and the UI takes ages. Is this the case?
Have you looked at the execution plan, it usually suggests indexes if there are glaring requirements. Otherwise just look for the most expensive operation and try and optimise it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ok so is this the same proc you are bitching about in the Lounge - and you need to ask the question.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No (at least I don't think so). I'm not bitching about it either. Here, I asked a question. In the Liunge, I merely stated my amazement at somethig regarding the job.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
So how did the execution plan investigation go?
I just noted the coincidence of the enquiry here and the Lounge entry and wondered if the very slow proc was 1500 lines of unformatted tsql which would probably account for the slow response 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm not entirely sure what sort of response you're looking for here. How bad would a stored proc have to be? Well, I guess that depends on what it does. If it's a simple select, then it would have to be pretty bad. If it's a massive stored proc that does a zillion different things and only gets run once a year then I guess that it might not be badly written, it might just be slow and time-consuming, and you'll either have to live with it or find a different approach (don't do it all in one stored proc, break it down into manageable chunks, something like that).
I don't know that anyone will really be able to give you much advice beyond what you've already got from the others, certainly not without knowing more detail about the stored proc.
|
|
|
|
|
I don't really know any of the details. The only things I know are that "somewhere" during the process of doing whatever they do in the database, it times out and they can only get about 3000 records before it does. I was just looking for a starting point as to where to look. What makes it harder to be more specific is that they don't know what part of the process is timing out. Like I said, they just say it times out, they shrug, and everyone (except me) just kinda smiles about it.
The front-end is written in VB, so that should give you an idea of what I'm dealing with.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
It can be very hard to track these things down. We once had a stored proc that ran a query, returned some data, the front end then set a flag and saved the data back again. In development it completed within seconds. In live production use, it took 15 minutes.
When we looked into it, it turned out that the stored proc was actually running very quickly. So was the front-end processing to set the flag. All the time was being taken transferring the data across the network, because in the live database each row included a massive XML document that took forever to marshall off the database server, move it over the network and then marshall it all back for the front-end to receive. When the developer first tested it, the XML documents he used were teeny-tiny little things so it all ran quickly and no-one spotted the problem.
We rewrote the application so that the update was done in the stored proc without returning anything to the front end, and it finished in less than 10 seconds. When we released it to the users, we had people phoning us up saying, "I don't think this did anything, it was too quick..."
|
|
|
|
|
Is it a stored procedure issue or a data issue?
We have a vendor supplied system where 90+ percent of the data is in one table.
We also have a user requested report that requires us to perform an inner join on said table with itself.
Tim
|
|
|
|
|
I don't know, because none of the people that should know have any idea what's causing it or at what point in the process it's happening.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Hi guys...I do not know whether it is right platform to ask the question. Suppose I update databse on my laptop. Now I want the same database on another location, to be updated automatically...obviously wirelessly. How to do that??
modified on Monday, January 4, 2010 1:35 PM
|
|
|
|
|
Hi,
It will likely depend on the database you are using. Also, is this connected at the same time, or do you mean to have the database on your laptop updated, then when you connect to the other location, it updates at that time?
Two main ways:
1) Replication - Review this in your db documentation and return if you have further questions.
2) Do it yourself in code - code your app to send the changes when you connect the the remote database.
Thanks.
|
|
|
|
|
There is Server1, Server2..... Server2 is nearer than Server1 at some location, to my device. Server2 is selected and the changes are written to the database on it. Now after this, changes should be automatically written to the Server1......hope made it clear to you. Having said this I"ll use your first suggestion.....Replication
|
|
|
|
|
What happens to the updates if your wifi connection is down?
What happens when there are 2 or more local databases?
This is a really bad design. If you are working offline (local database) then you need to implement a replication strategy. This involves more than just shoving data back and forth.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ans#1: what happens if your nearest ATM is not working well. You will have to wait untill it is fixed.
Ans#2: In case of 2 or more local DBs, nearest one will be selected to be updated.
Apart from this...im a student so any stupidity is hopefully forgiven 
|
|
|
|
|
Muzammil Saeed wrote: Apart from this...im a student so any stupidity is hopefully forgiven
Absolutely
This is not necessarily stupid, just part of the learning process, what sort of system are you trying to design you have completely confused me with ATM, wireless and nearest database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There is Server1, Server2..... Server2 is nearer than Server1 at some location, to my device.Hence Server2 is selected and the changes are written to the database on it. Now after this, changes should be automatically written to the Server1......hope made it clear to you
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
