question

JagjitSaini-0360 avatar image
0 Votes"
JagjitSaini-0360 asked EchoLiu-msft commented

YTD Column

Hi

I have below Stored Procedure . I want to calculate YTD also.
Suppose user enter @frDate = '01/01/2021' and @ToDate = '31/01/2021' then YTD value will be from 01/04/2020 t0 31/01/2021

Alter PROCEDURE [dbo].[SpBpGroupWiseSalePurchase]
@frDate date,
@toDate date,
@DocType nvarchar(1)
AS
BEGIN

 SET NOCOUNT ON;
 if @DocType = 'I'    
 Begin
     SELECT Code,Name,
     (Select sum(InvoiceTotal) from InvoiceLine where DocumentDate between @frDate and @ToDate) as "Total Sales"
     from InvoiceHeader T0
 End

END

Thanks

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

It seems that the fiscal year is from April 1 to March 31. So you need to find out the fiscal year start date based on the report start date (@frDate):

 Alter PROCEDURE [dbo].[SpBpGroupWiseSalePurchase]
     @frDate date,
     @toDate date,
     @DocType nvarchar(1)
 AS
 BEGIN
     SET NOCOUNT ON;
    
     DECLARE @fiscalYearStart date;
     DECLARE @fiscalStartMonthDate varchar(5) = '04-01';
     DECLARE @yearFrDate int;
    
     SET @yearFrDate = YEAR(@frDate);
    
     SET @fiscalYearStart = CONVERT(date, CAST(@yearFrDate AS varchar(4)) + '-' + @fiscalStartMonthDate, 121);
     IF @fiscalYearStart > @frDate
     BEGIN
         SET @fiscalYearStart = DATEADD(Year, - 1, @fiscalYearStart);
     END
    
     if @DocType = 'I'    
     Begin
         SELECT Code, Name,
         (Select sum(InvoiceTotal) from InvoiceLine where DocumentDate between @frDate and @ToDate) as "Total Sales",
         (Select sum(InvoiceTotal) from InvoiceLine where DocumentDate between @fiscalYearStart and @ToDate) as "YTD Sales"
         from InvoiceHeader
     END
 End
 GO
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @JagjitSaini-0360

Please also check:

 Alter PROCEDURE [dbo].[SpBpGroupWiseSalePurchase]
 @frDate date,
 @toDate date,
 @DocType nvarchar(1)
 AS
 BEGIN
    
  SET NOCOUNT ON;
  if @DocType = 'I'    
  Begin
      SELECT Code,Name,
      (Select sum(InvoiceTotal) 
   from InvoiceLine 
   where DocumentDate between @frDate and @ToDate) as "Total Sales",
   (Select sum(InvoiceTotal)
   from InvoiceLine 
   where DocumentDate between Dateadd(m,-9,@frDate) and @ToDate) as "YTD Sales"
      from InvoiceHeader T0
  End
 END

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

A better way to do this is to use a calendar table with a fiscal year value. It will make your query much easier and if you realign your fiscal year to Jan-Dec it will continue to work properly by updating the table for future years.

https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.