Querying Effective Dated Records

Hello,

Description:

I need to retrieve inactive transfer credit courses (Status of I below)  for a student. That course needs to be inactive for the max effective date.  

Example:

Here are the records for Biol 2401  on the

TRCrsID      Effdt                   Subject   Crse_nbr      Status

1235          2017-01-03         Biol           2401              A
1236          2017-01-04         Biol           2401              I
1237          2017-01-05         Biol           2401              A



TRCrsID        Effdt                   Subject   Crse_nbr      Status

1234          2017-01-03         Chem          2401              A
1236          2017-01-04         Chem          2401              A
1237          2017-01-05         Chem          2401              I



******** I want Chem in the resultset  but NOT Biol.  The problem is that I am getting both.





The query structure for Method 1:

SELECT DISTINCT
   STC.EMPLID
, STC.SCHOOL_SUBJECT
 , STC.SCHOOL_CRSE_NBR
   FROM PS_NC_XFERCRED_HDR A INNER JOIN PS_NC_XFERCRED_EXT B ON A.TRCrsID = B.TRCrsID
    INNER JOIN PS_NC_XFERCRED_STC STC
   ON A.EXT_ORG_ID = STC.EXT_ORG_ID

  WHERE A.EFFDT = (
 SELECT MAX(A1.EFFDT)
  FROM PS_NC_XFERCRED_HDR A1
 WHERE A.TRCrsID = A1.TRCrsID
   AND A1.EFFDT <= GETDATE() )

  AND A.STATUS = 'I'
   
     

As you can see I left the STATUS outside the  A.EFFDT correlated query.  This picks up both Biol and Chem.

Method 2:

The easiest thing for me to do is to  do an insert  into a record then delete all 'A' (active statuses) if I simply do this query:

SELECT DISTINCT
   STC.EMPLID
, STC.SCHOOL_SUBJECT
 , STC.SCHOOL_CRSE_NBR
   FROM PS_NC_XFERCRED_HDR A INNER JOIN PS_NC_XFERCRED_EXT B ON ATRCrsID = BTRCrsID
    INNER JOIN PS_NC_XFERCRED_STC STC
   ON A.EXT_ORG_ID = STC.EXT_ORG_ID

  WHERE A.EFFDT = (
 SELECT MAX(A1.EFFDT)
  FROM PS_NC_XFERCRED_HDR A1
 WHERE A.TRCrsID = A1.TRCrsID
  AND A1.EFFDT <= GETDATE() )

  AND A.STATUS IN ( 'I' , 'A')


This will pick up  Biol as 'A' and Chem as 'I' for that student.



How do I avoid method 2 and simply do it with out having to do an insert and delete.



Thanks for your help in advance,

Brock
BrockAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
You can use ROW_NUMBER() over (partition by    STC.EMPLID, STC.SCHOOL_SUBJECT , STC.SCHOOL_CRSE_NBR order by A.EFFDT desc) as RN

then select the RN's = 1 and status = 'I'

I am not too sure about your queries so making a few assumptions....
;with cte as
(
  SELECT DISTINCT 
   STC.EMPLID
, STC.SCHOOL_SUBJECT 
 , STC.SCHOOL_CRSE_NBR
 , ROW_NUMBER() over (partition by    STC.EMPLID, STC.SCHOOL_SUBJECT , STC.SCHOOL_CRSE_NBR order by A.EFFDT desc) as RN 
   FROM PS_NC_XFERCRED_HDR A 
   INNER JOIN PS_NC_XFERCRED_EXT B ON A.TRCrsID = B.TRCrsID
   INNER JOIN PS_NC_XFERCRED_STC STC    ON A.EXT_ORG_ID = STC.EXT_ORG_ID

) SELECT * from cte where RN = 1 and A.STATUS = 'I' 

Open in new window

Maybe some sample data matching the query / source tables would allow us to test...
0
 
xiao jinshouCommented:
To understand your question, I need the sample data for the three tables:
PS_NC_XFERCRED_HDR
PS_NC_XFERCRED_EXT
PS_NC_XFERCRED_STC

Your description: I need to retrieve inactive transfer credit courses (Status of I below)  for a student. That course needs to be inactive for the max effective date.  

Are you trying to retrieve the inactive transfer credit course for BOTH Biol and Chem?  What is your definition of "max effective date"?
0
 
BrockAuthor Commented:
Hello xiao,

So the issue is how the table was designed -different key for same course;

I created a table with some sample data:

CRSE_ID      SUBJECT      CATALOG_NBR      EFFDT      STATUS
1234      BIOL      1P92      2017-01-03 00:00:00.000      A
1235      BIOL      1P92      2017-01-04 00:00:00.000      I
1236      BIOL      1P92      2017-01-05 00:00:00.000      A
1237      CHEM      1P92      2017-01-03 00:00:00.000      A
1237      CHEM      1P92      2017-01-04 00:00:00.000      A
1237      CHEM      1P92      2017-01-05 00:00:00.000      I
1237      CHEM      1P92      2017-01-07 00:00:00.000      A

You see I gave Biol different id's and Chem the same id's


I am trying to grab only inactive courses  for a particular student. However, because of the way the programmer designed this table to have different id's for the same course I end up getting course that should not be in the result set like Biol 1p92  (because the most recent or max effdt row is for a particular identifier).

For example  using this query:
SELECT *
FROM PS_NC_TESTLNS2 LNS
WHERE EFFDT =
(SELECT MAX(EFFDT)
FROM PS_NC_TESTLNS2 TEST2
WHERE
TEST2.CRSE_ID = LNS.CRSE_ID
AND TEST2.SUBJECT = LNS.SUBJECT
AND TEST2.CATALOG_NBR = LNS.CATALOG_NBR
)
AND STATUS IN ( 'I')

returns  

CRSE_ID      SUBJECT      CATALOG_NBR      EFFDT      STATUS
1235      BIOL      1P92      2017-01-04 00:00:00.000      I


However, there is a more current date that is 'A'  and that is not what the client wants.
1236      BIOL      1P92      2017-01-05 00:00:00.000      A


I am going to try Mark's solution now.  

The real query is :

SELECT DISTINCT
   A.EFFDT
 , STC.EMPLID
 , STC.SCHOOL_SUBJECT
 , STC.SCHOOL_CRSE_NBR
 , D.SUBJECT
 , D.CATALOG_NBR
 , D.CRSE_ID
 , A.EXT_ORG_ID
 , A.STATUS
 , E_OCAS_REFNO
  FROM PS_NC_XFERCRED_HDR A INNER JOIN PS_NC_XFERCRED_EXT B ON A.NC_XFERCRED_GRP = B.NC_XFERCRED_GRP
   AND A.EFFDT = B.EFFDT INNER JOIN PS_NC_XFERCRED_INT C ON A.NC_XFERCRED_GRP = C.NC_XFERCRED_GRP INNER JOIN PS_CRSE_OFFER D ON C.SUBJECT = D.SUBJECT
   AND C.CATALOG_NBR = D.CATALOG_NBR
   INNER JOIN PS_NC_XFERCRED_STG STG ON A.EXT_ORG_ID = STG.EXT_ORG_ID
   INNER JOIN PS_NC_XFERCRED_STC STC ON STG.EMPLID = STC.EMPLID
   AND STC.SCHOOL_SUBJECT = STG.SCHOOL_SUBJECT
   AND STC.SCHOOL_CRSE_NBR = STC.SCHOOL_CRSE_NBR
   AND STC.CRSE_ID = D.CRSE_ID
   INNER JOIN PS_NC_XFERCRED_STS STS ON A.EXT_ORG_ID = STS.EXT_ORG_ID
   AND STG.EMPLID = STS.EMPLID
  AND E_OCAS_REFNO LIKE 'C2018%'
  WHERE A.EFFDT = (
 SELECT MAX(A1.EFFDT)
  FROM PS_NC_XFERCRED_HDR A1
 WHERE A.NC_XFERCRED_GRP = A1.NC_XFERCRED_GRP
   AND A1.EFFDT <= GETDATE() )
   AND A.STATUS = 'I'
   AND D.EFFDT = (
 SELECT MAX(D1.EFFDT)
  FROM PS_CRSE_OFFER D1
 WHERE D.CRSE_ID = D1.CRSE_ID
   AND D.CRSE_OFFER_NBR = D1.CRSE_OFFER_NBR
   AND D1.EFFDT <= GETDATE() )
   AND C.EFFDT = (
   SELECT MAX(C1.EFFDT)
  FROM PS_NC_XFERCRED_INT C1
  WHERE C.NC_XFERCRED_GRP = C1.NC_XFERCRED_GRP
  AND  C.SUBJECT = C1.SUBJECT
  AND  C.CATALOG_NBR = C1.CATALOG_NBR
  AND  C1.EFFDT <= GETDATE())


Thanks,
Brock
0
 
BrockAuthor Commented:
Thank you to all for responding to my inquiry. Thanks, Mark your solution worked marvelously.
0
 
BrockAuthor Commented:
So in my comment,  I described what the issue was.
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.