Dear Reynards and Vixens:
I have some SQL that I would like simplified.
I have a table of charge codes. (clcode and wccode are further
specialisations and are considered different charge codes for the
purposes described here.) I need to do some manipulation where I
need to know if the current year's charge code is the latest. My steps:
1) Create a cursor (mvt) containing the charge code with the
latest validtil date for each charge code.
2) Create a cursor (cvt) containing the charge code with the
latest validtil date in the year 2009 for each charge code.
3) Create a third cursor with the 2009 charge code data plus an
indication of whether the 2009 validtil is equal to the mvt validtil
date. The question being answered is whether each of the 2009 charge
codes is the latest by validtil charge code. (This is being used to
generate charge codes for next year.)
The code below works, but how do I get rid of the union? It
does not feel right that I have to have it to resolve the boolean,
nor that the two queries being unioned are nearly identical. What say you?
***** Start of Included Code *****
select funccode,clcode,wccode,max(validtil) as maxvt from cwkf;
group by funccode,clcode,wccode;
into cursor mvt
select funccode,clcode,wccode,max(validtil) as currvt from cwkf;
where validtil>={^2009.01.01} and validtil<={^2009.12.31};
group by funccode,clcode,wccode;
into cursor cvt
select funccode,clcode,wccode,currvt as validtil,.t. as ismax from cvt;
where exists;
(;
select funccode,clcode,wccode,maxvt from mvt;
where;
funccode=cvt.funccode and clcode=cvt.clcode and wccode=cvt.wccode and;
maxvt=currvt;
);
union;
select funccode,clcode,wccode,currvt as validtil,.f. as ismax from cvt;
where not exists;
(;
select funccode,clcode,wccode,maxvt from mvt;
where;
funccode=cvt.funccode and clcode=cvt.clcode and wccode=cvt.wccode and;
maxvt=currvt;
);
order by funccode,clcode,wccode;
into cursor truefalse
***** End of Included Code *****
Sincerely,
Gene Wirchenko
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
