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

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  October 29, 2018 at 1:06 am

  #2011479

  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

  October 29, 2018 at 9:41 am

  #2011541

  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: 110640

  More actions

  October 29, 2018 at 1:40 pm

  #2011588

  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: 11412

  More actions

  October 29, 2018 at 1:57 pm

  #2011590

  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

  November 14, 2018 at 7:02 pm

  #2013585

  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

  November 14, 2018 at 7:13 pm

  #2013587

  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

  November 14, 2018 at 7:32 pm

  #2013590

  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: 63517

  More actions

  November 14, 2018 at 9:07 pm

  #2013594

  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

  November 15, 2018 at 7:55 am

  #2013661

  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: 23786

  More actions

  November 15, 2018 at 7:56 am

  #2013662

  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;