| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-31 : 09:12:20
|
| Cathy writes "I could really use some help with this please...There are more select statements before and after this, but they cannot change, and they are running just fine. Somehow, in the following 2 statements I have to indicate only "distinct" salesorderitems.jobid in the salesorderitems table. Because there could be 2 records for jobid = 100 both with a valid productid number, it is counting the sum twice in the result.& "(select sum(salesorder.totalextension) from salesorder inner join (job inner join salesorderitems on job.jobid = salesorderitems.jobid) on salesorder.jobid = job.jobid where salesorderitems.productid <> '' and salesorderitems.productid <> '0' and job.SalesrepID = '" & rsRep("UniqueRep") & "' and job.JobOrderDate between '" & StartOfMonth & "' and '" & EndOfMonth & "' ) as SCSalesTotal, " _& "(select sum(salesorder.estimatedartcost) from salesorder inner join (job inner join salesorderitems on job.jobid = salesorderitems.jobid) on salesorder.jobid = job.jobid where salesorderitems.productid <> '' and salesorderitems.productid <> '0' and job.SalesrepID = '" & rsRep("UniqueRep") & "' and job.JobOrderDate between '" & StartOfMonth & "' and '" & EndOfMonth & "' ) as SCArtCost " _I really hope someone can help me with this, I have written and rewritten this about 40 times. I can't think anymore. Any help would be so much appreciated.Thanks,Cathy" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-31 : 10:30:51
|
I believe you can group by job.jobid <O> |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-31 : 10:32:56
|
| Oh bueatiful asp... hmm I think I'll need to see the SQL a bit more clearly on this one unfortunately. would it be possible to do the following for us?set somevar = "(select sum(salesorder.totalextension) from salesorder inner join (job inner join salesorderitems on job.jobid = salesorderitems.jobid) on salesorder.jobid = job.jobid where salesorderitems.productid <> '' and salesorderitems.productid <> '0' and job.SalesrepID = '" & rsRep("UniqueRep") & "' and job.JobOrderDate between '" & StartOfMonth & "' and '" & EndOfMonth & "' ) as SCSalesTotal, " _ response.write somevardo this for the second as well... run the page and it will write out the SQL code it's sending to the server. From there I'll be able to trouble shoot it a little easier for you |
|
|
|
Cathy
Starting Member
3 Posts |
Posted - 2002-06-02 : 08:35:37
|
| select sum(salesorder.totalextension) from salesorder inner join (job inner join salesorderitems on job.jobid = salesorderitems.jobid) on salesorder.jobid = job.jobid where salesorderitems.productid <> '' and salesorderitems.productid <> '0' and job.SalesrepID = '11' and job.JobOrderDate between '05/01/2002' and '05/31/2002'The result I am getting from this is 1311.36. I'm trying to get the sum of the salesorders where their associated salesorderitems contain a product id. With this particular rep, he has one salesorder that has 2 product ids and because it contains 2 separate products with their own product ids, it is counting the sum twice. The result of this should be 655.68. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-02 : 08:57:43
|
| Maybe you should try Page47's suggestion...<O> |
|
|
|
Cathy
Starting Member
3 Posts |
Posted - 2002-06-05 : 09:31:27
|
| I did try it, however it didn't make any difference in the result. It is still adding the salesorder sum twice because there are 2 product ids. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-05 : 10:09:27
|
please post your ddl (number 9) so we can better understand your schema....I'll make another blind guess for you, just cause I'm a sucka' . . .create proc cathy1 @jobid int, @start datetime, @end datetimeasselect sum(so.totalextension)from salesore sowhere exists ( select 'bling-bling' from job j inner join salesorderitesm soi on j.jobid = soi.jobid where so.jobid = j.jobid and soi.productid not in('',0) and j.salesrepid = @jobid and j.joborderdate between @start and @end )go <O> |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-05 : 10:16:29
|
| Cathy,First, as a design practice you should not be dynamically creating SQL in an ASP page and executing it. It's much faster and cleaner to create a stored procedure and have your ASP call it w/ the appropriate parameters. It also makes the subsequent debugging of the SQL much easier.Second, give us the select statement with and without the sum() so we can see the rowset that's being aggregated.Third, post the DDL for all tables involved. Remember that your audience has no idea what problem you're facing, what relationships exist between your tables, what your table names even mean ... only the DDL provides an unambiguous description of what's going on. Page's advice is good advice.setBasedIsTheTruepath<O> |
|
|
|
Cathy
Starting Member
3 Posts |
Posted - 2002-06-05 : 11:44:28
|
| I was actually successful in reworking the statement :select sum(salesorder.totalextension) from salesorder where salesorder.jobid IN(select distinct salesorderitems.jobid from salesorderitems inner join job on salesorderitems.jobid = job.jobid and job.salesrepID = '2' and job.joborderdate between '05/01/2002' and '05/31/2002' and salesorderitems.productid <> '' and salesorderitems.productid <> '0')Thanks so much for all of your help with this.Cathy |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-05 : 12:06:00
|
| It looks like your working solution is very similar to the 2nd guess.I would suggest trying both and looking at performance as generally a WHERE NOT EXISTS will outperform a WHERE NOT IN ... (but not always)<O> |
|
|
|
|
sql inner join with unique field