YTD Sale

  • Hi

    I have below Stored Procedure . I want YTd column value also.

    If user enters @frDate = 01/01/2021 and @toDate = 31/01/2021 then YTD value should be from 01/04/2020 to 31/01/2021

    If user enters @frDate = 01/12/2020 and @toDate = 31/12/2020 then YTD value should be from 01/04/2020 to 31/12/2020

    Create PROCEDURE [dbo].[SpBpGroupWiseSalePurchase]

    @frDate date,

    @toDate date,

    @CType nvarchar(1)

    AS

    BEGIN

    SELECT code,name

    (Select sum(INvTotal) from Invoice where dt between @frDate and @ToDate ) as "Total Sales"

    from InvoiceHeader T0

    End

    Thanks

  • Can you provide some consumable data, please? That is, some CREATE TABLE and INSERT statements which we can paste into SSMS and execute?

    Also, please explain the link between Invoice and InvoiceHeader, as your query above does not show one.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • How do you determine that 2021-01-04 (January 4th, 2021) is the first day of the year?  Is that valid for every year - or do other years start on a different day?  I am guessing, but it looks like you want the year to start on day 1 of ISO week 1 (which is the Monday prior to the first Thursday of the year).

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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