Post reply

Select Query

  • August 15, 2021 at 4:05 pm

    #3920134

    Hi

    If FromDate is 01/06/2021 & ToDate = 30/06/2021. I want that is should gives figures between from date & todate.

    Also from the beginning of financial year 01/04/2021 to till date

     

    If FromDate is 01/03/2021 & ToDate = 30/03/2021. then current year sales will be from 01/04/2020 to 30/03/2021.

    Thanks

  • August 15, 2021 at 8:40 pm

    #3920169

    You haven't really provided actionable data about what your data looks like and what you want to achieve, but here's a wild west shot from the hip:

    declare
        @fiscal_year_start date = '20210401',
        @FromDate date = '20210301',
        @ToDate date = '20210630'

    select 
        sum(case when InvoiceDate between @FromDate and @ToDate then InvoiceAmount else 0 end) as periodAmount,
        sum(case when InvoiceDate between case when @ToDate < @fiscal_year_start then dateadd(y,-1,@fiscal_year_start) else @fiscal_year_start end and @ToDate then InvoiceAmount else 0 end) as CurrentYearSales
    from InvoiceTable
    where InvoiceDate between 
                        case when @ToDate < @fiscal_year_start then dateadd(y,-1,@fiscal_year_start) else @FromDate end 
                      and 
                        @ToDate

    • This reply was modified 2 years, 8 months ago by  kaj. Reason: Little typo

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply