Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

top 1 1

Status
Not open for further replies.
wfd1bdb

wfd1bdb

MIS
Jun 21, 2006
25
0
0
US
I wrote some code that gives me these results but I only want the top 1 record for 177979. Not sure how to do this.

Many thanks ahead of time.

Brad

600630 DRYER DON 8/04/2007
177979 FOUNCH DONNA 8/10/2007
177979 FOUNCH DONNA 8/12/2007
177979 FOUNCH DONNA 8/14/2007
614283 GERDNER KEM 8/15/2007
 
Sort by date Sort by votes
Can you post your query.

- Paul
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
I only want the top 1 record for 177979
Click to expand...
What is the "top 1" record? The earliest date? The latest date? Either way, you probably want the Min or Max function along with a Group By.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.
 
Upvote 0 Downvote
Yes, it is the latest date.
So something like:

Select acct, lname, fname, max(date)

oedre by max(date)
 
Upvote 0 Downvote
Code: 
[COLOR=blue]SELECT[/color] MyTable.*
[COLOR=blue]FROM[/color] MyTable
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] FieldThatContains_177979,
                   [COLOR=#FF00FF]MAX[/color](DateField) [COLOR=blue]AS[/color] DateField
            [COLOR=blue]FROM[/color] MyTable
            [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] FieldThatContains_177979) Tbl1
[COLOR=blue]ON[/color] MyTable.FieldThatContains_177979 = Tbl1.FieldThatContains_177979 AND
   MyTable.DateField                = Tbl1.DateField

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Here is my code! I have to admit that I am having trouble inserting Borislav's code because of the multiple joins alerady in the script. I have been looking at this for 35 minutes and am becoming bug eyed.

Brad

SELECT DISTINCT a.ACCOUNT, b.PLNAME, b.PFNAME, d .PROV, d .NAME2, c.status_id, c.rundate, c.f12, c.f79 AS ReasonCode,
CASE WHEN c.f12 IS NULL THEN 'Make an entry in the Registration and Data Outcome Questionaire.'
WHEN c.f12 = 'SADAT_ERR' THEN 'Substance abuse Questionaire is incomplete.'
WHEN c.f12 = 'NOSCR' THEN 'No HAPI-A scores.'
WHEN c.f12 = 'NOTSIC_ERR' THEN 'Client does not meet diagnostic Criteria.'
WHEN c.f12 = 'INRES_ERR'THEN 'Client is not an Indiana resident'
WHEN c.f12 = 'INC_DNP' THEN 'Income data is missing.'
WHEN c.f12 = 'DXDNP' THEN 'Primary Diagnosis is missing.'
WHEN c.f12 = 'SSN_DNP' THEN 'Social Security number is missing'
WHEN c.f12 = 'NOTPOR' THEN 'Client does not meet HAP Financial Criteria.'
WHEN c.f12 = 'DX_ERR' THEN 'Primary Diagnosis is not valid.'
WHEN c.f12 = 'HAPQ_DNP' THEN 'Questionaire is incomplete.' WHEN c.f12 = 'ERR' THEN 'Check reason code.'
WHEN c.f12 = 'SOF' THEN 'Agreement type is SOF.'
WHEN c.f12 = 'SA1' THEN 'Agreement type is SA1.'
WHEN c.f12 = 'MI9' THEN 'Agreement type is MI9.'
WHEN c.f12 = 'MI8' THEN 'Agreement type is MI8.'
WHEN c.f12 = 'MI7' THEN 'Agreement type is MI7.'
WHEN c.f12 = 'MI6' THEN 'Agreement type is MI6.'
WHEN c.f12 = 'MI5' THEN 'Agreement type is MI5.'
WHEN c.f12 = 'MI4' THEN 'Agreement type is MI4.'
WHEN c.f12 = 'MI3' THEN 'Agreement type is MI3.'
WHEN c.f12 = 'MI2' THEN 'Agreement type is MI2.'
WHEN c.f12 = 'MI1' THEN 'Agreement type is MI1.'
WHEN c.f12 = 'DMI' THEN 'Agreement type is DMI.'
WHEN c.f12 = 'DED' THEN 'Agreement type is DED.'
WHEN c.f12 = 'DCA' THEN 'Agreement type is DCA.'
WHEN c.f12 = 'CM4' THEN 'Agreement type is CM4.'
WHEN c.f12 = 'CM3' THEN 'Agreement type is CM3.'
WHEN c.f12 = 'CM2' THEN 'Agreement type is CM2.'
WHEN c.f12 = 'CM1' THEN 'Agreement type is CM1.'
WHEN c.f12 = 'CA4' THEN 'Agreement type is CA4.'
WHEN c.f12 = 'CA3' THEN 'Agreement type is CA3.'
WHEN c.f12 = 'CA2' THEN 'Agreement type is CA2.'
WHEN c.f12 = 'AW4' THEN 'Agreement type is AW4.'
WHEN c.f12 = 'AW2' THEN 'Agreement type is AW2.'
WHEN c.f12 = 'ACT' THEN 'Agreement type is ACT.'
END AS ActionToBeTaken
FROM
CLCHRGE a INNER JOIN CLMSTER b ON a.ACCNT = b.ACCNT FULL OUTER JOIN CSDS_RDO c ON b.SSNO = c.ssno INNER JOIN
CLDOCTR d ON b.DOCTR = d .DOCTR

WHERE d .prov = '1007' AND (a.XACDATE2 BETWEEN '12-01-2006' AND '08-09-2007') AND (a.CPT NOT IN '0', '512', '522', '532', '542', '611', '621', '631', '690','711', '803', '811', '994', '995', '996', '7820', '9079','9080', '9081', '9179', '9180', '9181', '8030', '895', '8950', '896', '8960')) AND a.CHGAMOUNT - ISNULL(a.ADJUST, .0000) <> 0) AND (c.status_id = 0 OR
c.status_id IS NULL)
ORDER BY b.PLNAME
 
Upvote 0 Downvote
Isn't it much more readable if you formated that query this way:
Code: 
[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color] a.ACCOUNT,
                b.PLNAME,
                b.PFNAME,
                [COLOR=blue]d[/color] .PROV,
                [COLOR=blue]d[/color] .NAME2,
                c.status_id,
                c.rundate,
                c.f12,
                c.f79 [COLOR=blue]AS[/color] ReasonCode,
               [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] c.f12 [COLOR=blue]IS[/color] NULL 
                         [COLOR=blue]THEN[/color] [COLOR=red]'Make an entry in the Registration and Data Outcome Questionaire.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'SADAT_ERR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Substance abuse Questionaire is incomplete.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'NOSCR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'No HAPI-A scores.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'NOTSIC_ERR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Client does not meet diagnostic Criteria.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'INRES_ERR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Client is not an Indiana resident'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'INC_DNP'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Income data is missing.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'DXDNP'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Primary Diagnosis is missing.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'SSN_DNP'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Social Security number is missing'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'NOTPOR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Client does not meet HAP Financial Criteria.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'DX_ERR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Primary Diagnosis is not valid.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'HAPQ_DNP'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Questionaire is incomplete.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'ERR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Check reason code.'[/color]
               [COLOR=blue]ELSE[/color] [COLOR=red]'Agreement type is '[/color] + c.f12
               [COLOR=blue]END[/color] [COLOR=blue]AS[/color] ActionToBeTaken
[COLOR=blue]FROM[/color] CLCHRGE a
      [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CLMSTER b [COLOR=blue]ON[/color] a.ACCNT = b.ACCNT 
      [COLOR=blue]FULL[/color] [COLOR=blue]JOIN[/color] CSDS_RDO c [COLOR=blue]ON[/color] b.SSNO = c.ssno
      [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CLDOCTR [COLOR=blue]d[/color] [COLOR=blue]ON[/color] b.DOCTR = [COLOR=blue]d[/color] .DOCTR
[COLOR=blue]WHERE[/color] [COLOR=blue]d[/color] .prov = [COLOR=red]'1007'[/color]                                                                  AND
     (a.XACDATE2 BETWEEN [COLOR=red]'12-01-2006'[/color] AND [COLOR=red]'08-09-2007'[/color])                                 AND
     (a.CPT NOT IN [COLOR=red]'0'[/color], [COLOR=red]'512'[/color], [COLOR=red]'522'[/color], [COLOR=red]'532'[/color], [COLOR=red]'542'[/color], [COLOR=red]'611'[/color], [COLOR=red]'621'[/color], [COLOR=red]'631'[/color], [COLOR=red]'690'[/color],[COLOR=red]'711'[/color],
                    [COLOR=red]'803'[/color], [COLOR=red]'811'[/color], [COLOR=red]'994'[/color], [COLOR=red]'995'[/color], [COLOR=red]'996'[/color], [COLOR=red]'7820'[/color], [COLOR=red]'9079'[/color],[COLOR=red]'9080'[/color], [COLOR=red]'9081'[/color],
                    [COLOR=red]'9179'[/color], [COLOR=red]'9180'[/color], [COLOR=red]'9181'[/color], [COLOR=red]'8030'[/color], [COLOR=red]'895'[/color], [COLOR=red]'8950'[/color], [COLOR=red]'896'[/color], [COLOR=red]'8960'[/color])       AND
      (a.CHGAMOUNT - [COLOR=#FF00FF]ISNULL[/color](a.ADJUST, .0000) <> 0)                                      AND
     (c.status_id = 0 OR c.status_id [COLOR=blue]IS[/color] NULL)
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] b.PLNAME

So the question is: Where you want to put that code?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Yes, the question is...where do I put this code?

SELECT MyTable.*
FROM MyTable
INNER JOIN (SELECT a.account,
MAX(c.rundate) AS Test
FROM clchrge
GROUP BY a.account) Tbl1
ON a.account = a.account AND
c.rundate = Tbl1.DateField
 
Upvote 0 Downvote
On second thought WHY you need FULL join on CSDS_RDO? You never check there is some data in CLCHRGE, Try this:
Code: 
[COLOR=blue]SELECT[/color] Tbl1.*
[COLOR=blue]FROM[/color](
[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color] a.ACCOUNT,
                b.PLNAME,
                b.PFNAME,
                [COLOR=blue]d[/color] .PROV,
                [COLOR=blue]d[/color] .NAME2,
                c.status_id,
                c.rundate,
                c.f12,
                c.f79 [COLOR=blue]AS[/color] ReasonCode,
               [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] c.f12 [COLOR=blue]IS[/color] NULL
                         [COLOR=blue]THEN[/color] [COLOR=red]'Make an entry in the Registration and Data Outcome Questionaire.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'SADAT_ERR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Substance abuse Questionaire is incomplete.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'NOSCR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'No HAPI-A scores.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'NOTSIC_ERR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Client does not meet diagnostic Criteria.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'INRES_ERR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Client is not an Indiana resident'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'INC_DNP'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Income data is missing.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'DXDNP'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Primary Diagnosis is missing.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'SSN_DNP'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Social Security number is missing'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'NOTPOR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Client does not meet HAP Financial Criteria.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'DX_ERR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Primary Diagnosis is not valid.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'HAPQ_DNP'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Questionaire is incomplete.'[/color]
                    [COLOR=blue]WHEN[/color] c.f12 = [COLOR=red]'ERR'[/color]
                         [COLOR=blue]THEN[/color] [COLOR=red]'Check reason code.'[/color]
               [COLOR=blue]ELSE[/color] [COLOR=red]'Agreement type is '[/color] + c.f12
               [COLOR=blue]END[/color] [COLOR=blue]AS[/color] ActionToBeTaken
[COLOR=blue]FROM[/color] CLCHRGE a
      [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CLMSTER b [COLOR=blue]ON[/color] a.ACCNT = b.ACCNT
      [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CSDS_RDO c [COLOR=blue]ON[/color] b.SSNO = c.ssno
      [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CLDOCTR [COLOR=blue]d[/color] [COLOR=blue]ON[/color] b.DOCTR = [COLOR=blue]d[/color] .DOCTR
[COLOR=blue]WHERE[/color] [COLOR=blue]d[/color] .prov = [COLOR=red]'1007'[/color]                                                                  AND
     (a.XACDATE2 BETWEEN [COLOR=red]'12-01-2006'[/color] AND [COLOR=red]'08-09-2007'[/color])                                 AND
     (a.CPT NOT IN ([COLOR=red]'0'[/color], [COLOR=red]'512'[/color], [COLOR=red]'522'[/color], [COLOR=red]'532'[/color], [COLOR=red]'542'[/color], [COLOR=red]'611'[/color], [COLOR=red]'621'[/color], [COLOR=red]'631'[/color], [COLOR=red]'690'[/color],[COLOR=red]'711'[/color],
                    [COLOR=red]'803'[/color], [COLOR=red]'811'[/color], [COLOR=red]'994'[/color], [COLOR=red]'995'[/color], [COLOR=red]'996'[/color], [COLOR=red]'7820'[/color], [COLOR=red]'9079'[/color],[COLOR=red]'9080'[/color], [COLOR=red]'9081'[/color],
                    [COLOR=red]'9179'[/color], [COLOR=red]'9180'[/color], [COLOR=red]'9181'[/color], [COLOR=red]'8030'[/color], [COLOR=red]'895'[/color], [COLOR=red]'8950'[/color], [COLOR=red]'896'[/color], [COLOR=red]'8960'[/color]))      AND
      (a.CHGAMOUNT - [COLOR=#FF00FF]ISNULL[/color](a.ADJUST, .0000) <> 0)                                      AND
     (c.status_id = 0 OR c.status_id [COLOR=blue]IS[/color] NULL)) Tbl1
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] CLCHRGE.ACCOUNT,
                   [COLOR=#FF00FF]MAX[/color](CSDS_RDO.rundate) [COLOR=blue]AS[/color] rundate
                   [COLOR=blue]FROM[/color] CLCHRGE
                   [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CLMSTER   [COLOR=blue]ON[/color] CLCHRGE.ACCNT = CLMSTER.ACCNT
                   [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] CSDS_RDO  [COLOR=blue]ON[/color] CLMSTER.SSNO        = CSDS_RDO.ssno
            [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] CLCHRGE.ACCOUNT) Tbl2
      [COLOR=blue]ON[/color] Tbl1.ACCOUNT = Tbl2.ACCOUNT AND
         Tbl1.rundate = Tbl2.rundate
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] Tbl1.PLNAME
(not tested at all)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Borislav,

yeah that works but I do not understand how! Using tbl1 and tbl2. That is new to me. Thanks!!
 
Upvote 0 Downvote
Check Derived tables in BOL.

Tbl1 and Tbl2 are just ALIASes of the derived tables.
As you see you main query become derived table:
Code: 
SELECT ...
       FROM (SELECT ....) Tbl1
That Tbl1 is alias that helps you to access that table fields, make joins etc. All derived tables must be ALIASed. I used Tbl1 and Tbl2 here just because I don't know your data, when I use them in my application I named them with names that speaks something to me :)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Borislav,

This seems to work well and returns the MAX reported date but there are times when this reported date field is empty or null. In that scenario I also need that record. I tried to add a CASE statement: (but that did not work) Any help is greatly appreciated...now I am off to lunch!

Brad


INNER JOIN (SELECT CLCHARGE.ACCOUNT,
case when CSDS_RDO.rundate is null
then 'test"
else max(csds_rdo.rundate)
end AS rundate
FroM CLCHARGE
INNER JOIN CLMASTER ON CLCHARGE.ACCOUNT =
CLMASTER.ACCOUNT
Inner JOIN CSDS_RDO ON CLMASTER.SSNO = CSDS_RDO.ssno
GROUP BY CLCHARGE.ACCOUNT, csds_rdo.rundate) Tbl2
ON Tbl1.ACCOUNT = Tbl2.ACCOUNT AND
Tbl1.rundate = Tbl2.rundate
ORDER BY Tbl1.PLNAME
 
Upvote 0 Downvote
Holy perambulating spacetoads! What a case statement. Try this:

Code: 
CASE WHEN c.f12 IS NULL THEN 'Make an entry in the Registration and Data Outcome Questionnaire.' 
WHEN c.f12 = 'SADAT_ERR' THEN 'Substance abuse Questionnaire is incomplete.'
WHEN c.f12 = 'NOSCR' THEN 'No HAPI-A scores.' 
WHEN c.f12 = 'NOTSIC_ERR' THEN 'Client does not meet diagnostic Criteria.' 
WHEN c.f12 = 'INRES_ERR'THEN 'Client is not an Indiana resident' 
WHEN c.f12 = 'INC_DNP' THEN 'Income data is missing.' 
WHEN c.f12 = 'DXDNP' THEN 'Primary Diagnosis is missing.'
WHEN c.f12 = 'SSN_DNP' THEN 'Social Security number is missing' 
WHEN c.f12 = 'NOTPOR' THEN 'Client does not meet HAP Financial Criteria.' 
WHEN c.f12 = 'DX_ERR' THEN 'Primary Diagnosis is not valid.' 
WHEN c.f12 = 'HAPQ_DNP' THEN 'Questionnaire is incomplete.' WHEN c.f12 = 'ERR' THEN 'Check reason code.'
[blue]WHEN c.f12 LIKE 'MI[0-9]' OR c.f12 IN ('SOF', 'SA1', 'DMI', 'DED', 'DCA', 'CM4', 'CM3', 'CM2', 'CM1', 'CA4', 'CA3', 'CA3', 'CA2', 'AW4', 'AW2', 'ACT') THEN 'Agreement type is ' + c.f12 + '.'[/blue]
END
Even that's still not great. You're putting data in code, data which really belongs in a table. Is this the ONLY place you'll ever need to convert these codes to descriptions? Even if it was, will they ever change or have new codes added? Then put them in a table.

Code: 
CREATE TABLE QuestionnaireActions (
   f12 varchar(10) NOT NULL PRIMARY KEY CLUSTERED,
   ActionToBeTaken varchar(100) NOT NULL
)

INSERT QuestionnaireActions 
SELECT 'SADAT_ERR', 'Substance abuse Questionnaire is incomplete.'
UNION ALL SELECT 'NOSCR', 'No HAPI-A scores.'
UNION ALL SELECT 'NOTSIC_ERR', 'Client does not meet diagnostic Criteria.'
UNION ALL SELECT 'INRES_ERR', 'Client is not an Indiana resident'
UNION ALL SELECT 'INC_DNP', 'Income data is missing.'
UNION ALL SELECT 'DXDNP', 'Primary Diagnosis is missing.'
UNION ALL SELECT 'SSN_DNP', 'Social Security number is missing'
UNION ALL SELECT 'NOTPOR', 'Client does not meet HAP Financial Criteria.'
UNION ALL SELECT 'DX_ERR', 'Primary Diagnosis is not valid.'
UNION ALL SELECT 'HAPQ_DNP', 'Questionnaire is incomplete.'
UNION ALL SELECT 'ERR', 'Check reason code.'
UNION ALL SELECT 'SOF', 'Agreement type is SOF.'
UNION ALL SELECT 'SA1', 'Agreement type is SA1.'
UNION ALL SELECT 'MI9', 'Agreement type is MI9.'
UNION ALL SELECT 'MI8', 'Agreement type is MI8.'
UNION ALL SELECT 'MI7', 'Agreement type is MI7.'
UNION ALL SELECT 'MI6', 'Agreement type is MI6.'
UNION ALL SELECT 'MI5', 'Agreement type is MI5.'
UNION ALL SELECT 'MI4', 'Agreement type is MI4.'
UNION ALL SELECT 'MI3', 'Agreement type is MI3.'
UNION ALL SELECT 'MI2', 'Agreement type is MI2.'
UNION ALL SELECT 'MI1', 'Agreement type is MI1.'
UNION ALL SELECT 'DMI', 'Agreement type is DMI.'
UNION ALL SELECT 'DED', 'Agreement type is DED.'
UNION ALL SELECT 'DCA', 'Agreement type is DCA.'
UNION ALL SELECT 'CM4', 'Agreement type is CM4.'
UNION ALL SELECT 'CM3', 'Agreement type is CM3.'
UNION ALL SELECT 'CM2', 'Agreement type is CM2.'
UNION ALL SELECT 'CM1', 'Agreement type is CM1.'
UNION ALL SELECT 'CA4', 'Agreement type is CA4.'
UNION ALL SELECT 'CA3', 'Agreement type is CA3.'
UNION ALL SELECT 'CA2', 'Agreement type is CA2.'
UNION ALL SELECT 'AW4', 'Agreement type is AW4.'
UNION ALL SELECT 'AW2', 'Agreement type is AW2.'
UNION ALL SELECT 'ACT', 'Agreement type is ACT.'
Now left join to this table for your ActionToBeTaken. You could even leave out the 'Agreement type is XXX' entries and assume that if they're not in the list they're an agreement type. And you'll have just one more thing, when f12 is null then 'Make an entry in the Registration and Data Outcome Questionnaire.'

Code: 
SELECT
   ...
   ActionToBeTaken =
      CASE
      WHEN c.f12 IS NULL THEN 'Make an entry in the Registration and Data Outcome Questionnaire.'
      ELSE IsNull(Q.ActionToBeTaken, 'Agreement type is ' + c.f12') + '.'
      END
   ...
FROM
   ...
   INNER JOIN CSDS_RDO c ON b.SSNO = c.ssno
   LEFT JOIN QuestionnaireActions Q ON c.f12 = Q.f12 
   ...
P.S. Questionnaire is spelled with two Ns.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
93
grnzbra
grnzbra
Jago11
  • Locked
  • Question
When to use 1=1 logic 1
Replies
7
Views
88
Andrzejek
Andrzejek
Cobby1812
  • Locked
  • Question
Multiple Rows in 1 Row 2
Replies
4
Views
73
Cobby1812
Cobby1812
JeffModen
  • Locked
  • Question
thread183-1814079 Why do people
Replies
3
Views
73
Mike Lewis
Mike Lewis
bkrampe
  • Locked
  • Question
Round Down to nearest 16th Inch 1
Replies
7
Views
98
bkrampe
bkrampe

Part and Inventory Search

Sponsor

Back
Top