August 26, 2013 at 7:35 am
Hi, I'd really appreciate if someone can help, beforehand, thank you
I have sites with contact list table where every contact has a "cs_seqno" number; every contact has a phone number
Sample:
Table name: contact
Site 50 has a contact list with six contacts where the order to display them start by cs_seqno 1
Contact table
site Name cs_seqno phone
50 Amy Benh 1 916584520
50 John Smith 10 388258125
50 Carl Denver 20 640640120
50 Ed Coleman 30 98640120
50 Ben Harmon 40 621621001
site Name cs_seqno phone
60 John Sunny 1 966638520
60 Aaron Davis 10 460252825
60 Phil Waitman 20 540656122
60 Ron Ellis 30 619288630
60 Don Willet 40 621621001
I need get the first contact that has a phone number starting by 6, so for the upper list i need select and get the cs_seqno 20 & 30
site Name cs_seqno phone
50 Carl Denver 20 640640120
60 Ron Ellis 30 619288630
Thanks for your help.
Cphspain
August 26, 2013 at 7:47 am
I'd try something like this:
WITH CTE_PhoneStartsWith6 AS
(
SELECT site, Name, cs_seqno, phone
WHERE phone LIKE '6%'
)
SELECT site, Name, cs_seqno, phone FROM
(
SELECT site, Name, cs_seqno, phone, RID = ROW_NUMBER() OVER (PARTITION BY site ORDER BY cs_seqno)
FROM CTE_PhoneStartsWith6
)
WHERE RID = 1;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 26, 2013 at 7:50 am
Your question topic suggests you want the first row but in your example it is different.
Could it be as simple as this:
Where phone like '6%'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 26, 2013 at 9:08 am
Thank you SSChampion, how can i adjust your advise in the following sentence where i only can get the MIN one, not really what i am looking for
SELECT contact_link.cs_seqno, system.cs_no, contact.last_name, contact.first_name, contact_phone.phone_number, contact_phone.email_address
FROM contact_link INNER JOIN
site ON contact_link.site_no = site.site_no INNER JOIN
contact ON contact_link.contact_no = contact.contact_no INNER JOIN
contact_phone ON contact_link.contact_no = contact_phone.contact_no INNER JOIN
system ON site.site_no = system.site_no
WHERE (contact_link.cs_seqno =(SELECT MIN (contact_link.cs_seqno)AS Expr1 from contact_link)) and (site.sitestat_id = 'a')
AND (contact_link.end_date = '01-01-2079') AND (contact_phone.phone_number LIKE '6%') AND (contact_phone.email_address IS NULL) AND
(site.phone2 IS NULL)
GROUP BY system.cs_no, contact.last_name, contact.first_name, contact_phone.phone_number, contact_phone.email_address, contact_link.cs_seqno
August 26, 2013 at 9:28 am
Thank you Sean
August 26, 2013 at 1:11 pm
Claudio Pinto (8/26/2013)
Thank you SSChampion, how can i adjust your advise in the following sentence where i only can get the MIN one, not really what i am looking forSELECT contact_link.cs_seqno, system.cs_no, contact.last_name, contact.first_name, contact_phone.phone_number, contact_phone.email_address
FROM contact_link INNER JOIN
site ON contact_link.site_no = site.site_no INNER JOIN
contact ON contact_link.contact_no = contact.contact_no INNER JOIN
contact_phone ON contact_link.contact_no = contact_phone.contact_no INNER JOIN
system ON site.site_no = system.site_no
WHERE (contact_link.cs_seqno =(SELECT MIN (contact_link.cs_seqno)AS Expr1 from contact_link)) and (site.sitestat_id = 'a')
AND (contact_link.end_date = '01-01-2079') AND (contact_phone.phone_number LIKE '6%') AND (contact_phone.email_address IS NULL) AND
(site.phone2 IS NULL)
GROUP BY system.cs_no, contact.last_name, contact.first_name, contact_phone.phone_number, contact_phone.email_address, contact_link.cs_seqno
Can you post the table definitions, some sample input and the desired output?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 26, 2013 at 2:46 pm
Hi Koen,
Thank you for your support
These are table definitions and sample values for contact_link.cs_seqno 20 assuming that contact cs_seqno 20 is the first contact that has a phone number starting by 6
Contact_link tabletype sample value
Cs_seqno smallint 20
Site_noint 128470
Contact_no int 126
End_date smalldatetime01-01-2079
Contact table
Last_name varchar(30) Smith
First_name varchar (20) John
Contact_phone
Phone_numbervarchar(16) 630630630
Email addressvarchar (50) null
Contact_no int 126
System table
Cs_novarchar (20) 12-1111
Site_noint 128470
Site table
Site_noint 128470
Phone2varchar(16) null
Sitestat_id varchar (16) A
August 26, 2013 at 2:51 pm
Claudio Pinto (8/26/2013)
Hi Koen,Thank you for your support
These are table definitions and sample values for contact_link.cs_seqno 20 assuming that contact cs_seqno 20 is the first contact that has a phone number starting by 6
Contact_link tabletype sample value
Cs_seqno smallint 20
Site_noint 128470
Contact_no int 126
End_date smalldatetime01-01-2079
Contact table
Last_name varchar(30) Smith
First_name varchar (20) John
Contact_phone
Phone_numbervarchar(16) 630630630
Email addressvarchar (50) null
Contact_no int 126
System table
Cs_novarchar (20) 12-1111
Site_noint 128470
Site table
Site_noint 128470
Phone2varchar(16) null
Sitestat_id varchar (16) A
If you can turn this into create table and insert statements it would be a LOT easier and you are far more likely to get somebody to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 27, 2013 at 2:29 am
Okay Sean, thanks for your advise, let's see if the following lines are valid
CREATE TABLE [contact_link](
[contact_no] [int] NOT NULL,
[end_date] [smalldatetime] NOT NULL,
[site_no] [int] NULL,
[cs_seqno] [smallint] NULL,
)
CREATE TABLE [contact](
[contact_no] [int] NOT NULL,
[last_name] [varchar](30) NOT NULL,
[first_name] [varchar](20) NULL,
)
CREATE TABLE [contact_phone](
[contact_no] [int] NOT NULL,
[phone_number] [varchar](16) NULL,
[email_address] [varchar](50) NULL,
)
CREATE TABLE [site](
[site_no] [int] NOT NULL,
[sitestat_id] [char](6) NOT NULL,
[phone2] [varchar](16) NULL,
)
CREATE TABLE [system](
[site_no] [int] NOT NULL,
[cs_no] [varchar](20) NULL,
)
INSERT INTO [contact_link]([contact_no], [end_date], [site_no], [cs_seqno])
values ('126','2079-01-01 09:38:12.227','128470', '10')
values ('130','2079-01-01 09:38:12.227','128470', '20')
values ('140','2079-01-01 09:38:12.227','128470', '30')
values ('150','2079-01-01 09:38:12.227','128470', '40')
values ('160','2079-01-01 09:38:12.227','128480', '10')
values ('180','2079-01-01 09:38:12.227','128480', '20')
values ('200','2079-01-01 09:38:12.227','128480', '30')
values ('210','2079-01-01 09:38:12.227','128480', '40')
INSERT INTO [contact]([contact_no], [last_name], [first_name])
values ('126','Willet', 'Albert')
values ('130','Burn', 'Penny')
values ('140','Novak', 'Terence')
values ('150','Hank', 'Phil')
values ('160','Parker', 'Alan')
values ('180','Preston', 'Ian')
values ('200','Rawls', 'Tod')
values ('210','Staton', 'Mark')
INSERT INTO [contact_phone]([contact_no], [phone_number], [email_address])
values ('126','362123443', 'null')
values ('130','856326123', 'null')
values ('140','606325123', 'null')
values ('150','650630443', 'null')
values ('160','462803443', 'null')
values ('180','622123240', 'null')
values ('200','606255123', 'null')
values ('210','950360443', 'null')
INSERT INTO [site]([site_no], [sitestat_id], [phone2])
values ('128470','A', 'null')
values ('128480','A', 'null')
INSERT INTO [system]([site_no], [cs_no])
values ('128470','11-2233’)
values ('128480','11-5551’)
I need a Select / get for every cs_no / contact list the first contact that have a phone number starting by 6, following the sample records inserted it should be
cs_seqnocs_no last_namefirst_namephone_numberemail_address
30 11-2233NOVAKTERENCE606325123 NULL
20 11-5511 PRESTON IAN 622123240 NULL
Thanks,
August 27, 2013 at 2:49 am
Using the code Koen Verbeek provided and applying this to your table definitions, will result in this code:
;WITH CTE_PhoneStartsWith6 AS
(
SELECT contact_link.cs_seqno, system.cs_no, contact.last_name, contact.first_name, phone_number
FROM contact_link
INNER JOIN site
ON contact_link.site_no = site.site_no
INNER JOIN contact
ON contact_link.contact_no = contact.contact_no
INNER JOIN contact_phone
ON contact_link.contact_no = contact_phone.contact_no
INNER JOIN system
ON site.site_no = system.site_no
WHERE phone_number LIKE '6%'
)
SELECT cs_no, last_name, first_name, cs_seqno, phone_number FROM
(
SELECT cs_no, last_name, first_name, cs_seqno, phone_number, RID = ROW_NUMBER() OVER (PARTITION BY cs_no ORDER BY cs_seqno)
FROM CTE_PhoneStartsWith6
) sub_select
WHERE RID = 1;
August 27, 2013 at 3:00 am
HanShi, it works okay 🙂 Just what i'm looking for
Thanks to everybody for your prompt and kindly help
Regards,
Claudio
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply