Odtnhj

I am trying to add MTD Sales in a SQL Query. I figured out how to do that with JOINS but i want to use a CTE to calculate MTD sales and then use that to calculate projected_sales.Formula for projected sales is (MTD/wkdaysinmonth*wkdaystodate)[which is also stored in CTE Table). Is there a way to make it easy? I wrote the following code;

Input:

Email PaymentAmount orderdate
xyz@gmail.com 10 11/01/2018
xyz@gmail.com 20 11/09/2018

sample output:

EmailAddress MTD Projected_sales
xyz@gmail.com 30 0.19

where Projected sales is calculated as number of days passed=7 and total number of business days in november 22. [30/7*22]=0.19 (Present date = 11/09/2018)

with dates as(
 select dateadd(d,-day(getdate())+1,convert(date,getdate())) as startofmonth,
 dateadd(d,-1,dateadd(m,1,dateadd(d,-day(getdate())+1,convert(date,getdate())))) as endofmonth,
 convert(date,getdate()) as today
)
,daycounts as(
 select dates.*,

 (DATEDIFF(dd, startofmonth, endofmonth) + 1)
 -(DATEDIFF(wk, startofmonth, endofmonth) * 2)
 -(CASE WHEN DATENAME(dw, startofmonth) = 'Sunday' THEN 1 ELSE 0 END) 
 -(CASE WHEN DATENAME(dw, endofmonth) = 'Saturday' THEN 1 ELSE 0 END) as wkdaysinmonth,

 (DATEDIFF(dd, startofmonth, today) + 1)
 -(DATEDIFF(wk, startofmonth, today) * 2)
 -(CASE WHEN DATENAME(dw, startofmonth) = 'Sunday' THEN 1 ELSE 0 END) 
 -(CASE WHEN DATENAME(dw, today) = 'Saturday' THEN 1 ELSE 0 END) as wkdaystodate

 from dates
) 
SELECT DISTINCT Customers.EmailAddress as email,
o1.YTD
FROM
 Customers
INNER JOIN
 Orders
 ON
 Orders.CustomerID= Customers.CustomerID
JOIN
(SELECT 
c.EmailAddress,
SUM(Orders.PaymentAmount) AS YTD
FROM
Customers c
JOIN 
Orders
ON c.CustomerID=Orders.CustomerID
WHERE
Orders.OrderDate BETWEEN '01/01/2018 00:00' AND GETDATE()
GROUP BY
EmailAddress) AS o1 ON o1.EmailAddress = Customers.EmailAddress
WHERE
Orders.OrderDate >= (GETDATE()-7)

You can try to use cte recursive create a calendar table for orderdate startDate to endDate.

Then OUTER JOIN base on calendar table and do condition aggregate function in subquery get workdate.

;WITH cte 
 AS (SELECT email, 
 Dateadd(day, 1, Eomonth(Min(orderdate), -1)) minDt, 
 Dateadd(day, 1, Eomonth(Max(orderdate))) maxDt 
 FROM t 
 GROUP BY email 
 UNION ALL 
 SELECT email, 
 Dateadd(day, 1, mindt), 
 maxdt 
 FROM cte 
 WHERE Dateadd(day, 1, mindt) < maxdt), 
 cte2 
 AS (SELECT *, 
 Count(CASE 
 WHEN Datename(dw, t1.mindt) NOT IN ('Sunday', 'Saturday' ) 
 THEN 
 1 
 END) OVER( ORDER BY t1.mindt) workdt 
 FROM cte t1) 
SELECT t1.email, 
 t2.total, 
 Max(diffdt) / ( Max(workdt) * Max(workdtmax) * 1.0 ) Projected_sales 
FROM (SELECT *, 
 Max(workdt) 
 OVER( 
 partition BY email 
 ORDER BY workdt DESC) workdtMax, 
 Datediff(day, Min(mindt) OVER(partition BY email ORDER BY workdt) 
 , Max(mindt) OVER(partition BY email ORDER BY workdt DESC)) + 1 diffdt 
 FROM cte2) t1 
 LEFT JOIN (SELECT email, 
 Sum(paymentamount) total, 
 Min(orderdate) minDt, 
 Max(orderdate) maxDt 
 FROM t 
 GROUP BY email) t2 
 ON t1.mindt BETWEEN t2.mindt AND t2.maxdt 
 AND t1.email = t2.email 
WHERE t2.total IS NOT NULL 
GROUP BY t1.email, 
 t2.total 

sqlfiddle

Reuslt

email total Projected_sales
xyz@gmail.com 30 0.19480519480519

You can generate a "calendar" table that has the weekdays for each day in the month.

Your calculation for the projected doesn't make sense to me. So, I've also included what I consider to be a better calculation:

with dates as (
 select distinct dte,
 (case when datename(weekday, dte) not in ('Saturday', 'Sunday') then 1 else 0 end) as num_weekdays,
 dte as month_start
 from t cross apply
 (values (dateadd(day, 1 - day(orderdate), orderdate))) v(dte)
 union all
 select dateadd(day, 1, d.dte),
 (case when datename(weekday, dte) not in ('Saturday', 'Sunday') then 1 else 0 end) + num_weekdays,
 d.month_start
 from dates d
 where dte < dateadd(day, -1, dateadd(month, 1, month_start))
 ),
 d as (
 select d.*, max(num_weekdays) over (partition by month_start) as month_weekdays
 from dates d
 )
select d.month_start, t.email,
 sum(paymentamount) as mtd,
 sum(paymentamount) * max(month_weekdays) / max(d.num_weekdays) as my_projected,
 sum(paymentamount) * 1.0 / (max(month_weekdays) * max(d.num_weekdays)) as your_projected
from t join
 d
 on t.orderdate = d.orderdate
group by d.month_start, t.email;

Here is a db<>fiddle.