Query help 1

[NotSQL]

Hi Guys, I have this query below: It works fine however i'd like to use

Where t2.NumberOFMeeting = 'Not visited' in the where clause instead of:

where t2.NumberOFMeeting is null

any help would be appreciated

Declare @Year varchar(4) = 2010, @Quarter varchar(9) = null, @Month varchar(3) = null

Select t1.AccountName, Case when ISNull(t2.NumberOFMeeting,0) = 0
THEN 'Not Vistited' else 'Visited' End As NumberOfMeetings
From
(Select AccountName
From Sales_Matrix
where [YEAR] = @Year
and (@Quarter Is Null Or Quarter_Name = @Quarter)
and (@Month Is NULL or [MONTH] = @Month)
Group By AccountName)t1 left outer join (Select MaginusAccount, Count(MaginusAccount) As NumberOFMeeting
From V_TimeCard
where [YEAR] = @Year
and (@Quarter Is Null Or [Quarter] = @Quarter)
and (@Month Is NULL or [MONTH] = @Month)

Group by MaginusAccount) t2 on t1.AccountName = t2.MaginusAccount
Where t2.NumberOFMeeting is null

 

[bborissov]

You can't.
If there is no matching record in t2 the all fields based on t2 will be NULL.
But of course you could use the whole query as derived table and then put the WHERE clause:

Declare @Year varchar(4) = 2010,
        @Quarter varchar(9) = null,
        @Month varchar(3) = null
    

SELECT * FROM (
Select t1.AccountName,
      Case when ISNull(t2.NumberOFMeeting,0) = 0
                THEN  'Not Vistited'
           else 'Visited' End As NumberOfMeetings
From (Select AccountName
          From Sales_Matrix
               where [YEAR] = @Year
                and (@Quarter Is Null Or Quarter_Name = @Quarter)
                and (@Month Is NULL or [MONTH] = @Month)
        Group By AccountName)t1
left outer join (Select MaginusAccount,
                        Count(MaginusAccount) As NumberOFMeeting
                        From V_TimeCard
                 where [YEAR] = @Year
                  and (@Quarter Is Null Or [Quarter] = @Quarter)
                  and (@Month Is NULL or [MONTH] = @Month)
                 Group by MaginusAccount) t2 on t1.AccountName = t2.MaginusAccount
) Where NumberOFMeeting =   'Not Vistited'

But that is a little too much for me


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[NotSQL]

Thanks bborissov, however i've just tested the query and it doesnt seem to work..

It complains about the column NumberOFMeeting

 

[bborissov]

Because it should be [NumberOfMeetings] not [NumberOfMeeting]


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[NotSQL]

bborissov, this still isnt working, even with NumberOfMeetings..... it states its a invalid column.....

 

[NotSQL]

Anyone got any ideas on how i might get the above working.

I want the bottome where clause to be a parameter so the user can type either visited or not visited


Cheers

 

[TheBugSlayer]

I think the problem is because you did not alias the derived table. Try this:

SELECT [COLOR=red]T.[/color]* FROM 
(
--Code removed for simplicity
) [COLOR=red]T[/color] Where NumberOFMeeting =   [COLOR=red]@ParamVisit[/color]

where @ParamVisit[color] is a parameter that you could pass through a stored procedure.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[TheBugSlayer]

Sorry, you need to use the alias to refer to NumberOfMeeting like so

SELECT T.* FROM (--Code removed for simplicity) T Where [COLOR=red][b]T.[/b][/color]NumberOFMeeting =   @ParamVisit

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)