Need some help please! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need some help please!

Author

Topic

mxwebb00
Starting Member

17 Posts

Posted - 2015-02-11 : 11:59:43

Working 3 sample Query for VFW.

Can someone help please?

1. The MEMBERS table has the phone number broken into three fields:
CountryCode - e,g., '1' for the United States
AreaCode - e.g., three digits for the United States
Phone - e.g., 7 digits, with or without a dash between the first three digits (Exchange) and last four digits (Line)

Any or all of the fields may be missing (null) or blank or contain only spaces.

Write a T-SQL statement to concatenate the three fields into a complete phone number
with the format: CountryCode(AreaCode)Exchange-Line, e.g., 1(816)123-4567
If no Phone is present, return a blank string.
If no area code is present, return only the Phone number. Do not return an empty pair of parentheses or the CountryCode.

2. The PERSON_CAMPAIGN table contains a row for each war/conflict the member served in. A member may have served in multiple conflicts
For this purpose, each row contains:
PersonID - unique member identifier
Campaign - name of war/conflict

Write a T-SQL statement to return one row per member with all campaigns concatenated into a single field and separated by commas
E.g., PersonID Campaigns
-------- --------------------------------------------------------------------------------
12345678 Global War on Terror, Iraq, Afghanistan

3. The MEMBER_STATISTICS table contains one row per post.
For this purpose, each row contains the post's:
Division - a way of grouping posts by their member size
Department - the state in which the post is located
PostNumber - unique post identifier
Reinstated - count of members whose annual subscription had lapsed for at least two years but who have now subscribed for the current year

Write a T-SQL statement to determine the top ten posts in each division based on the number of reinstated members, with a minimum of 50 reinstated members.
Rank them by highest to lowest reinstated count.
Return their Division, Rank, Department, PostNumber, Reinstated

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-11 : 12:56:14

What have you got so far?

mxwebb00
Starting Member

17 Posts

Posted - 2015-02-11 : 13:15:13

Really just getting started so any help would be nice.
#1
Select
IF Phone is null OR Phone = ‘’ Then Phone = Null
Else
IF Mid(Phone,4,1) = ‘-‘ Then Phone
Else
Mid(Phone,1,3) & ‘-‘ & Mid(Phone,4,4))
END AS PHONE

FROM MEMBERS

This check if Phone is null or blank return Null
Or if the phone is missing - Then add it

Now need to validate the Country code and areacode

Maybe put it all in CTE once I do all the Validations?
Then Select and Concatenate?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-11 : 14:26:11

This looks very much like vb code, but as you asked is mssql forum, I assume you want the "mssql way".
The "if statement" is called "case" and the "mid statement" is called "substring".
Now convering your query to the right functions and syntax, it could look something like:

select case when Phone is null or Phone=''
            then Phone
            else case when substring(Phone,4,1)='-'
                      then Phone
                      else substring(Phone,1,3)+'-'+substring(Phone,4,4)
                 end
       end as phone
  from yourtable

Was I to solve it, I would use several other functions, but your idéa is basically right (except function name and syntax which you offcause need to read up on).
I'd suggest you get your current code to work (and see result without syntax errors), before you build more into your query.

mxwebb00
Starting Member

17 Posts

Posted - 2015-02-11 : 14:35:00

Hey thanks I agree I would have created one function had it return
a good phone number. Gets ugly doing the validation and concatenating.

Was not sure started with a case then thought a If might work
so thanks for the help.

Can you help with other two?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-11 : 14:53:08

For the second assignment, you should look into "stuff function" and "for xml path" - here's a link that shows it quite nicely: [url]http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path[/url]

The third assignment you should look into "group by" and aggregation function "count" and/or "sum". Also the "top" and "order by" clauses will be used.

mxwebb00
Starting Member

17 Posts

Posted - 2015-02-11 : 19:59:26

Ok Here is my what I got for #1 Really need help on others

ANSWER******************
Notes: created a funtion to format the phone
Then used the this function in a select to concatenate Phone

CREATE FUNCTION dbo.FORMATPHONE (@CountryCode int, @AreCode int, @Phone VARCHAR(14))
RETURNS VARCHAR(14)
AS BEGIN
DECLARE @ReturnPhone VARCHAR(14)
DECLARE @NewPhone VARCHAR(14)

-- Note case sets newphone to null if phone null or '' also to see if phone has '-' in it if not inserts into newphone

case
when @Phone is null or @Phone = ''
Then SET @NewPhone = Null
when @Phone = substring(@Phone,4,1)='-'
Then SET @NewPhone = @Phone
else
SET @NewPhone = substring(@Phone,1,3)+'-'+ substring(@Phone,4,4)

End

case
when @NewPhone is null then SET @ReturnPhone = @NewPhone
elese case
when @AreCode is null or @AreCode = '' then SET @ReturnPhone = @NewPhone
else
SET @ReturnPhone = @CountryCode + '(' + @AreCode + ')' + @NewPhone
END
END

RETURN @ReturnPhone
END

select dbo.FORMATPHONE(CountryCode,AreCode,Phone)
from MEMBERS

mxwebb00
Starting Member

17 Posts

Posted - 2015-02-11 : 20:13:32

#2 Need some help

The PERSON_CAMPAIGN table contains a row for each war/conflict the member served in. A member may have served in multiple conflicts
For this purpose, each row contains:
PersonID - unique member identifier
Campaign - name of war/conflict

Write a T-SQL statement to return one row per member with all campaigns concatenated into a single field and separated by commas
E.g., PersonID Campaigns
-------- --------------------------------------------------------------------------------
12345678 Global War on Terror, Iraq, Afghanistan

Ok
So it would look like this
PersonID Campaign
12345678 Global War on Terror
12345678 Iraq
12345678 Afghanistan

So I would
Select
PersonID,

******
HELP HERE NOT SURE WHAT HOW TO concatenated Campaign into on field?

From PERSON_CAMPAIGN
Group by PersonID

mxwebb00
Starting Member

17 Posts

Posted - 2015-02-11 : 20:40:44

Here is what I got for #2

2. The PERSON_CAMPAIGN table contains a row for each war/conflict the member served in. A member may have served in multiple conflicts
For this purpose, each row contains:
PersonID - unique member identifier
Campaign - name of war/conflict

Write a T-SQL statement to return one row per member with all campaigns concatenated into a single field and separated by commas
E.g., PersonID Campaigns
-------- --------------------------------------------------------------------------------
12345678 Global War on Terror, Iraq, Afghanistan

ANSWER******************
SELECT PersonID,
STUFF(( SELECT ',' + Campaign AS [text()]
– Add a comma (,) before each value
FROM PERSON_CAMPAIGN
WHERE (PersonID = Results.ID)
FOR XML PATH('') – Select it as XML
), 1, 1, '' )
– This is done to remove the first character (,)
– from the result
AS Campaigns
FROM PERSON_CAMPAIGN Results

mxwebb00
Starting Member

17 Posts

Posted - 2015-02-11 : 20:42:20

Ok suggestion on #3 please !!!!!

mxwebb00
Starting Member

17 Posts

Posted - 2015-02-12 : 10:25:49