Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query help 1

Status
Not open for further replies.

NotSQL

Technical User
May 17, 2005
205
GB
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
 
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:
Code:
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.
 
Thanks bborissov, however i've just tested the query and it doesnt seem to work..

It complains about the column NumberOFMeeting
 
Because it should be [NumberOfMeetings] not [NumberOfMeeting] :)


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
bborissov, this still isnt working, even with NumberOfMeetings..... it states its a invalid column.....
 
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
 
I think the problem is because you did not alias the derived table. Try this:
Code:
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)
 
Sorry, you need to use the alias to refer to NumberOfMeeting like so
Code:
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)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top