T-SQL Syntax Question

My knowledge of SQL is from Fox and Access.  I'm creating a view in T-SQl on the SQL Server and having trouble transcribing what I would have done in Fox SQL to T-SQL.

I’m picking up the address for these Leads from a table separate from the lead table where the “C”-Current address is on one line and “P” – Parent Address is on another.  AddressNumber=1 and =2 respectively.  So I need these addresses both on one line in the view.  I would have used iif() and grouped by the person but apparently that's not how it's done in T-SQL.  I was trying to use a Case statement as a replacement for iif, but can't figure out how to group it ... anyway... what's the best way to do this?  Thanks!

SELECT       IX.Co_activedate, IX.Co_name, U.Co_name as UcodeID,
      S.FirstName + ' ' + S.LastName as AD,IX.Co_invitationtype as InvType,CX.Co_chaptername as Chapter,
      A.customersizecode-199999 as tier, L.FirstName + L.LastName as Name, L.emailaddress1, IX.Co_prospecttype, SX.Co_seasoncode,
      CASE WHEN AddressNumber=1 THEN LA.Line1 END as Addr1C,
      CASE WHEN AddressNumber=1 THEN LA.Line2 END as Addr2C,
      CASE WHEN AddressNumber=1 THEN LA.City END as CityC,
      CASE WHEN AddressNumber=2 THEN LA.Line1 END as Addr1P,
      CASE WHEN AddressNumber=2 THEN LA.Line2 END as Addr2P,
      CASE WHEN AddressNumber=2 THEN LA.City END as CityP
FROM GKIHSINT_MSCRM.dbo.Co_InvitationExtensionBase IX WITH (NOLOCK)
      INNER JOIN GKIHSINT_MSCRM.dbo.LeadExtensionBase LX WITH (NOLOCK) ON LX.Co_primaryinvitationid = IX.Co_invitationid
      INNER JOIN GKIHSINT_MSCRM.dbo.LeadBase L WITH (NOLOCK) on LX.LeadId = L.LeadId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_ChapterExtensionBase CX WITH (NOLOCK) ON LX.Co_primarychapterid = CX.Co_chapterid
      INNER JOIN GKIHSINT_MSCRM.dbo.AccountBase A WITH (NOLOCK) ON CX.Co_universityaccountid = A.AccountId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_CountryExtensionBase COX WITH (NOLOCK) ON LX.Co_countryid = COX.Co_countryid
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_membershipdriveExtensionBase MX  WITH (NOLOCK) ON IX.Co_membershipdriveId = MX.Co_MembershipDriveId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_seasonExtensionBase SX WITH (NOLOCK) ON MX.Co_SeasonId = SX.Co_seasonId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_ucode U WITH (NOLOCK) ON CX.Co_ucodeid = U.Co_ucodeId
      LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.LeadAddressBase LA with (NOLOCK) ON LX.LeadId = LA.ParentId
      LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.SystemUserBase S WITH (NOLOCK) ON CX.Co_associatedirectorid = S.SystemUserId
WHERE COX.Co_name in ('USA', 'Bahamas') and coalesce(LX.Co_InvitationCount,0)<3 and      
      datediff(d, getdate(),IX.Co_activedate)  > -72  and IX.Co_invitationaccepted = 0
      AND L.statecode = 0
GROUP BY IX.Co_activedate, IX.Co_name, U.Co_name,
      S.FirstName + ' ' + S.LastName,IX.Co_invitationtype,CX.Co_chaptername,
      A.customersizecode-199999, L.FirstName + L.LastName,L.emailaddress1, IX.Co_prospecttype, SX.Co_seasoncode
sarahellisAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
try

SELECT IX.Co_activedate, IX.Co_name, U.Co_name as UcodeID,
      S.FirstName + ' ' + S.LastName as AD,IX.Co_invitationtype as InvType,CX.Co_chaptername as Chapter,
      A.customersizecode-199999 as tier, L.FirstName + L.LastName as Name, L.emailaddress1, IX.Co_prospecttype, SX.Co_seasoncode,
      LA1.Line1 Addr1C,
      LA1.Line2 Addr2C,
      LA1.City CityC,
      LA2.Line1 Addr1P,
      LA2.Line1 Addr2P,
      LA2.Line1 CityP
FROM GKIHSINT_MSCRM.dbo.Co_InvitationExtensionBase IX WITH (NOLOCK)
      INNER JOIN GKIHSINT_MSCRM.dbo.LeadExtensionBase LX WITH (NOLOCK) ON LX.Co_primaryinvitationid = IX.Co_invitationid
      INNER JOIN GKIHSINT_MSCRM.dbo.LeadBase L WITH (NOLOCK) on LX.LeadId = L.LeadId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_ChapterExtensionBase CX WITH (NOLOCK) ON LX.Co_primarychapterid = CX.Co_chapterid
      INNER JOIN GKIHSINT_MSCRM.dbo.AccountBase A WITH (NOLOCK) ON CX.Co_universityaccountid = A.AccountId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_CountryExtensionBase COX WITH (NOLOCK) ON LX.Co_countryid = COX.Co_countryid
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_membershipdriveExtensionBase MX  WITH (NOLOCK) ON IX.Co_membershipdriveId = MX.Co_MembershipDriveId 
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_seasonExtensionBase SX WITH (NOLOCK) ON MX.Co_SeasonId = SX.Co_seasonId
      INNER JOIN GKIHSINT_MSCRM.dbo.Co_ucode U WITH (NOLOCK) ON CX.Co_ucodeid = U.Co_ucodeId
      LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.LeadAddressBase LA1 with (NOLOCK) ON LX.LeadId = LA1.ParentId
      LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.LeadAddressBase LA2 with (NOLOCK) ON LA2.ParentId = LA1.ParentID
      LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.SystemUserBase S WITH (NOLOCK) ON CX.Co_associatedirectorid = S.SystemUserId
WHERE COX.Co_name in ('USA', 'Bahamas') and coalesce(LX.Co_InvitationCount,0)<3 and      
      datediff(d, getdate(),IX.Co_activedate)  > -72  and IX.Co_invitationaccepted = 0
      AND L.statecode = 0
GROUP BY IX.Co_activedate, IX.Co_name, U.Co_name,
      S.FirstName + ' ' + S.LastName,IX.Co_invitationtype,CX.Co_chaptername,
      A.customersizecode-199999, L.FirstName + L.LastName,L.emailaddress1, IX.Co_prospecttype, SX.Co_seasoncode

Open in new window

0
 
brd24gorCommented:
Microsoft has a great page on how to use CASE in T-SQL. There is a section on how you'd replace an IIF statement from ACCESS. It looks like you're very close with syntax. What kind of errors are you getting?

http://msdn.microsoft.com/en-us/library/ms181765.aspx
0
 
sarahellisAuthor Commented:
Yes, that's actually where I got the CASE syntax.  I need to be able to group it on all the other fields (I've since added LeadID to the select clause, and that's actually the unique identifier).  It doesn't like the CASE statement since has to be either an aggregate function or in the group by clause.  Which should it be?  I've tried clumsily to do both, to no avail.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sarahellisAuthor Commented:
In the address table, each lead has only one addressnumber = 1 and addressnumber = 2.    So there's never two lines addressnumber=2 for any singe lead.  So I would have probaly used max() to group.  Could coalesce() work to find the first not null?  If so, where exactly would I put that?
0
 
SharathData EngineerCommented:
Can you post the result of your query and the expected result?
0
 
sarahellisAuthor Commented:
Well the result of the query above was giving me two rows.   One for each address.  I really should have posted a more simplified version of what I'm asking.  Maybe the complexity has muddied the water.

Basically what I’m trying to do is get the following information into one record for each LeadID:

From the Lead Table:   Lead.LeadID              

Then from the Address table joined on LeadID:
Where addressnumber = 1  select  Address as CAddress
Where addressnumber = 2  select  Address as PAddress

There never more than 2 address records (addressnumber = 1 and addressnumber = 2) for each LeadID .

In Fox I would have...

select leadid, max(iif(addressnumber=1,Address,’’))as CAddress, max(iif(addressnumber=2,Address,''))as PAddress
from lead left join address on lead.leadid=address.leadid
group by LeadID

How would this be best accomplished in T-SQL?
0
 
sarahellisAuthor Commented:
Thanks!  Great idea.  Coincidentally, someone else I know came up with the same idea of joining on the table twice with different aliases.   Although I still have to identify the address line using the AddressNumber field, so I'll probably try something like:

LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.LeadAddressBase LA1 with (NOLOCK) ON LX.LeadId = LA1.ParentId and addressnumber=1
LEFT OUTER JOIN GKIHSINT_MSCRM.dbo.LeadAddressBase LA2 with (NOLOCK) ON LX.LeadId = LA2.ParentId and addressnumber=2

Hopefully that will work.  I'll let you know on Tuesday when I'm back on that job.
0
 
sarahellisAuthor Commented:
So sorry for the delay in posting the points.  I completely forgot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.