Home SQL over clause PARTITION BY
Reply: 0

SQL over clause PARTITION BY

user5258
1#
user5258 Published in September 19, 2018, 11:02 am

Struggling to get the over partition by clause working with multiple tables that I want the sum values from.

Example: Table1

Uidx as autonumber,
RepNumber int,
TransDT smalldatetime,
ProductNumber int,
Cost money,

Table2

Uidx as autonumber,
T1Uidx int,
PaymentAmount money,

Select RepNumber,ProductNumber,
TotalCost = sum(Cost)  OVER (PARTITION BY RepNumber) ,
TotalPayments = IsNull(sum(PaymentAmount),0)  OVER (PARTITION BY RepNumber) 
from Table1 Left outer join
     Table2 on Table2.T1Uidx = Table1.Uidx
Where TransDT between '3/1/2018' and '3/31/2018'

'Once I add the Left outer Join to the query it takes FOREVER!. 'I checked the indexs 'I do have some null values (I can use isnull around the payment field) 'Table size Table1 about 32million 'Table size Table2 about 100million

I would like an output similiar to:

RepNumber,ProductNumber,TransDT,TotalCost,TotalPayments
123,999,1/1/2018,50.00,25.00
123,999,1/2/2018,50.00,5.00
456,222,1/4/2018,50.00,40.00
456,333,1/5/2018,50.00,10.00

Any suggestion would be great,.

As long as I DO NOT include the left outer inner works like a champ. However as soon as I include the left outer join it gets very slow. I would apperciate any advice you may give.

share|improve this question

1 Answer 1

active oldest votes
up vote 1 down vote
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.338165 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO