September 6, 2016 at 3:51 pm
I need help writing a query which retrieves all permutations of a combination of the values: instrument_id, scoring_id, style_id.
I have 3 tables: TopSellerInstrumentsLastWeek, TopSellerScoringLastWeek, TopSellerStyleLastWeek
I have come up with 7 possible permutations, I am using the column name:
instrument_id
instrument_id, scoring_id
instrument_id, style_id
scoring_id
style_id, scoring_id
style_id
instrument_id, style_id, scoring_id
I need to be able to find the Top 5 sellers using all the permutations listed above. Regardless of the combination, there should always be 5 results. This should be a stored procedure that takes in all possible combinations.
I am including data to populate the 3 tables, and also the create table scripts. Not all the tables contain the same amount of data, instruments is the table that has more data than any other.
Any help I can get, would mean the world, since I have been struggling understanding how to write a query like this one. I hope I am explaining my problem clearly, but if I am not, please let me know and I can try to provide more details to this.
Thank you very much.
September 6, 2016 at 4:15 pm
20,000+ rows does not constitute SAMPLE data. If you need more than 100 rows to illustrate your problem, then it's too complex for a free forum. Why don't you try again with a reasonable amount of data? And don't put it in a file this time.
And include expected results.
It's also not clear what the relationship between the files is, if any, and therefore, how they should be joined together. I suspect that this data has already been summarized, and that the link between the files has been lost in that summarization.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 6, 2016 at 4:41 pm
Ditto everything Drew said. We'll need more info to help you.
Perhaps something like this (I did a few permutations based on my best guess of what you need).
SELECT TOP (5) instrument_id
FROM
(
SELECT DISTINCT i.*, scoring_id, style_id
FROM dbo.TopSellerInstrumentsLastWeek i
LEFT JOIN dbo.TopSellerScoringLastWeek sc ON i.pf_id = sc.pf_id
LEFT JOIN dbo.TopSellerStyleLastWeek st ON i.pf_id = sc.pf_id
) x
GROUP BY instrument_id
ORDER BY SUM(purchases) DESC
SELECT TOP (5) instrument_id, scoring_id, SUM(purchases)
FROM
(
SELECT DISTINCT i.*, scoring_id, style_id
FROM dbo.TopSellerInstrumentsLastWeek i
LEFT JOIN dbo.TopSellerScoringLastWeek sc ON i.pf_id = sc.pf_id
LEFT JOIN dbo.TopSellerStyleLastWeek st ON i.pf_id = sc.pf_id
) x
GROUP BY instrument_id, scoring_id
ORDER BY SUM(purchases) DESC
SELECT TOP (5) instrument_id, style_id, SUM(purchases)
FROM
(
SELECT DISTINCT i.*, style_id
FROM dbo.TopSellerInstrumentsLastWeek i
LEFT JOIN dbo.TopSellerScoringLastWeek sc ON i.pf_id = sc.pf_id
LEFT JOIN dbo.TopSellerStyleLastWeek st ON i.pf_id = sc.pf_id
) x
GROUP BY instrument_id, style_id
ORDER BY SUM(purchases) DESC
-- Itzik Ben-Gan 2001
September 6, 2016 at 8:33 pm
The tables and data you included look like result sets based on each of the elements. To figure out permutations don't you need to start with the the original data that produced these result sets?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 7, 2016 at 10:36 am
Hello, I think you are correct. I just do not know how to include all the tables and data involved in the creation of these "smaller" sets and ask my question in a reasonable fashion without abusing the people that kindly offer help in this forum.
September 7, 2016 at 10:49 am
itortu (9/7/2016)
Hello, I think you are correct. I just do not know how to include all the tables and data involved in the creation of these "smaller" sets and ask my question in a reasonable fashion without abusing the people that kindly offer help in this forum.
this article clearly guides you on how to present sample data etc
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 7, 2016 at 12:49 pm
This is presented completely different than the ask on the MSDN forum... https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0b897a76-3d65-476d-8f40-4f2242629da0/write-query-with-all-possible-permutations?forum=transactsql
Any way... Try this...
USE tempdb;
GO
/*
CREATE TABLE dbo.TopSellerInstrumentsLastWeek
(
RegionCHAR(2)
,SalesRankINT
,pf_idVARCHAR(15)
,PurchasesDECIMAL(18,4)
,PageIDINT
,instrument_idINT
);
CREATE TABLE dbo.TopSellerScoringLastWeek
(
RegionCHAR(2)
,SalesRankINT
,pf_idVARCHAR(15)
,PurchasesDECIMAL(18,4)
,PageIDINT
,scoring_idINT
);
CREATE TABLE dbo.TopSellerStyleLastWeek
(
RegionCHAR(2)
,SalesRankINT
,pf_idVARCHAR(15)
,PurchasesDECIMAL(18,4)
,PageIDINT
,style_idINT
);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 1, 'MN0163104', 284.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 2, 'MN0163432', 32.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 3, 'MN0075262', 27.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 4, 'MN0160075', 26.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 5, 'MN0166341', 25.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 6, 'MN0163273', 22.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 7, 'MN0162362', 22.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 8, 'MN0053712', 20.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 9, 'MN0053698', 18.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 10, 'MN0164752', 18.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 11, 'MN0161647', 17.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 12, 'MN0118860', 17.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 13, 'MN0142069', 17.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 14, 'MN0042761', 16.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 15, 'MN0163979', 16.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 16, 'MN0048698', 15.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 17, 'MN0137618', 15.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 18, 'MN0125461', 14.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 19, 'MN0164195', 14.0000, 1, 21);
INSERT INTO TopSellerStyleLastWeek VALUES (1 , 20, 'MN0093414', 13.0000, 1, 21);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 1, 'MN0163104', 284.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 2, 'MN0074193', 196.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 3, 'MN0162044', 157.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 4, 'MN0161860', 98.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 5, 'MN0097929', 97.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 6, 'MN0093373', 94.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 7, 'MN0160887', 78.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 8, 'MN0161874', 74.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 9, 'MN0157299', 61.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 10, 'MN0052128', 60.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 11, 'MN0123125', 59.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 12, 'MN0101019', 52.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 13, 'MN0114444', 44.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 14, 'MN0166386', 44.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 15, 'MN0165177', 43.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 16, 'MN0074197', 43.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 17, 'MN0127731', 41.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 18, 'MN0163998', 41.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 19, 'MN0163560', 40.0000, 1, 285);
INSERT INTO TopSellerScoringLastWeek VALUES (1 , 20, 'MN0038918', 39.0000, 1, 285);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 1, 'MN0163104', 284.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 2, 'MN0074193', 196.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 3, 'MN0162044', 157.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 4, 'MN0155994', 114.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 5, 'MN0161860', 98.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 6, 'MN0097929', 97.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 7, 'MN0093373', 94.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 8, 'MN0059679', 82.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 9, 'MN0160887', 78.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 10, 'MN0163254', 75.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 11, 'MN0161874', 74.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 12, 'MN0076745', 73.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 13, 'MN0164753', 61.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 14, 'MN0157299', 61.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 15, 'MN0052128', 60.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 16, 'MN0123125', 59.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 17, 'MN0073222', 58.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 18, 'MN0101019', 52.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 19, 'MN0113731', 50.0000, 1, 17);
INSERT INTO TopSellerInstrumentsLastWeek VALUES (1 , 20, 'MN0075906', 50.0000, 1, 17);
SELECT * FROM dbo.TopSellerInstrumentsLastWeek ;
SELECT * FROM dbo.TopSellerScoringLastWeek ;
SELECT * FROM dbo.TopSellerStyleLastWeek ;
*/
WITH
cte_Instruments AS (
SELECT TOP 5
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
i.pf_id,
Purchases = SUM(i.Purchases)
FROM
dbo.TopSellerInstrumentsLastWeek i
GROUP BY
i.pf_id
ORDER BY
SUM(i.Purchases) DESC
),
cte_Scoring AS (
SELECT TOP 5
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
sc.pf_id,
Purchases = SUM(sc.Purchases)
FROM
dbo.TopSellerScoringLastWeek sc
GROUP BY
sc.pf_id
ORDER BY
SUM(sc.Purchases) DESC
),
cte_Style AS (
SELECT TOP 5
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
st.pf_id,
Purchases = SUM(st.Purchases)
FROM
dbo.TopSellerStyleLastWeek st
GROUP BY
st.pf_id
ORDER BY
SUM(st.Purchases) DESC
),
cte_Instruments_Score AS (
SELECT TOP 5
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
isc.pf_id,
Purchases = SUM(isc.Purchases)
FROM (
SELECT i.pf_id, i.Purchases FROM dbo.TopSellerInstrumentsLastWeek i
UNION
SELECT sc.pf_id, sc.Purchases FROM dbo.TopSellerScoringLastWeek sc
) isc
GROUP BY
isc.pf_id
ORDER BY
SUM(isc.Purchases) DESC
),
cte_Instruments_Style AS (
SELECT TOP 5
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
ist.pf_id,
Purchases = SUM(ist.Purchases)
FROM (
SELECT i.pf_id, i.Purchases FROM dbo.TopSellerInstrumentsLastWeek i
UNION
SELECT st.pf_id, st.Purchases FROM dbo.TopSellerStyleLastWeek st
) ist
GROUP BY
ist.pf_id
ORDER BY
SUM(ist.Purchases) DESC
),
cte_Score_Style AS (
SELECT TOP 5
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
scst.pf_id,
Purchases = SUM(scst.Purchases)
FROM (
SELECT sc.pf_id, sc.Purchases FROM dbo.TopSellerScoringLastWeek sc
UNION
SELECT st.pf_id, st.Purchases FROM dbo.TopSellerStyleLastWeek st
) scst
GROUP BY
scst.pf_id
ORDER BY
SUM(scst.Purchases) DESC
),
cte_Instruments_Score_Style AS (
SELECT TOP 5
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
iscst.pf_id,
Purchases = SUM(iscst.Purchases)
FROM (
SELECT i.pf_id, i.Purchases FROM dbo.TopSellerInstrumentsLastWeek i
UNION
SELECT sc.pf_id, sc.Purchases FROM dbo.TopSellerScoringLastWeek sc
UNION
SELECT st.pf_id, st.Purchases FROM dbo.TopSellerStyleLastWeek st
) iscst
GROUP BY
iscst.pf_id
ORDER BY
SUM(iscst.Purchases) DESC
),
cte_JustFive AS (
SELECT rn FROM (VALUES (1), (2), (3), (4), (5)) x (rn)
)
SELECT
jf.rn,
InstrumentsID = i.pf_id,
InstrumentsPurch = i.Purchases,
ScoreID = sc.pf_id,
ScorePurch = sc.Purchases,
StypeID = st.pf_id,
StypePurch = st.Purchases,
Instrument_ScoreID = isc.pf_id,
Instrument_ScorePurch = isc.Purchases,
Instrument_StyleID = ist.pf_id,
Instrument_StylePurch = ist.Purchases,
Score_StyleID = scst.pf_id,
Score_StylePurch = scst.Purchases,
Instrument_Score_StyleID = iscst.pf_id,
Instrument_Score_StylePurch = iscst.Purchases
FROM
cte_JustFive jf
JOIN cte_Instruments i
ON jf.rn = i.rn
JOIN cte_Scoring sc
ON jf.rn = sc.rn
JOIN cte_Style st
ON jf.rn = st.rn
JOIN cte_Instruments_Score isc
ON jf.rn = isc.rn
JOIN cte_Instruments_Style ist
ON jf.rn = ist.rn
JOIN cte_Score_Style scst
ON jf.rn = scst.rn
JOIN cte_Instruments_Score_Style iscst
ON jf.rn = iscst.rn
ORDER BY
jf.rn;
September 7, 2016 at 1:14 pm
Are you by any chance looking for this?
SELECT TOP(5) * FROM TopSellerInstrumentsLastWeek i
FULL OUTER JOIN TopSellerScoringLastWeek sc
ON i.pf_id = sc.pf_id
FULL OUTER JOIN TopSellerStyleLastWeek st
ON i.pf_id = st.pf_id
OR sc.pf_id = st.pf_id
ORDER BY COALESCE(i.Purchases, sc.Purchases, st.Purchases) DESC
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 8, 2016 at 8:45 am
Hello, sorry I did not respond to your comment before, my son got sick and had to take a day off. I am going to look at your code and see what you are doing, and try it. I hope I can make it work. Is there any information I can offer so to make sure I am giving all details I can possible offer? Thank you very much for your help. I am going to start working on this now.
September 8, 2016 at 10:37 am
Hello Jason, I have a question, I checked the results that your code returns and I think it does A LOT of what I need to have as a result. I would like to ask you if this code could work as an stored procedure that receives any combination of parameters to produce a specific combination and its results. Thank you for you help, and your patience with my lack of details and overwhelming load of data.
September 8, 2016 at 11:06 am
itortu (9/8/2016)
Hello Jason, I have a question, I checked the results that your code returns and I think it does A LOT of what I need to have as a result. I would like to ask you if this code could work as an stored procedure that receives any combination of parameters to produce a specific combination and its results. Thank you for you help, and your patience with my lack of details and overwhelming load of data.
No problem...
Making into a stored proc that accepts parameters isn't a problem... Allowing the user to pick and choose the various combinations could be made to work using dynamic sql.
If you're just wanting to display the particular combination that the user selects, this should be fairly simple.
If you want to display ALL possible combinations that exist within the selected categories, that may get painful... If so, are you limiting it to just these 3 tables? The reason I ask is that you'd want to code out ALL possible combinations and then turn different parts on and off based on the chosen parameter options. The fact that 3 tables generated 7 combinations should illustrate that this can grow out of hand very quickly if you start adding more tables.
September 8, 2016 at 11:12 am
Just a heads up... I do see a screw up in the code I posted yesterday... All the UNIONs should actually be UNION ALLs...
September 8, 2016 at 12:03 pm
If it's the "just the specific combination that's requested" version, something like the following should work...
SET NOCOUNT ON;
DECLARE
@TablesToUse VARCHAR(50) = 'Instruments, Scoring, Style',
@DeBug BIT = 1;
IF OBJECT_ID('tempdb..#TablesToUse', 'U') IS NOT NULL
DROP TABLE #TablesToUse;
CREATE TABLE #TablesToUse (
TableName VARCHAR(255) NOT NULL,
TableAlias CHAR(3)
);
DECLARE @Split VARCHAR(1000) = CONCAT('
SELECT
TableNmae = CASE x.TableName
WHEN ''Instruments'' THEN ''dbo.TopSellerInstrumentsLastWeek''
WHEN ''Scoring'' THEN ''dbo.TopSellerScoringLastWeek''
WHEN ''Style'' THEN ''dbo.TopSellerStyleLastWeek''
END,
TableAlias = LOWER(LEFT(x.TableName, 3))
FROM ( VALUES (''', REPLACE(REPLACE(@TablesToUse, ' ', ''), ',', '''),('''), ''')) x (TableName);');
INSERT #TablesToUse (TableName,TableAlias)
EXEC(@Split);
DECLARE @sql VARCHAR(8000) = '';
SELECT
@sql = CONCAT(@sql, CHAR(13), 'UNION ALL', CHAR(13), 'SELECT ', ttu.TableAlias, '.pf_id, ', ttu.TableAlias, '.Purchases FROM ', ttu.TableName, ' ', ttu.TableAlias)
FROM
#TablesToUse ttu;
SET @sql = CONCAT('
SELECT TOP(5)
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
x.pf_id,
Purchases = SUM(x.Purchases)
FROM (', STUFF(@sql, 1, 11, ''), '
) x
GROUP BY
x.pf_id
ORDER BY
SUM(x.Purchases) DESC;');
IF @DeBug = 1
BEGIN
PRINT(@sql);
END;
ELSE
BEGIN
EXEC(@sql);
END;
September 8, 2016 at 1:07 pm
Hello Jason,
I am not sure if I completely follow what your comments regarding the parameters are, but let me give it a try.
The situation is that this stored procedure will ultimately be executed from a web page, this page will contain 3 drop down lists that will act as the selection criteria for the result set.
ddl1: instrument (displays text, passes integer to the sp)
ddl2: scoring (displays text, passes integer to the sp)
ddl1: style (displays text, passes integer to the sp)
There will only be 3 tables, but between the three, there can be a total of 7 permutations, but 1 combination is a minimum. I will not need to display ALL combinations at one time, only one combination can be selected at a time.
Based on this, do you think I will need to use dynamic sql?
I have not yet tested your most recent code. By the way thank you for pointing out the correction that needed to be made.
September 8, 2016 at 2:03 pm
itortu (9/8/2016)
Hello Jason,I am not sure if I completely follow what your comments regarding the parameters are, but let me give it a try.
The situation is that this stored procedure will ultimately be executed from a web page, this page will contain 3 drop down lists that will act as the selection criteria for the result set.
ddl1: instrument (displays text, passes integer to the sp)
ddl2: scoring (displays text, passes integer to the sp)
ddl1: style (displays text, passes integer to the sp)
There will only be 3 tables, but between the three, there can be a total of 7 permutations, but 1 combination is a minimum. I will not need to display ALL combinations at one time, only one combination can be selected at a time.
Based on this, do you think I will need to use dynamic sql?
I have not yet tested your most recent code. By the way thank you for pointing out the correction that needed to be made.
Yea... The the dynamic code I provided above does exactly that.
Rather than using 3 separate parameters, I used a single parameter that gets parsed but that's an easy fix...
Something like this... (use the @DeBug parameter to switch between viewing the generated SQL and executing it)
SET NOCOUNT ON;
DECLARE
@UseInstruments BIT = 1,
@UseScoring BIT = 1,
@UseStyle BIT = 1,
@DeBug BIT = 0;
IF OBJECT_ID('tempdb..#TablesToUse', 'U') IS NOT NULL
DROP TABLE #TablesToUse;
CREATE TABLE #TablesToUse (
TableName VARCHAR(255) NOT NULL,
TableAlias CHAR(3)
);
IF @UseInstruments = 1
BEGIN
INSERT #TablesToUse (TableName,TableAlias) VALUES ('dbo.TopSellerInstrumentsLastWeek', 'ins');
END;
IF @UseScoring = 1
BEGIN
INSERT #TablesToUse (TableName,TableAlias) VALUES ('dbo.TopSellerScoringLastWeek', 'sco');
END;
IF @UseStyle = 1
BEGIN
INSERT #TablesToUse (TableName,TableAlias) VALUES ('dbo.TopSellerStyleLastWeek', 'sty');
END;
DECLARE @sql VARCHAR(8000) = '';
SELECT
@sql = CONCAT(@sql, CHAR(13), 'UNION ALL', CHAR(13), 'SELECT ', ttu.TableAlias, '.pf_id, ', ttu.TableAlias, '.Purchases FROM ', ttu.TableName, ' ', ttu.TableAlias)
FROM
#TablesToUse ttu;
SET @sql = CONCAT('
SELECT TOP(5)
rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
x.pf_id,
Purchases = SUM(x.Purchases)
FROM (', STUFF(@sql, 1, 11, ''), '
) x
GROUP BY
x.pf_id
ORDER BY
SUM(x.Purchases) DESC;');
IF @DeBug = 1
BEGIN
PRINT(@sql);
END;
ELSE
BEGIN
EXEC(@sql);
END;
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply