sql - How to GroupBy based on GetDate() , CASE and fixed column aliases

Question

I am trying to create an openAR file and I am stuck trying to group this data by Customer and Invoice. The file will get created daily.

[FILE DATE]	[CUSTOMER ID]	[INVOICE NUMBER]	[INVOICE TYPE]	[INVOICE DATE]	[OPEN INVOICE AMOUNT]
01/22/2021	00100000	INV1000	INV	06/08/2020	1000
01/22/2021	00100000	INV1001	INV	06/15/2020	50
01/22/2021	00100000	INV1002	INV	08/20/2020	50
01/22/2021	00100000	INV1005	CM	10/18/2020	-100
01/22/2021	00100000	PAY1000	PAY	06/15/2020	-750
01/22/2021	00100000	PAY1000	PAY	06/15/2020	820

question from:https://stackoverflow.com/questions/65850167/how-to-groupby-based-on-getdate-case-and-fixed-column-aliases

Answer 1

Using CTE to reference aliases. Alternatively, as in my comment just group on "CONVERT (nvarchar(30), GETDATE(), 101)" or "(GACC.AMOUNT_0 * GACC.SNS_0)"

With MyInvoices as 
(
    SELECT 
        CONVERT (nvarchar(30), GETDATE(), 101) as [FILE DATE],
        GACC.BPR_0 as [CUSTOMER ID],
        GACC.NUM_0 as [INVOICE NUMBER],
        GACC.TYP_0 as [INVOICE TYPE], 
        Case
        When GACC.TYP_0 in ('INV', 'CM') Then CONVERT (nvarchar(30), SI.BPRDAT_0 , 101)
        Else CONVERT (nvarchar(30), PAY.ACCDAT_0 , 101)
        End as [INVOICE DATE],
        (GACC.AMOUNT_0 * GACC.SNS_0) as [OPEN INVOICE AMOUNT]   
    FROM dbo.GACCDUDATE as GACC
    left join dbo.SINVOICE as SI 
    on GACC.NUM_0 = SIV.NUM_0
    left join dbo.PAYMENT as PAY  
    on PAY.NUM_0 = GACC.NUM_0
)
select [FILE DATE], [CUSTOMER ID], [INVOICE NUMBER], [INVOICE TYPE],[INVOICE DATE],[OPEN INVOICE AMOUNT] from MyInvoices
group by  [FILE DATE], [CUSTOMER ID], [INVOICE NUMBER], [INVOICE TYPE],[INVOICE DATE]