>>I didn't really read the original problem, so I don't know if proposed solution is good or not. I just commented that it's better to use T-SQL to create views and not SSMS designer which can not work with complex views.
>
>Yes thanks for helping
>
>I describe issue here in short.
>
>I am using these codes provided by Sir Christian Isberner
>
>
>CREATE VIEW Test
>AS SELECT Date, DatePart(hour,Date) as Hr, reading
>FROM TABLE1 A
>WHERE Date IN
>(SELECT Min(Date) as date
>FROM TABLE1 B
>WHERE DatePart(hour,A.Date) = DatePart(hour,B.Date)
>UNION
>SELECT Min(Date) as date
>FROM TABLE2 C
>WHERE DatePart(hour,A.Date) = DatePart(hour,C.Date))
>
>
>I want to create view, the codes takes long time to display data.
>My question is: is it possible to modify above codes with JOIN clause?
>If yes then I guess the view will not take long time to display data.
>
>Regards
Using IN() with subselect is usually very slow and you should always use EXISTS() instead.
But that may be more tricky, my guess would be the following, but I cannot test it (never used Having in Exists subquery before):
>CREATE VIEW Test
AS SELECT Date, DatePart(hour,Date) as Hr, reading
FROM TABLE1 A
WHERE EXISTS(
(SELECT Min(Date) as date_B
FROM TABLE1 B
WHERE DatePart(hour,A.Date) = DatePart(hour,B.Date)
HAVING B.Date_B = A.Date)
OR EXISTS(
SELECT Min(Date) as date_C
FROM TABLE2 C
WHERE DatePart(hour,A.Date) = DatePart(hour,C.Date)
HAVING C.Date_C = A.Date)
Christian Isberner
Software Consultant