Beginner Query Help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Beginner Query Help

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2014-11-07 : 14:56:59

Hi

I'm struggling with this query and looking for help. I have a list of companies in table named 'PCAs' and another table named 'PCACalls_Assignments'. I want the query results to drop off the name of the companies (PCA) for whatever ReviewDate I pass to it. For example, if my ReviewDate parameter is '11/14/2014', then the results would contain every company in the PCAs table except for PCAID 1 and PCAID 2 because they already have records in the PCACalls_Assignments table. This query doesn't work because if I pass '11/14/2014' to it PCAID 1 will still show up in the results because there is another record in the Assignments table with a ReviewDate = '10/22/2014'

SELECT
PCAs.PCAID
,PCA
FROM
PCACalls_Assignments RIGHT OUTER JOIN
PCAs ON PCACalls_Assignments.PCAID = PCAs.PCAID
WHERE
(CONVERT(VARCHAR(10), PCACalls_Assignments.ReviewDate, 110) <> @ReviewDate)
ORDER BY
PCA

Table: PCAs
PCAID PCA
1 Company1
2 Company2
3 Company3
4 Company4
5 Company5
6 Company6
7 Company7
8 Company8
9 Company9
10 Company10

Table: PCACalls_Assignments
PCAID ReviewDate
1 11/14/2014 0:00
2 11/14/2014 0:00
3 10/22/2015 0:00
3 11/1/2014 0:00
4 10/22/2015 0:00
1 10/22/2014 0:00
7 10/22/2014 0:00

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-07 : 15:07:21

What is the format of @ReviewDate? e.g. If I just do this:


SELECT (CONVERT(VARCHAR(10), GETDATE(), 110)) AS Varchar_Date

I get:

quote:

Varchar_Date
11-07-2014

Note the hyphens in the date. Does that match the format of your variable @ReviewDate?

evanburen
Posting Yak Master

167 Posts

Posted - 2014-11-07 : 15:36:33

Hi

My date format is mm/dd/yyyy

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-11-07 : 17:19:11

Don't use functions on any column if you can avoid it, and never convert a date/datetime column to char to do comparisons.


WHERE
    PCACalls_Assignments.ReviewDate >= @ReviewDate AND
    PCACalls_Assignments.ReviewDate < DATEADD(DAY, 1, @ReviewDate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources