Gene Wirchenko wrote:
> 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
>
>
Hi Gene,
Why not combine the first two cursors into one:
select funccode,clcode,wccode,max(validtil) as
maxvt,iif(year(max(validtil))=2009,.t.,.f.) as ismax from cwkf;
group by funccode,clcode,wccode;
into cursor mvt
You then have one cursor with a flag to say if the date is 2009, which
is what you create with the union.
Peter
----------------------------------------------------------------
Rajan Imports has changed - we are now Whispering Smith Ltd. For more
information see our website at www.whisperingsmith.com
Please update your address book with my new email address:
[email protected]
.
This communication is intended for the person or organisation to whom it is
addressed. The contents are confidential and may be protected in law.
Unauthorised use, copying or
disclosure of any of it may be unlawful. If you have received this message in
error, please notify us immediately by telephone or email.
www.whisperingsmith.com
Whispering Smith Ltd
Head Office:61 Great Ducie Street, Manchester M3 1RR. Tel:0161 831 3700
Fax:0161 831 3715
London Office:17-19 Foley Street, London W1W 6DW Tel:0207 299 7960
_______________________________________________
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.
