Database

5-Sep-07 0:56

Using a UDF within a stored procedure can have negative performance effects as it must run for every record it pulls form the database.

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Filter the Null Column

RichardBlare4-Sep-07 22:59

4-Sep-07 22:59

Hi , here's my query result.
I want to filter out the column with NULL values. How can I do it ?

Dept Name Newyork LA Washington City1 SanFrn City2

Department1 0 0 0 NULL 1 NULL
Department2 4 0 0 NULL 2 NULL
Department3 0 0 0 NULL 0 NULL

Thanks in advance,

Vipin.d4-Sep-07 23:07

Vipin.d

4-Sep-07 23:07

SELECT DeptName,ISNULL(NEWYORK,0),ISNULL(LA,0) from MYTABLE WHERE....

All I ever wanted is what others have....
CrazySanker

RichardBlare4-Sep-07 23:34

4-Sep-07 23:34

Dear Vipin dev,
Actually, I just want to remove the columns with NULL value. If I use ISNULL(NEWYORK,0), the column still in the result set and column name is 0.
Is there anyway I can do it ?

Thanks in advance,

Vasudevan Deepak Kumar4-Sep-07 23:40

Vasudevan Deepak Kumar

4-Sep-07 23:40

There is a light refinment in display that is outlined here. Hope that would help you to some extent:

http://www.sql-server-performance.com/faq/null_values_resultset_p1.aspx[^]

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

kumarrumak5-Sep-07 0:48

kumarrumak

5-Sep-07 0:48

hi check out this

select * from tablename where col is null or col='NULL'

Frank Kerrigan5-Sep-07 1:00

5-Sep-07 1:00

I think what you want is COALESCE

select COALESCE(fieldname, 0)

COALESCE will select the first non null from a list so you can even have

select COALESCE(field1,field2,field3,field4, 0)

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

DST Implementation

ramdil4-Sep-07 22:46

ramdil

4-Sep-07 22:46

Hi
How can i check Day Light saving time in sql.
I know there is a inbuilt function called ISDST in C# , but i want that check to be done in Sql procedure.Thanks in Advance

Regards
DilipRam

Re: DST Implementation

Vasudevan Deepak Kumar4-Sep-07 23:44

Vasudevan Deepak Kumar

4-Sep-07 23:44

[^]

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Sql server Date Diff

Praveen0294-Sep-07 21:35

Praveen029

4-Sep-07 21:35

Hi, all

select convert(varchar,(datediff(dd,'1979-09-04 12:34:50.603',getdate())/365))

here getdate()='2007-09-05 12:34:50.603'

I need find the age of employee but this result is showing 28 instead of 27
because till one day is required to complete 27...

Praveen Kumar

Re: Sql server Date Diff

Frank Kerrigan5-Sep-07 1:01

5-Sep-07 1:01

How are you getting the age, Are you using DATEDIFF ?

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Re: Sql server Date Diff

Michael Potter5-Sep-07 5:48

Michael Potter

5-Sep-07 5:48

Try something like this for a more accurate result:

SELECT
    YEAR(GETDATE()) - YEAR('1979-09-04 12:34:50.603') - 
    CASE
        WHEN DATEPART(dayofyear,GETDATE()) < DATEPART(dayofyear,'1979-09-04 12:34:50.603') THEN 1
        ELSE 0
    END AS Age

Re: Sql server Date Diff

Frank Kerrigan5-Sep-07 22:56

Re: Distinct Select Query

5-Sep-07 22:56

Why do you use

<br />
<br />
DATEDIFF(YY, @tartdate, getdate())<br />
<br />
<br />
<br />

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Distinct Select Query

Shajeel4-Sep-07 21:23

Shajeel

4-Sep-07 21:23

Hi I have table with three columns
col1, col2, col3
i need distinct values of col1 with any values in col2 and col3, eg if data is
1, A, A
1, B, B
2, C, C
i need my query to bring
1, A, A or 1, B, B (only one not both)
2, C, C
i need query for this. i cannot use select dintinct col1, col2, col3 from table due to 2nd row. i am using sql server.

Regards
Shajeel

John-ph5-Sep-07 0:10

John-ph

5-Sep-07 0:10

Shajeel wrote:
I have table with three columns
col1, col2, col3
i need distinct values of col1 with any values in col2 and col3, eg if data is
1, A, A
1, B, B
2, C, C
i need my query to bring
1, A, A or 1, B, B (only one not both)
2, C, C

Don't know which version of SQL Server you are using. If 2000 then below written sql will solve the problem. If you are using 2005 then you can use ROW_NUMBER() instead of IDENTITY() and there is no need for #TempTable. If you are using 2000 and table has a Identity column then also there is no need for #TempTable.

Identity Column or the higher version (2005) will help you to do in single sql stmt...

select <br />
IDENTITY(INT, 1, 1)AS KeyCol, Col1, col2, col3 into #TempTable from tbldistinct<br />
<br />
select col1,col2,col3 from #TempTable B<br />
where (Select Count(*) from #TempTable A where A.col1 = B.col1 and A.KeyCol <= B.KeyCol) =1<br />
<br />
Drop Table #TempTable

Regards
J O H N

"Even eagles need a push." David McNally

Re: Distinct Select Query

Shajeel5-Sep-07 1:19

Shajeel

5-Sep-07 1:19

Thanks

Regards
Shajeel

Re: Distinct Select Query

Frank Kerrigan5-Sep-07 1:03

5-Sep-07 1:03

Use a groupby would be the easiest why without DISTINCT

<br />
select col1, col3, col3<br />
from table<br />
ORDER BY col1, col3, col3<br />

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Sql Ansi

Click^ok4-Sep-07 16:50

Click^ok

4-Sep-07 16:50

Does someone knows where I find the SQL Ansi specs?

For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life.(John 3:16)

Re: Sql Ansi

Sathesh Sakthivel4-Sep-07 20:13

Sathesh Sakthivel

4-Sep-07 20:13

Sql Ansi

SSK.

How to filter column ?

RichardBlare4-Sep-07 16:34

Re: How to filter column ?

4-Sep-07 16:34

Hi,
Here is my query.
Select * from Table1

Result

Department NewYork Chicago Washington LA

DepartmentA 1 5 4 0
DepartMentB 2 0 0 3
DepartMentC 0 1 1 1

What I need to do is to get the Top 3 City. ( here Chicago,WashingTon,LA)
Can i filter out the columne with the query ? When I use TOP, it can show top 3 rows.
But, that's not what I want.

Can anybody help me, please?

John-ph5-Sep-07 0:42

John-ph

5-Sep-07 0:42

RichardBlare wrote:
Select * from Table1

I suppose this is you query...and My doubt is

RichardBlare wrote:
Department NewYork Chicago Washington LA

Is this your table columns?

RichardBlare wrote:
DepartmentA 1 5 4 0
DepartMentB 2 0 0 3
DepartMentC 0 1 1 1

Is this your output?

RichardBlare wrote:
Chicago,WashingTon,LA

Is this you want?

RichardBlare wrote:
Can anybody help me, please?

If I'm not wrong please tell...Do you need a output that gives you the list of Column names that has maximum values?
If I'm wrong Please explain more clearly what you want...

Regards
J O H N

"Even eagles need a push." David McNally

Re: How to filter column ?

RichardBlare5-Sep-07 0:52

Re: How to filter column ?

5-Sep-07 0:52

Dear John,
I'm sorry that my question confuse you. Actually, I just need the result with the Column names that has maximum values.

Let me try to be clear.
Here is the result of my query.

Department City1 City2 City3 City4 City5 City6

Operations 4 2 6 NULL 1 NULL
Risk Management 4 5 1 NULL 2 NULL
Technology 2 3 5 NULL 1 NULL

I want to remove the column with NULL values.
That's exactly what I want.

Thanks ,
RB

Frank Kerrigan5-Sep-07 1:10