Select Query

  • 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

  • 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 3 months, 2 weeks ago by  kaj. Reason: Little typo

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

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