question

newbee avatar image
0 Votes"
newbee asked pituach edited

How to get WTD,MTD and QTD in sql server

Hello All,

I have table sales where I have daily ,Weekly and monthly sales Data.

The columns are TrendType,Date and Sales

So here my requirement is I need to create a query on my table to calculate the last 12 months, 12 weeks and last 30 days along with MTD,WTD and QTD data.

So here the trendtype for MTD should show as monthly, trendtype for WTD should show as Weekly and trendtype for QTD should show as Quarterly as these comes when we select the respected trend types in our reports.(Ex: if I select monthly then I can see last 12 months and MTD also)

Could any one please help how to do

sql-server-generalsql-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.

Yes, if you post CREATE TABLE statement for your table(s) and INSERT statements with sample data the result you expect from the sample. Then we have something we can test. And we get a better understanding of what you are looking for.

0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered pituach edited

Hi,

In general in order to get the Week to date (WTD), Month to date (MTD) and Year to date (YTD) in SQL Server, you can use WINDOWS FUNCTION SUM and make the sum only on the rows which has the same interval parameter (for example for MTD we will use CASE which only SUM the rows where the DATEDIFF by month is zero between the current row and the row we aggregate).

You should lern more about using the OVER Clause in order to configure the running windows taht are calculated in a windows functions like SUM

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15&WT.mc_id=DP-MVP-5001699

The basic use in your case can be something like below:

 -- TrendType,Date and Sales
 CREATE TABLE Tbl (
   TrendType nvarchar(20),
   MyDate date,
   Sales INT
 )
    
 TRUNCATE TABLE Tbl
 INSERT INTO Tbl(TrendType, MyDate, Sales) VALUES 
  ('Ronen', '2021-02-27', 1),
  ('Ronen', '2021-02-26', 2),
  ('Ronen', '2021-02-25', 3),
  ('Ronen', '2021-03-26', 4),
  ('Ronen', '2021-04-25', 5),
  ('Ronen', '2022-02-27', 6),
  ('Ronen', '2022-02-26', 7),
  ('Ronen', '2023-02-27', 8);
 GO
    
    
 Select t.TrendType, t.MyDate, t.Sales, 
  --y=YEAR(t.MyDate), m=MONTH(t.MyDate), w=DATEPART(WEEK,t.MyDate),
  WTD = SUM(t.Sales)
  OVER (PARTITION BY t.TrendType, YEAR(t.MyDate), MONTH(t.MyDate), DATEPART(WEEK,t.MyDate) ORDER BY t.MyDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  MTD = SUM(t.Sales) 
  OVER (PARTITION BY t.TrendType, YEAR(t.MyDate), MONTH(t.MyDate) ORDER BY t.MyDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
  YTD = SUM(t.Sales) 
  OVER (PARTITION BY t.TrendType, YEAR(t.MyDate) ORDER BY t.MyDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
 FROM Tbl t

Or you can get better performance with something like below

 Select a.TrendType, a.MyDate, a.Sales, 
   WTD = Sum(Case When datediff(WEEK, b.MyDate, a.MyDate) = 0 Then b.Sales Else 0 End),
   MTD = Sum(Case When datediff(month, b.MyDate, a.MyDate) = 0 Then b.Sales Else 0 End),
   YTD = Sum(Case When datediff(year, b.MyDate, a.MyDate) = 0 Then b.Sales Else 0 End)
 From Tbl a
    join Tbl b
       on a.TrendType = b.TrendType
          and b.MyDate <= a.MyDate
 Group by a.TrendType, a.MyDate,  a.Sales
 GO

If this not fir your case, then please provide queries to create your table and insert sample data + the expected result set according to the ample data - as Erland asked

· 2
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.

Why do you think the second query will give better performance?

0 Votes 0 ·

Hi,

Good point... I am not sure :-)

The second query need to scan the table twice and the first query need to scan the table once, IO will be less in the first one probably.

but since the partitioning in the first query is different for each column of the three, then the server cannot use the same order for the aggregation and it will probably need to sort the data one time for each of the windows function. This sorting will cost a lot since we do not have indexes and might even lead to spool.

In the second query you have a simple aggregation which only need one move over the data (but using JOIN with the second scan).

In any case best answer is to check, as it might be different from server to server, but my guess is that with this exact poor DDL+DML the second one will be better.

It might be a "fight" between more IO to scan the rows (in second query) and the sorting (in the first query) :-)

0 Votes 0 ·
BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered BertZhoumsft-7490 edited

Hi,@newbee

Welcome to Microsoft T-SQL Q&A Forum!

Experts seem to have given you the correct way to use the window function. My query is based on his foundation. Using cte is more complicated. I still recommend his writing method.
By the way, if your TrendType is more of the same data, it is recommended to use partitions. It is better to write SQL statements directly, and put month(DATE) and year(DATE) after the select.

 declare @Sales table (TrendType nchar(20),Date date, Sales int)
 insert @Sales 
 select 'P01','2022-01-20',  100 union all
 select 'P02','2022-02-01',  200 union all
 select 'P01','2022-03-12', 50 union all
 select 'P02','2022-03-20', 75 union all
 select 'P02','2022-01-01',  120 union all
 select 'P01', '2022-01-18', 60 union all
 select 'P01', '2022-02-11', 90;
    
   Select TrendType, Date,
   sum(sales)over(partition by TrendType,DATEPART(week,date ) order by date )as WTD,
   sum(sales)over(partition by TrendType,month(date) order by date) as MTD,
   sum(sales)over(partition by TrendType,year(date),DATEPART(quarter,date) order by date) as QTD
      
 From @Sales 
 group by TrendType,Date,Sales

Best regards,
Bert Zhou


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



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.