?
Solved

Demand Calcuations with SQL

Posted on 2008-06-09
11
Medium Priority
?
253 Views
Last Modified: 2010-03-20
I am trying to do some inventory demand forecasting and I would like to keep it all server side with SQL (as opposed to an ASP.net page doing the calculating).  Can I do this with a SQL statement?  I am posting some psuedo code below...please let me know if you have any questions.
--Delcare Varialbes--
--Busisness Days--
declare @BdPrvYr
declare @BdCurYr
 
--Total Usage By Month--
declare @LstYrPrvMntTotUsg
declare @LstYrCurMntTotUsg
 
--Total Usage By Business Day--
declare @LstYrPrvMntBdUsg
declare @LstYrCurMntBdUsg
 
--Weighted Extensions--
declare @LstYrPrvMntExt
declare @LstYrCurMntExt
 
--Trending Varialbes--
declare @CurYrTrnd
declare @PrvYrTrnd
declare @TrndFactor
 
--Estimated Period Usage Varialbes--
declare @EstPerUsg
 
--Forecasted Demand Varialbe--
declare @FrcstDmnd
 
--Initialize Business Days--
/*Array for BdPrvYr from 1-12 (for the months) for the number of business days per month last year*/
/*Array for BdCurYr from 1-12 (for the months) for the number of business days per month this year*/
 
--Set Total Usage By Month Variables--
set @LstPrvMntTotUsg = select sum(trxqty) from IV30300 where itemnmbr=@itemNumber and locncode=@location and docdate=/*This month last year - 1 month*/
set @LstCurMntTotUsg = select sum(trxqty) from IV30300 where itemnmbr=@itemNumber and locncode=@location and docdate=/*This month last year*/
 
--Set Total Usage By Business Day--
set @LstYrPrvMntBdUsg = @LstPrvMntTotUsg / /*Number of business days this month last year - 1 month*/
set @LstYrCurMntBdUsg = @LstCurMntTotUsg / /*Number of business days this month last year*/
 
--Calculate Extensions--
set @LstYrPrvMntExt = @LstYrPrvMntBdUsg * 2.0
set @LstYrCurMntExt = @LstYrCurMntBdUsg * 1.0
 
--Calcualate Trending Factors--
set @PrvYrTrnd = select sum(trxqty) from IV30300 where itemnmbr=@itemNumber and locncode=@location and docdate=/*This month last year's previous 3 months*/
set @CurYrTrnd = select sum(trxqty) from IV30300 where itemnmbr=@itemNumber and locncode=@location and docdate=/*This months previous 3 months*/
 
set @TrndFactor = (@CurYrTrnd - @PrvYrTrnd) / @PrvYrTrnd
 
--Calculate Estimated Period Usage--
set @EstPerUsg = (/*Number of busisness days this month*/ * ((@LstYrPrvMntExt + @LstYrCurMntExt) / 3.0))
 
--Calculate Forecasted Demand--
set @FrcstDmnd = @EstPerUsg + @TrndFactor%

Open in new window

0
Comment
Question by:r270ba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
11 Comments
 

Author Comment

by:r270ba
ID: 21746348
Ok so I have been working some more and here is what I have so far.  The calculations seem to be pretty striaght forward with the exception of the number of business days in a month.  

As you can see line 17,18, and 31 need to have the # of business days for the /*month needed denoted by the comments*/.  Any ideas?
declare @LstYrPrvMntTotUsg float
declare @LstYrCurMntTotUsg float
declare @LstYrPrvMntBdUsg float
declare @LstYrCurMntBdUsg float
declare @LstYrPrvMntExt float
declare @LstYrCurMntExt float
declare @PrvYrTrnd float
declare @CurYrTrnd float
declare @TrndFactor float
declare @EstPerUsg float
declare @FrcstDmnd float
 
select @LstYrPrvMntTotUsg = -sum(trxqty) from IV30300 where itemnmbr='5004pc' and docdate>DATEADD(month, DATEDIFF(month, 0, GETDATE())-13, 0) and docdate<DATEADD(month, DATEDIFF(month, 0, GETDATE())-12, 0) and (doctype='5' or doctype='6') and trxloctn='tsp'
select @LstYrCurMntTotUsg = -sum(trxqty) from IV30300 where itemnmbr='5004pc' and docdate>DATEADD(month, DATEDIFF(month, 0, GETDATE())-12, 0) and docdate<DATEADD(month, DATEDIFF(month, 0, GETDATE())-11, 0) and (doctype='5' or doctype='6') and trxloctn='tsp'
 
--Set Total Usage By Business Day--
set @LstYrPrvMntBdUsg = @LstYrPrvMntTotUsg / 20/*Number of business days this month last year - 1 month*/
set @LstYrCurMntBdUsg = @LstYrCurMntTotUsg / 19/*Number of business days this month last year*/
 
--Calculate Extensions--
set @LstYrPrvMntExt = @LstYrPrvMntBdUsg * 2.0
set @LstYrCurMntExt = @LstYrCurMntBdUsg * 1.0
 
--Calcualate Trending Factors--
select @PrvYrTrnd = -sum(trxqty) from IV30300 where itemnmbr='5004pc' and trxloctn='tsp' and docdate>DATEADD(month, DATEDIFF(month, 0, GETDATE())-15, 0) and docdate<DATEADD(month, DATEDIFF(month, 0, GETDATE())-12, 0) and (doctype='5' or doctype='6')
select @CurYrTrnd = -sum(trxqty) from IV30300 where itemnmbr='5004pc' and trxloctn='tsp' and docdate>DATEADD(month, DATEDIFF(month, 0, GETDATE())-3, 0) and docdate<DATEADD(month, DATEDIFF(month, 0, GETDATE())-0, 0) and (doctype='5' or doctype='6')
 
set @TrndFactor = ((@CurYrTrnd - @PrvYrTrnd) / @PrvYrTrnd)*100
 
--Calculate Estimated Period Usage--
set @EstPerUsg = (/*Number of business days current month*/20 * ((@LstYrPrvMntExt + @LstYrCurMntExt) / 3.0))
 
--Calculate Forecasted Demand--
set @FrcstDmnd = @EstPerUsg + @TrndFactor
 
Print 'Total Usage By Month'
print @LstYrPrvMntTotUsg
print @LstYrCurMntTotUsg
print 'Total Usage Business Day'
print @LstYrPrvMntBdUsg
print @LstYrCurMntBdUsg
print 'Extensions'
print @LstYrPrvMntExt
print @LstYrCurMntExt
print 'Trending'
print @PrvYrTrnd
print @CurYrTrnd
print ''
print @TrndFactor
print 'Estimated Period Usage'
print @EstPerUsg
print 'Forecasted Demand For Current Month'
print @FrcstDmnd

Open in new window

0
 
LVL 22

Accepted Solution

by:
JesterToo earned 1000 total points
ID: 21747276
T-SQL isn't the ideal language to perform this type of data manipulation.  Since this SS2005 you do have an option to write this code in .net on the server.

However, it can be done in t-sql also.  There are two approaches... one is to create a "calendar" of the months involved and simply loop thru this structure determining of a date is a weekday or not (I assume "business day" equates to "week day" in this algorithm... if not, then you're going to need an external calendar, such as another table, to hold the dates that are business days).  

The other approach is to use the datediff and datepart functions to determine the calendar values dynamically if coding them statically isn't desirable.

0
 

Author Comment

by:r270ba
ID: 21750691
So then I should use ASP.net?  
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:JesterToo
ID: 21751244
asp.net is for web pages... SS2005 supports writing triggers and functions in any of the .net languages (VB, C#, C++) directly.

I'm not suggesting that you change from t-sql to .net... that depends on how comfortable you are with either of the "languages".  It seems you already have most of your logic written using t-sql and only need a function to a date based on the number of business days from another date.  You could write this on the server in either t-sql or one of the .net languages... whichever is easier for you.

But, the first thing you have to establish is what is the definition of "business days"... does it consider holidays in its determination.  If so, how do you determine what the holidays are.
0
 

Author Comment

by:r270ba
ID: 21751311
Well this is definately going to be incorporated into an inventory management website written in ASP.net, but I was wondering if I should do the programming logic in SQL or allow the asp.net code behind (c# in my case) do the logic.
0
 
LVL 22

Expert Comment

by:JesterToo
ID: 21751620
Normally, for good performance and encapsulation, you would want to keep this kind of calculation close to the data... that implies either in the database itself or in a business layer between the presentation layer (the web ui) and the data layer (the database).  If you can wait until tomorrow I'll try to create a function in t-sql that calculates one business date that is x days from a second date disregarding holidays.  I don't have time to do it today due to other pressing requirements at my office.

Lynn
0
 

Author Comment

by:r270ba
ID: 21751652
I REALLY appreciate it and I will wait!
0
 
LVL 10

Assisted Solution

by:tboy6423
tboy6423 earned 1000 total points
ID: 21754647
if you are purely looking for Business Days being Monday thru Friday, here is code that will give you the number of actual days
You just need to define your start and end dates...
if you need to include holidays, you would have to define those holidays in another table..i.e.

--Holiday--, --Date--
4thJul, 07/14/08
Flag Date, 06/14/08

can do a quick count of holidays that fall within the begin and end date, then subtract that from the total business days.

declare @startdate smalldatetime
declare @enddate smalldatetime
Set @Startdate='05/01/08'
set @enddate ='05/31/08'

select ((DATEDIFF(DAY, @Startdate, @enddate)+1) / 7 * 5 + (DATEDIFF(DAY, @Startdate, @enddate)+1) % 7 -  
               (SELECT COUNT(*)FROM ( SELECT 1 AS d UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4  
                UNION ALL SELECT 5  UNION ALL SELECT 6 UNION ALL SELECT 7 ) weekdays
                    WHERE d <= (DATEDIFF(DAY, @Startdate, @enddate)+1) % 7AND DATENAME(WEEKDAY, @enddate - d + 1)  
                    IN ('Saturday','Sunday'))) as busdays

Based on what you are showing above, you can easily set  your Start and End dates for the periods you need.
0
 

Author Comment

by:r270ba
ID: 21754790
Could I set the start and end dates to a dynamic value?

Example:

startdate = last month
enddate = this month
0
 
LVL 10

Expert Comment

by:tboy6423
ID: 21760246
I'm guessing you want to get the entire month for the previous month, and last day of the current month...

couple of ways to do it..here is what i generally use...

Declare @DateFunc as datetime
declare @enddate as datetime  
 SET @vDateFunc = CAST(YEAR(getdate()) AS VARCHAR(4)) + '/' +
                       CAST(MONTH(getdate()) AS VARCHAR(2)) + '/01'
--This gets the first day of the next month, and subtracts one day, this will accomodate leap years
    SET @enddate  = DATEADD(DD, -1, DATEADD(M, 1, @DateFunc))

--Checks if the month is january to adjust for previous year data when crossing a year
Set @StartDate = case when month(getdate())-1=0 then '12/01/'+cast (year(getdate())-1 as varchar(4))
             else cast(month(getdate())-1 as varchar(2))+'/01/'+cast (year(getdate())-1 as varchar(4)) end
0
 

Author Comment

by:r270ba
ID: 21843243
We have decided that we will hire someone out to do this for us.  Thanks for all of your help.  I will apply points appropriately.
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question