If records meet criteria in TblA, insert single record in tblB

Question

Post reply

If records meet criteria in TblA, insert single record in tblB

TSQLExplorer

Ten Centuries

Points: 1279
More actions
January 30, 2006 at 11:23 am

#96574

I have a query I've been working on (below) which I want to use to find groups of records in table A (SASI.ACHS5031) based upon my criteria (there must be a record for term 1 and a record for term 2).   Once I identify a student with this criteria (including the term 1 and term 2 requirement), i want to create a new single record composed of data not contained in table A into table B (SASI.ASAH5031) to signify that the criteria was met in table A

INSERT INTO SASI.ASAH5031

        (STATUS, SCHOOLNUM, STULINK, [SEQUENCE], USERFIELD1, USERFIELD2,

USERSTAMP, DATESTAMP, [TIMESTAMP], SCHLYEAR, FROMDATE,

TODATE, SCHLATTNUM, SCHLATTNM, CITY, STATE, GRADELEVEL,

COUNTRY, BYAUTOPROC, EOYSTATUS, RESERVED, FILLER, SCHOOLFLAG,

DISTNO, FLSCHLNO, U$DATE, PROVINCE)

SELECT STATUS, TERM, SCHOOLNUM, STULINK, [SEQUENCE], USERFIELD1, USERFIELD2,

USERSTAMP, DATESTAMP, [TIMESTAMP], SCHLYEAR,

'9/1/2002' AS FROMDATE,

'6/1/2003' AS TODATE,

'091' AS SCHLATTNUM,

'Central Junior High' AS SCHLATTNM,

'SilverCity' AS CITY,

'NY' AS STATE,

'09' AS GRADELEVEL,

'' AS COUNTRY,

'' AS BYAUTOPROC,

'' AS EOYSTATUS,

'' AS RESERVED,

'' AS FILLER,

'' AS SCHOOLFLAG,

'' AS DISTNO,

'' AS FLSCHLNO,

'' AS U$DATE,

'' AS PROVINCE

FROM    SASI.ACHS5031

WHERE   ((SCHLYEAR = '2002')

AND (GRADE = '09')

AND (TERM like '%1')

AND (SCHOOLATTN = '021'))

or

((SCHLYEAR = '2002')

AND (GRADE = '09')

AND (TERM like '%2')

AND (SCHOOLATTN = '021'))

Your advice appreciated...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

question is which record if many are found do you want to insert?

Distinct won't work, because any one of these fields could be different.

STATUS, TERM, SCHOOLNUM, STULINK, [SEQUENCE], USERFIELD1, USERFIELD2,

USERSTAMP, DATESTAMP, [TIMESTAMP],

so if there are many records which one do you want to get this information from?

TSQLExplorer Ten Centuries Points: 1279 More actions · Answer 2

Your question quite rightly outlined the poor syntax of my question...I've re-written it.... and include it here..

sorry for the me induced confusion...

I have a query I've been working on (below) which I want to use to find groups of records in table A (SASI.ACHS5031) based upon my criteria (there must be a record for term 1 and a record for term 2). Once I identify a student with this criteria (including the term 1 and term 2 requirement), I want to create a new single record composed of data not contained in table A into table B (SASI.ASAH5031) to signify that the criteria was met in table A

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Not knowing yoru data I had to make an assumption that Stulink was the reference to the student.

Anyway here's a table and example and you can see what I did.

Create table #test (pk int identity, Grade char(2), Term varchar(5),

SchoolAttn varchar(5), SCHLYear char(4), Status char(1)

, SchoolNum smallint, Stulink char(1))

insert into #Test (Grade,Term,SchoolAttn,SCHLYear,Status,SchoolNum,Stulink)

select '09', '001', '021', '2002', 'A', 101, 'A' union

select '09', '001', '021', '2002', 'A', 101, 'B' union

select '09', '002', '021', '2002', 'A', 101, 'B' union

select '09', '001', '021', '2002', 'A', 101, 'C' union

select '09', '001', '021', '2002', 'A', 101, 'D' union

select '09', '002', '021', '2002', 'A', 101, 'D'

select *

from #Test

Results

1,09,001,021,2002,A,101,A

2,09,001,021,2002,A,101,B

3,09,001,021,2002,A,101,C

4,09,001,021,2002,A,101,D

5,09,002,021,2002,A,101,B

6,09,002,021,2002,A,101,D

So in your example Stulink B and D have records for term 1 and 2.

select *

FROM #Test A

WHERE ((SCHLYEAR = '2002')

AND (GRADE = '09')

AND (TERM like '%1')

AND (SCHOOLATTN = '021'))

AND exists (SELECT *

FROM #Test B

WHERE ((SCHLYEAR = '2002')

AND (GRADE = '09')

AND (TERM like '%2')

AND (SCHOOLATTN = '021')

AND a.Stulink = b.Stulink)

)

Results

pk,Grade,Term,SchoolAttn,SCHLYear,Status,SchoolNum,Stulink

2,09,001,021,2002,A,101,B

4,09,001,021,2002,A,101,D

or you can use derived tables.

SELECT *

FROM (SELECT Grade,Term,SchoolAttn,SCHLYear,Status,SchoolNum,Stulink

FROM #Test

WHERE ((SCHLYEAR = '2002')

AND (GRADE = '09')

AND (TERM like '%1')

AND (SCHOOLATTN = '021'))) as Term1

Join (SELECT Grade,Term,SchoolAttn,SCHLYear,Status,SchoolNum,Stulink

FROM #Test

WHERE ((SCHLYEAR = '2002')

AND (GRADE = '09')

AND (TERM like '%2')

AND (SCHOOLATTN = '021'))) as Term2 on Term1.Stulink = Term2.stulink

Results

Grade,Term,SchoolAttn,SCHLYear,Status,SchoolNum,Stulink

09,001,021,2002,A,101,B

09,001,021,2002,A,101,D

Hope this helps

TSQLExplorer Ten Centuries Points: 1279 More actions · Answer 4

Your code makes perfect sense..but when I run it on my data.. as ...

SELECT *

FROM (SELECT Grade,Term,SchoolAttn,SCHLYear,Status,SchoolNum,Stulink

FROM SASI.ACHS5031

WHERE ((SCHLYEAR = '2002')

AND (GRADE = '09')

AND (TERM like '%1')

AND (SCHOOLATTN = '021'))) as Term1

Join (SELECT Grade,Term,SchoolAttn,SCHLYear,Status,SchoolNum,Stulink

FROM SASI.ACHS5031

WHERE ((SCHLYEAR = '2002')

AND (GRADE = '09')

AND (TERM like '%2')

AND (SCHOOLATTN = '021')))

as Term2

on Term1.Stulink = Term2.stulink

I get multiple records and two colums per student per term...

09 1 021 2002 031 8068 09 2 021 2002 031 8068

09 1 021 2002 031 8240 09 2 021 2002 031 8240

09 1 021 2002 031 7367 09 2 021 2002 031 7367

and so on.....

Ray M SSC-Insane Points: 21093 More actions · Answer 5

Hmm..

Looks like there is more than 1 record per student for SCHLYEAR, GRADE, TERM, SCHOOLATTN. So going back to your initial request

you simply want a list of stulink ids where year = 2002, grade = 09 SchoolAttn = 021.

So you should just have to do a Distinct

SELECT Distinct Term1.*

FROM (SELECT Grade,Term,SchoolAttn,SCHLYear,Status,SchoolNum,Stulink

FROM #Test

WHERE ((SCHLYEAR = '2002')

AND (GRADE = '09')

AND (TERM like '%1')

AND (SCHOOLATTN = '021'))) as Term1

Join (SELECT Grade,Term,SchoolAttn,SCHLYear,Status,SchoolNum,Stulink

FROM #Test

WHERE ((SCHLYEAR = '2002')

AND (GRADE = '09')

AND (TERM like '%2')

AND (SCHOOLATTN = '021'))) as Term2 on Term1.Stulink = Term2.stulink

But looking at your original query, I think you might need more.