Another advanced query ....

Last Post 11 Jul 2008 09:06 AM by GoodFella3993. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
GoodFella3993
New Member
New Member

--
23 Aug 2007 10:20 AM
I have a table ...

Store | Customer | TotalSpent


This table consists of 100 stores.
Each store has about 4,000 customers.

I am trying to find a way to select the 500 best customers per store (Highest TotalSpent).

One way is to have 100 queries with each store as a criteria, saying 'TOP 500', and sorting the data.

My question ...
Is there a way to have one query that will select the top 500 performing customers for EACH store?

Thank You.
SwePeso
New Member
New Member

--
24 Aug 2007 01:39 AM
-- prepare sample data
declare @BaseTable table (CustNo int, StoreNo int, TransDate char(10), NumberOfTrans int, TotalNetRetail decimal (12,2))

insert @BaseTable values (962,9412,'2006-10-07',1,159.00)
insert @BaseTable values (962,9144,'2007-06-15',4,203.43)
insert @BaseTable values (961,9412,'2006-10-07',1,159.00)
insert @BaseTable values (961,9144,'2007-06-15',1,203.43)
insert @BaseTable values (960,9412,'2006-10-07',2,203.43)
insert @BaseTable values (960,9144,'2007-06-15',1,159.00)
insert @BaseTable values (952,9412,'2006-10-07',1,259.00)
insert @BaseTable values (952,9144,'2007-06-15',4,303.43)
insert @BaseTable values (951,9412,'2006-10-07',1,259.00)
insert @BaseTable values (951,9144,'2007-06-15',1,303.43)
insert @BaseTable values (950,9412,'2006-10-07',2,259.00)
insert @BaseTable values (950,9144,'2007-06-15',1,303.43)

-- show the expected output
SELECT t1.StoreNo,
t1.CustNo
FROM @BaseTable AS t1
WHERE t1.CustNo IN (SELECT TOP 500 t2.CustNo FROM @BaseTable AS t2 WHERE t2.StoreNo = t1.StoreNo GROUP BY t2.CustNo ORDER BY SUM(NumberOfTrans) DESC, SUM(TotalNetRetail))
ORDER BY t1.StoreNo,
2

GoodFella3993
New Member
New Member

--
25 Aug 2007 05:19 AM
Peter this is great. I really appreciate it.

I am able to run this query fine when I limit the 'BaseTable'to 3 stores worth of data. However when I have all the stores data there are over 1,000,000 rows. The query is taking well over 5 hours and I have canceled it at that point.

Would you recommend Indexing this table? I am very new to indexes. Doing some research online, it seems creating a Non Clustered Index on CustNo and a Clustered Index on StoreNo would be the way to go.

Thanks again Peter
SwePeso
New Member
New Member

--
25 Aug 2007 03:15 PM
-- prepare sample data
declare @BaseTable table (CustNo int, StoreNo int, TransDate char(10), NumberOfTrans int, TotalNetRetail decimal (12,2))

insert @BaseTable values (962,9412,'2006-10-07',1,159.00)
insert @BaseTable values (962,9144,'2007-06-15',4,203.43)
insert @BaseTable values (961,9412,'2006-10-07',1,159.00)
insert @BaseTable values (961,9144,'2007-06-15',1,203.43)
insert @BaseTable values (960,9412,'2006-10-07',2,203.43)
insert @BaseTable values (960,9144,'2007-06-15',1,159.00)
insert @BaseTable values (952,9412,'2006-10-07',1,259.00)
insert @BaseTable values (952,9144,'2007-06-15',4,303.43)
insert @BaseTable values (951,9412,'2006-10-07',1,259.00)
insert @BaseTable values (951,9144,'2007-06-15',1,303.43)
insert @BaseTable values (950,9412,'2006-10-07',2,259.00)
insert @BaseTable values (950,9144,'2007-06-15',1,303.43)

SELECT StoreNo,
CustNo,
NumberOfTrans,
TotalNetRetail
FROM (
SELECT StoreNo,
CustNo,
ROW_NUMBER() OVER (PARTITION BY StoreNo ORDER BY SUM(NumberOfTrans), SUM(TotalNetRetail)) AS RecID,
SUM(NumberOfTrans) AS NumberOfTrans,
SUM(TotalNetRetail) AS TotalNetRetail
FROM @BaseTable
GROUP BY StoreNo,
CustNo
) AS d
WHERE RecID BETWEEN 1 AND 2
ORDER BY StoreNo,
CustNo
GoodFella3993
New Member
New Member

--
11 Jul 2008 09:06 AM
Hi guys,

Working on a new project and am using the code in this thread as a basis.

INCOMMING DATA

CAMPAIGN_COUNTRY | CAMPAIGN_SOURCE_CODE | CAMPAIGN_CODE
___canada_________________ABC_______________D-CA-3-102V79A
___canada_________________ABC_______________D-LLH
___usa____________________ABC_______________D-CA-3-102V79A




________________________________________________________

QUERY:

select case (select top 1 t2.campaign_code
from mapping_test as t2
where t2.Campaign_Source_Code = t1.Campaign_Source_Code

group by t2.campaign_code
order by len(campaign_code) desc)
when t1.campaign_code then 1
else 0
end as [primary],
t1.campaign_country, t1.hp_campaign_source_code, t1.campaign_code
from mapping_test as t1
where campaign_source_code <> ''
order by Campaign_Source_Code, [primary]

________________________________________________________


OUTPUT:

PRIMARY | CAMPAIGN_COUNTRY | CAMPAIGN_SOURCE_CODE | CAMPAIGN_CODE
__0_________canada_________________ABC_______________D-CA-3-102V79A
__0_________canada_________________ABC_______________D-LLH
__1_________usa____________________ABC_______________D-CA-3-102V79A


OUTPUT SHOULD BE:

PRIMARY | CAMPAIGN_COUNTRY | CAMPAIGN_SOURCE_CODE | CAMPAIGN_CODE
__1_________canada_________________ABC_______________D-CA-3-102V79A
__0_________canada_________________ABC_______________D-LLH
__1_________usa____________________ABC_______________D-CA-3-102V79A



I am running to an issue of the grouping of the campaign country. It should consider only records that have matching campaign counries before designating them as primary.

Any suggestions?

Thank You,
Marc
GoodFella3993
New Member
New Member

--
11 Jul 2008 09:09 AM
I think i just figured it out after typing it all out ......



where t2.Campaign_Source_Code = t1.Campaign_Source_Code
and t2.campaign_country = t1.campaign_country
You are not authorized to post a reply.

Acceptable Use Policy