Solved

Demand Calcuations with SQL

Posted on 2008-06-09
11
246 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
  • 6
  • 3
  • 2
11 Comments
 

Author Comment

by:r270ba
Comment Utility
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 20

Accepted Solution

by:
JesterToo earned 250 total points
Comment Utility
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
Comment Utility
So then I should use ASP.net?  
0
 
LVL 20

Expert Comment

by:JesterToo
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 20

Expert Comment

by:JesterToo
Comment Utility
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
Comment Utility
I REALLY appreciate it and I will wait!
0
 
LVL 10

Assisted Solution

by:tboy6423
tboy6423 earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now