Compare top 2 transactions from 12 months from the current transaction date

Question

Compare top 2 transactions from 12 months from the current transaction date

cfm

Old Hand

Points: 386
More actions
October 28, 2018 at 8:43 pm

#416397
I would like to change the where clause to pull 12 months from the most current transaction date WHERE date >= @start_date AND date < @end_date
How do I change the where clause to read @start_date would equal top(date) - 12 and @end _date would be from top(date)
I can not figure how to do this with the below code.
Input Values
```
account number, date, and transaction amount.  7428, 2018-01-26, 27428, 2018-12-30, 516988 2016-02-14, 10016988 2016-01-15, 2522450 1971-04-19, 822450 1971-08-29, 10
```
Results
```
AccountNumber   Number  Amount------------------------------7428    2   5.0016988   2   25.0022450   2   10.0026997   2   10.0027316   2   25.0027365   2   25.0028620   2   10.0028951   2   10.0029905   2   5.00 
```
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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

saravanatn SSCarpal Tunnel Points: 4533 More actions · Answer 1

cfm - Sunday, October 28, 2018 8:43 PM
I would like to change the where clause to pull 12 months from the most current transaction date WHERE date >= @start_date AND date < @end_date
How do I change the where clause to read @start_date would equal top(date) - 12 and @end _date would be from top(date)
I can not figure how to do this with the below code.
Input Values
account number, date, and transaction amount. 7428, 2018-01-26, 27428, 2018-12-30, 516988 2016-02-14, 10016988 2016-01-15, 2522450 1971-04-19, 822450 1971-08-29, 10
Results
AccountNumber Number Amount------------------------------7428 2 5.0016988 2 25.0022450 2 10.0026997 2 10.0027316 2 25.0027365 2 25.0028620 2 10.0028951 2 10.0029905 2 5.00
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

I think you need to use between between date >= @start_date and date < @end_date

Can you kindly below one:
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 Between date >= @start_date and date < @end_date ) AS tt WHERE row_num IN (1, 2) GROUP BY AccountNumber

Saravanan

cfm Old Hand Points: 386 More actions · Answer 2

saravanatn - Monday, October 29, 2018 1:06 AM
cfm - Sunday, October 28, 2018 8:43 PM
I would like to change the where clause to pull 12 months from the most current transaction date WHERE date >= @start_date AND date < @end_date
How do I change the where clause to read @start_date would equal top(date) - 12 and @end _date would be from top(date)
I can not figure how to do this with the below code.
Input Values
account number, date, and transaction amount. 7428, 2018-01-26, 27428, 2018-12-30, 516988 2016-02-14, 10016988 2016-01-15, 2522450 1971-04-19, 822450 1971-08-29, 10
Results
AccountNumber Number Amount------------------------------7428 2 5.0016988 2 25.0022450 2 10.0026997 2 10.0027316 2 25.0027365 2 25.0028620 2 10.0028951 2 10.0029905 2 5.00
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
I think you need to use between between date >= @start_date and date < @end_date
Can you kindly below one:
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 Between date >= @start_date and date < @end_date ) AS tt WHERE row_num IN (1, 2) GROUP BY AccountNumber

I would like to change the way the date value is checking the date range. How do I change from the current date - 12 months To the most recent transaction date - 12 months. If I use the current date I will be missing a lot of transactions.

sgmunson SSC Guru Points: 110639 More actions · Answer 3

cfm - Monday, October 29, 2018 9:41 AM
saravanatn - Monday, October 29, 2018 1:06 AM
cfm - Sunday, October 28, 2018 8:43 PM
I would like to change the where clause to pull 12 months from the most current transaction date WHERE date >= @start_date AND date < @end_date
How do I change the where clause to read @start_date would equal top(date) - 12 and @end _date would be from top(date)
I can not figure how to do this with the below code.
Input Values
account number, date, and transaction amount. 7428, 2018-01-26, 27428, 2018-12-30, 516988 2016-02-14, 10016988 2016-01-15, 2522450 1971-04-19, 822450 1971-08-29, 10
Results
AccountNumber Number Amount------------------------------7428 2 5.0016988 2 25.0022450 2 10.0026997 2 10.0027316 2 25.0027365 2 25.0028620 2 10.0028951 2 10.0029905 2 5.00
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
I think you need to use between between date >= @start_date and date < @end_date
Can you kindly below one:
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 Between date >= @start_date and date < @end_date ) AS tt WHERE row_num IN (1, 2) GROUP BY AccountNumber
I would like to change the way the date value is checking the date range. How do I change from the current date - 12 months To the most recent transaction date - 12 months. If I use the current date I will be missing a lot of transactions.

If the "most recent transaction date" is a column in your table, you're going to have performance issues. If it would need to be derived from your table using an aggregate, then you need to use a query to derive your end date as that value, and then compute the start date from it instead.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Mike01 SSChampion Points: 11393 More actions · Answer 4

It would be nice if you supplied some meaningful data and expected results. What you provided is missing amounts for all records expect the last one. You're expected results are the same. They have 1 account number and then a list of numbers and amounts

Input Values account number, date, and transaction amount. 7428, 2018-01-26, 27428, 2018-12-30, 516988 2016-02-14, 10016988 2016-01-15, 2522450 1971-04-19, 822450 1971-08-29, 10

Results
AccountNumber Number Amount------------------------------ 7428 2 5.0016988 2 25.0022450 2 10.0026997 2 10.0027316 2 25.0027365 2 25.0028620 2 10.0028951 2 10.0029905 2 5.00

Can you supply some usable ddl and data along with expected outcome? Something like below?

Drop table if exists #t

create table #T( AccountNnumber int, TransactionDate date, TransactionAmount numeric(10,2))

insert into #T values (7428, '2018-01-26', 100), (27428, '2018-12-30', 50), (516988, '2016-02-14', 75), (1001698, '2016-01-15', 150), (2522450, '1971-04-19', 236), (822450, '1971-08-29', 10)

-- return last 1 year of data select * from #T where TransactionDate between DATEADD(YEAR, -1, GETDATE()) and GETDATE()

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

cfm Old Hand Points: 386 More actions · Answer 5

the results:
131 1 25.00
650 2 50.00
7500 2 100.00
9608 2 25.00
10893 2 10.00
11227 2 20.00
12216 2 50.00
12497 2 25.00
16326 2 25.00
17028 2 200.00
18196 2 90.00
18230 1 500.01
19719 2 150.00
21091 2 20.00

cfm Old Hand Points: 386 More actions · Answer 6

Is it possible to pull from date range from 12 months from the most recent transaction date and the recent date. Performance is not an issue because I would only run it when I need it.

cfm Old Hand Points: 386 More actions · Answer 7

I can not use the clause WHERE date >= @start_date AND date < @end_date.
it limits the transaction from within 12 months from current date and I need to pull all transactions that are from 12 months from the most recent transaction date

pietlinden SSC Guru Points: 63436 More actions · Answer 8

DECLARE @start_date date DECLARE @end_date date SET @start_date = DATEADD(YEAR, -1, GETDATE()) SET @end_date = GETDATE()

Instead of the above value for @start_date, you could do something like

SELECT @start_date = MAX(TransactionDate) FROM MyTable WHERE....
and then set the @end_Date to be a year after that ... just use DATEADD()

cfm Old Hand Points: 386 More actions · Answer 9

I tryed adding this statement with zero results
WHERE date >= (select DATEADD(year, 1, MAX(Date)) FROM dbo.[T01_TransactionMaster]) and date < (select Max(date)FROM dbo.[T01_TransactionMaster])

I'm adding it to this code:
    DECLARE @start_date date
    DECLARE @end_date date
    SET @start_date = DATEADD(YEAR, -2, GETDATE())
    SET @end_date = GETDATE()

    SELECT
    AccountNumber, count(amount) as Number,
    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 --INTO #TransAskArrys
    FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY amount DESC) AS row_num
    FROM dbo.[T01_TransactionMaster]
    WHERE date >= (select DATEADD(year, 1, MAX(Date)) FROM dbo.[T01_TransactionMaster]) and date < (select Max(date)FROM dbo.[T01_TransactionMaster]) --date >= @start_date AND date < @end_date
    ) AS tt
    WHERE row_num IN (1, 2) and amount > 0 --and AccountNumber = 301692
   GROUP BY AccountNumber

Jason A. Long SSC-Insane Points: 23781 More actions · Answer 10

You're going to need to query the "latest date" separately from the main query.
Something like one of the following...

DECLARE @begdate DATE = (SELECT DATEADD(MONTH, -12, MAX(t.transaction_date)) FROM dbo.Transactions t);

SELECT * FROM dbo.Transactions t WHERE t.transaction_date >= @begdate;
or

SELECT t.* FROM dbo.Transactions t JOIN ( SELECT begdate = DATEADD(MONTH, -12, MAX(t2.transaction_date)) FROM dbo.Transactions t2 ) bd ON t.transaction_date >= bd.begdate;