help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with query

Author

Topic

smithani
Starting Member

42 Posts

Posted - 2007-09-13 : 10:23:40

Hi all ,
The rows of my table are as follows:

round status sdate
1 Y 2007-1-12
2 y null
3 y null
4 n null
5 y null

I would like to get min(sdate), but if sdate is null in any collumn I want it to be null

select
case
when isnull(a.startdate, '1/1/1900')<>'1/1/1900'
then min(a.startdate)
else null
end as minstart,
from Serv
where status = 'Y'

If I use this query it is giving me two rows

like so

minstart
NULL
2007-01-12 00:00:00.000 ,

I wnat it to return just one row, that is null, how do I do this.

Any input will be greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 10:28:37

[code]DECLARE @Sample TABLE ([Round] INT, Status CHAR(1), Date DATETIME)

INSERT @Sample
SELECT 1, 'Y', '2007-1-12' union all
SELECT 2, 'y', null union all
SELECT 3, 'y', null union all
SELECT 4, 'n', null union all
SELECT 6, 'p', '20070914' union all
SELECT 5, 'y', null

SELECT Status,
CASE MIN(CASE WHEN Date IS NULL THEN 0 ELSE 1 END)
WHEN 0 THEN NULL
ELSE MIN(Date)
END AS theDate
FROM @Sample
GROUP BY Status[/code]

E 12°55'05.25"
N 56°04'39.16"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-13 : 10:31:31

select
case when AA.mindt = '1/1/1900' then null else AA.mindt as mindtmod
from
(
select
min(case sdate is null then '1/1/1900' else sdate end) mindt
from serv
) AA

PS: If anyone is interested in a SQL Server Job in CT with excellent pay please send your resume to ValterBorges@msn.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 10:35:11

That will work to, until the date of "Jan 1, 1900" actually is present in the table.

E 12°55'05.25"
N 56°04'39.16"

smithani
Starting Member

42 Posts

Posted - 2007-09-13 : 11:01:20

Instead of using Table variables , how can i do this using temp tables,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:03:11

By replacing the table names to the tabel names used in YOUR environment.
I don't have access to your database, so I had to fake a name.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:03:41

[code]SELECT Status,
CASE MIN(CASE WHEN Date IS NULL THEN 0 ELSE 1 END)
WHEN 0 THEN NULL
ELSE MIN(Date)
END AS theDate
FROM {Your table name here}
GROUP BY Status[/code]

E 12°55'05.25"
N 56°04'39.16"

smithani
Starting Member

42 Posts

Posted - 2007-09-13 : 11:21:59

Oops, sorry for that dumb question Peso.Thanks for indulging me.
I was using the date in my group by and was getting two rows again, but when i took it out of the group by , it ran correctly.

Thanks a million .

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:27:34

So basically you want help from us, but you do not want to run and try the solutions we provide.
Instead you alter the solutions and add things, and when your revised query do not work, you ask for help again?

I am happy things worked out for you at last.

E 12°55'05.25"
N 56°04'39.16"

smithani
Starting Member

42 Posts

Posted - 2007-09-13 : 11:48:05

i tried your solution, that is exactly what I have in my query now , i had extra date in my group by , since my query has other tables in it also, that I am connecting to , so i had to group my results, so I have just the solution you provided,except in the group by(I goofed up).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources