April 10, 2018 at 2:13 pm
Taking all transactions in the last 12 months and compare the top two highest single transaction. If the highest single gift within that time period is more than two times greater than the 2nd largest single transaction, take the 2nd highest single gift. If the #1 single highest gift is not two times greater, it is used. I also have to figure out For a account that has only 2 transactions, the most recent transaction is the amount used.
input data
account amount date
12 25 4/1/2018
12 10 4/10/2018
12 30 1/7/2018
14 10 4/1/2018
14 5 4/10/2018
14 8 1/7/2018
14 10 4/1/2018
11 15 4/10/2018
11 80 1/7/2018
11 15 4/1/2018
11 30 4/10/2018
10 8 1/7/2018
10 10 3/7/2018
So far I have this code figured out. I can not seem to figure out how to compare the 2 highest
SQL
SELECT
tt.accountnumber,
tt.Amount,
count(tt.amount),
tt.Date,
--Get max(Amount) of all transaction in last 12 month
TopAmount=(SELECT max(Amount)
FROM [dbo].[T01_TransactionMaster] tt2
WHERE tt.AccountNumber = tt2.AccountNumber
and date <= GETDATE() - 12)
--AND --exclude last 12 months)
FROM [dbo].[T01_TransactionMaster] tt
GROUP BY tt.AccountNumber,tt.Amount,tt.Date
ORDER BY tt.Date desc
April 11, 2018 at 12:40 am
cfm - Tuesday, April 10, 2018 2:13 PMTaking all transactions in the last 12 months and compare the top two highest single transaction. If the highest single gift within that time period is more than two times greater than the 2nd largest single transaction, take the 2nd highest single gift. If the #1 single highest gift is not two times greater, it is used. I also have to figure out For a account that has only 2 transactions, the most recent transaction is the amount used.input data
account amount date
12 25 4/1/2018
12 10 4/10/2018
12 30 1/7/2018
14 10 4/1/2018
14 5 4/10/2018
14 8 1/7/2018
14 10 4/1/2018
11 15 4/10/2018
11 80 1/7/2018
11 15 4/1/2018
11 30 4/10/2018
10 8 1/7/2018
10 10 3/7/2018So far I have this code figured out. I can not seem to figure out how to compare the 2 highest
SQL
SELECT
tt.accountnumber,
tt.Amount,
count(tt.amount),
tt.Date,
--Get max(Amount) of all transaction in last 12 month
TopAmount=(SELECT max(Amount)
FROM [dbo].[T01_TransactionMaster] tt2
WHERE tt.AccountNumber = tt2.AccountNumber
and date <= GETDATE() - 12)
--AND --exclude last 12 months)
FROM [dbo].[T01_TransactionMaster] ttGROUP BY tt.AccountNumber,tt.Amount,tt.Date
ORDER BY tt.Date desc
Your requirement is not clear. I am taking a shot at the dark...
Create Table accounts (
account int,
amount int,
trans_date date
);
Insert into accounts values ('12','25','4/1/2018');
Insert into accounts values ('12','10','4/10/2018');
Insert into accounts values ('12','30','1/7/2018');
Insert into accounts values ('14','10','4/1/2018');
Insert into accounts values ('14','5','4/10/2018');
Insert into accounts values ('14','8','1/7/2018');
Insert into accounts values ('14','10','4/1/2018');
Insert into accounts values ('11','15','4/10/2018');
Insert into accounts values ('11','80','1/7/2018');
Insert into accounts values ('11','15','4/1/2018');
Insert into accounts values ('11','30','4/10/2018');
Insert into accounts values ('10','8','1/7/2018');
Insert into accounts values ('10','10','3/7/2018');
with cte as
(select *,rank() over (order by amount desc,trans_date desc) as top_amt
from accounts where datediff(day,trans_date,getdate())<=365),
cte1 as
(
select *,rank() over (order by amount desc,trans_date desc) as second_amt
from accounts where datediff(day,trans_date,getdate())<=365)
select *,case when a.amount>(2*b.amount) then b.amount else a.amount end as gift_amt
from cte a,cte1 b
where a.top_amt=1 and b.second_amt=2
Saravanan
April 12, 2018 at 7:01 am
cfm - Tuesday, April 10, 2018 2:13 PMTaking all transactions in the last 12 months and compare the top two highest single transaction. If the highest single gift within that time period is more than two times greater than the 2nd largest single transaction, take the 2nd highest single gift. If the #1 single highest gift is not two times greater, it is used. I also have to figure out For a account that has only 2 transactions, the most recent transaction is the amount used.input data
account amount date
12 25 4/1/2018
12 10 4/10/2018
12 30 1/7/2018
14 10 4/1/2018
14 5 4/10/2018
14 8 1/7/2018
14 10 4/1/2018
11 15 4/10/2018
11 80 1/7/2018
11 15 4/1/2018
11 30 4/10/2018
10 8 1/7/2018
10 10 3/7/2018So far I have this code figured out. I can not seem to figure out how to compare the 2 highest
SQL
SELECT
tt.accountnumber,
tt.Amount,
count(tt.amount),
tt.Date,
--Get max(Amount) of all transaction in last 12 month
TopAmount=(SELECT max(Amount)
FROM [dbo].[T01_TransactionMaster] tt2
WHERE tt.AccountNumber = tt2.AccountNumber
and date <= GETDATE() - 12)
--AND --exclude last 12 months)
FROM [dbo].[T01_TransactionMaster] ttGROUP BY tt.AccountNumber,tt.Amount,tt.Date
ORDER BY tt.Date desc
What's not clear is whether you need the things you asked for, for each account? Mainly because you started by stating "Taking all transactions...", but then further on in your post you mention whether or not an account has just two transactions and what to do then. I'd rather not assume that this is all done by account, or if you have two separate requirements in play... Please post on which it is, and the expected results from your posted data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 12, 2018 at 4:06 pm
I need a single top transaction for each account but if the first top transaction is 2 * more than the second top transaction then use the second top transaction. If there are only 2 transactions, use the most resent transaction. all the transactions have to be rolling 12 month period.
April 12, 2018 at 4:28 pm
I need a single top transaction for each account but if the first top transaction is 2 * more than the second top transaction then use the second top transaction. If there are only 2 transactions, use the most resent transaction. all the transactions have to be rolling 12 month period.
saravanatn - Wednesday, April 11, 2018 12:40 AMcfm - Tuesday, April 10, 2018 2:13 PMTaking all transactions in the last 12 months and compare the top two highest single transaction. If the highest single gift within that time period is more than two times greater than the 2nd largest single transaction, take the 2nd highest single gift. If the #1 single highest gift is not two times greater, it is used. I also have to figure out For a account that has only 2 transactions, the most recent transaction is the amount used.input data
account amount date
12 25 4/1/2018
12 10 4/10/2018
12 30 1/7/2018
14 10 4/1/2018
14 5 4/10/2018
14 8 1/7/2018
14 10 4/1/2018
11 15 4/10/2018
11 80 1/7/2018
11 15 4/1/2018
11 30 4/10/2018
10 8 1/7/2018
10 10 3/7/2018So far I have this code figured out. I can not seem to figure out how to compare the 2 highest
SQL
SELECT
tt.accountnumber,
tt.Amount,
count(tt.amount),
tt.Date,
--Get max(Amount) of all transaction in last 12 month
TopAmount=(SELECT max(Amount)
FROM [dbo].[T01_TransactionMaster] tt2
WHERE tt.AccountNumber = tt2.AccountNumber
and date <= GETDATE() - 12)
--AND --exclude last 12 months)
FROM [dbo].[T01_TransactionMaster] ttGROUP BY tt.AccountNumber,tt.Amount,tt.Date
ORDER BY tt.Date descYour requirement is not clear. I am taking a shot at the dark...
Create Table accounts (
account int,
amount int,
trans_date date
);
Insert into accounts values ('12','25','4/1/2018');
Insert into accounts values ('12','10','4/10/2018');
Insert into accounts values ('12','30','1/7/2018');
Insert into accounts values ('14','10','4/1/2018');
Insert into accounts values ('14','5','4/10/2018');
Insert into accounts values ('14','8','1/7/2018');
Insert into accounts values ('14','10','4/1/2018');
Insert into accounts values ('11','15','4/10/2018');
Insert into accounts values ('11','80','1/7/2018');
Insert into accounts values ('11','15','4/1/2018');
Insert into accounts values ('11','30','4/10/2018');
Insert into accounts values ('10','8','1/7/2018');
Insert into accounts values ('10','10','3/7/2018');
with cte as
(select *,rank() over (order by amount desc,trans_date desc) as top_amt
from accounts where datediff(day,trans_date,getdate())<=365),
cte1 as
(
select *,rank() over (order by amount desc,trans_date desc) as second_amt
from accounts where datediff(day,trans_date,getdate())<=365)
select *,case when a.amount>(2*b.amount) then b.amount else a.amount end as gift_amt
from cte a,cte1 b
where a.top_amt=1 and b.second_amt=2
I need a single top transaction for each account in the table but if the first top transaction is 2 * more than the second top transaction then use the second top transaction. If there are only 2 transactions, use the most resent transaction. all the transactions have to be rolling 12 month period.
April 12, 2018 at 4:32 pm
cfm - Thursday, April 12, 2018 4:28 PMI need a single top transaction for each account but if the first top transaction is 2 * more than the second top transaction then use the second top transaction. If there are only 2 transactions, use the most resent transaction. all the transactions have to be rolling 12 month period.saravanatn - Wednesday, April 11, 2018 12:40 AMcfm - Tuesday, April 10, 2018 2:13 PMTaking all transactions in the last 12 months and compare the top two highest single transaction. If the highest single gift within that time period is more than two times greater than the 2nd largest single transaction, take the 2nd highest single gift. If the #1 single highest gift is not two times greater, it is used. I also have to figure out For a account that has only 2 transactions, the most recent transaction is the amount used.input data
account amount date
12 25 4/1/2018
12 10 4/10/2018
12 30 1/7/2018
14 10 4/1/2018
14 5 4/10/2018
14 8 1/7/2018
14 10 4/1/2018
11 15 4/10/2018
11 80 1/7/2018
11 15 4/1/2018
11 30 4/10/2018
10 8 1/7/2018
10 10 3/7/2018So far I have this code figured out. I can not seem to figure out how to compare the 2 highest
SQL
SELECT
tt.accountnumber,
tt.Amount,
count(tt.amount),
tt.Date,
--Get max(Amount) of all transaction in last 12 month
TopAmount=(SELECT max(Amount)
FROM [dbo].[T01_TransactionMaster] tt2
WHERE tt.AccountNumber = tt2.AccountNumber
and date <= GETDATE() - 12)
--AND --exclude last 12 months)
FROM [dbo].[T01_TransactionMaster] ttGROUP BY tt.AccountNumber,tt.Amount,tt.Date
ORDER BY tt.Date descYour requirement is not clear. I am taking a shot at the dark...
Create Table accounts (
account int,
amount int,
trans_date date
);
Insert into accounts values ('12','25','4/1/2018');
Insert into accounts values ('12','10','4/10/2018');
Insert into accounts values ('12','30','1/7/2018');
Insert into accounts values ('14','10','4/1/2018');
Insert into accounts values ('14','5','4/10/2018');
Insert into accounts values ('14','8','1/7/2018');
Insert into accounts values ('14','10','4/1/2018');
Insert into accounts values ('11','15','4/10/2018');
Insert into accounts values ('11','80','1/7/2018');
Insert into accounts values ('11','15','4/1/2018');
Insert into accounts values ('11','30','4/10/2018');
Insert into accounts values ('10','8','1/7/2018');
Insert into accounts values ('10','10','3/7/2018');
with cte as
(select *,rank() over (order by amount desc,trans_date desc) as top_amt
from accounts where datediff(day,trans_date,getdate())<=365),
cte1 as
(
select *,rank() over (order by amount desc,trans_date desc) as second_amt
from accounts where datediff(day,trans_date,getdate())<=365)
select *,case when a.amount>(2*b.amount) then b.amount else a.amount end as gift_amt
from cte a,cte1 b
where a.top_amt=1 and b.second_amt=2I need a single top transaction for each account in the table but if the first top transaction is 2 * more than the second top transaction then use the second top transaction. If there are only 2 transactions, use the most resent transaction. all the transactions have to be rolling 12 month period.
The current SQL statement only list one account. I need it to show all accounts in the table.
April 12, 2018 at 9:31 pm
cfm - Thursday, April 12, 2018 4:32 PMcfm - Thursday, April 12, 2018 4:28 PMI need a single top transaction for each account but if the first top transaction is 2 * more than the second top transaction then use the second top transaction. If there are only 2 transactions, use the most resent transaction. all the transactions have to be rolling 12 month period.saravanatn - Wednesday, April 11, 2018 12:40 AMcfm - Tuesday, April 10, 2018 2:13 PMTaking all transactions in the last 12 months and compare the top two highest single transaction. If the highest single gift within that time period is more than two times greater than the 2nd largest single transaction, take the 2nd highest single gift. If the #1 single highest gift is not two times greater, it is used. I also have to figure out For a account that has only 2 transactions, the most recent transaction is the amount used.input data
account amount date
12 25 4/1/2018
12 10 4/10/2018
12 30 1/7/2018
14 10 4/1/2018
14 5 4/10/2018
14 8 1/7/2018
14 10 4/1/2018
11 15 4/10/2018
11 80 1/7/2018
11 15 4/1/2018
11 30 4/10/2018
10 8 1/7/2018
10 10 3/7/2018So far I have this code figured out. I can not seem to figure out how to compare the 2 highest
SQL
SELECT
tt.accountnumber,
tt.Amount,
count(tt.amount),
tt.Date,
--Get max(Amount) of all transaction in last 12 month
TopAmount=(SELECT max(Amount)
FROM [dbo].[T01_TransactionMaster] tt2
WHERE tt.AccountNumber = tt2.AccountNumber
and date <= GETDATE() - 12)
--AND --exclude last 12 months)
FROM [dbo].[T01_TransactionMaster] ttGROUP BY tt.AccountNumber,tt.Amount,tt.Date
ORDER BY tt.Date descYour requirement is not clear. I am taking a shot at the dark...
Create Table accounts (
account int,
amount int,
trans_date date
);
Insert into accounts values ('12','25','4/1/2018');
Insert into accounts values ('12','10','4/10/2018');
Insert into accounts values ('12','30','1/7/2018');
Insert into accounts values ('14','10','4/1/2018');
Insert into accounts values ('14','5','4/10/2018');
Insert into accounts values ('14','8','1/7/2018');
Insert into accounts values ('14','10','4/1/2018');
Insert into accounts values ('11','15','4/10/2018');
Insert into accounts values ('11','80','1/7/2018');
Insert into accounts values ('11','15','4/1/2018');
Insert into accounts values ('11','30','4/10/2018');
Insert into accounts values ('10','8','1/7/2018');
Insert into accounts values ('10','10','3/7/2018');
with cte as
(select *,rank() over (order by amount desc,trans_date desc) as top_amt
from accounts where datediff(day,trans_date,getdate())<=365),
cte1 as
(
select *,rank() over (order by amount desc,trans_date desc) as second_amt
from accounts where datediff(day,trans_date,getdate())<=365)
select *,case when a.amount>(2*b.amount) then b.amount else a.amount end as gift_amt
from cte a,cte1 b
where a.top_amt=1 and b.second_amt=2I need a single top transaction for each account in the table but if the first top transaction is 2 * more than the second top transaction then use the second top transaction. If there are only 2 transactions, use the most resent transaction. all the transactions have to be rolling 12 month period.
The current SQL statement only list one account. I need it to show all accounts in the table.
Can you kindly show the output result as like the input data you showed.
Saravanan
April 13, 2018 at 8:46 am
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = DATEADD(YEAR, -1, GETDATE())
SET @end_date = GETDATE()
SELECT
AccountNumber,
CASE WHEN MAX(CASE WHEN row_num = 1 THEN amount END) > MAX(CASE WHEN row_num = 2 THEN amount END) * 2
THEN MAX(CASE WHEN row_num = 2 THEN amount END) ELSE MAX(CASE WHEN row_num = 1 THEN amount END) END AS amount
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY amount DESC) AS row_num
FROM dbo.[T01_TransactionMaster]
WHERE date >= @start_date AND date < @end_date
) AS tt
WHERE row_num IN (1, 2)
GROUP BY AccountNumber
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 13, 2018 at 9:41 am
ScottPletcher - Friday, April 13, 2018 8:46 AM
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = DATEADD(YEAR, -1, GETDATE())
SET @end_date = GETDATE()SELECT
AccountNumber,
CASE WHEN MAX(CASE WHEN row_num = 1 THEN amount END) > MAX(CASE WHEN row_num = 2 THEN amount END) * 2
THEN MAX(CASE WHEN row_num = 2 THEN amount END) ELSE MAX(CASE WHEN row_num = 1 THEN amount END) END AS amount
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY amount DESC) AS row_num
FROM dbo.[T01_TransactionMaster]
WHERE date >= @start_date AND date < @end_date
) AS tt
WHERE row_num IN (1, 2)
GROUP BY AccountNumber
Nicely done, Scott!
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 13, 2018 at 10:22 am
ScottPletcher - Friday, April 13, 2018 8:46 AM
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = DATEADD(YEAR, -1, GETDATE())
SET @end_date = GETDATE()SELECT
AccountNumber,
CASE WHEN MAX(CASE WHEN row_num = 1 THEN amount END) > MAX(CASE WHEN row_num = 2 THEN amount END) * 2
THEN MAX(CASE WHEN row_num = 2 THEN amount END) ELSE MAX(CASE WHEN row_num = 1 THEN amount END) END AS amount
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY amount DESC) AS row_num
FROM dbo.[T01_TransactionMaster]
WHERE date >= @start_date AND date < @end_date
) AS tt
WHERE row_num IN (1, 2)
GROUP BY AccountNumber
Is there a way to exclude zero dollar amount transaction in the table. I have a lot of transactions with free zero amount gifts. Those transaction are messing up the top gifts amount. I'm getting zero dollar for the top gift.
example of the transactions
account amount date
12 0 4/1/2018
12 10 4/10/2018
12 0 1/7/2018
14 30 4/1/2018
14 0 4/10/2018
14 30 1/7/2018
14 0 4/1/2018
April 13, 2018 at 11:18 am
cfm - Friday, April 13, 2018 10:22 AMScottPletcher - Friday, April 13, 2018 8:46 AM
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = DATEADD(YEAR, -1, GETDATE())
SET @end_date = GETDATE()SELECT
AccountNumber,
CASE WHEN MAX(CASE WHEN row_num = 1 THEN amount END) > MAX(CASE WHEN row_num = 2 THEN amount END) * 2
THEN MAX(CASE WHEN row_num = 2 THEN amount END) ELSE MAX(CASE WHEN row_num = 1 THEN amount END) END AS amount
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY amount DESC) AS row_num
FROM dbo.[T01_TransactionMaster]
WHERE date >= @start_date AND date < @end_date
) AS tt
WHERE row_num IN (1, 2)
GROUP BY AccountNumberIs there a way to exclude zero dollar amount transaction in the table. I have a lot of transactions with free zero amount gifts. Those transaction are messing up the top gifts amount. I'm getting zero dollar for the top gift.
example of the transactions
account amount date
12 0 4/1/2018
12 10 4/10/2018
12 0 1/7/2018
14 30 4/1/2018
14 0 4/10/2018
14 30 1/7/2018
14 0 4/1/2018
WHERE amount > 0
Although since the query is taking the highest amounts, you shouldn't see 0 anyway unless there was no amount higher than 0 in that time period.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 13, 2018 at 11:50 am
ScottPletcher - Friday, April 13, 2018 11:18 AMcfm - Friday, April 13, 2018 10:22 AMScottPletcher - Friday, April 13, 2018 8:46 AM
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = DATEADD(YEAR, -1, GETDATE())
SET @end_date = GETDATE()SELECT
AccountNumber,
CASE WHEN MAX(CASE WHEN row_num = 1 THEN amount END) > MAX(CASE WHEN row_num = 2 THEN amount END) * 2
THEN MAX(CASE WHEN row_num = 2 THEN amount END) ELSE MAX(CASE WHEN row_num = 1 THEN amount END) END AS amount
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY amount DESC) AS row_num
FROM dbo.[T01_TransactionMaster]
WHERE date >= @start_date AND date < @end_date
) AS tt
WHERE row_num IN (1, 2)
GROUP BY AccountNumberIs there a way to exclude zero dollar amount transaction in the table. I have a lot of transactions with free zero amount gifts. Those transaction are messing up the top gifts amount. I'm getting zero dollar for the top gift.
example of the transactions
account amount date
12 0 4/1/2018
12 10 4/10/2018
12 0 1/7/2018
14 30 4/1/2018
14 0 4/10/2018
14 30 1/7/2018
14 0 4/1/2018WHERE amount > 0
Although since the query is taking the highest amounts, you shouldn't see 0 anyway unless there was no amount higher than 0 in that time period.
It adds the second transaction amount of zero when the first max transaction amount is 2 * greater . The amount > 0 fixed that problem. Thanks so much.
Is there a way to change the data range to that gets the most resent dates instead of only the dates within the last year. There are many transactions that would be missed and do not have a transaction within the year that I would like to include.
April 13, 2018 at 12:56 pm
cfm - Friday, April 13, 2018 11:50 AMScottPletcher - Friday, April 13, 2018 11:18 AMcfm - Friday, April 13, 2018 10:22 AMScottPletcher - Friday, April 13, 2018 8:46 AM
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = DATEADD(YEAR, -1, GETDATE())
SET @end_date = GETDATE()SELECT
AccountNumber,
CASE WHEN MAX(CASE WHEN row_num = 1 THEN amount END) > MAX(CASE WHEN row_num = 2 THEN amount END) * 2
THEN MAX(CASE WHEN row_num = 2 THEN amount END) ELSE MAX(CASE WHEN row_num = 1 THEN amount END) END AS amount
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY amount DESC) AS row_num
FROM dbo.[T01_TransactionMaster]
WHERE date >= @start_date AND date < @end_date
) AS tt
WHERE row_num IN (1, 2)
GROUP BY AccountNumberIs there a way to exclude zero dollar amount transaction in the table. I have a lot of transactions with free zero amount gifts. Those transaction are messing up the top gifts amount. I'm getting zero dollar for the top gift.
example of the transactions
account amount date
12 0 4/1/2018
12 10 4/10/2018
12 0 1/7/2018
14 30 4/1/2018
14 0 4/10/2018
14 30 1/7/2018
14 0 4/1/2018WHERE amount > 0
Although since the query is taking the highest amounts, you shouldn't see 0 anyway unless there was no amount higher than 0 in that time period.
It adds the second transaction amount of zero when the first max transaction amount is 2 * greater . The amount > 0 fixed that problem. Thanks so much.
Ah, good point, I hadn't thought of that little quirk.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 13, 2018 at 11:02 pm
ScottPletcher - Friday, April 13, 2018 11:18 AMcfm - Friday, April 13, 2018 10:22 AMScottPletcher - Friday, April 13, 2018 8:46 AM
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = DATEADD(YEAR, -1, GETDATE())
SET @end_date = GETDATE()SELECT
AccountNumber,
CASE WHEN MAX(CASE WHEN row_num = 1 THEN amount END) > MAX(CASE WHEN row_num = 2 THEN amount END) * 2
THEN MAX(CASE WHEN row_num = 2 THEN amount END) ELSE MAX(CASE WHEN row_num = 1 THEN amount END) END AS amount
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY amount DESC) AS row_num
FROM dbo.[T01_TransactionMaster]
WHERE date >= @start_date AND date < @end_date
) AS tt
WHERE row_num IN (1, 2)
GROUP BY AccountNumberIs there a way to exclude zero dollar amount transaction in the table. I have a lot of transactions with free zero amount gifts. Those transaction are messing up the top gifts amount. I'm getting zero dollar for the top gift.
example of the transactions
account amount date
12 0 4/1/2018
12 10 4/10/2018
12 0 1/7/2018
14 30 4/1/2018
14 0 4/10/2018
14 30 1/7/2018
14 0 4/1/2018WHERE amount > 0
Although since the query is taking the highest amounts, you shouldn't see 0 anyway unless there was no amount higher than 0 in that time period.
That was awesome Scott
Saravanan
October 16, 2018 at 3:36 pm
I would like to add to this code. I would like to reference another table with gift arrays. I would like to compare the amount against the ask array table. How would I add this code to the above statement?
Here is my ask array table
MinCGL MaxCGL Ask1 Ask2 Ask3
0.01 7.49 5 10 15
7.5 11.24 10 15 20
11.25 11.66 10 15 25
11.67 12.45 10 20 25
12.46 13.74 15 20 25
13.75 14.99 15 20 30
15 16.23 15 25 30
16.24 17.44 15 25 35
17.45 18.33 20 25 35
18.34 18.73 20 30 35
18.74 21.16 20 30 40
21.17 21.63 20 30 45
21.64 22.4 20 35 45
22.41 23.74 25 35 45
23.75 24.97 25 35 50
24.98 26.18 25 40 50
26.19 27.42 25 40 55
27.43 28.33 30 40 55
28.34 28.72 30 45 55
28.73 31.17 30 45 60
31.18 31.63 30 45 65
31.64 32.41 30 50 65
32.42 33.73 35 50 65
33.74 34.89 35 50 70
34.9 36.24 35 55 70
36.25 37.41 35 55 75
37.42 38.24 40 55 75
38.25 38.5 40 60 75
38.51 41 40 60 80
41.01 41.66 40 60 85
41.67 42.43 40 65 85
42.44 43.62 45 65 85
43.63 44.84 45 65 90
44.85 46 45 70 90
46.01 47.48 45 70 95
47.49 48.33 50 70 95
48.34 48.6 50 75 95
48.61 51.03 50 75 100
51.04 51.62 50 75 105
51.63 52.2 50 80 105
52.21 53.66 55 80 105
53.67 54.94 55 80 110
54.95 56.19 55 85 110
56.2 57.41 55 85 115
57.42 58 60 85 115
58.01 58.7 60 90 115
58.71 61 60 90 120
61.01 61.58 60 90 125
61.59 62.46 60 95 125
62.47 63.7 65 95 125
63.71 64.5 65 95 130
64.51 66 65 100 130
66.01 67.33 65 100 135
67.34 68.08 70 100 135
68.09 68.46 70 105 135
68.47 71.2 70 105 140
71.21 72.03 70 110 145
72.04 73.61 75 110 145
73.62 74.77 75 110 150
75.78 76 75 115 150
76.01 75 115 150
WHERE date >= @start_date AND date < @end_date
) AS tt
join [ClientImport].[dbo].[MSSC_AskArray] AA on TT.amount between AA.MinCGL and AA.MaxCGL
WHERE row_num IN (1, 2) and amount > 0
GROUP BY AccountNumber"]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply