[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query Help needed! Subquery on one table in Sql server 2000.** Kind of urgent **

Posted on 2006-04-05
14
Medium Priority
?
278 Views
Last Modified: 2012-08-13
I have one table and i want a subquery or a scalar subquery within same table:

ID, CODE, effdt                 COLLEGE  COMMENTS
1   0     2005-09-19               S        1K
1   0     2006-03-19             W        2K
2   0     2005-10-19             M        3K
2   1     2005-11-19             S        3K
3   0     2005-10-20             M        4SK
3   1     2006-01-19              S        4SK
3   2     2006-01-10              S       4XK
3   2     2005-12-19             M        GK

id, code, effdt and college are keys

I am looking to write a query which will tell me identify dupliacete code in multiple colleges for one emplid.
in this case one example is ID 1. It has code of 0 in both college S and college W. In my query I want to identify only those records which have duplicate codes in different college with effective dated.

So when i write my query i should see

ID, CODE, effdt                 COLLEGE  COMMENTS
1   0     2006-03-19               W        2K
3   2     2006-01-10             S       4xK


So in above, I got 2 Ids which had multiple code values in two differnet colleges. I got the most recent (effdt) out of those with the college name, code, ID and the comments

I have written sub blurbs that give me the IDs but it doesnt give me effdt.
A sample effdt query is written as

select flda, fldb, effdt from table 1
where effdt = (select max(a.effdt) from table 1 a where
a.flda = flda and a.fldb = fldb)



0
Comment
Question by:tgatif
14 Comments
 
LVL 6

Expert Comment

by:hans_vd
ID: 16384503
Try this query:


select flda, fldb, effdt, college, comments
from table1
where (flda, fldb, effdt) in (
    select flda, fldb, min(effdt)
    from table1
    group by flda, fldb
    having count(*) > 1
)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16384598
select *
from yourtable t
where exists ( select null from yourtable c
                      where c.id = t.id
                      and c.code = t.code
                      group by c.id, c.code
                      having count(*) > 1
                    )
and effdt = ( select max ( i.effdt ) from yourtable i
                      where c.id = t.id
                      and c.code = t.code
                  )
 
       
0
 

Author Comment

by:tgatif
ID: 16384603
HI, can u please use the above names i am using from the table. thats would help a lot. like id, code etc..
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16384711
to ensure just the 1 row per id and code then i believe it actually needs to become this...
 for an id and code get the max effectivedate
         with the row having the max college and max coment for that college
 
select Distinct t.*
from yourtable t
where exists ( select null from yourtable c
                      where c.id = t.id
                      and c.code = t.code
                      group by c.id, c.code
                      having count(distinct college) > 1
                    )
and effdt = ( select max ( i.effdt ) from yourtable i
                      where c.id = t.id
                      and c.code = t.code
                  )
 and college = (select max(college) from yourtable x
                         where t.id = x.id
                        and t.effdt=x.effdt
                        and t.code=x.code
                       )
and comments = (select max(comments) from yourtable as x
                         where t.id = x.id
                        and t.effdt=x.effdt
                        and t.code=x.code
                        and t.college = x.college
                       )
0
 

Author Comment

by:tgatif
ID: 16384822
this is what i have so far after putting into the live system with real tables.
Out of this table.

CUSTID, ACAD_CAREER, STDNT_CAR_NBR, EFFDT, EFFSEQ are keys.
When irun this code, it  bring thousands of data. doesnt seem right....

select *
from ACAD T
where exists ( select null from ACAD C
                      where C.CUSTID = T.CUSTID
                      AND C.STDNT_CAR_NBR = T.STDNT_CAR_NBR
                   AND C.ACAD_CAREER = T.ACAD_CAREER
                  AND C.EFFSEQ = T.EFFSEQ
                  group by C.CUSTID, C.ACAD_CAREER, C.STDNT_CAR_NBR
                      having count(*) > 1
                    )
and T.EFFDT = ( select max ( I.EFFDT ) from ACAD I
                      where T.CUSTID = I.CUSTID
                      and T.STDNT_CAR_NBR = I.STDNT_CAR_NBR
                   AND T.ACAD_CAREER = I.ACAD_CAREER
                        AND T.EFFSEQ = I.EFFSEQ
                  )
0
 

Author Comment

by:tgatif
ID: 16384994
Lowfatspread

I also have to doa cross check and see if the code exists for the other college or not.
Meaning that return only the rows which have STDNT_CAR_NBR the same for multiple COLLEGES

SO DATA in the dabase is like

ID      STDNT_CAR_NBR      ACAD_PROG      EFFDT        EFFSEQ      COLLEGE
1            0            DEGR        2005-10-19   0               S
1            0            DEGR        2006-10-19   0               M
1            0            DEGR        2002-10-19   0               S
0
 

Author Comment

by:tgatif
ID: 16385013
ID      STDNT_CAR_NBR      ACAD_PROG      EFFDT        EFFSEQ      COLLEGE
1            0                          DEGR        2005-10-19   0               S
1            0                          DEGR        2006-10-19   0               M
1            0                          DEGR        2002-10-19   0               S
2            0                          DEGR        2003-10-19   0               M
2            1                          DEGR        2001-10-19   0               S                        


SO AS ABOVE I WANT
the second row to be selected for ID 1 only. As ID 2 has 2 different stdnt_car_nbr, it shouldnt be in the query results.
line 2 of custid 1 is the one because its effective dated out of all 3 matching ones
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16385065
Please try this; sorry about col names, will re-work soon if needed:


SELECT acad.*
FROM acad
INNER JOIN (
    SELECT flda, fldb, MIN(effdt) AS minEffdt
    FROM acad
    GROUP BY flda, fldb
    HAVING COUNT(DISTINCT college) > 1
) AS acadDups ON acadDups.flda = acad.flda AND acadDups.fldb = acad.fldb AND
    acad.minEffdt <> acad.effdt
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16385095
CORRECTED column names:

SELECT acad.*
FROM acad
INNER JOIN (
    SELECT id, stdnt_car_nbr, MIN(effdt) AS minEffdt
    FROM acad
    GROUP BY id, stdnt_car_nbr
    HAVING COUNT(DISTINCT college) > 1
) AS acadDups ON acadDups.id = acad.id AND acadDups.stdnt_car_nbr = acad.stdnt_car_nbr AND
    acadDups.minEffdt <> acad.effdt
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16385138
Seems to work.  

Probably a better alias is "acadGood" [rather than acadDups], since the subquery/derived table is finding the one to *keep* not the one(s) to get rid of -- the outer query determines that and lists them.
0
 

Author Comment

by:tgatif
ID: 16385477
i MODIFIED AND THIS IS MY FINAL


SELECT ACAD.*
FROM ACAD_PROG ACAD
INNER JOIN (
    SELECT CUSTID, STDNT_CAR_NBR, MIN(EFFDT) AS minEffdt, ACAD_CAREER
    FROM ACAD_PROG
    GROUP BY CUSTID, STDNT_CAR_NBR, ACAD_CAREER
    HAVING COUNT(DISTINCT COLLEGE) > 1
) AS acadDups ON acadDups.CUSTID = ACAD.CUSTID AND acadDups.STDNT_CAR_NBR = ACAD.STDNT_CAR_NBR AND
    acadDups.minEffdt <> ACAD.EFFDT AND ACAD.ACAD_CAREER = acadDups.ACAD_CAREER
ORDER BY ACAD.EMPLID

When I run the one you gave, it gives me still 14000 records. When i added rest of the keys like acad_career, it now has narrowed down my results to 117. more reasonable now.

but the problem is that its bringing max effdt results per college. So when i run the report for ID 2300 (lets assume) and that is was in two colleges lets assume (W and M), The current query will bring two rows of data such as

ID     STDNT_CAR_NBR     ACAD_PROG     EFFDT            EFFSEQ     COLLEGE
2300            0                          DEGR        2005-10-19       0               S
2300            0                          DEGR        2006-10-19       0               M

instead it should bring me only row 2 because it has the highest effective date.
0
 

Author Comment

by:tgatif
ID: 16385514
     
Comment from hans_vd
Date: 04/05/2006 11:07AM MST
      Comment       Accept

Try this query:


select flda, fldb, effdt, college, comments
from table1
where (flda, fldb, effdt) in (
    select flda, fldb, min(effdt)
    from table1
    group by flda, fldb
    having count(*) > 1
)

Comment from angelIII
Date: 04/05/2006 11:16AM MST
      Comment       Accept

select *
from yourtable t
where exists ( select null from yourtable c
                      where c.id = t.id
                      and c.code = t.code
                      group by c.id, c.code
                      having count(*) > 1
                    )
and effdt = ( select max ( i.effdt ) from yourtable i
                      where c.id = t.id
                      and c.code = t.code
                  )
 
       

Comment from tgatif
Date: 04/05/2006 11:17AM MST
      Your Comment       

HI, can u please use the above names i am using from the table. thats would help a lot. like id, code etc..

Comment from Lowfatspread
Date: 04/05/2006 11:27AM MST
      Comment       Accept

to ensure just the 1 row per id and code then i believe it actually needs to become this...
 for an id and code get the max effectivedate
         with the row having the max college and max coment for that college
 
select Distinct t.*
from yourtable t
where exists ( select null from yourtable c
                      where c.id = t.id
                      and c.code = t.code
                      group by c.id, c.code
                      having count(distinct college) > 1
                    )
and effdt = ( select max ( i.effdt ) from yourtable i
                      where c.id = t.id
                      and c.code = t.code
                  )
 and college = (select max(college) from yourtable x
                         where t.id = x.id
                        and t.effdt=x.effdt
                        and t.code=x.code
                       )
and comments = (select max(comments) from yourtable as x
                         where t.id = x.id
                        and t.effdt=x.effdt
                        and t.code=x.code
                        and t.college = x.college
                       )

Comment from tgatif
Date: 04/05/2006 11:38AM MST
      Your Comment       

this is what i have so far after putting into the live system with real tables.
Out of this table.

CUSTID, ACAD_CAREER, STDNT_CAR_NBR, EFFDT, EFFSEQ are keys.
When irun this code, it  bring thousands of data. doesnt seem right....

select *
from ACAD T
where exists ( select null from ACAD C
                      where C.CUSTID = T.CUSTID
                      AND C.STDNT_CAR_NBR = T.STDNT_CAR_NBR
                 AND C.ACAD_CAREER = T.ACAD_CAREER
                AND C.EFFSEQ = T.EFFSEQ
                group by C.CUSTID, C.ACAD_CAREER, C.STDNT_CAR_NBR
                      having count(*) > 1
                    )
and T.EFFDT = ( select max ( I.EFFDT ) from ACAD I
                      where T.CUSTID = I.CUSTID
                      and T.STDNT_CAR_NBR = I.STDNT_CAR_NBR
                 AND T.ACAD_CAREER = I.ACAD_CAREER
                        AND T.EFFSEQ = I.EFFSEQ
                  )

Comment from tgatif
Date: 04/05/2006 11:52AM MST
      Your Comment       

Lowfatspread

I also have to doa cross check and see if the code exists for the other college or not.
Meaning that return only the rows which have STDNT_CAR_NBR the same for multiple COLLEGES

SO DATA in the dabase is like

ID     STDNT_CAR_NBR     ACAD_PROG     EFFDT       EFFSEQ     COLLEGE
1            0            DEGR        2005-10-19   0               S
1            0            DEGR        2006-10-19   0               M
1            0            DEGR        2002-10-19   0               S

Comment from tgatif
Date: 04/05/2006 11:54AM MST
      Your Comment       

ID     STDNT_CAR_NBR     ACAD_PROG     EFFDT       EFFSEQ     COLLEGE
1            0                          DEGR        2005-10-19   0               S
1            0                          DEGR        2006-10-19   0               M
1            0                          DEGR        2002-10-19   0               S
2            0                          DEGR        2003-10-19   0               M
2            1                          DEGR        2001-10-19   0               S                        


SO AS ABOVE I WANT
the second row to be selected for ID 1 only. As ID 2 has 2 different stdnt_car_nbr, it shouldnt be in the query results.
line 2 of custid 1 is the one because its effective dated out of all 3 matching ones

Comment from ScottPletcher
Date: 04/05/2006 11:58AM MST
      Comment       Accept

Please try this; sorry about col names, will re-work soon if needed:


SELECT acad.*
FROM acad
INNER JOIN (
    SELECT flda, fldb, MIN(effdt) AS minEffdt
    FROM acad
    GROUP BY flda, fldb
    HAVING COUNT(DISTINCT college) > 1
) AS acadDups ON acadDups.flda = acad.flda AND acadDups.fldb = acad.fldb AND
    acad.minEffdt <> acad.effdt

Comment from ScottPletcher
Date: 04/05/2006 12:02PM MST
      Comment       Accept

CORRECTED column names:

SELECT acad.*
FROM acad
INNER JOIN (
    SELECT id, stdnt_car_nbr, MIN(effdt) AS minEffdt
    FROM acad
    GROUP BY id, stdnt_car_nbr
    HAVING COUNT(DISTINCT college) > 1
) AS acadDups ON acadDups.id = acad.id AND acadDups.stdnt_car_nbr = acad.stdnt_car_nbr AND
    acadDups.minEffdt <> acad.effdt

Comment from ScottPletcher
Date: 04/05/2006 12:05PM MST
      Comment       Accept

Seems to work.  

Probably a better alias is "acadGood" [rather than acadDups], since the subquery/derived table is finding the one to *keep* not the one(s) to get rid of -- the outer query determines that and lists them.

Comment from tgatif
Date: 04/05/2006 12:31PM MST
      Your Comment       

i MODIFIED AND THIS IS MY FINAL


SELECT ACAD.*
FROM ACAD_PROG ACAD
INNER JOIN (
    SELECT CUSTID, STDNT_CAR_NBR, MIN(EFFDT) AS minEffdt, ACAD_CAREER
    FROM ACAD_PROG
    GROUP BY CUSTID, STDNT_CAR_NBR, ACAD_CAREER
    HAVING COUNT(DISTINCT COLLEGE) > 1
) AS acadDups ON acadDups.CUSTID = ACAD.CUSTID AND acadDups.STDNT_CAR_NBR = ACAD.STDNT_CAR_NBR AND
    acadDups.minEffdt <> ACAD.EFFDT AND ACAD.ACAD_CAREER = acadDups.ACAD_CAREER
ORDER BY ACAD.CUSTID
0
 

Author Comment

by:tgatif
ID: 16385587
shoot!
my post disspeared

This is the final I have
SELECT ACAD.*
FROM ACAD_PROG ACAD
INNER JOIN (
    SELECT CUSTID, STDNT_CAR_NBR, MIN(EFFDT) AS minEffdt, ACAD_CAREER
    FROM ACAD_PROG
    GROUP BY CUSTID, STDNT_CAR_NBR, ACAD_CAREER
    HAVING COUNT(DISTINCT COLLEGE) > 1
) AS acadDups ON acadDups.CUSTID = ACAD.CUSTID AND acadDups.STDNT_CAR_NBR = ACAD.STDNT_CAR_NBR AND
    acadDups.minEffdt <> ACAD.EFFDT AND ACAD.ACAD_CAREER = acadDups.ACAD_CAREER
ORDER BY ACAD.CUSTID


Problem. Its still bringing results for two colleges. its bringing max effdt results per college. lets say custid 2300 has this data

ID     STDNT_CAR_NBR     ACAD_PROG     EFFDT       EFFSEQ     COLLEGE
23            0                          DEGR        2005-10-19   0               S
23          0                            DEGR        2006-10-19   0               M
23          0                            DEGR        2001-10-19   0               S
23          0                            DEGR        2002-10-19   0               S


It should bring the second row as its the highest max effdt of all. but instead it bring two rows. one for max effdt when college is M and one when 'S' (row 1)

My query is to get the college name and IDs from this table as which college had last modified the record (based on effdt) and which had duplicate stdn record number in other colleges
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 16385643
SORRY, I think I misinterpreted what you wanted.  Please try this:


SELECT ACAD.*
FROM ACAD_PROG ACAD
INNER JOIN (
    SELECT CUSTID, STDNT_CAR_NBR, MAX(EFFDT) AS maxEffdt, ACAD_CAREER
    FROM ACAD_PROG
    GROUP BY CUSTID, STDNT_CAR_NBR, ACAD_CAREER
    HAVING COUNT(DISTINCT COLLEGE) > 1
) AS acadBad ON acadBad.CUSTID = ACAD.CUSTID AND acadBad.STDNT_CAR_NBR = ACAD.STDNT_CAR_NBR AND
    acadBad.maxEffdt = ACAD.EFFDT AND ACAD.ACAD_CAREER = acadBad.ACAD_CAREER
ORDER BY ACAD.CUSTID
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question