I'm so close..... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

I'm so close.....

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2003-05-30 : 11:27:31

Here's my query:


select distinct(partnumber), convert(money, sum(totalsalenet)) as [Total Sales], 
	c.customer_name, count(et.username) as [Total Hits], 
	min(et.requestdatetime) as [First hit], max(et.requestdatetime) as [Last Hit] from sales_history s
join 
customer_data c on 
	s.accountnumber=c.accountnumber
join 
smc_etech_key se on 
	c.accountnumber=se.accountnumber
join 
smc_new_products.dbo.usr_smc us on 
	se.etech_customer_id=us.unique_id
left outer join 
smc_new_products.dbo.etechmodelrequests et on
	convert(varchar(50), us.[user_id])=et.username
	and s.partnumber=et.configname
	and requestdatetime between '1/1/2002' and '12/31/2002'
	and interfacename like '%down%'
where billedyear=2002
group by partnumber, customer_name
order by c.customer_name

Everything works except for the "count(et.username) as [Total Hits]" field.

What happens is this: if there are 10 instances of a specific partnumber in the "sales_history" field, it multiplies whatever my [Total Hits] are by that number giving me (in this case) 10 times the amount of hits I actually had. So if I had 3 actual hits from the Etechmodelrequest table from my "count(er.username)" field, I would end up with 30 because it aggregates each instance of 3 times the amount of occurences of the partnumber it's aggregating.

Is there anyway to stop this from happening?

-Thanks.

cas_o
Posting Yak Master

154 Posts

Posted - 2003-05-30 : 11:43:30

You need a sub query inside your from clause that groups the parts before you count hits.

Or you could create a temp table, and break your code down into smaller more manageable chunks and insert your counts and sums one at a time.

;-]

label
Posting Yak Master

197 Posts

Posted - 2003-05-30 : 12:52:04

quote:

You need a sub query inside your from clause that groups the parts before you count hits.

I've tried coming up with something like that but couldn't get it to work. How would your solution look?

quote:
Or you could create a temp table, and break your code down into smaller more manageable chunks and insert your counts and sums one at a time.

I'd really like to avoid looping through a cursor as there are several requirements, not just one, that each row that is inserted would have to be subjected to.

For instance, a result set looks like this:

quote:

NVHS3000-N03 | 1132.2000 | AIRLINE HYDRAULICS CORPORATION | 17 | 2002-10-17 10:55:31.683 | 2002-10-17 10:55:31.683

I put the "|" in there to help dilineate the columns. Basically, this shows me that Airline Hydraulics made 1132.20 of purchases for that part number in the year 2002.

The "17" should be only "1", but since there were 17 transactions in the sales_history table, it multiplied my [Total Hits] by that number.

If I were to loop through the results, I'd have to do it not only by part number, but also which company and the datetime they purchased it as well which would leave me with at least one nested cursor loop inside another one.

Like I said, I'm very close here, if I can just get the [Total hits], I'm good to go.

Edited by - label on 05/30/2003 12:52:20

label
Posting Yak Master

197 Posts

Posted - 2003-05-30 : 15:01:50

No luck yet on my end, I know it's late friday, still, I'd really like to get this working today if anyone's got a suggestion....

Thanks.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-30 : 15:13:09

post your ddl, some sample data and sample results and I'll see what I can do.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 15:16:07

quote:

if anyone's got a suggestion....

Consume mass quantites of BEER

Brett

8-)

label
Posting Yak Master

197 Posts

Posted - 2003-05-30 : 17:23:55

quote:

post your ddl, some sample data and sample results and I'll see what I can do.

Ok, but it's going to have to be monday as I got pulled off this to put out a fire this afternoon.

However, I very much appreciate the offer and will post it as soon as I can.

Thanks.

label
Posting Yak Master

197 Posts

Posted - 2003-05-30 : 17:24:29

quote:

quote:

if anyone's got a suggestion....

Consume mass quantites of BEER

Brett

8-)

Lol ;) That's definitely in the plans tonight after banging my head against the wall all day.

cas_o
Posting Yak Master

154 Posts

Posted - 2003-06-02 : 03:41:07

select
subgroup.partnumber,subgroup.[Total Sales],subgroup.customer_name,
count(et.username) as [Total Hits],
min(et.requestdatetime) as [First hit], max(et.requestdatetime) as [Last Hit]
from
(
select distinct convert(varchar(50), us.[user_id]) [user id], (partnumber), convert(money, sum(totalsalenet)) as [Total Sales],
c.customer_name
from
sales_history s
join
customer_data c on
s.accountnumber=c.accountnumber
join
smc_etech_key se on
c.accountnumber=se.accountnumber
join
smc_new_products.dbo.usr_smc us on
se.etech_customer_id=us.unique_id
where billedyear=2002
group by [user id],partnumber, customer_name
)subgroup

left outer join
smc_new_products.dbo.etechmodelrequests et on
subgroup.[user_id])=et.username
and subgroup.partnumber=et.configname
and requestdatetime between '1/1/2002' and '12/31/2002'
and interfacename like '%down%'

group by
subgroup.partnumber,subgroup.[Total Sales],subgroup.customer_name
order by
subgroup.customer_name

Let me know what happens.

;-]

label
Posting Yak Master

197 Posts

Posted - 2003-06-02 : 08:32:36

quote:

select
subgroup.partnumber,subgroup.[Total Sales],subgroup.customer_name,
count(et.username) as [Total Hits],
min(et.requestdatetime) as [First hit], max(et.requestdatetime) as [Last Hit]
from
(
select distinct convert(varchar(50), us.[user_id]) [user id], (partnumber), convert(money, sum(totalsalenet)) as [Total Sales],
c.customer_name
from
sales_history s
join
customer_data c on
s.accountnumber=c.accountnumber
join
smc_etech_key se on
c.accountnumber=se.accountnumber
join
smc_new_products.dbo.usr_smc us on
se.etech_customer_id=us.unique_id
where billedyear=2002
group by [user id],partnumber, customer_name
)subgroup

left outer join
smc_new_products.dbo.etechmodelrequests et on
subgroup.[user_id])=et.username
and subgroup.partnumber=et.configname
and requestdatetime between '1/1/2002' and '12/31/2002'
and interfacename like '%down%'

group by
subgroup.partnumber,subgroup.[Total Sales],subgroup.customer_name
order by
subgroup.customer_name

Let me know what happens.

;-]

I got this error:

quote:

Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near ')'.

I pasted it without any modifications into the Query Analyzer......

cas_o
Posting Yak Master

154 Posts

Posted - 2003-06-02 : 09:47:24

Ooops, I forgot to delete a small bracket on line 25 of the statement, try this revised sql:

select
subgroup.partnumber,subgroup.[Total Sales],subgroup.customer_name,
count(et.username) as [Total Hits],
min(et.requestdatetime) as [First hit], max(et.requestdatetime) as [Last Hit]
from
(
select distinct convert(varchar(50), us.[user_id]) [user id], (partnumber), convert(money, sum(totalsalenet)) as [Total Sales],
c.customer_name
from
sales_history s
join
customer_data c on
s.accountnumber=c.accountnumber
join
smc_etech_key se on
c.accountnumber=se.accountnumber
join
smc_new_products.dbo.usr_smc us on
se.etech_customer_id=us.unique_id
where billedyear=2002
group by [user id],partnumber, customer_name
)subgroup

left outer join
smc_new_products.dbo.etechmodelrequests et on
subgroup.[user_id]=et.username
and subgroup.partnumber=et.configname
and requestdatetime between '1/1/2002' and '12/31/2002'
and interfacename like '%down%'

group by
subgroup.partnumber,subgroup.[Total Sales],subgroup.customer_name
order by
subgroup.customer_name

;-]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources