Database

Time Difference w.r.t Dates

5-Jul-09 22:10

Check this.[^]

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.

AliAmjad5-Jul-09 19:37

AliAmjad

5-Jul-09 19:37

Actually i just want to output the difference of two Timespans with respect
to its dates e.g.

07/03/2009 05:00:00
07/04/2009 10:00:00

and the difference i get is 05:00:00 which is not correct i am using the following code:

DECLARE @EnterTime datetime, @DischargeTime datetime

Select @EnterTime = convert(varchar,EnterTime,8) from visit

Select @DischargeTime = convert(varchar,NextTime,8) from visit

Select convert(varchar,@DischargeTime - @EnterTime,8)

the above code is working fine if the duration is between 24 hours. Can you please suggest me a solution or i have to apply the logic on programmatic side i.e. .NET?

AliAmjad(MCP)

First make it Run THEN make it Run Fast!

Blue_Boy5-Jul-09 20:41

5-Jul-09 20:41

Do you want to get days or hours or minutes between two dates?

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.

AliAmjad5-Jul-09 21:01

AliAmjad

5-Jul-09 21:01

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!

Blue_Boy5-Jul-09 21:39

5-Jul-09 21:39

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.

AliAmjad5-Jul-09 21:54

AliAmjad

5-Jul-09 21:54

Thanks Bro... Smile | :)

AliAmjad(MCP)

First make it Run THEN make it Run Fast!

Blue_Boy5-Jul-09 22:01

5-Jul-09 22:01

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.

BFILE, BLOB?? [modified]

Muammar©4-Jul-09 7:56

Muammar©

4-Jul-09 7:56

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

Niladri_Biswas4-Jul-09 18:00

Niladri_Biswas

4-Jul-09 18:00

Yes it can be done.

If you are using Sql Server 2008, you can take the advantage of the new feature FILESTREAM Storage.
http://msdn.microsoft.com/en-us/library/cc949109.aspx[^]

If you are using Sql Server 2005 store it in native SQL Server blob type.

Here's a nice introduction
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1273934,00.html[^]
to blob types in SQL Server by Denny Cherry.

Here's an example of writing blobs using C#.
http://www.akadia.com/services/dotnet_read_write_blob.html[^]

Hope this helps.
Smile | :)

Niladri Biswas

Muammar©5-Jul-09 0:40

Muammar©

5-Jul-09 0:40

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)
(><)

ScottM16-Jul-09 8:46

ScottM1

6-Jul-09 8:46

Try this[^]

Muammar©6-Jul-09 10:31

Muammar©

6-Jul-09 10:31

Thanks!

All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)

Top N Per StockCode

Elizma4-Jul-09 3:27

4-Jul-09 3:27

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

Niladri_Biswas4-Jul-09 3:43

Niladri_Biswas

4-Jul-09 3:43

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
Smile | :)

Niladri Biswas

Elizma5-Jul-09 6:45

5-Jul-09 6:45

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

Isaac Gordon5-Jul-09 22:01

Isaac Gordon

5-Jul-09 22:01

I am Not getting your Question pls clearly define it.
use ranking Function like Dense_Rank() of SQL Server.

Dinesh

Elizma5-Jul-09 22:23

5-Jul-09 22:23

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

Enver Maroshi5-Jul-09 23:22

Enver Maroshi

5-Jul-09 23:22

Cross Apply is solution to your problem, with combination of XML, you can get a very nice output.

Elizma6-Jul-09 1:42

6-Jul-09 1:42

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!

Isaac Gordon5-Jul-09 23:59

Isaac Gordon

5-Jul-09 23:59

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

Elizma6-Jul-09 1:38