SELECT column puzzle
-
Hello World:
I have a brain busting requirement. I can't alter the original table due to business.
However I'm required to write a select statement for a report.
The requirement I'm having trouble with is best shown in the picture below.
If a matching contact has identical values of asmemberof but different ballot values, i need to not show that value or make it ''.
I'm starting to think that's not possible. If not I'm not sure what to tell my higher ups.
here is the code I used:
USE MS_CRM3
GO
CREATE TABLE #Ballot (
ballotcommitteeid NVARCHAR(38),
ballotcommitteename NVARCHAR(8)
)
INSERT INTO #Ballot (ballotcommitteeid, ballotcommitteename)
SELECT DISTINCT ballotcommitteeid, ballotcommitteename
FROM MS_CRM3.dbo.user_tcc_committeeinactivityreport
WHERE ballotenddate >= CONVERT(DATETIME, '2008-04-01 00:00:00',102)
ORDER BY ballotcommitteename ASC
CREATE TABLE #InactiveContact (
pa_contactid NVARCHAR(38),
pa_contactidname NVARCHAR(100)
)
INSERT INTO #inactivecontact (pa_contactid, pa_contactidname)
SELECT DISTINCT pa_contactid, pa_contactidname
FROM MS_CRM3.dbo.user_tcc_committeeinactivityreport
WHERE ballotenddate >= CONVERT (DATETIME, '2008-04-01 00:00:00',102)
ORDER BY pa_contactidname
CREATE TABLE #AsMemberOf (
asmemberofid INT IDENTITY(1,1),
asmemberof NVARCHAR(100),
asmemberof1 NVARCHAR(6),
asmemberof2 NVARCHAR(6),
asmemberof3 NVARCHAR(6),
asmemberof4 NVARCHAR(6),
asmemberof5 NVARCHAR(6),
asmemberof6 NVARCHAR(6),
ballotcommitteeid NVARCHAR(38)
)
INSERT INTO #AsMemberOf (asmemberof, asmemberof1, asmemberof2, asmemberof3, asmemberof4, asmemberof5, asmemberof6, ballotcommitteeid)
SELECT DISTINCT asmemberof,
SUBSTRING(asmemberof, 1,6) AS asmemberof1,
SUBSTRING(asmemberof, 9,6) AS asmemberof2,
SUBSTRING(asmemberof,17,6) AS asmemberof3,
SUBSTRING(asmemberof, 25,6) AS asmemberof4,
SUBSTRING(asmemberof, 33,6) AS asmemberof5,
SUBSTRING(asmemberof, 41,6) AS asmemberof6,
ballotcommitteeid
FROM MS_CRM3.dbo.user_tcc_committeeinactivityreport
WHERE (ballotenddate >= CONVERT(DATETIME, '2008-04-01 00:00:00',102))
AND asmemberof LIKE 'STG%,_STG%,_STG%'
CREATE TABLE #inactivecontactasmemberof (
pa_contactid NVARCHAR(38),
asmemberofid INT
)
INSERT INTO #inactivecontactasmemberof (pa_contactid, asmemberofid)
SELECT a.pa_contactid, amo.asmemberofid
FROM MS_CRM3.dbo.user_tcc_committeeinactivityreport a
INNER JOIN #asmemberof amo ON a.asmemberof = amo.asmemberof
AND a.ballotcommitteeid = amo.ballotcommitteeid
WHERE (a.ballotenddate >= CONVERT(DATETIME, '2008-04-01 00:00:00',102))
SELECT DISTINCT contact.pa_contactidname,
asmemberof.asmemberof,
asmemberof.asmemberof1,
asmemberof.asmemberof2,
asmemberof.asmemberof3,
asmemberof4 = CASE WHEN (asmemberof.asmemberof4) LIKE 'TG%' THEN '' ELSE (asmemberof.asmemberof4) END,
asmemberof5 = CASE WHEN (asmemberof.asmemberof5) LIKE 'TG%' THEN '' ELSE (asmemberof.asmemberof5) END,
asmemberof6 = CASE WHEN (asmemberof.asmemberof6) LIKE 'TG%' THEN '' ELSE (asmemberof.asmemberof6) END,
ballot.ballotcommitteename
FROM #inactivecontact contact
INNER JOIN #inactivecontactasmemberof contactasmemberof
ON contact.pa_contactid = contactasmemberof.pa_contactid
INNER JOIN #asmemberof asmemberof
ON contactasmemberof.asmemberofid = asmemberof.asmemberofid
INNER JOIN #ballot ballot
ON asmemberof.ballotcommitteeid = ballot.ballotcommitteeid
ORDER BY pa_contactidname ASC
DROP TABLE #Ballot
DROP TABLE #InactiveContact
DROP TABLE #AsMemberOf
DROP TABLE #inactivecontactasmemberof
-
Can you provide us with the DDL for the table(s), some sample data (as insert statements), and expected results based on the sample data. From looking at your picture, we shouldn't need much data, just enough to recreate your problem so that we can then work on a solution as well.
-
Hi Lynn
attached are the table ddl and sample inserts
thanks
-
peter.nguyen (7/22/2009)
Hi Lynnattached are the table ddl and sample inserts
thanks
Okay, but what about the expected results based on the sample data?
-
It would also be nice if you had two or three individuals in the sample data instead of just one.
-
Also, the table and data provided to seem to match what you showed us in your original post. How about a little more information about what you are looking for here.
-
Sorry.
Attachments for more sample data. ignore the first sample data I sent earlier.
Here is a pic of the output that is required:
Thanks again so much for wanting to help 😀
-
I forgot to mention three or more STGs for each individual.
It's ok if John Doe in row 1 and Jane Doe in row 2 and Peter Doe in row 3 have three of the same STG.
But it's not ok if Row 1-3 were John Doe with matching STGs
-
Your data still doesn't match your ariginal picture. Is there a query that is run against this data that produces the data as seen in your original post?
Nevermind, STILL not enough caffine. Would help to look at all of the original post.
-
Did you find enough info?
the original data got changed over night so I had to send you the newer data that needed to be displayed differently
-
peter.nguyen (7/22/2009)
Sorry.Attachments for more sample data. ignore the first sample data I sent earlier.
Here is a pic of the output that is required:
Thanks again so much for wanting to help 😀
Now to paraphrase, for each row where an individual is reported more than once, you only want to see the unique STG's in the individual memberof columns? Or is the number 3 important here (ie STG05 is in 2 of the rows).
-
that is correct.
I didn't know how to approach it so I created temporary tables and splitted the columns but then I didn't know how to not show the duplicate values in the splitted columns.
-
I'm starting to think the solution is to somehow merge records? I'm not sure if that would be the right approach or if it even exists in t-sql
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply