November 12, 2015 at 3:38 am
Hi,
I aggregiere data from a table, and should get the max 3 Sales Amount.
If the Customer has only 2 sales, is a DummyRow be issued with value 0; Can I join with a dummy table (Values 1,2,3) ??
No. CustID SalesAmount
-----------------------------
1100794297.00
210079499.00
3100794101.00
1100813124.00
2100813110.00
=> here there must be a Dummy Row like '3 1008130'
Thanks
Regards
Nicole
November 12, 2015 at 3:53 am
info 58414 (11/12/2015)
Hi,I aggregiere data from a table, and should get the max 3 Sales Amount.
If the Customer has only 2 sales, is a DummyRow be issued with value 0; Can I join with a dummy table (Values 1,2,3) ??
No. CustID SalesAmount
-----------------------------
1100794297.00
210079499.00
3100794101.00
1100813124.00
2100813110.00
=> here there must be a Dummy Row like '3 1008130'
Thanks
Regards
Nicole
Quick thought, union the dummy rows to the initial query and use TOP making certain that the dummies are last.
😎
November 12, 2015 at 4:18 am
Another possible scenario.
1. What if a Customer have only one Sale then two dummy rows should be shown or Single because if single your always 3 rows rules violates here.
2. What if a Customer don't have any sale then should there three dummy rows or now data should be shown?
November 12, 2015 at 7:41 am
I'm guessing that it would be better to correct this from the start instead of adding work to a result that you already calculated.
November 12, 2015 at 8:03 am
This is another way to and its a little more dynamic, and saves the
DECLARE @CustOrder TABLE
(
CustId Int
,SalesValue Money
)
INSERT INTO @CustOrder
(CustId,SalesValue)
Values
(100813, 124)
,(100813, 110)
,(100794, 297)
,(100794, 99)
,(100794, 101);
WITH CTE_data
As
(
SELECT ROW_NUMBER() OVER (PARTITION BY CustId Order by CustId) rn
,CustId
,SalesValue
From @CustOrder
)
,CTE
AS
(
SELECT
CustId
,Max(CASE rn WHEN 1 THEN 1 ELse 1 END) Number1
,Max(CASE rn WHEN 1 THEN SalesValue ELse 0 END) Salesvalue1
,Max(CASE rn WHEN 2 THEN 2 ELse 2 END) Number2
,Max(CASE rn WHEN 2 THEN SalesValue ELse 0 END) Salesvalue2
,Max(CASE rn WHEN 3 THEN 3 ELse 3 END) Number3
,Max(CASE rn WHEN 3 THEN SalesValue ELse 0 END) Salesvalue3
From CTE_Data
Group by CustId
)
SELECT
Number,CustId, SalesValue
FROM
CTE
CROSS APPLY
(VALUES
(Number1,SalesValue1)
,(Number2,SalesValue2)
,(Number3,SalesValue3)
) x (Number,SalesValue)
Not sure of the performance on a very large table but splitting out the first CTE that adds a row number into a Temporary table.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply