Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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
0
sarahellis
Asked:
sarahellis
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Ephraim WangoyaCommented:
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
 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now