|
Hi All,
I have used (datepart(hour,CR_callstart)) As Hours to group Data by Hours.
What i would like is to group them by Every 30 Minutes. Is there any thing i can use so that
I can get data grouped every hlaf an hour.
At the moment i can get :
Time Revenue
10:00 $100
11:00 $200
With :
Select (datepart(hour,CR_callstart)) As Hours,sum(cost)as Revenue from dbo.Tablename
Group by Hours
But I want to get
Time Revenue
10:00 $75
10:30 $25
11:00 $100
11:30 $100
Please advice.
Thanks
|
|
|
|
|
Test the time component (using datepart) for the number of minutes, set a value 0 or 30 and then group by the value. This probably can all me done in 1 select statement.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks so Much for your reply.Its much appreciated.Sorry i am Having trouble to understand it.How do i modify :
(datepart(hour,CR_callstart)) As Hours
Many thanks.
|
|
|
|
|
This should do it.
SELECT
DATEPART(hour,CR_callstart) as hour,
CASE
WHEN DATEPART(minute,CR_callstart) between 0 AND 29 THEN 0
ELSE 30
END as minute,
sum(cost) as revenue
from tablename
group by DATEPART(hour,CR_callstart),
CASE
WHEN DATEPART(minute,CR_callstart) between 0 AND 29 THEN 0
ELSE 30
END
|
|
|
|
|
That is Fantastic.Is there any way I can put Hour and Minute in One Column.
Like :
Time
10:00
10:30
11:00
11:30
etcc..
Thanks so much for your time.
|
|
|
|
|
It_tech wrote: Is there any way I can put Hour and Minute in One Column.
Sure, but thats presentation logic and should be handled appropriately.
|
|
|
|
|
Thanks For your reply and sorry to bother you.Can i do that from a Sql level?
It would be great if you can give me an example.
Many thanks.
|
|
|
|
|
He wrote the code for you - what do you want, that he should go out and test your app as well.
Jamie stated that the requirement is PRESENTATION logic therefore it does not belong in the database and you then ask him if it can be done in the database.
Do your own work and use your brain instead of relying on the forum for ideas.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks.I have already sorted it out.
Cheers
|
|
|
|
|
Hi Guru's
I have a Linked Server in SQL 2005 to an Access 2003 database.
I have written a stored procedure to insert a record into the Access DB. When I run the Proc on it's own, it perfoms the task, but the moment I call it from a Insert Trigger, do I get an Error Message Saying that the Linked Server does not support the required interface.
Could someone please help me with a solution as to how I am going to insert / update the table in Access from SQL?
Your help will be greatly appreciated.
Kind Regards,
Elizma
|
|
|
|
|
It seems likely that linked servers do not support triggers (spit - I hate triggers) so I would do the following.
Do some reading on linked server/triggers (you mey get a definitive answer here).
Move the insert to Access out of the trigger and into the proc that inserts into your SQL table. You will miss any inserts that do not go through the proc but is will work.
If you have data inserting from another source then track it down and nail it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi all I am using Microsoft.Jet.OleDb.4.0 provider to obtaint data from Excel files. Here's the connection string and it works properly:
string conn = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=source.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
The problem is that I want whenever excel file was opened or to be using by another programs, OleDb provider wouldn't access to the excel files. I guess that I have to set some properties on Extened Properties. I have googled alot but it seems nothing about it.
anybody help me! thanks
[N][Q][H]
|
|
|
|
|
Do you get an error if the file is in use?
If not how do you expect the connection to detect the file is in use. If you are then trap the error and tell the user.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Perhaps you are checking for the wrong thing... what you could consider doing is checking if the file is open before attempting to open it yourself...
There's a nice example here[^] that I found with a simple google search - it's about half way down - the one that uses the API calls.
|
|
|
|
|
this's just look like when you use
File.OpenRead(filename)
error will be returned when the file is opening.
In my case, if the file is still opening and user can modify the content while I am trying to obtain data. That's bad isn't it?
[N][Q][H]
|
|
|
|
|
Dear all,
Pls help me solve this.
select distinct voyage_no,vessel_code from D4A_RAW_BLP where vessel_code in (select distinct vessel_code,vessel_desc from D4A_RAW_BLP where vessel_code<>'') order by vessel_code
when i execute this query error occour as below
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Thanks and best regards
|
|
|
|
|
The sub query returns 2 columns but in where clause only one column. The query should be like
select distinct voyage_no,vessel_code from D4A_RAW_BLP where vessel_code in (select distinct vessel_code from D4A_RAW_BLP where vessel_code<>'') order by vessel_code
|
|
|
|
|
I had to read that twice before it sank in what you are doing. Besides trying to compare 1 field to 2 fields in the subselect (which is only allowed to return 1 field) have you looked at the where clause.
select distinct voyage_no,vessel_code from D4A_RAW_BLP where vessel_code <> ''
This should achieve the same thing without the sub select.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
try this query..........
select distinct voyage_no,vessel_code from D4A_RAW_BLP where vessel_code in (select distinct vessel_code from D4A_RAW_BLP where vessel_code is not null)
order by vessel_code
Regards:
Ganu Sharma
|
|
|
|
|
Dear All,
I am doing a project where it needs a mechanism as Replication does in Sql 2k or Sql 2k5 where i have to do in some programming language. I am searching for technical aspect of replication.
I would appreciate to have your ideas or any reference which i should be able to get an idea about technical part in replication.
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Abdul Rahman Hamidy wrote: any reference
Here[^] it is 
I are Troll 
|
|
|
|
|
Good Day All
maybe i am missing something here. I have Database A and Database B. These two database have a Stored procedure called "sp_OOPS" and in all the databases the Stored Procedure is Difference. Without any any third party software , is there a way to use Generate Script Wizard to Sync Database B with Database A.
basically when i use this wizard, it can do the if exists and all other nice sql futures, but if the sp definations are not the same it does not alter. Why i am trying to understand , is does the Wizard do that ?
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
A couple of things, do not prefix your procedures with sp_ SQL Server uses this prefix for system procedures and your procs are now being included in that set. If you must prefix I suggest usp
I'm not going to be able to help with the wizard, I very rarely use them as they tend to be limited (have you ever tried writing a wizard). However you can get at the contents of the procedures using the system views. This is what 3rd party software does.
Your expectations are a little unrealistic. The reason 3rd party software exists is because the built in tools are limited. I use SQL Compare to do this.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Good Day Holmes
Thank you for your kind reply. I don't prefix my procedures with sp, this was only an example. i know red-gate SQL Compare can do that.I was just trying to make sure if the SQL Wizard can generate a Alter when the objects have different code.
Thank you once more
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
We are about to change our configuration.
From:
32 bit Server application running on Win 2000 32 bit server
32 bit MS SQL Server 2000 running on Win 2000 32 bit server
To:
32 bit Server application running on virtual (VMWare) Win 2003 64 bit server
64 bit MS SQL Server 2008 running on Win 2008 64 bit server
Furthermore, network resources are upgraded and are, according to tests, showing improved throughput.
The new servers are equipped with 32 GB of RAM, as opposed to the old setup with 3 GB.
But, allthough queries are performed aproximately 4 times faster, fetches are considerably slower. The overall performance of our system is now roughly the same as on the old servers. In fact, they seem to perform a bit slower.
I have narrowed the problem down to fetch time. As mentioned earlier, the network seems to perform much better, so logically I should be able to take that out of the equation. Number crunching is also way faster on the new servers. Disk access on the application server is at least as fast as on the old configuration.
Now I am totally out of reasonable ideas, does any of you guys know any issues about this configuration?
"God doesn't play dice" - Albert Einstein
"God not only plays dice, He sometimes throws the dices where they cannot be seen" - Niels Bohr
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
