Need Query Help

I have the following queries which probably violates all sorts of rules.
I'm doing year over year changes and can't seem to get the entire calucation
done in one query. So I'm pulling data from one query (SQL2) and using it
in another query (SQL1). You'll see real numbers being used in SQL1 (ie
(totalmem - 30152)). The 30152 is coming from the other query.
I would rather have access to all the data in a single query. My real issue
is that from period to period health plans come and go in our data file
because they cease operations in a State and then reenter the market a year
or two later... Most health plans are constant in the data. A few come and
go. I might have 5 records in 2Q03 data and 6 in 2Q02 data. This causes
SQL1 to break because the rs2 data does not exisit if there is more data in
SQL1 than SQL2.
Data format:
MCO HMO MeciareMem MedicaidMem CommMem TotalMem
If I could get the data returned like this:
MCO HMO MedicareMem2Q03 MedicareMem2Q02, MedicaidMem2Q03,
MedicareMem2Q02... I would be in great shape
My data file looks like this:
Period MCO HMO MeciareMem MedicaidMem CommMem TotalMem
2Q02 Aetna Aetna Health of CT 0 0 37947 37947
2Q03 Aetna Aetna Health of CT 0 0 41110 41110
except that I have 11,000 rows of data.
I'd love some help on this one. A sample data set is below.
***
SQL2= "Select MCO, HMO, medicaremem, medicaidmem, commmem, totalmem from
sheet1$ where period = '" & varPeriod4 & "' AND " & strStateSQL1 & " order
by " & varsort & " " & varorder
renders like this:
SQL2 = Select MCO, HMO, medicaremem, medicaidmem, commmem, totalmem from
sheet1$ where period = '3Q03' AND (domicile LIKE '%ct%' OR domicile LIKE
'%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR domicile LIKE
'%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR domicile LIKE
'%nj%' OR domicile LIKE '%pa%') order by hmo asc
***
SQL1= "Select MCO, HMO, medicaremem, (medicaremem - " & rs2("medicaremem") &
") as medicarememchange, medicaidmem, commmem, totalmem, (totalmem - " &
rs2("totalmem") & ") as totalmemchange from sheet1$ where totalmem <> '' and
period = '" & varPeriod & "' AND " & strStateSQL1 & " order by " & varsort &
" " & varorder
renders like this:
SQL1 = Select MCO, HMO, medicaremem, (medicaremem - 0) as medicarememchange,
medicaidmem, commmem, totalmem, (totalmem - 30152) as totalmemchange from
sheet1$ where totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%'
OR domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
domicile LIKE '%nj%' OR domicile LIKE '%pa%') order by hmo ascHi
Posting DDL and example data is the best way to help people answer your
questions, see http://www.aspfaq.com/etiquett_e.asp?id=5006
This may give you some indication of what you require assuming primary the
key is MCO, HMO, medicaremem
Select a.MCO, a.HMO, a.medicaremem, a.medicaidmem-b.medicaidmem, a.commmem,
b.commmem, a.totalmem-b.totalmem
from sheet1$ a
JOIN sheet1$ b ON a.MCO = b.MCO AND a.HMO = b.HMO AND a.medicaremem =
b.medicaremem
where a.period = '3Q03'
and b.period = '3Q04'
If there is not always a record in '3Q04' then try:
Select a.MCO, a.HMO, a.medicaremem, a.medicaidmem-ISNULL(b.medicaidmem,0),
a.commmem, b.commmem, a.totalmem - ISNULL(b.totalmem,0)
from sheet1$ a
LEFT JOIN sheet1$ b ON a.MCO = b.MCO AND a.HMO = b.HMO AND a.medicaremem =
b.medicaremem
where a.period = '3Q03'
and b.period = '3Q04'
John
"William" wrote:

> I have the following queries which probably violates all sorts of rules.
> I'm doing year over year changes and can't seem to get the entire calucati
on
> done in one query. So I'm pulling data from one query (SQL2) and using it
> in another query (SQL1). You'll see real numbers being used in SQL1 (ie
> (totalmem - 30152)). The 30152 is coming from the other query.
> I would rather have access to all the data in a single query. My real iss
ue
> is that from period to period health plans come and go in our data file
> because they cease operations in a State and then reenter the market a yea
r
> or two later... Most health plans are constant in the data. A few come a
nd
> go. I might have 5 records in 2Q03 data and 6 in 2Q02 data. This causes
> SQL1 to break because the rs2 data does not exisit if there is more data i
n
> SQL1 than SQL2.
> Data format:
> MCO HMO MeciareMem MedicaidMem CommMem TotalMem
>
> If I could get the data returned like this:
> MCO HMO MedicareMem2Q03 MedicareMem2Q02, MedicaidMem2Q03,
> MedicareMem2Q02... I would be in great shape
> My data file looks like this:
> Period MCO HMO MeciareMem MedicaidMem CommMem TotalMem
> 2Q02 Aetna Aetna Health of CT 0 0 37947 37947
> 2Q03 Aetna Aetna Health of CT 0 0 41110 41110
> except that I have 11,000 rows of data.
>
> I'd love some help on this one. A sample data set is below.
> ***
> SQL2= "Select MCO, HMO, medicaremem, medicaidmem, commmem, totalmem from
> sheet1$ where period = '" & varPeriod4 & "' AND " & strStateSQL1 & " order
> by " & varsort & " " & varorder
> renders like this:
> SQL2 = Select MCO, HMO, medicaremem, medicaidmem, commmem, totalmem from
> sheet1$ where period = '3Q03' AND (domicile LIKE '%ct%' OR domicile LIKE
> '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR domicile LIKE
> '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR domicile LIKE
> '%nj%' OR domicile LIKE '%pa%') order by hmo asc
> ***
> SQL1= "Select MCO, HMO, medicaremem, (medicaremem - " & rs2("medicaremem")
&
> ") as medicarememchange, medicaidmem, commmem, totalmem, (totalmem - " &
> rs2("totalmem") & ") as totalmemchange from sheet1$ where totalmem <> '' a
nd
> period = '" & varPeriod & "' AND " & strStateSQL1 & " order by " & varsort
&
> " " & varorder
> renders like this:
> SQL1 = Select MCO, HMO, medicaremem, (medicaremem - 0) as medicarememchang
e,
> medicaidmem, commmem, totalmem, (totalmem - 30152) as totalmemchange from
> sheet1$ where totalmem <> '' and period = '3Q04' AND (domicile LIKE '%ct%'
> OR domicile LIKE '%ma%' OR domicile LIKE '%nh%' OR domicile LIKE '%me%' OR
> domicile LIKE '%ri%' OR domicile LIKE '%vt%' OR domicile LIKE '%ny%' OR
> domicile LIKE '%nj%' OR domicile LIKE '%pa%') order by hmo asc
>
>
>|||I'd build a table of the periods, then do a LEFT OUTER JOIN on it to
the membership table. This will give you NULLs for the periods that
the particular company was not in the State.
If you really must have things on one row, then you can use CASE
expressions inside aggregates.
I also wonder why you use things like "(domicile LIKE '%pa%')" instead
of scrubbing the data to assure that you have CHAR(2) valid state
codes.