aggregate query question

Question

aggregate query question

mrpolecat

SSCertifiable

Points: 7014
More actions
September 20, 2007 at 9:47 am

#118624

Result Needed CustomerNumber,StoreMostUsed
Business Rule in event of tie:
Primary Store if in the tie else random choice from tie

Two tables
Table A
CustomerNumber (Unique)
PrimaryStore

Table B (already aggregated from transaction data)
CustomerNumber (Not Unique)
StoreUsed
Times Used

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Can you post sample DDL and data along with the expected results from your sample data? It would help if you epand on the tie breaker as well - include it in your example data.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Loner SSC-Insane Points: 21279 More actions · Answer 2

The result you needed CustomerNumber, StoreMostUsed. Is the primary store that you want? Otherwise, if the TableB already aggregated from the transaction data, you can just select the max of Times used for the customer.

Also TableB the customerNumber is not unique, but is the storeUsed unique?

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

a customer number is an integer, a store number is an integer, storeusedtimes is an an integer

Table A

CustomerNumber,Store Number

1,1

2,1

3,2

4,4

5,2

TableB

CustomerNumber,StoreUsed,TimesUsed

1,1,5

1,2,5

1,3,1

2,1,1

2,2,1

2,3,1

3,2,1

3,5,5

3,4,5

4,1,1

4,2,2

4,3,3

Results

CustomerNUmber,StoreMostUsed

1,1 PrimaryStore in tie

2,2 Primary Store in tie

3,5 PrimaryStore not in tie

4,3 No Tie

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

Loner-

In tableB the combination of cutomernumber and store is unique. It was created from a transaction table that contained a date and time for each store use.

I want the primary store if two stores are used equally and the most and the primary store is one of those.

Hopefully my post to John's request will clarify yours as well.

Vladan SSC-Insane Points: 22072 More actions · Answer 5

mrpolecat,

unfortunately you have typo either in the data or in the desired result. For example, Customer number 2 has PrimaryStore 1, but in result you wish to display Store 2. I hope I understood correctly what you need...

Let's try whether this solves your problem:

/*tables for testing*/

CREATE TABLE #TableA (CustomerNumber INT, PrimaryStore INT)

CREATE TABLE #TableB (CustomerNumber INT, StoreUsed INT, TimesUsed INT)

/*test values*/

INSERT INTO #TableA (CustomerNumber, PrimaryStore) VALUES (1,1)

INSERT INTO #TableA (CustomerNumber, PrimaryStore) VALUES (2,1)

INSERT INTO #TableA (CustomerNumber, PrimaryStore) VALUES (3,2)

INSERT INTO #TableA (CustomerNumber, PrimaryStore) VALUES (4,4)

INSERT INTO #TableA (CustomerNumber, PrimaryStore) VALUES (5,2)

INSERT INTO #TableB (CustomerNumber, StoreUsed, TimesUsed) VALUES (1,1,5)

INSERT INTO #TableB (CustomerNumber, StoreUsed, TimesUsed) VALUES (1,2,5)

INSERT INTO #TableB (CustomerNumber, StoreUsed, TimesUsed) VALUES (1,3,1)

INSERT INTO #TableB (CustomerNumber, StoreUsed, TimesUsed) VALUES (2,1,1)

INSERT INTO #TableB (CustomerNumber, StoreUsed, TimesUsed) VALUES (2,2,1)

INSERT INTO #TableB (CustomerNumber, StoreUsed, TimesUsed) VALUES (2,3,1)

INSERT INTO #TableB (CustomerNumber, StoreUsed, TimesUsed) VALUES (3,2,1)

INSERT INTO #TableB (CustomerNumber, StoreUsed, TimesUsed) VALUES (3,5,5)

INSERT INTO #TableB (CustomerNumber, StoreUsed, TimesUsed) VALUES (3,4,5)

INSERT INTO #TableB (CustomerNumber, StoreUsed, TimesUsed) VALUES (4,1,1)

INSERT INTO #TableB (CustomerNumber, StoreUsed, TimesUsed) VALUES (4,2,2)

INSERT INTO #TableB (CustomerNumber, StoreUsed, TimesUsed) VALUES (4,3,3)

/*result*/

SELECT b.CustomerNumber, b.TimesUsed, COALESCE(MAX(a.PrimaryStore), MAX(b.StoreUsed)) as MostUsedStore

FROM #TableB b

JOIN (SELECT CustomerNumber, MAX(TimesUsed) as maxused

FROM #tableB

GROUP BY CustomerNumber) as maxtbl ON maxtbl.maxused=b.TimesUsed AND maxtbl.CustomerNumber=b.CustomerNumber

LEFT JOIN #TableA a ON a.CustomerNumber=b.CustomerNumber AND a.PrimaryStore=b.StoreUsed

GROUP BY b.CustomerNumber, b.TimesUsed

ORDER BY b.CustomerNumber

/*cleanup*/

DROP TABLE #TableA

DROP TABLE #TableB

CustomerNumber TimesUsed MostUsedStore

-------------- ----------- -------------

1 5 1

2 1 1

3 5 5

4 3 3

(4 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.

PS: I supposed that "random choice" means that it does not matter which of the tied stores will be shown. When selecting from the same data, it will always be the same store.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 6

you are correct about the typo. Since this is sample data we'll go with an incorrect result set, which makes your result set correct. You are also correct that random choice really means coders choice and the result set should be reproducable. I am out of the office today but I will try your approach on Monday. Thanks for the help.