June 17, 2014 at 10:57 pm
Good day
have the following questions:
query1
SELECT so.ClientID, 'All Channels' as CustomerGroup, so.StatementID, so.Brand, so.Product,
Sum(so.Amount) Amount, Sum(so.Value_CP) Value_CP
into #t1
FROM RG_SalesOut_Report so
WHERE so.Block=0 AND so.[All Sources]='SalesOUT'
GROUP BY so.ClientID, so.CustomerGroup, so.StatementID, so.Brand, so.Product
HAVING Sum(so.Value_CP)>0 AND Sum(so.Amount)>0 AND
so.Brand in('Brand1', 'Brand2')
query 2
select t1.ClientID, t1.CustomerGroup, t1.StatementID, t1.Brand, t1.Product,
Sum(t1.Amount) AS Amount, Sum(t1.Value_CP) AS Value_CP
into #t2
from #t1 t1
group by t1.ClientID, t1.CustomerGroup, t1.StatementID, t1.Brand, t1.Product
query 3
select ROW_NUMBER() over(order by t2.ClientID desc) as ID, *, CONCAT(t2.ClientID, t2.Product) AS Code
into #t3
from #t2 t2
group by t2.ClientID, t2.CustomerGroup, t2.StatementID, t2.Brand, t2.Product, t2.Amount, t2.Value_CP, CONCAT(t2.ClientID, t2.Product)
ORDER BY t2.ClientID DESC, t2.Product, t2.StatementID desc
query 4
select tab1.ID, tab2.ID as ID_2, tab1.ClientID, tab2.ClientID as cl_ID2, tab1.CustomerGroup, tab1.StatementID, tab1.Brand,
tab1.Product, tab1.Amount, tab1.Value_CP, IIF(tab1.code=tab2.code, DATEDIFF(MONTH,tab2.StatementID, tab1.StatementID), 0) AS M_SALES
FROM #t3 tab1
RIGHT JOIN #t3 tab2
ON tab1.ID=tab2.ID-1
where tab1.StatementID>='2013-01-01'
order by tab1.ID asc
works well, but run 4 times to get the desired result
is it possible to write one complex query and run one time?
thanks for your precious time
June 17, 2014 at 11:04 pm
Quick questions: are you on 2008 platform? Can you provide some sample data?
😎
June 17, 2014 at 11:06 pm
yes 2008
June 17, 2014 at 11:11 pm
gurbanov.1984 (6/17/2014)
yes 2008
No problem there, only excludes the 2012/14 window functions. Could you provide some sample data + table structure?
😎
June 17, 2014 at 11:13 pm
syntax as IIF and CONCAT, as a hint that the server - 2012
June 18, 2014 at 12:04 am
my server
2014
thank you for participating,
Yes, everything works well alone
but I have to run 4 times
use in their reports query result 4
June 18, 2014 at 12:18 am
I dont think there is need for query 2 as you are already grouping and aggregating on the same columns in query 1.
Can you verify whether the resultset from query 1 and query 2 is different ?
SELECT so.ClientID, 'All Channels' as CustomerGroup, so.StatementID, so.Brand, so.Product,
Sum(so.Amount) Amount, Sum(so.Value_CP) Value_CP
into #t1
FROM RG_SalesOut_Report so
WHERE so.Block=0 AND so.[All Sources]='SalesOUT'
GROUP BY so.ClientID, so.CustomerGroup, so.StatementID, so.Brand, so.Product
HAVING Sum(so.Value_CP)>0 AND Sum(so.Amount)>0 AND
so.Brand in('Brand1', 'Brand2')
select * from #t1
select t1.ClientID, t1.CustomerGroup, t1.StatementID, t1.Brand, t1.Product,
Sum(t1.Amount) AS Amount, Sum(t1.Value_CP) AS Value_CP
into #t2
from #t1 t1
group by t1.ClientID, t1.CustomerGroup, t1.StatementID, t1.Brand, t1.Product
Select * from #t2
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 18, 2014 at 12:34 am
gurbanov.1984 (6/18/2014)
my server2014
That was a very quick upgrade:-P
Any sample data?
😎
June 18, 2014 at 12:55 am
ClientIDCustomerGroupStatementIDBrandProductAmountValue_CPM_SALES
988439All Channels01.05.2014Brand 1SKU 1274,7009880
988370All Channels01.05.2014Brand 1SKU 2259,8236220
988285All Channels01.05.2014Brand 1SKU 3148,9573520
988275All Channels01.05.2014Brand 1SKU 1137,3504950
988231All Channels01.05.2014Brand 1SKU 2129,9118110
988134All Channels01.05.2014Brand 1SKU 4136,9365050
988033All Channels01.05.2014Brand 1SKU 4136,9365050
988023All Channels01.05.2014Brand 1SKU 2389,7354330
987950All Channels01.05.2014Brand 1SKU 1137,3504950
987950All Channels01.05.2014Brand 1SKU 4136,9365050
the table has 11,666,036 rows
month is added about 1.5 million lines
June 18, 2014 at 2:10 am
gurbanov.1984 (6/18/2014)
ClientIDCustomerGroupStatementIDBrandProductAmountValue_CPM_SALES988439All Channels01.05.2014Brand 1SKU 1274,7009880
988370All Channels01.05.2014Brand 1SKU 2259,8236220
988285All Channels01.05.2014Brand 1SKU 3148,9573520
988275All Channels01.05.2014Brand 1SKU 1137,3504950
988231All Channels01.05.2014Brand 1SKU 2129,9118110
988134All Channels01.05.2014Brand 1SKU 4136,9365050
988033All Channels01.05.2014Brand 1SKU 4136,9365050
988023All Channels01.05.2014Brand 1SKU 2389,7354330
987950All Channels01.05.2014Brand 1SKU 1137,3504950
987950All Channels01.05.2014Brand 1SKU 4136,9365050
the table has 11,666,036 rows
month is added about 1.5 million lines
How about setting this up as a CREATE TABLE statement and a bunch of inserts so folks don't have to spend half an hour preparing your sample data before they can code against it? If you're not sure how to do this, read the article in my sig "please read this".
-- query1 put filters in the right place
SELECT
ClientID,
[CustomerGroup] = 'All Channels',
StatementID,
Brand,
Product,
[Amount] = SUM(Amount),
[Value_CP] = SUM(Value_CP)
INTO #t1
FROM RG_SalesOut_Report
WHERE [lock] = 0
AND [All Sources] = 'SalesOUT'
AND Brand IN ('Brand1', 'Brand2')
GROUP BY ClientID, CustomerGroup, StatementID, Brand, Product
HAVING SUM(Value_CP) > 0 AND SUM(Amount) > 0
-- query 2 achieves nothing
SELECT *
INTO #t2
FROM #t1 t1
-- query 3 pointless, both new columns could be in the original query
SELECT
[ID] = ROW_NUMBER() OVER(ORDER BY ClientID DESC),
ClientID,
CustomerGroup,
StatementID,
Brand,
Product,
Amount,
Value_CP,
Code = CONCAT(ClientID, Product)
INTO #t3
FROM #t2
--group by t2.ClientID, t2.CustomerGroup, t2.StatementID, t2.Brand, t2.Product, t2.Amount, t2.Value_CP, CONCAT(t2.ClientID, t2.Product)
--ORDER BY t2.ClientID DESC, t2.Product, t2.StatementID desc
--query 4
select
tab1.ID,
tab2.ID as ID_2,
tab1.ClientID,
tab2.ClientID as cl_ID2,
tab1.CustomerGroup,
tab1.StatementID,
tab1.Brand,
tab1.Product,
tab1.Amount,
tab1.Value_CP,
M_SALES = IIF(tab1.code = tab2.code, DATEDIFF(MONTH,tab2.StatementID, tab1.StatementID), 0)
FROM #t3 tab1
RIGHT OUTER JOIN #t3 tab2
ON tab1.ID = tab2.ID - 1
WHERE tab1.StatementID >= '2013-01-01'
ORDER BY tab1.ID asc
-- Replacement
; WITH Mainquery AS (
SELECT
[ID] = ROW_NUMBER() OVER(ORDER BY ClientID DESC),
ClientID,
[CustomerGroup] = 'All Channels',
StatementID,
Brand,
Product,
[Amount] = SUM(Amount),
[Value_CP] = SUM(Value_CP),
Code = CONCAT(ClientID, Product)
FROM RG_SalesOut_Report
WHERE [lock] = 0
AND [All Sources] = 'SalesOUT'
AND Brand IN ('Brand1', 'Brand2')
GROUP BY ClientID, CustomerGroup, StatementID, Brand, Product
HAVING SUM(Value_CP) > 0 AND SUM(Amount) > 0
)
SELECT
tab1.ID,
tab2.ID as ID_2,
tab1.ClientID,
tab2.ClientID as cl_ID2,
tab1.CustomerGroup,
tab1.StatementID,
tab1.Brand,
tab1.Product,
tab1.Amount,
tab1.Value_CP,
M_SALES = IIF(tab1.code = tab2.code, DATEDIFF(MONTH,tab2.StatementID, tab1.StatementID), 0)
FROM Mainquery tab1
RIGHT OUTER JOIN Mainquery tab2
ON tab1.ID = tab2.ID - 1
WHERE tab1.StatementID >= '2013-01-01'
ORDER BY tab1.ID asc
Note that RIGHT OUTER JOINs are very rarely hand-coded. Most folks use LEFT OUTER JOIN instead, as it can be tricky to interpret one when you are used to the other.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 18, 2014 at 4:08 am
-- query1 put filters in the right place
SELECT
ClientID,
[CustomerGroup] = 'All Channels',
StatementID,
Brand,
Product,
[Amount] = SUM(Amount),
[Value_CP] = SUM(Value_CP)
INTO #t1
FROM RG_SalesOut_Report
WHERE [lock] = 0
AND [All Sources] = 'SalesOUT'
AND Brand IN ('Brand1', 'Brand2')
GROUP BY ClientID, CustomerGroup, StatementID, Brand, Product
HAVING SUM(Value_CP) > 0 AND SUM(Amount) > 0
-- query 2 achieves nothing
SELECT *
INTO #t2
FROM #t1 t1
-- query 3 pointless, both new columns could be in the original query
SELECT
[ID] = ROW_NUMBER() OVER(ORDER BY ClientID DESC),
ClientID,
CustomerGroup,
StatementID,
Brand,
Product,
Amount,
Value_CP,
Code = CONCAT(ClientID, Product)
INTO #t3
FROM #t2
--group by t2.ClientID, t2.CustomerGroup, t2.StatementID, t2.Brand, t2.Product, t2.Amount, t2.Value_CP, CONCAT(t2.ClientID, t2.Product)
--ORDER BY t2.ClientID DESC, t2.Product, t2.StatementID desc
--query 4
select
tab1.ID,
tab2.ID as ID_2,
tab1.ClientID,
tab2.ClientID as cl_ID2,
tab1.CustomerGroup,
tab1.StatementID,
tab1.Brand,
tab1.Product,
tab1.Amount,
tab1.Value_CP,
M_SALES = IIF(tab1.code = tab2.code, DATEDIFF(MONTH,tab2.StatementID, tab1.StatementID), 0)
FROM #t3 tab1
RIGHT OUTER JOIN #t3 tab2
ON tab1.ID = tab2.ID - 1
WHERE tab1.StatementID >= '2013-01-01'
ORDER BY tab1.ID asc
-- Replacement
; WITH Mainquery AS (
SELECT
[ID] = ROW_NUMBER() OVER(ORDER BY ClientID DESC),
ClientID,
[CustomerGroup] = 'All Channels',
StatementID,
Brand,
Product,
[Amount] = SUM(Amount),
[Value_CP] = SUM(Value_CP),
Code = CONCAT(ClientID, Product)
FROM RG_SalesOut_Report
WHERE [lock] = 0
AND [All Sources] = 'SalesOUT'
AND Brand IN ('Brand1', 'Brand2')
GROUP BY ClientID, CustomerGroup, StatementID, Brand, Product
HAVING SUM(Value_CP) > 0 AND SUM(Amount) > 0
)
SELECT
tab1.ID,
tab2.ID as ID_2,
tab1.ClientID,
tab2.ClientID as cl_ID2,
tab1.CustomerGroup,
tab1.StatementID,
tab1.Brand,
tab1.Product,
tab1.Amount,
tab1.Value_CP,
M_SALES = IIF(tab1.code = tab2.code, DATEDIFF(MONTH,tab2.StatementID, tab1.StatementID), 0)
FROM Mainquery tab1
RIGHT OUTER JOIN Mainquery tab2
ON tab1.ID = tab2.ID - 1
WHERE tab1.StatementID >= '2013-01-01'
ORDER BY tab1.ID asc
thanks for your precious time
but why you do not use order by
it is a basic condition for the calculation field (M_Sales), if we miss, the query gets 4 wrong data, therefore, will be in vain all
June 18, 2014 at 4:50 am
gurbanov.1984 (6/18/2014)
...thanks for your precious time
but why you do not use order by
it is a basic condition for the calculation field (M_Sales), if we miss, the query gets 4 wrong data, therefore, will be in vain all
Which ORDER BY in which query?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 18, 2014 at 5:39 am
3 performs the query:
1) adds counter
2) adds a column Code
3) sorts the three columns
ORDER BY t2.ClientID DESC, t2.Product, t2.StatementID desc
example
ClientID Product StatementID M_Sales
9 SKU1 01/05/2014 1
9 SKU1 01/04/2014 -1
9 SKU2 01/05/2014 0
8 SKU1 01/05/2014 4
8 SKU1 01/01/2014 -2
7 SKU2 01/03/2014 Null
analysis of the client:
if> = 0, the client bought this product 1 times
therefore, if we do not sort the then get the wrong data and not be able to make the right decisions for our customers and business department managers work
I think the answer to your question
Thank you for your cooperation
June 18, 2014 at 6:09 am
gurbanov.1984 (6/18/2014)
3 performs the query:1) adds counter
2) adds a column Code
3) sorts the three columns
ORDER BY t2.ClientID DESC, t2.Product, t2.StatementID desc
example
ClientID Product StatementID M_Sales
9 SKU1 01/05/2014 1
9 SKU1 01/04/2014 -1
9 SKU2 01/05/2014 0
8 SKU1 01/05/2014 4
8 SKU1 01/01/2014 -2
7 SKU2 01/03/2014 Null
analysis of the client:
if> = 0, the client bought this product 1 times
therefore, if we do not sort the then get the wrong data and not be able to make the right decisions for our customers and business department managers work
I think the answer to your question
Thank you for your cooperation
I don't think that ordering the intermediate result sets will make any difference to the calculation results.
The sample data you posted does not match your queries and is not in a suitable format for testing against. We need something like this:
IF OBJECT_ID ('tempdb..#RG_SalesOut_Report') IS NOT NULL
DROP TABLE #RG_SalesOut_Report;
WITH RG_SalesOut_Report (ClientID, CustomerGroup, StatementID, Brand, Product, Amount, Value_CP, M_SALES) AS (
SELECT 988439, 'All Channels', '01.05.2014', 'Brand 1', 'SKU 1', 2, '74,700988', 0 UNION ALL
SELECT 988370, 'All Channels', '01.05.2014', 'Brand 1', 'SKU 2', 2, '59,823622', 0 UNION ALL
SELECT 988285, 'All Channels', '01.05.2014', 'Brand 1', 'SKU 3', 1, '48,957352', 0 UNION ALL
SELECT 988275, 'All Channels', '01.05.2014', 'Brand 1', 'SKU 1', 1, '37,350495', 0 UNION ALL
SELECT 988231, 'All Channels', '01.05.2014', 'Brand 1', 'SKU 2', 1, '29,911811', 0 UNION ALL
SELECT 988134, 'All Channels', '01.05.2014', 'Brand 1', 'SKU 4', 1, '36,936505', 0 UNION ALL
SELECT 988033, 'All Channels', '01.05.2014', 'Brand 1', 'SKU 4', 1, '36,936505', 0 UNION ALL
SELECT 988023, 'All Channels', '01.05.2014', 'Brand 1', 'SKU 2', 3, '89,735433', 0 UNION ALL
SELECT 987950, 'All Channels', '01.05.2014', 'Brand 1', 'SKU 1', 1, '37,350495', 0 UNION ALL
SELECT 987950, 'All Channels', '01.05.2014', 'Brand 1', 'SKU 4', 1, '36,936505', 0)
SELECT ClientID, CustomerGroup, StatementID, Brand, Product, Amount, Value_CP = CAST(REPLACE(Value_CP,',','') AS INT), M_SALES
INTO #RG_SalesOut_Report
FROM RG_SalesOut_Report
- with column names and data types which match your query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 18, 2014 at 6:34 am
thanks for your precious time
but why us # RG_SalesOut_Report (test pattern) that it give us?
baseline data about 12 million lines and how you want to write such a request with a list of data, the database is increased 1.5 million / month.?
if you are not understood correctly verify pls
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply