|
I want to get time in HH:mm:ss format e.g. 13:15:02
AliAmjad(MCP)
First make it Run THEN make it Run Fast!
|
|
|
|
|
This will do your trick.
DECLARE @d1 DATETIME, @d2 DATETIME, @sd INT
SET @d1 = '20090706 00:00:00'
SET @d2 = '20090707 01:30:01'
SET @sd = DATEDIFF(SECOND, @d1, @d2)
SELECT
CASE WHEN @sd/3600<10 THEN '0' ELSE '' END
+ RTRIM(@sd/3600)
+ ':' + RIGHT('0'+RTRIM((@sd % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((@sd % 3600) % 60),2) AS Hours
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Thanks Bro... 
AliAmjad(MCP)
First make it Run THEN make it Run Fast!
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hey everyone,
In Oracle, can we store files as attachments in a table field "Files like MS. Word, Excel, PDFs" so it contains all the files embedded in a table and when exporting the database, you dont need to also backup a folder of files from your drive as well.
Please advise.
Many thanks!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
modified on Sunday, July 5, 2009 6:46 AM
|
|
|
|
|
|
Thanks mate but I'm sorry I forgot to mention that I'm using Oracle and apparently whatever the case is, I still need to store external files on the drive which's mainly my question.. Thanks anyways mate.
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
|
Thanks!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Hi Clever People.
I have a scenario and no matter what I try (other than cursors) I cant seem to get this working on an easy way.
I have StockCodes Say 10 Detail Rows per StockCode, but I want to see say the top 5 rows per StockCode.
Can anyone give me advice please on a quick and painless way or should I just go with the cursor?
Kind Regards
Elizma
|
|
|
|
|
Hope you have a table(tblStock) with a column like StockCode
StockCode
--------
1
2
3
4
5
6
7
8
9
10
The query is:
SELECT TOP 5 StockCode FROM TBLStockCode
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi
Sorry. Thanx for your supply, maybe I didn't ask the question properly. I now the plain top 5 query. My problem is:
I have a master StockCode table that will contain unique StockCodes and their information.
I also have a Details Table that is linked to the Master StockCode Table. Each Stock Code in the Master might have 20+ rows in the Detail table. I want to specify the Top N PER StockCode.
E.g. If we use only TOP 2
StockCode1 - DetailDataRow1
DetailDataRow2
StockCode2 - DetailDataRow1
DetailDataRow2
Not maybe in this format, but I still need to get the TOP 2 rows per StockCode and not the First Top 2 Rows like what the normal solution that you suggested would do.
Hope this is a bit more clear.
Thanx for trying to help.
Elizma
|
|
|
|
|
I am Not getting your Question pls clearly define it.
use ranking Function like Dense_Rank() of SQL Server.
Dinesh
|
|
|
|
|
Hi
Thanx for your willingness to help, but I dont think there is an easy way to do what I was looking for and it is quite difficult to explain, so I decided to not waste too much time on a easy way out and to rather go for the cursor option to solve my problem.
I dont think your solution would have worked either since my problem was that I have 20+ Rows Reletad to 1 StockCode. I have 1000 StockCodes. So EACH StockCode Have 20 Related Rows. Giving the Total Number of Details to 20000.
So my question was. I only want to select the top 5 rows PER StockCode. I.O.W. I would now have 5000 Records returned to me. 5 Per StockCode.
Hope this makes more sense.
Kind Regards,
Elizma
|
|
|
|
|
Cross Apply is solution to your problem, with combination of XML, you can get a very nice output.
|
|
|
|
|
Hi
Thanx for your suggestion. It might have been able to work, but I forgot to say that this is on a SQL 2000 Server and unfortunately does not support XML!
|
|
|
|
|
Do you mean suppose i have a Table
stockcode item qty
1 101 10
1 102 20
1 103 20
2 104 25
2 105 20
2 106 25
2 107 26
with abc
as
(select Row_number() over (Partition by Stockcode order by stockcode) as [Row],StockCode,Item,Qty
from Stock)
select top 4 row,Item,Sum(qty) as [QTY] from abc group by Item,row
result
1 101 10
1 104 25
2 102 20
2 105 20
|
|
|
|
|
Hi
Also not quite what I needed. If I use your example:
stockcode item qty
1 101 10
1 102 20
1 103 20
2 104 25
2 105 20
2 106 25
2 107 26
The result I require is the following.
(Say I want the Top 2 PER StockCode)
Result:
StockCode Item Qty
1 101 10
1 102 20
2 104 25
2 105 20
Thanx for trying to help. I really do appreciate it.
Elizma
|
|
|
|
|
Dear All,
I have two Servers in two different locations which both runs Ms Sql 2005 Instance.
Server A with Ip address 72.32.16.32 which is public ip address located in London
and Server B with Ip address 192.168.2.3 which is locally configured and is a database server in Kabul.
Now i would like to establish transactional replication between these two servers.
1. can i establish replication between these servers?
2. can i establish linked server between these two servers.
any reference, idea would be really appreciated.
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
|
Yes to both.
Check out Books Online for further details[^]
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Select *
Into #BC
From dfCert -- Creates master Certification table -> #BC
Declare @BoardCert Char(3)
Set @BoardCert = 'BCn'
Declare @Counter int
Set @Counter = 1
While @Counter < 10
Begin
Select
Biog_Nbr,
Max(Biog_Cert_ID) as aMaxBiogCertID,
Max(Cert_Year) as aMaxCertYear
Into
#tblBoardCert
From
#BC
Group By
Biog_Nbr
Delete From #BC
From #BC
Inner Join #tblBoardCert
On
#BC.Biog_Nbr = #tblBoardCert.Biog_Nbr
and
#BC.Biog_Cert_ID = #tblBoardCert.AMaxBiogCertID
and
#BC.Cert_Year = #tblBoardCert.aMaxCertYear
Where
#BC.Biog_Nbr = #tblBoardCert.Biog_Nbr
and
#BC.Biog_Cert_ID = #tblBoardCert.AMaxBiogCertID
and
#BC.Cert_Year = #tblBoardCert.aMaxCertYear
Set @BoardCert = Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','')
Select *
Into #@BoardCert --<< Here's the problem. I need this to be #BC1 then BC2, etc up to BC9
From #tblBoardCert
Drop Table #tblBoardCert
Set @Counter = @Counter + 1
Print 'The counter has just increased to ' + Cast(@Counter as Char)
End
|
|
|
|
|
I guess there is some problem in the counter placement.
You are dropping the #TtblBoardCert and then you are incrementing.
Drop Table #tblBoardCert<br />
<br />
Set @Counter = @Counter + 1
So before the first increment itself the table is dropped.
Also I have a doubt in this line
Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','')
Check if it is giving you the correct result or not.
Hope this helps.
Niladri Biswas
|
|
|
|
|
I drop the table because it needs to be rebuild on each cycle. The "Max" records are removed on the first cycle and then I pull the next set of "Max" records for the second cycle. This goes on until #BC is empty. Replace(Left(@BoardCert,2)+Cast(@Counter as Char),' ','') actually works. The real problem is the following:
Select *
Into #@BoardCert
From #tblBoardCert
It doesn't see #@BoardCert as #BC1 then #BC2 etc., it only sees it as #@BoardCert and so it errors because it thinks it's already written to #@BoardCert on the first cycle.
|
|
|
|
|
This just sounds weird, without going through the details of your script I think you can achieve this in 1 table by including a setID. If you need to create an incremented value over a set you can use row_number (over setID).
Creating and dropping temp tables is just bad design.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
