May 11, 2009 at 2:17 pm
I am drawing a blank. I am trying to retrieve data with the following:
SELECT ent.firstname + ' ' + ent.lastname AS "FullName",
addr.addressline1,
addr.addressline2,
addr.city,
stcd.statecode,
addr.postalcode
FROM
Entity AS ent
inner join Address addr ON addr.entityid = ent.entityid
inner join StateCodes AS stcd ON stcd.stateid = addr.stateid
WHERE
ent.LastName IS NOT NULL AND LEN(ent.lastname) > 0
ORDER BY ent.LastName
Now, most of the addresses are flagged with isShipping = 1 and isMailing = 1. There is the possibility that there will be one person who has 2 address records, one flagged with isShipping = 1 and isMailing = 0 and the other address record flagged as isShipping = 0 and isMailing = 1.
I need to filter the result so that I get all records that has both flags set to 1 and those records that have the isShipping = 0 and isMailing = 1. Also, if a different person has one address with the flag set to isShipping = 1 and isMailing = 0, then I need that record also.
This sounds confusing. I am thinking I can use the Case statment, but am not sure how to apply it.
May 11, 2009 at 2:23 pm
Please post table definitions, sample data and desired output. I'm not 100% sure I follow what you want. Read this to see the best way to post this to get quick responses.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2009 at 3:00 pm
The actual tables are much more extensive.
CREATE TABLE [dbo].[tmpEntity](
[EntityID] [int] NULL,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tmpAddress](
[ADDRESSID] [int] NOT NULL,
[ENTITYID] [int] NOT NULL,
[ADDRESSLINE1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ADDRESSLINE2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CITY] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATEID] [int] NULL,
[POSTALCODE] [nvarchar](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISMAILING] [numeric](1, 0) NULL,
[ISSHIPPING] [numeric](1, 0) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tmpState](
[STATEID] [int] NOT NULL,
[STATECODE] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Insert into tmpentity (entityid,firstname,lastname) values (1,'Ray','Smith')
Insert into tmpentity (entityid,firstname,lastname) values (1,'Jane','Taylor')
Insert into tmpentity (entityid,firstname,lastname) values (1,'Shirley','Test')
Insert into tmpAddress
(ADDRESSID,ENTITYID,ADDRESSLINE1,ADDRESSLINE2,CITY,STATEID,
POSTALCODE,ISMAILING,ISSHIPPING)
values (1,1,'123 Main St',Null,'Anywhere',1,'60001',1,1)
Insert into tmpAddress
(ADDRESSID,ENTITYID,ADDRESSLINE1,ADDRESSLINE2,CITY,STATEID,
POSTALCODE,ISMAILING,ISSHIPPING)
values (2,2,'456 Back St',Null,'Somewhere Else',1,'60001',0,1)
Insert into tmpAddress
(ADDRESSID,ENTITYID,ADDRESSLINE1,ADDRESSLINE2,CITY,STATEID,
POSTALCODE,ISMAILING,ISSHIPPING)
values (3,2,'987 Front Street',Null,'Somewhere Else',1,'60001',1,0)
Insert into tmpAddress
(ADDRESSID,ENTITYID,ADDRESSLINE1,ADDRESSLINE2,CITY,STATEID,
POSTALCODE,ISMAILING,ISSHIPPING)
values (4,3,'23452 West St',Null,'Homewood',1,'65555',1,1)
Insert into tmpState
(STATEID,STATECODE)
values (1,'IL')
Results:
Ray Smith
123 Main St
Anywhere, IL 60001
Jane Taylor
987 Front Street (because isMailing = 1 and isShipping = 0)
Somewhere Else, IL 60001
Shirley Test
23452 West St
Homewood, IL 65555
May 11, 2009 at 3:01 pm
I agree with Gail. Post as she has asked as your requirements are more complex than just using a CASE statement as your case statement will return all combinations of isMailing and isShipping except for when both are 0.
I'm thinking you will probably want to use a CTE and COUNT() OVER() to get what you need, but would need the information that Gail has requested to be sure.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 11, 2009 at 11:04 pm
Try this...
with wcte as
(Select *,(Select Count(*) from tmpAddress a where a.entityID = tmpAddress.entityID) rno
from tmpAddress )
SELECT ent.firstname + ' ' + ent.lastname AS "FullName",
addr.addressline1,
addr.addressline2,
addr.city,
stcd.statecode,
addr.postalcode,
addr.isMailing,addr.IsShipping
FROM
tmpEntity AS ent
inner join (Select * from tmpAddress where (IsMailing = 1 and IsShipping = 1)
or (IsMailing = 1 and IsShipping = 0)
or AddressID in (Select addressID from wcte where rno = 1 and IsMailing = 0 and IsShipping = 1)
) addr ON addr.entityid = ent.entityid
inner join tmpState AS stcd ON stcd.stateid = addr.stateid
WHERE
ent.LastName IS NOT NULL AND LEN(ent.lastname) > 0
ORDER BY ent.LastName
May 12, 2009 at 6:46 am
Am I completely missing the point.. But It sounds like you are just asking for items where isMailing = 1
SELECT ent.firstname + ' ' + ent.lastname AS "FullName",
addr.addressline1,
addr.addressline2,
addr.city,
stcd.statecode,
addr.postalcode
FROM
Entity AS ent
inner join Address addr ON addr.entityid = ent.entityid
inner join StateCodes AS stcd ON stcd.stateid = addr.stateid
WHERE
ent.LastName IS NOT NULL AND LEN(ent.lastname) > 0 and
addr.IsMailing =1
ORDER BY ent.LastName
May 12, 2009 at 7:21 am
Here is another option which I think is a little easier to read than Atif's, but in my simple tests between the 2 does not perform quite as well and doesn't look like it will scale as well based on 3 row tests.
;WITH cteRows AS
(
SELECT
COUNT(TA.ADDRESSID) OVER (PARTITION BY TE.EntityId) AS no_addresses,
TE.FirstName + ' ' + TE.LastName AS name,
TA.ADDRESSLINE1,
TA.ADDRESSLINE2,
TA.CITY,
TS.STATECODE,
TA.POSTALCODE,
TA.ISMAILING,
TA.ISSHIPPING
FROM
dbo.tmpEntity AS TE JOIN
dbo.tmpAddress AS TA ON TE.EntityID = TA.ENTITYID JOIN
dbo.tmpState AS TS ON TA.STATEID = TS.STATEID
)
SELECT
NAME,
AddressLine1,
AddressLine2,
City,
StateCode,
PostalCode,
isMailing,
isShipping
FROM
cteRows AS CR
WHERE
CASE
WHEN CR.isMailing = 1 THEN 1
WHEN CR.no_addresses = 1 AND CR.isMailing = 0 AND CR.isSHipping = 1 THEN 1
ELSE 0
END = 1
I added this data in order to have a row that met your criteria for an entity having 1 address that is only set as the shipping address:
Insert into tmpentity (entityid,firstname,lastname) values (4,'John','Doe')
Insert into
tmpAddress
(
ADDRESSID,
ENTITYID,
ADDRESSLINE1,
ADDRESSLINE2,
CITY,
STATEID,
POSTALCODE,
ISMAILING,
ISSHIPPING
)
values
(
5,
4,
'125 Main St',
Null,
'Anywhere',
1,
'60001',
0,
1
)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 12, 2009 at 9:48 pm
To Jason:
I need to filter the result so that I get all records that has both flags set to 1 and those records that have the isShipping = 0 and isMailing = 1. Also, if a different person has one address with the flag set to isShipping = 1 and isMailing = 0, then I need that record also.
There is a point of complexity when an entity has a address with
IsMailing = 0 and IsSHipping = 1. The OP needs that record as well (but just in case of single address against the respective entity)
To Jack:
I have tested both the queries, and the results are same and Statistics are nearly equal. Is there any performance issue that I could rectify...?
May 13, 2009 at 1:03 am
Ah.. thanks It seems like I didn't read the question properly.
May 13, 2009 at 6:13 am
If I recall correctly, on my machine, mine included a worktable while yours didn't and mine had more logical reads than yours (Set Statistics IO ON). For small data sets you probably wouldn't notice the difference, but I think you would on larger ones and highly active machines.
I mainly thought mine was easier to read and follow because yours had the CTE and a derived table with a sub-query referencing the CTE in the where clause.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 14, 2009 at 7:59 pm
It sounds like you want to pick the single best address for each entity, using the isMailing and isShipping fields to define "best" (with preference given to isMailing). All you have to do is define "best" with an expression in a ROW_NUMBER() ORDER BY clause.
SELECT ent.firstname + ' ' + ent.lastname AS "FullName",
addr.addressline1,
addr.addressline2,
addr.city,
stcd.statecode,
addr.postalcode
FROM Entity AS ent
INNER JOIN ( SELECT entityid,
addressline1,
addressline2,
city,
postalcode,
ROW_NUMBER() OVER ( PARTITION BY entityid
ORDER BY ISNULL(isMailing, 0) * 2 + ISNULL(isShipping, 0) DESC ) AS rn
FROM Address
) addr ON addr.entityid = ent.entityid
AND rn = 1
INNER JOIN StateCodes AS stcd ON stcd.stateid = addr.stateid
WHERE LEN(ent.lastname) > 0
ORDER BY ent.LastName
May 15, 2009 at 6:01 am
Scott, I like your solution. I have found that using row_number when you need the best of several potential result records works great as long as you can find a way to order the records some how. It also works well for removing duplicate records from a table.
If it was easy, everybody would be doing it!;)
May 15, 2009 at 6:19 am
Thank you very much for your help. This worked great.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply