|
Blue_boy,
Please read the original post in this thread. Thanks for your help but you appear to have misinterpretated my question.
|
|
|
|
|
i have two tables the first has foreign to another like this
table1:
table1Id table1Name table2Id
1 mahm
2 kha 1
table2:
table2Id table2Name
1 cc
2 dd
i need the following result
table1Id table1Name table2Name
1 mahm
2 kha cc
|
|
|
|
|
select table1Id, table1Name, table2Name
from table1
left join table2 on table2.table2Id = table1.table2Id
|
|
|
|
|
Dear Friend,
I am working on database which is having huge data and having complicated stored Procedure, some time i face problem because of this, want to clear all this....
To overtook this all problem i start working on Procedure which is making issue and fine tune some extend, i want to change them just like blood vain in my body so data can be flow just like the blood but this can only happen if i know the imp things....
You got idea what i want, i want to know the exact process for all this.
Me using following characters and syntex in my queries
1) IN and NOT IN
2) <>
3) CASE
4) GROUP BY.
5) Using Date Converting it to nvarchar datatype (for view only not in where clause).
6) UNION and UNION ALL
7) SUB Query
8) INNER JOINS (other joints are use but lesser amount)
Read some where that it is not good to use following character in where clause
1) IN and NOT IN
2) <>
3) CASE
I am using all this which is not possible to not use all this, if you have any other idea please give it to me.
Give me some link some article which help for all this
thanks and regards in advance,
Sasmi
|
|
|
|
|
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.
|
|
|
|
|
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!
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|