Get first date that account-prod get discount sql

Question

I have a huge volume of a history of transactions table with acc_nbr, tran_nbr, tran_amt, tran_dt, and tran_dis tran_prd

I need to find the first date for each acc_nbr and tran_prd

Find the First date When the account with this type of product purchase it with a discount

Lets say we have the below input table for the same account number :

tran_dt	acc_nbr	tran_prd	tran_amt	tran_dis
1-1-2020	666	A	777	0
3-4-2020	666	A	777	0
5-5-2021	666	C	222	18
7-1-2022	666	A	777	20
3-5-2023	666	A	777	99

And i want the output be :

acc_nbr	tran_prd	Effective dis date	tran_amt (sum)	tran_dis sum
666	A	7-1-2022	1,554	119
666	C	5-5-2021	222	18

I presume you’re summing some of the other values but you haven’t explained that - what are you summing and what are the rules for which rows to include? — NickW, Jul 22 '23 at 17:40
You can use a subquery to find the Effective_dis_date, i.e. MIN(tran_dt) for each acc_nbr, tran_prd where tran_dis is nonzero. Then join that back to the original table and SUM. — Fred, Jul 24 '23 at 14:51

score 0 · Answer 1 · answered Jul 25 '23 at 12:03

create table #tran_hist
(
    tran_dt date,
    acc_nbr int,
    tran_prd varchar(1),
    tran_amt int,
    tran_dis int
)

insert into #tran_hist values
('2020-1-1',666,'A',777,0),
('2020-4-3',666,'A',777,0),
('2021-5-5',666,'C',222,18),
('2022-1-7',666,'A',777,20),
('2023-5-3',666,'A',777,99)

select
    acc_nbr,
    tran_prd,
    min(tran_dt) as [Effective dis date],
    sum(tran_amt) as [tran_amt (sum)],
    sum(tran_dis) as [tran_dis sum]
from #tran_hist
where tran_dis <> 0
group by acc_nbr,
         tran_prd

drop table #tran_hist

Results:

/-------------------------------------------------------------------------\
| acc_nbr | tran_prd | Effective dis date | tran_amt (sum) | tran_dis sum |
|---------|----------|--------------------|----------------|--------------|
|   666   |    A     |     2022-01-07     |      1554      |     119      |
|   666   |    C     |     2021-05-05     |       222      |      18      |
\-------------------------------------------------------------------------/

Get first date that account-prod get discount sql

1 Answers1