October 1, 2003 at 3:24 pm
Hi All,
I have this table - Dummy - with these values :
Date MW1 Price1
----------- ------------ ---------------------
20030926 NULL NULL
20030927 40.0000 NULL
20030928 80.0000 NULL
20030929 40.0000 NULL
20030930 40.0000 NULL
20031001 40.0000 NULL
20031002 40.0000 NULL
20031003 40.0000 NULL
20031004 40.0000 NULL
20031005 40.0000 NULL
20031006 80.0000 NULL
20031007 40.0000 NULL
I want to group by Distinct MW, Price value where
date value is not overllaping.
I want to return this -
Min(Date) Max(Date) MW1 Price1
--------- --------- ---- ------
20030926 20030926 NULL NULL
20030927 20030927 40.0000 NULL
20030928 20030928 80.0000 NULL
20030929 20031005 40.0000 NULL
20031006 20031006 80.0000 NULL
20031007 20031007 40.0000 NULL
How do I specify the group by
Thanks
KOY
October 1, 2003 at 4:58 pm
I don't think I'd use GROUP BY unless you need to use an aggregate function on Price1.
SELECT DISTINCT
(SELECT ISNULL(MIN(Date), d.Date)
FROM Dummy
WHERE MW1 = d.MW1 AND Date >
(SELECT MAX(Date)
FROM Dummy
WHERE Date < d.Date AND MW1 <> d.MW1)) MinDate,
(SELECT ISNULL(MAX(Date), d.Date)
FROM Dummy
WHERE MW1 = d.MW1 AND Date <
(SELECT MIN(Date)
FROM Dummy
WHERE Date > d.Date AND MW1 <> d.MW1)) MaxDate,
MW1, Price1
FROM Dummy d
--Jonathan
--Jonathan
October 1, 2003 at 5:07 pm
Wow - Thanks a million - it actually works - I am kind of blown off my feet - I did not think it was going to work but it DID !!!
Thanks,
KOY
quote:
I don't think I'd use GROUP BY unless you need to use an aggregate function on Price1.SELECT DISTINCT
(SELECT ISNULL(MIN(Date), d.Date)
FROM Dummy
WHERE MW1 = d.MW1 AND Date >
(SELECT MAX(Date)
FROM Dummy
WHERE Date < d.Date AND MW1 <> d.MW1)) MinDate,
(SELECT ISNULL(MAX(Date), d.Date)
FROM Dummy
WHERE MW1 = d.MW1 AND Date <
(SELECT MIN(Date)
FROM Dummy
WHERE Date > d.Date AND MW1 <> d.MW1)) MaxDate,
MW1, Price1
FROM Dummy d
--Jonathan
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply