Returning First/Last Date and PIVOT-CodePudding

I am trying to put a view together in SQL that uses transactional type data to find the first transaction date, last transaction date, and whether it was a credit or debit. This is what it looks like currently:

Account Number	Date	Credit/Debit
123	1-1-22	Debit
123	1-2-22	Credit
456	1-1-22	Debit
456	1-2-22	Credit

I want it to look like this:

Account Number	FirstDate	LastDate	First Credit/Debit	Last Credit/Debit
123	1-1-22	1-2-22	Debit	Credit
456	1-1-22	1-2-22	Debit	Credit

I have created something close with the following code, but am having trouble figuring out how to bring in the First/Last Credit/Debit columns.

SELECT * FROM
(
  
  SELECT * FROM
   ( 
     SELECT 'Earliest' as [TransDate], [Account], [Date], [Credit/Debit],
     ROW_NUMBER() OVER (PARTITION BY [Account] ORDER BY [Date]) as rn
     FROM DataTable
   ) e
  WHERE e.rn = 1

  UNION ALL

  SELECT * FROM
   (
     SELECT  'Latest' as [TransDate], [Account], [Date], [Credit/Debit],
     ROW_NUMBER() OVER (PARTITION BY [Account] ORDER BY [Date] DESC) as rn
     FROM DataTable
   ) l
  WHERE l.rn = 1

) t1

PIVOT (min([Date])) FOR [TransDate] in ([Latest], [Earliest])
) P

CodePudding user response：

You can use ROW_NUMBER() function to get the first and the last row for each account number

;with
dd as (
    select *, 
        ROW_NUMBER() over (partition by [Account Number] order by [Date]) rFirst, 
        ROW_NUMBER() over (partition by [Account Number] order by [Date] desc) rLast
    from DataTable
)
select 
    d1.[Account Number], 
    d1.[Date] FirstDate, d2.[Date] LastDate, 
    d1.[Credit/Debit] [First Credit/Debit], d2.[Credit/Debit] [Last Credit/Debit]
from dd d1
join dd d2 on d1.[Account Number] = d2.[Account Number]  
and d1.rFirst=1 and d2.rLast=1

Pay attention, I think you have an error in your data, you wrote 455 instead of 456

CodePudding user response：

SELECT
DISTINCT
Account_Number,
FIRST_VALUE(DATE) OVER (PARTITION BY Account_Number ORDER BY DATE ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS FirstDate,
LAST_VALUE(DATE) OVER (PARTITION BY Account_Number ORDER BY DATE ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastDate,
FIRST_VALUE(Credit_Debit) OVER (PARTITION BY Account_Number ORDER BY DATE ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS FirstCreditDebit,    
LAST_VALUE(Credit_Debit) OVER (PARTITION BY Account_Number ORDER BY DATE ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastCreditDebit
FROM t

CodePudding user response：

We can achieve this result with group by. No need to pivot.

select   Account_Number
        ,min(date) as FirstDate 
        ,max(date) as LastDate
        ,min(fcd)  as "First Credit/Debit"
        ,min(lcd)  as "Last Credit/Debit"

from     (
          select *
                 ,first_value(Credit_Debit) over(partition by Account_Number order by Date) as fcd
                 ,last_value(Credit_Debit)  over(partition by Account_Number order by Date) as lcd  
          from t
         ) t
group by Account_Number

Account_Number	FirstDate	LastDate	First Credit/Debit	Last Credit/Debit
123	2022-01-01	2022-01-02	Debit	Credit
456	2022-01-01	2022-01-02	Debit	Credit

Fiddle