|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL StatementI'm having a tough time writing a sql stmt. Any kind of help is very much appreciated. The info is as follows: SalesOrder sono so-suffix solineno qtyord netamt 1495738 1 33 3 4779.45 1495738 0 33 3 0.00 1490507 0 15 2 0.00 1490507 1 15 2 0.00 1490507 2 15 2 0.00 1490507 3 15 2 3874.86 1497351 0 11 2 0.00 1497351 1 11 2 0.00 1491566 0 8 2 0.00 1491566 1 8 2 0.00 1491566 2 8 2 0.00 1491566 3 8 2 0.00 The purpose of my query is to get the total netamt on a sales order. The resultset I'm looking for is, all the salesorder records where netamt is <> 0 and if there is a salesorder with 0 netamt, I need to get the last occurence of that salesorder record to count the total qtyord (which is the tricky part). So my resultset should be 1495738 1 33 3 4779.45 1490507 3 15 2 3874.86 & 1497351 1 11 2 0.00 1491566 3 8 2 0.00 The first 2 records will be netamt <> 0. The second 2 records will be the last occurence of the salesorder. The field so-suffix is important here. Pleaseeeeeeee help me. Thank you so very much. this should give the result based on the sample data...
select sono,max([so-suffix]), max(solineno),sum(qtyord netamt) from SalesOrder group by sono -- Show quote-oj "helpplease" <helpple***@discussions.microsoft.com> wrote in message news:86D3CABE-A8D7-42F5-887F-6AC2A72098EA@microsoft.com... > Hi, > > I'm having a tough time writing a sql stmt. Any kind of help is very much > appreciated. > > The info is as follows: > > SalesOrder > sono so-suffix solineno qtyord netamt > 1495738 1 33 3 4779.45 > 1495738 0 33 3 0.00 > 1490507 0 15 2 0.00 > 1490507 1 15 2 0.00 > 1490507 2 15 2 0.00 > 1490507 3 15 2 3874.86 > 1497351 0 11 2 0.00 > 1497351 1 11 2 0.00 > 1491566 0 8 2 0.00 > 1491566 1 8 2 0.00 > 1491566 2 8 2 0.00 > 1491566 3 8 2 0.00 > > The purpose of my query is to get the total netamt on a sales order. > > The resultset I'm looking for is, all the salesorder records where netamt > is > <> 0 and if there is a salesorder with 0 netamt, I need to get the last > occurence of that salesorder record to count the total qtyord (which is > the > tricky part). > > So my resultset should be > 1495738 1 33 3 4779.45 > 1490507 3 15 2 3874.86 > & > 1497351 1 11 2 0.00 > 1491566 3 8 2 0.00 > > The first 2 records will be netamt <> 0. The second 2 records will be the > last occurence of the salesorder. The field so-suffix is important here. > > Pleaseeeeeeee help me. > > Thank you so very much. > > I'm not sure who you are, but you are the most intelligent person in this
whole world. I had been pulling out my hair from yesterday. Thank you so much. YOU ROCK!! Show quote "oj" wrote: > this should give the result based on the sample data... > > select sono,max([so-suffix]), max(solineno),sum(qtyord netamt) > from SalesOrder > group by sono > > -- > -oj > > > "helpplease" <helpple***@discussions.microsoft.com> wrote in message > news:86D3CABE-A8D7-42F5-887F-6AC2A72098EA@microsoft.com... > > Hi, > > > > I'm having a tough time writing a sql stmt. Any kind of help is very much > > appreciated. > > > > The info is as follows: > > > > SalesOrder > > sono so-suffix solineno qtyord netamt > > 1495738 1 33 3 4779.45 > > 1495738 0 33 3 0.00 > > 1490507 0 15 2 0.00 > > 1490507 1 15 2 0.00 > > 1490507 2 15 2 0.00 > > 1490507 3 15 2 3874.86 > > 1497351 0 11 2 0.00 > > 1497351 1 11 2 0.00 > > 1491566 0 8 2 0.00 > > 1491566 1 8 2 0.00 > > 1491566 2 8 2 0.00 > > 1491566 3 8 2 0.00 > > > > The purpose of my query is to get the total netamt on a sales order. > > > > The resultset I'm looking for is, all the salesorder records where netamt > > is > > <> 0 and if there is a salesorder with 0 netamt, I need to get the last > > occurence of that salesorder record to count the total qtyord (which is > > the > > tricky part). > > > > So my resultset should be > > 1495738 1 33 3 4779.45 > > 1490507 3 15 2 3874.86 > > & > > 1497351 1 11 2 0.00 > > 1491566 3 8 2 0.00 > > > > The first 2 records will be netamt <> 0. The second 2 records will be the > > last occurence of the salesorder. The field so-suffix is important here. > > > > Pleaseeeeeeee help me. > > > > Thank you so very much. > > > > > > > On Thu, 29 Sep 2005 15:20:01 -0700, helpplease wrote:
Show quote >Hi, Hi helpplease,> >I'm having a tough time writing a sql stmt. Any kind of help is very much >appreciated. > >The info is as follows: > >SalesOrder >sono so-suffix solineno qtyord netamt >1495738 1 33 3 4779.45 >1495738 0 33 3 0.00 >1490507 0 15 2 0.00 >1490507 1 15 2 0.00 >1490507 2 15 2 0.00 >1490507 3 15 2 3874.86 >1497351 0 11 2 0.00 >1497351 1 11 2 0.00 >1491566 0 8 2 0.00 >1491566 1 8 2 0.00 >1491566 2 8 2 0.00 >1491566 3 8 2 0.00 > >The purpose of my query is to get the total netamt on a sales order. > >The resultset I'm looking for is, all the salesorder records where netamt is ><> 0 and if there is a salesorder with 0 netamt, I need to get the last >occurence of that salesorder record to count the total qtyord (which is the >tricky part). > >So my resultset should be >1495738 1 33 3 4779.45 >1490507 3 15 2 3874.86 >& >1497351 1 11 2 0.00 >1491566 3 8 2 0.00 > >The first 2 records will be netamt <> 0. The second 2 records will be the >last occurence of the salesorder. The field so-suffix is important here. > >Pleaseeeeeeee help me. > >Thank you so very much. > Assuming that the "last" occurance of a salesorder is defined by the highest so-suffix value, try this: SELECT a.sono, a.so_suffix, a,solineno, a.qtyord, a.netamt FROM YourTable AS a WHERE a.netamt <> 0 OR NOT EXISTS (SELECT * FROM YourTable AS b WHERE b.sono = a.sono AND ( b.netamt <> 0 OR b.so_suffix > a.so_suffix) (untested - see www.aspfaq.com/5006 if you prefer a tested reply) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) select sono,so-suffix,solineno, qtyord,netamt
from SalesOrder SO1 where netamt<>0 UNION select sono,so-suffix,solineno, qtyord,netamt from SalesOrder SO2 join (select sono,mx-suffix=max(so-suffix) from SalesOrder where netamt=0 group by sono) as SO3 on SO3.sono=SO2.sono and SO2.mx-suffix=SO2.so-suffix left join SalesOrder SO4 on SO4.sono=SO2.sono and SO4.netamt<>0 where SO4.sono is NULL -- prevent 1495738,0 etc order by 1 you have given example of multiple rows with netamt<>0 for same sono - did you want them summed ? Another subquery should crack that if you put another six-pack on the table! Also beware of equality tests on netamt (perhaps you should consider epsilon) HTH Dick Show quote "helpplease" wrote: > Hi, > > I'm having a tough time writing a sql stmt. Any kind of help is very much > appreciated. > > The info is as follows: > > SalesOrder > sono so-suffix solineno qtyord netamt > 1495738 1 33 3 4779.45 > 1495738 0 33 3 0.00 > 1490507 0 15 2 0.00 > 1490507 1 15 2 0.00 > 1490507 2 15 2 0.00 > 1490507 3 15 2 3874.86 > 1497351 0 11 2 0.00 > 1497351 1 11 2 0.00 > 1491566 0 8 2 0.00 > 1491566 1 8 2 0.00 > 1491566 2 8 2 0.00 > 1491566 3 8 2 0.00 > > The purpose of my query is to get the total netamt on a sales order. > > The resultset I'm looking for is, all the salesorder records where netamt is > <> 0 and if there is a salesorder with 0 netamt, I need to get the last > occurence of that salesorder record to count the total qtyord (which is the > tricky part). > > So my resultset should be > 1495738 1 33 3 4779.45 > 1490507 3 15 2 3874.86 > & > 1497351 1 11 2 0.00 > 1491566 3 8 2 0.00 > > The first 2 records will be netamt <> 0. The second 2 records will be the > last occurence of the salesorder. The field so-suffix is important here. > > Pleaseeeeeeee help me. > > Thank you so very much. > >  |
|||||||||||||||||||||||
Other interesting topics