| Groups | Blog | Home |
sql server (alternate) : Return one row
I was wondering if someone could help me out with something. With the following rows: agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl 1 1 1 10-10-2000 01 10-15-2000 02 1 1 2 10-09-2000 06 05 1 2 1 10-08-2000 05 10-20-2000 01 1 2 1 10-05-2000 01 10-15-2000 03 What I need is SQL to get one row per agnt/supv with the following: strt_dt, strt_lvl of earliest strt_dt trm_dt, trm_lvl of the latest trm_dt but only if all trm_dts for the supv are filled in, otherwise null for both agnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl 1 1 10-09-2000 06 null null 1 2 10-05-2000 01 10-20-2000 01 If anyone has any ideas on the most efficient way to accomplish this, I'd appreciate a reply.
[quoted text, click to view]
Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<om8a70lubrt4fpa79ht4cpq80t2rdejrak@4ax.com>... > On 7 Apr 2004 20:56:48 -0700, Bill wrote: > > >Hello > > > >I was wondering if someone could help me out with something. > > > >With the following rows: > > > >agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl > >1 1 1 10-10-2000 01 10-15-2000 02 > >1 1 2 10-09-2000 06 05 > >1 2 1 10-08-2000 05 10-20-2000 01 > >1 2 1 10-05-2000 01 10-15-2000 03 > > > >What I need is SQL to get one row per agnt/supv with the following: > > strt_dt, strt_lvl of earliest strt_dt > > trm_dt, trm_lvl of the latest trm_dt but only if all trm_dts for > >the supv are > > filled in, otherwise null for both > > > >agnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl > >1 1 10-09-2000 06 null null > >1 2 10-05-2000 01 10-20-2000 01 > > > >If anyone has any ideas on the most efficient way to accomplish this, > >I'd appreciate a reply. > > > >Thanks! > > Maybe this: > > select D.agnt_num, D.supv_num, > D.strt_dt, J1.strt_lvl, > D.trm_dt, J2.trm_lvl > from (select agnt_num, supv_num, min(strt_dt) as strt_dt, > nullif(max(coalesce(trm_dt,'99991231')),'99991231') as trm_dt > from MyTable > group by agnt_num, supv_num) D > inner join MyTable as J1 > on J1.agnt_num = D.agnt_num > and J1.supv_num = D.supv_num > and J1.strt_dt = D.strt_dt > left join MyTable as J2 > on J2.agnt_num = D.agnt_num > and J2.supv_num = D.supv_num > and J2.trm_dt = D.trm_dt > > (untested) > > Best, Hugo Hello Thank you very much for the reply. I guess one thing I forgot to mention is that the start and trm dates could be the same for two or more meet_nums. So somehow I think I'd need to get the meet_num for the high and low of the dates to avoid getting more than one row. Any ideas? Thanks again
[quoted text, click to view]
On 7 Apr 2004 20:56:48 -0700, Bill wrote: >Hello > >I was wondering if someone could help me out with something. > >With the following rows: > >agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl >1 1 1 10-10-2000 01 10-15-2000 02 >1 1 2 10-09-2000 06 05 >1 2 1 10-08-2000 05 10-20-2000 01 >1 2 1 10-05-2000 01 10-15-2000 03 > >What I need is SQL to get one row per agnt/supv with the following: > strt_dt, strt_lvl of earliest strt_dt > trm_dt, trm_lvl of the latest trm_dt but only if all trm_dts for >the supv are > filled in, otherwise null for both > >agnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl >1 1 10-09-2000 06 null null >1 2 10-05-2000 01 10-20-2000 01 > >If anyone has any ideas on the most efficient way to accomplish this, >I'd appreciate a reply. > >Thanks! Maybe this: select D.agnt_num, D.supv_num, D.strt_dt, J1.strt_lvl, D.trm_dt, J2.trm_lvl from (select agnt_num, supv_num, min(strt_dt) as strt_dt, nullif(max(coalesce(trm_dt,'99991231')),'99991231') as trm_dt from MyTable group by agnt_num, supv_num) D inner join MyTable as J1 on J1.agnt_num = D.agnt_num and J1.supv_num = D.supv_num and J1.strt_dt = D.strt_dt left join MyTable as J2 on J2.agnt_num = D.agnt_num and J2.supv_num = D.supv_num and J2.trm_dt = D.trm_dt (untested) Best, Hugo --
[quoted text, click to view]
Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<6nbb70pftuk6vh5ea0503ak2ues0pvrnlu@4ax.com>... > On 8 Apr 2004 06:20:12 -0700, Bill wrote: > > (snip) > >Thank you very much for the reply. I guess one thing I forgot to > >mention is that the start and trm dates could be the same for two or > >more meet_nums. So somehow I think I'd need to get the meet_num for > >the high and low of the dates to avoid getting more than one row. > > > >Any ideas? > > It looks as if you're problem is not "how to code", but "what to > code". > > Let's change your original sample data a bit: > > agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl > 1 1 1 10-10-2000 01 10-15-2000 02 > 1 1 2 10-10-2000 06 05 > 1 2 1 10-08-2000 05 10-15-2000 01 > 1 2 2 10-05-2000 01 10-15-2000 03 > > > Now agnt/supv 1/1 have two meets with the same strt_dt and 1/2 have > two meetings with the same trm_dt. If you still want one row per > supv/agnt, then what sould go at the place of the question marks? > > gnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl > 1 1 10-10-2000 ?? null null > 1 2 10-05-2000 01 10-15-2000 ?? > > In other words - which strt_lvl and trm_lvl to select when multiple > rows share the minimum strt_dt resp. rmp_dt? > > You'll have to get that clear first, before you waste any time coding > something your customer probably doesn't want at all. > > Best, Hugo Hello You are correct sir. I'm sorry for the crummy data and bad specs. When the dates are the same I need the lowest meet_num. I was told by the customer that the dates shouldn't be the same, but I've always been a bit on the paranoid side. Thanks for patience!
[quoted text, click to view]
On 8 Apr 2004 06:20:12 -0700, Bill wrote: (snip) [quoted text, click to view] >Thank you very much for the reply. I guess one thing I forgot to >mention is that the start and trm dates could be the same for two or >more meet_nums. So somehow I think I'd need to get the meet_num for >the high and low of the dates to avoid getting more than one row. > >Any ideas? It looks as if you're problem is not "how to code", but "what to code". Let's change your original sample data a bit: agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl 1 1 1 10-10-2000 01 10-15-2000 02 1 1 2 10-10-2000 06 05 1 2 1 10-08-2000 05 10-15-2000 01 1 2 2 10-05-2000 01 10-15-2000 03 Now agnt/supv 1/1 have two meets with the same strt_dt and 1/2 have two meetings with the same trm_dt. If you still want one row per supv/agnt, then what sould go at the place of the question marks? gnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl 1 1 10-10-2000 ?? null null 1 2 10-05-2000 01 10-15-2000 ?? In other words - which strt_lvl and trm_lvl to select when multiple rows share the minimum strt_dt resp. rmp_dt? You'll have to get that clear first, before you waste any time coding something your customer probably doesn't want at all. Best, Hugo --
[quoted text, click to view]
On 8 Apr 2004 18:20:35 -0700, Bill wrote: >Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<6nbb70pftuk6vh5ea0503ak2ues0pvrnlu@4ax.com>... >> On 8 Apr 2004 06:20:12 -0700, Bill wrote: >> >> (snip) >> >Thank you very much for the reply. I guess one thing I forgot to >> >mention is that the start and trm dates could be the same for two or >> >more meet_nums. So somehow I think I'd need to get the meet_num for >> >the high and low of the dates to avoid getting more than one row. >> > >> >Any ideas? >> >> It looks as if you're problem is not "how to code", but "what to >> code". >> >> Let's change your original sample data a bit: >> >> agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl >> 1 1 1 10-10-2000 01 10-15-2000 02 >> 1 1 2 10-10-2000 06 05 >> 1 2 1 10-08-2000 05 10-15-2000 01 >> 1 2 2 10-05-2000 01 10-15-2000 03 >> >> >> Now agnt/supv 1/1 have two meets with the same strt_dt and 1/2 have >> two meetings with the same trm_dt. If you still want one row per >> supv/agnt, then what sould go at the place of the question marks? >> >> gnt_num supv_num strt_dt strt_lvl trm_dt trm_lvl >> 1 1 10-10-2000 ?? null null >> 1 2 10-05-2000 01 10-15-2000 ?? >> >> In other words - which strt_lvl and trm_lvl to select when multiple >> rows share the minimum strt_dt resp. rmp_dt? >> >> You'll have to get that clear first, before you waste any time coding >> something your customer probably doesn't want at all. >> >> Best, Hugo > >Hello > >You are correct sir. I'm sorry for the crummy data and bad specs. >When the dates are the same I need the lowest meet_num. I was told by >the customer that the dates shouldn't be the same, but I've always >been a bit on the paranoid side. Thanks for patience! > >Bill I'd use a view in this case. It can proably all be done in one big and complicated query with derived tables, but if you wwant to understand your code later, that might not be a good idea. CREATE VIEW ParanoidStrt AS SELECT agnt_num, supv_num, strt_dt, strt_lvl FROM MyTable AS O WHERE NOT EXISTS (SELECT * FROM MyTable AS I WHERE I.agnt_num = O.agnt_num AND I.supv_num = O.supv_num AND I.strt_dt = O.strt_dt AND I.meet_num < O.meet_num) go CREATE VIEW ParanoidTrm AS SELECT agnt_num, supv_num, trm_dt, trm_lvl FROM MyTable AS O WHERE NOT EXISTS (SELECT * FROM MyTable AS I WHERE I.agnt_num = O.agnt_num AND I.supv_num = O.supv_num AND I.trm_dt = O.trm_dt AND I.meet_num < O.meet_num) go -- And now comes the query: select D.agnt_num, D.supv_num, D.strt_dt, J1.strt_lvl, D.trm_dt, J2.trm_lvl from (select agnt_num, supv_num, min(strt_dt) as strt_dt, nullif(max(coalesce(trm_dt,'99991231')),'99991231') as trm_dt from MyTable group by agnt_num, supv_num) D inner join ParanoidStrt as J1 on J1.agnt_num = D.agnt_num and J1.supv_num = D.supv_num and J1.strt_dt = D.strt_dt left join ParanoidTrm as J2 on J2.agnt_num = D.agnt_num and J2.supv_num = D.supv_num and J2.trm_dt = D.trm_dt Note 1: This is still untested. I can only test soluition I present in this newsgroup if I have some DDL plus INSERT statements that I can copy and paste in Query Analyser to recreate your table sturcture in my test DB. Note 2: If you don't want to use the views, simply replace the name of each view in the query by the select statement of that view, enclosed in (parantheses). Best, Hugo --
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |