Sql query - Projected Monthly Revenue
-
Hi,
I've been sitting with a projected revenue query for sometime , trying to figure out the best way to do this.
Basically, I'm trying to get a monthly revenue number based on daily shipment totals.
eg: Total Rev MTD $88,720 / 9 days = $9,857 * 23 shipping days = $226,731 as Monthly Project Total
The 23 days is derived from March having 31 days and subtracting 8 days for weekends. I'm multiplying this at the end.
The problem is, we don't ship on the weekends ie. Sat and Sun and so in the above example the days should read 7 and not 9 , (less 2 day weekend)
I need help to figure out how to add the days and exclude the weekend at the point in time the query is run.
SELECT
z.[name]
,y.[item]
,x.[ship_date]
,y.[price]
,x.[qty_shipped]
,Cast(x.[qty_shipped]* y.[price] as decimal(10, 2) ) as Rev
,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS StartOfMonth
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as EOMonthDate
,Datediff(day, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0), getdate()) as TotalDays
,DATEDIFF(DAY,GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1,0)) - 7 As remaining_days /*remaining days see -8 to minus weekends*/
,DAY(DATEADD(DD,-1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))) DaysinMonth
,DAY(DATEADD(DD,-1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)))-8 TotalDYLessWkend
FROM [EES_App].[dbo].[co_ship] x
inner JOIN [EES_App].[dbo].[coitem] y
on x.co_num=y.co_num
INNER JOIN [EES_App].[dbo].[custaddr] z
on y.co_cust_num=z.cust_num
WHERE
Year(x.ship_date ) = Year(CURRENT_TIMESTAMP)
AND Month(x.ship_date ) = Month(CURRENT_TIMESTAMP)As you see, I tried a few combinations, I have a start and end of month , also number of days in the month and you can see I tried to hard code -8 for weekend.
appreciated any guidance
Thanks
Greg
-
This would be much easier using a Calendar table or function. We can get those counts by generating the list of dates - then counting the dates that are weekends, the dates that are not weekends - and the total number of days.
Declare @startDate datetime = '2021-03-01'
, @endDate datetime = '2021-03-31';
With t(n)
As (
Select t.n
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, dates (CalendarDate)
As (
Select Top (datediff(day, @startDate, @endDate) + 1)
dateadd(day, checksum(row_number() over(Order By @@spid) - 1), @startDate)
From t t1, t t2, t t3
)
, dateCounts (WeekendCount, WeekdayCount, TotalDays)
As (
Select sum(Case When datepart(weekday, dt.CalendarDate) > 5 Then 1 Else 0 End)
, sum(Case When datepart(weekday, dt.CalendarDate) < 6 Then 1 Else 0 End)
, count(*)
From dates dt
)
Select *
From dateCounts;Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I've added some date calc ctes before the query to isolate the date calcs from the main query (also makes it much easier to test them standalone for assorted dates). You can CROSS JOIN the last cte to use those values in the main query as you need to. I'm not at all sure what results you want backed from each column in the main query (your column name is not really helpful at determining what should be returned in each column). I also adjusted the WHERE clause to get rid of the function calls on the table column per standard best practice.
DECLARE @date date
SET @date = GETDATE() --'20210112'
;WITH cte_date_calcs1 AS (
SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0) AS StartOfMonth,
DATEADD(month, DATEDIFF(month, 0, @date) + 1, 0) AS StartOfNextMonth
),
cte_date_calcs2 AS (
SELECT *, DATEDIFF(DAY, 0, StartOfMonth) % 7 AS StartDay,
DATEDIFF(DAY, StartOfMonth, @date) AS TotalDaysMTD,
DATEDIFF(DAY, StartOfMonth, StartOfNextMonth) AS TotalDaysInMonth
FROM cte_date_calcs1
),
cte_date_calcs3 AS (
SELECT *,
TotalDaysMTD - (TotalDaysMTD / 7 * 2) - CASE
WHEN StartDay + TotalDaysMTD % 7 - 1 >= 6 THEN 2
WHEN StartDay + TotalDaysMTD % 7 - 1 = 5 THEN 1 ELSE 0 END AS ShippingDaysMTD,
TotalDaysInMonth - (TotalDaysInMonth / 7 * 2) - CASE
WHEN StartDay + TotalDaysInMonth % 7 - 1 >= 6 THEN 2
WHEN StartDay + TotalDaysInMonth % 7 - 1 = 5 THEN 1 ELSE 0 END AS ShippingDaysInMonth
FROM cte_date_calcs2
)
SELECT
z.[name]
,y.[item]
,x.[ship_date]
,y.[price]
,x.[qty_shipped]
,Cast(x.[qty_shipped]* y.[price] as decimal(10, 2) ) as Rev
,StartOfMonth
,DATEADD(SECOND, -1, StartOfNextMonth) AS EOMonthDate
,TotalDaysMTD AS TotalDays
--? not sure what results you want for the columns below. Hopefully
--? from my code you can determine the correct columns to do the calcs you want
,DATEDIFF(DAY,GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1,0)) - 7 As remaining_days /*remaining days see -8 to minus weekends*/
,DAY(DATEADD(DD,-1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))) DaysinMonth
,DAY(DATEADD(DD,-1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)))-8 TotalDYLessWkend
FROM [EES_App].[dbo].[co_ship] x
CROSS APPLY cte_date_calcs3 --<<--
inner JOIN [EES_App].[dbo].[coitem] y
on x.co_num=y.co_num
INNER JOIN [EES_App].[dbo].[custaddr] z
on y.co_cust_num=z.cust_num
WHERE
x.ship_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0) AND
x.ship_date < DATEADD(MONTH, DATEDIFF(MONTH, 0, @date) + 1, 0)SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- gjoelson 29755 wrote:
...
The problem is, we don't ship on the weekends ie. Sat and SunThe problem is, we don't ship on the weekends ie. Sat and Sun ...
Do you ship on Christmas Day? On New Year day?
_____________
Code for TallyGenerator -
Thank you for your guidance and reformatting this...it is helpful.
However the main calculation that I'm looking for is to figure out how to add the days and exclude the weekend at the point in time the query is run. in the above above example or even using today as an example . its the 12th March , so 12 days , but I need to exclude last weekend , I need the day count to be 10 days.
thanks.
-
Good Point Sergiy.
Public holidays are also excluded. , but right now Id just like to solve the weekends and then see if there is a solution for hoildays as well.
thanks
- gjoelson 29755 wrote:
Thank you for your guidance and reformatting this...it is helpful.
However the main calculation that I'm looking for is to figure out how to add the days and exclude the weekend at the point in time the query is run. in the above above example or even using today as an example . its the 12th March , so 12 days , but I need to exclude last weekend , I need the day count to be 10 days.
thanks.
That is what the ShippingDays counts are in my code above. Run the ctes by themselves and return all columns from the last cte to see all the calcs that were done.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- Sergiy wrote:gjoelson 29755 wrote:
... The problem is, we don't ship on the weekends ie. Sat and SunThe problem is, we don't ship on the weekends ie. Sat and Sun ...
Do you ship on Christmas Day? On New Year day?
When I worked at Smith & Nephew, yes, we did. We supplied medical parts, sometimes for emergency surgery. We potentially shipped on every day of the year.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Scott, My bad .....
I just didn't see that particular one....That's exactly what I needed. many thanks !
-
You're welcome! Yeah, I'm sorry too, but I couldn't figure out exactly what values to return in what columns based on your original query, so I was kinda' forced to punt it back to you to fill in those calcs in the main query.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- gjoelson 29755 wrote:
Good Point Sergiy.
Public holidays are also excluded. , but right now Id just like to solve the weekends and then see if there is a solution for hoildays as well.
thanks
if you need to consider public holidays then your solution must be based on a Calendar table. Which will give you both holidays and weekends. So, your solution “for weekends only” appears to be just a waste of time.
You have to consider, that in different situations in different locations in different organisations and even in different departments of the same organisation observed public holidays may be different.
So, Scott’s solution may look good to you now, but it does not solve the problem.
Not to mention, hardcoded value “-8” suggest it’s not correct.
_____________
Code for TallyGenerator -
Yet another application where a tally function could be useful. The projection is off by $1.11 perhaps due to rounding
declare
@revMTD int = 88720,
@today date = '2021-03-11';
declare
@curr_month_day_1 date=datefromparts(year(@today), month(@today), 1),
@curr_month_last date=eomonth(@today);
select sum(mtd.day_count) mtd_day_count,
count(*) wk_day_count,
cast((@revMTD/(sum(mtd.day_count)*1.0)*count(*)) as decimal(14, 2)) as monthly_project_total
from dbo.fnTally(0, datediff(day, @curr_month_day_1, @curr_month_last)) fn
cross apply (values (dateadd(day, fn.n, @curr_month_day_1))) v(dt)
cross apply (values (case when v.dt<=@today then 1 else 0 end)) mtd(day_count)
where datepart(weekday, v.dt) between 2 and 6;mtd_day_countwk_day_countmonthly_project_total
923226728.89Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- Sergiy wrote:gjoelson 29755 wrote:
Good Point Sergiy.
Public holidays are also excluded. , but right now Id just like to solve the weekends and then see if there is a solution for hoildays as well.
thanks
if you need to consider public holidays then your solution must be based on a Calendar table. Which will give you both holidays and weekends. So, your solution “for weekends only” appears to be just a waste of time. You have to consider, that in different situations in different locations in different organisations and even in different departments of the same organisation observed public holidays may be different.
So, Scott’s solution may look good to you now, but it does not solve the problem. Not to mention, hardcoded value “-8” suggest it’s not correct.
I answered the q asked.
OP will need a nonworkdays table, but NOT a full calendar table: those are a waste of resources to read thru every time you need to find just nonwork days, particularly since they are often very bloated, will all kinds of date formatting being added to that table.
I isolated the date calcs in my code to (1) allow it be run stand-alone and (2) allow adjustments for nonworkdays to be added to the date calc code, so then the code in the main query does NOT have to change.
I did not hard-code anything.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Suppose there's a table of holidays with St Patrick's Day in it. The holiday(s) could be excluded in the query using WHERE NOT EXISTS and a correlated subquery. Because St Patrick's Day is on a Wednesday there's 1 less shipping day
/* table of holidays */
drop table if exists #holidays;
go
create table #holidays(
holiday_dt date primary key not null,
holiday_name nvarchar(40));
insert into #holidays(holiday_dt, holiday_name) values
('2021-03-17', 'St. Patricks Day');
--select * from #holidays;
declare
@revMTD int = 88720,
@today date = '2021-03-11';
declare
@curr_month_day_1 date=datefromparts(year(@today), month(@today), 1),
@curr_month_last date=eomonth(@today);
select sum(mtd.day_count) mtd_day_count,
count(*) wk_day_count,
cast((@revMTD/(sum(mtd.day_count)*1.0)*count(*)) as decimal(14, 2)) as monthly_project_total
from dbo.fnTally(0, datediff(day, @curr_month_day_1, @curr_month_last)) fn
cross apply (values (dateadd(day, fn.n, @curr_month_day_1))) v(dt)
cross apply (values (case when v.dt<=@today then 1 else 0 end)) mtd(day_count)
where datepart(weekday, v.dt) between 2 and 6
and not exists(select 1
from #holidays h
where v.dt=h.holiday_dt);mtd_day_countwk_day_countmonthly_project_total
922216871.11Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
It's easy and flexible to include weekends in a Calendar table, usually with two flags one that says weekends, one that says holidays. Sometimes Christmas falls on a Saturday, but you don't want to count it twice (once as a weekend and once as a holiday.) If you need a cookbook on creating and using calendar tables, you can find some code here:
https://www.sqlservercentral.com/steps/bones-of-sql-the-calendar-table
Some people prefer not to leave workdays in the calendar, which makes the table a little smaller. Then they use the table to get the number of days to be excluded from a simple DATEDIFF().
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply