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