Find every user that changed the amount(int) of a certain type column between dates a and b-CodePudding

My table has 6 columns, ID, username, address, date, type and amount.

I want to find every user that changed the amount(int) of a certain type column between dates a and b. I need the exact difference if it's negative it must display a negative number. The problem I am facing is that there is no unique identifier in the database(there are instances where everything but the amount is the same). ID is useless since it's just an autoincrement.

I wrote this statement:

SELECT a.Username, a.Address, a.diff, a.Type 
FROM (
      SELECT Username, Address, Type, MAX(Amount)-MIN(amount)AS diff 
      FROM dbo.data 
      WHERE Date='date1' OR Date='date2'  
      GROUP BY Username, Address, Type 
      HAVING MAX(amount) - MIN(amount) > 0
     ) a 
ORDER BY diff;

The problem with this statement are:

this diff is only positive numbers and
if the same date has the same user with a different amount of the same type it will output that difference and I only want to see if there was a difference between the dates.

I know this database is badly designed but this is how this company has it. I appreciate any input.

SAMPLE DATA:

ID  USERNAME   ADDRESS   TYPE   AMOUNT    DATE
0   JOHN       street1   NKK    200       2022-05-22
1   ALEX       street3   NKK    400       2022-05-22
2   MIKE       street2   MKK    400       2022-05-22
3   MIKE       street2   MKK    300       2022-05-22
4   MIKE       street2   MLB    500       2022-05-22
5   JOHN       street1   NKK    100       2022-05-23
6   ALEX       street3   NKK    400       2022-05-23
7   MIKE       street2   MKK    500       2022-05-23
8   MIKE       street2   MKK    300       2022-05-23
9   MIKE       street2   MLB    600       2022-05-23

OUTPUT:

USERNAME STREET   TYPE   DIFF
JOHN     street1  NKK    -100
MIKE     street2  MKK    100
MIKE     street2  MLB    100

CodePudding user response：

I think you just need to conditionally aggregate within your two dates:

with amounts as (
  select *, 
    Sum(case when DATE='20220523' then AMOUNT end) over(partition by USERNAME,ADDRESS,TYPE) 
    - Sum(case when DATE='20220522' then AMOUNT end) over(partition by USERNAME,ADDRESS,TYPE) as Diff
  from data
  where DATE in ('20220522','20220523')
)
select distinct USERNAME, ADDRESS, TYPE, Diff
from amounts
where Diff != 0;

See demo fiddle