Cross referencing a column and adding to another

This is the current infromation the sql query below pulls, what I need to do is add a field
called ACN and then count in tblACNRequest the # of Para and Lines that show up for requests
that match what I am pulling.  So for instance I have saved the 1st para and line in tblACNRequest  so in my query it would count a 1 towards that para and line.

UIC    Para  Line  Grade  DMOS Gender Auth  Asgn  OS  VAC  UNIT  Status
PULAA  101   01    O5     42B0       I      1         1      0    0    MTOE   Critical
PULAA  101   02    O4     42B0       I      1          0        0    1   MTOE         Critical

This is what is saved in tblACNRequest
intRecID  strACN    Para   Line  strUIC  dtExpire  dtTimeStamp
    1    200917-05     101     01 PULAA  5/1/2009   5/1/2009
SELECT   at.UPC as UIC,
                at.AUTH_PARA_DSG as Para,
                at.AUTH_LINE_DSG as Line,
                at.GRADE as Grade, 
                substring(at.POSC, 1, 4) as DMOS, 
                case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
               at.AUTH_STR,
               at.ASGN_STR,
             case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE)  - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end as 'OS',
               at.AUTH_STR + case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE)  - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR as VAC, 
             case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' end UNIT_TYPE,
             case s.strStat when 'C' then 'Critical' when 'L' then 'Locked' else 'None' end as Status
from         SIPERS.PERS_AUTH_STR_TBL at INNER JOIN
                SIPERS.PERS_UNIT_TBL ut on ut.upc = at.UPC Inner Join
                SIPERS.MNLOCAL_APVSE_UIC_TBL mn on mn.UIC = at.UPC Left Outer join
                tblStatUic as s on s.strUIC = at.UPC WHERE substring(at.POSC, 1, 4) = '42B0'

Open in new window

kdeutschAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
alchemyst999Connect With a Mentor Commented:
The attached code may work

Cheers,
Al
SELECT   at.UPC as UIC,
                at.AUTH_PARA_DSG as Para,
                at.AUTH_LINE_DSG as Line,
                at.GRADE as Grade, 
                substring(at.POSC, 1, 4) as DMOS, 
                case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
               at.AUTH_STR,
               at.ASGN_STR,
             case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE)  - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end as 'OS',
               at.AUTH_STR + case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE)  - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR as VAC, 
             case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' end UNIT_TYPE,
             case s.strStat when 'C' then 'Critical' when 'L' then 'Locked' else 'None' end as Status,
			 plc.PLCount as ACN
from         SIPERS.PERS_AUTH_STR_TBL at INNER JOIN
                SIPERS.PERS_UNIT_TBL ut on ut.upc = at.UPC Inner Join
                SIPERS.MNLOCAL_APVSE_UIC_TBL mn on mn.UIC = at.UPC Left Outer join
                tblStatUic as s on s.strUIC = at.UPC WHERE substring(at.POSC, 1, 4) = '42B0' Left join
				(SELECT AR.Para, AR.Line, COUNT(*) As PLCount
				FROM 	tblACNRequest As AR
				GROUP BY AR.Para, AR.Line) As plc on (at.AUTH_PARA_DSG = plc.para) and (at.AUTH_LINE_DSG = plc.line)

Open in new window

0
 
kdeutschAuthor Commented:
HI, I got this to work somewhat, but the problem is that it shows the same field 7 times but only counts one of them.

SELECT      at.UPC as UIC,
        at.AUTH_PARA_DSG as Para,
        at.AUTH_LINE_DSG as Line,
        at.GRADE as Grade,
        substring(at.POSC, 1, 4) as DMOS,
        case at.AUTH_PERS_IDENT when 'E' then 'M' when 'W' then 'M' when 'O' then 'M' else 'I' end GENDER,
      at.AUTH_STR,
      at.ASGN_STR,
      case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE)  - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end as 'OS',
      (select Count(r.strUIc) tblACNReques where r.strPara in (select at.AUTH_PARA_DSG from SIDPERS..SIDPERS.PERS_AUTH_STR_TBL where at.auth_Para_DSG = r.strPara) and
      r.strLine in (Select at.AUTH_LINE_DSG from SIDPERS..SIDPERS.PERS_AUTH_STR_TBL where at.auth_Line_DSG = r.strLine)) as ACN,
      at.AUTH_STR + case when at.AUTH_DOC_POSN_TITLE like '%(%)%' then substring(at.AUTH_DOC_POSN_TITLE, 1 + charindex('(', at.AUTH_DOC_POSN_TITLE), charindex(')', at.AUTH_DOC_POSN_TITLE)  - (charindex('(', at.AUTH_DOC_POSN_TITLE) + 1)) else '0' end - at.ASGN_STR as VAC,
      case substring(at.DOC_NBR, 1, 2) when 'NG' then 'TDA' else 'MTOE' end UNIT_TYPE,
      case s.strStat when 'C' then 'Critical' when 'L' then 'Locked' else 'None' end as Status
from    SIDPERS..SIDPERS.PERS_AUTH_STR_TBL at INNER JOIN
        SIDPERS..SIDPERS.PERS_UNIT_TBL ut on ut.upc = at.UPC Inner Join
        SIDPERS..SIDPERS.MNLOCAL_APVSE_UIC_TBL mn on mn.UIC = at.UPC Left Outer join
       tbtblStatUic as s on s.strUIC = at.UPC
Group by  at.upc, at.AUTH_PARA_DSG, at.AUTH_LINE_DSG, at.GRADE, at.POSC, at.AUTH_PERS_IDENT,  at.AUTH_STR, at.ASGN_STR, at.AUTH_DOC_POSN_TITLE, r.strPara, r.strLine, s.strStat, at.DOC_NBR


0
 
SharathData EngineerCommented:
What is your expected result for the sample set provided?
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
kdeutschAuthor Commented:
Hi,
teh resulat should be like this, only with the field ACn now added
UIC    Para  Line  Grade  DMOS Gender Auth  Asgn  OS  VAC  UNIT  Status
PULAA  101   01    O5     42B0       I      1         1      0    0    MTOE   Critical
PULAA  101   02    O4     42B0       I      1          0        0    1   MTOE         Critical

But what I get is each of these multiplied 7 times, but only one of them has the ACN count and the rest are null
0
 
alchemyst999Commented:
Well, you could always add 'WHERE ACN IS NOT NULL' to the end, but that doesnt realy sort the issues with the code out (and at first glance that will be running in cubic time/O(N^3)).

Was there any issues the code I added above?
0
 
kdeutschAuthor Commented:
Hi,
Here are the erros it gave me
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'Left'.
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'As'.

I took out the where statement and then it gives me this error
LE DB provider 'sidpers' does not contain table '"sidpers"."PERS_UNIT_TBL"'.  The table either does not exist or the current user does not have permissions on that table.

But if I take out added sql it works just fine and has access to table.

0
 
alchemyst999Commented:
Is it supposed to be 'SIPERS' or 'SIDPERS..SIDPERS', you seem to have switched up.
0
 
kdeutschAuthor Commented:
Hi,
It is sidpers..sidpers, I cut out the link protion before so it did not look so messed up on page.
0
 
kdeutschAuthor Commented:
Closing old question, this worked but changed order or group by
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.