Exists

Hi,
My query works fine without cast(DWTime as date)='2013-10-02'
 but I need add cast(DWTime as date)='2013-10-02'
Thanks

select * from T1
where not exists(select 1 from T2 where T2.Name=T1.Name and cast(Time as date)='2013-10-02')
And Type=2
and cast(Time as date)='2013-10-02'
SanPrgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Lose the 'as date' when using it in a query.

select * from T1
where not exists(select 1 from T2 where T2.Name=T1.Name and cast([Time])='2013-10-02')

Open in new window


Also, just in case Time is the actual column name, it's not considered a best practice to name columns the same name as functions, data types, etc. as this will cause confusion and possibly the query to fail.

The 'as date' is used when you're declaring and assigning variables in one step.

Declare @big_stuff bit = 1

Open in new window

0
PortletPaulfreelancerCommented:
?
DWTime exists in which table t1 or t2? (we don't know this)
DWTime isn't referenced in you query either. Is it the last line you are referring to?

& is [Time] a time data type?

plus, there could be overall more efficient way of doing this.

Perhaps you could you provide some sample data (just a small sample) from t1 and t2?
0
SanPrgAuthor Commented:
jimhorn, I tried it but no result

 PortletPaul,
DWTime is DateTime type.
sorry it was typo, the correct query is

select * from T1
where not exists(select 1 from T2 where T2.Name=T1.Name and cast(DWTime as date)='2013-10-02')
And Type=2
and cast(DWTime as date)='2013-10-02'
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulfreelancerCommented:
making quite a few assumptions, this might work:
SELECT
        T1.*
FROM T1
LEFT JOIN T2
        ON t1.name = t2.name
        AND (t2.[DWTime] >= '2013-10-02' AND t2.[DWTime] < '2013-10-03') -- sargable equivalent of cast([field] as date)

WHERE t2.name IS NULL --<< the equivalent of your 'not exists'
AND (t1.[DWTime] >= '2013-10-02' AND t1.[DWTime] < '2013-10-03') -- sargable equivalent of cast([field] as date)
;

Open in new window

by using date ranges as shown here casting the data is avoided. It's generally better for performance to avoid changing the data to suit a filter; instead change the method of filtering to suit the data. (the number of filters in way smaller than the number of rows, so the query is doing less work, PLUS the query can utilize indexes)

see: http://en.wikipedia.org/wiki/Sargable

Rules of thumb:
Avoid functions using table values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.
0
PortletPaulfreelancerCommented:
Thanks your reply confirms some of my assumptions.

Note '2013-10-02' and '2013-10-03' might be parameter driven, like this:
declare @from as datetime
set @from = '2013-10-02'

SELECT
        T1.*
FROM T1
LEFT JOIN T2
        ON t1.name = t2.name
        AND (t2.[DWTime] >= @from AND t2.[DWTime] < dateadd(day,1,@start))

WHERE t2.name IS NULL --<< the equivalent of your 'not exists'
AND (t1.[DWTime] >= @from AND t1.[DWTime] < dateadd(day,1,@start)) 
;

Open in new window

0
PortletPaulfreelancerCommented:
oops, you need to add back this in the where clause:

and t1.type = 2
0
SanPrgAuthor Commented:
Thanks for reply.
Forgot @start ;),
The date and Type filter work fine.
But I need all name in T1 not exists in T2.
0
PortletPaulfreelancerCommented:
>>But I need all name in T1 not exists in T2.

a 'left outer join' between t1 and t2, PLUS:

where t2.name IS NULL

is the same - functionally - as your 'NOT EXISTS' correlated subquery

you can continue to use the NOT EXIST() construct if you prefer it, but the results should be the same.

Otherwise: I'm not understanding your comment

you may need to provide some sample data and an expected result from that data to illustrate what should be achieved.
0
PortletPaulfreelancerCommented:
I have to leave for a while, so here are 3 options all based on the assumption that maybe the date filtering is the problem, basically they are the same just removing date filters in various places. Sorry in advance if this isn't helpful
-- option 1
declare @from as datetime
set @from = '2013-10-02'

SELECT
        T1.*
FROM T1
LEFT JOIN T2
        ON t1.name = t2.name
        -- AND (t2.[DWTime] >= @from AND t2.[DWTime] < dateadd(day,1,@start))

WHERE t2.name IS NULL
AND t1.type = 2
AND (t1.[DWTime] >= @from AND t1.[DWTime] < dateadd(day,1,@start)) 
;

-- option 2
declare @from as datetime
set @from = '2013-10-02'

SELECT
        T1.*
FROM T1
LEFT JOIN T2
        ON t1.name = t2.name
        AND (t2.[DWTime] >= @from AND t2.[DWTime] < dateadd(day,1,@start))

WHERE t2.name IS NULL
AND t1.type = 2
-- AND (t1.[DWTime] >= @from AND t1.[DWTime] < dateadd(day,1,@start)) 
;

-- option 3
declare @from as datetime
set @from = '2013-10-02'

SELECT
        T1.*
FROM T1
LEFT JOIN T2
        ON t1.name = t2.name
        -- AND (t2.[DWTime] >= @from AND t2.[DWTime] < dateadd(day,1,@start))

WHERE t2.name IS NULL
AND t1.type = 2
--AND (t1.[DWTime] >= @from AND t1.[DWTime] < dateadd(day,1,@start)) 
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SanPrgAuthor Commented:
Excellent, it works.
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.