Solved

T-SQL Syntax Question

Posted on 2011-03-11
8
329 Views
Last Modified: 2012-05-11
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
Comment
Question by:sarahellis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 3

Expert Comment

by:brd24gor
ID: 35110400
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
 

Author Comment

by:sarahellis
ID: 35110547
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
 

Author Comment

by:sarahellis
ID: 35110611
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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 41

Expert Comment

by:Sharath
ID: 35112234
Can you post the result of your query and the expected result?
0
 

Author Comment

by:sarahellis
ID: 35112654
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
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 35119223
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
 

Author Comment

by:sarahellis
ID: 35121384
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
 

Author Closing Comment

by:sarahellis
ID: 35319258
So sorry for the delay in posting the points.  I completely forgot.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question