Q
Problem solve Get help with specific problems with your technologies, process and projects.

Using SQL to forecast growth, part 2

Here is the continuation of Jason's answer. See part 1.

Here's the actual SQL and its resulting rows:

```select Cardinal + 1 + ( select max(TimePeriod)
from History ) TimePeriod,
( select max(Magnitude)
from History
where TimePeriod = ( select max(TimePeriod)
from History ) ) +
( ( select avg(H.Magnitude-PrevH.Magnitude)
from History H
inner join
History PrevH
on H.TimePeriod-1 = PrevH.TimePeriod )
* ( Cardinal + 1 ) ) Forecasted
from TwoDigitCardinals
order by Cardinal;

TIMEPERIOD FORECASTED
---------- ----------
5                2466
6                2832
7                3198
8                3564
9                3930
10               4296
11               4662
...
102             37968
103             38334
104             38700
```
For exponential growth, the SQL to get our multiplier looks like this:
```select avg(H.Magnitude/PrevH.Magnitude) Multiplier
from History H
inner join
History PrevH
on H.TimePeriod-1 = PrevH.TimePeriod;

Multiplier
----------
1.284
```
Mathematically, my formula for exponential growth looks like f(x) = a^x + b, where x is the time period extending into the future, and f(x) is the projected magnitude.

Here is the SQL and result set for exponential growth, which uses the POWER function to raise the multiplier to a power relative to the forecasted time period. (Please note, the POWER function or a function with similar functionality should exist in your SQL implementation. I know POWER exists in Oracle, MS SQL Server, and MySQL.)

```select Cardinal + 1 + ( select max(TimePeriod)
from History ) TimePeriod,
( select max(Magnitude)
from History
where TimePeriod = ( select max(TimePeriod)
from History ) ) *
power( ( select avg(H.Magnitude/PrevH.Magnitude)
from History H
inner join
History PrevH
on H.TimePeriod-1 = PrevH.TimePeriod ),
Cardinal + 1) Forecast
from TwoDigitCardinals
order by Cardinal;

TIMEPERIOD FORECASTED
---------- -------------------
5                     2695.897
6                     3460.887
7                     4442.951
8                     5703.686
9                     7322.167
10                    9399.911
11                   12067.236
...
102         89879002753597.187
103        115383130030686.330
104	148124325902641.780
```
You can see how quickly the exponential growth model can grow. You'll want to tailor which model you use to your specific circumstances. Perhaps, even an average or a weighted average of the two will give you a better forecast. We can create any mathematical function such that f(x) is our magnitude and use the view based on the Cardinals table to create forecasts.

SearchDataManagement

• Why consider an augmented data catalog?

Automated and augmented data catalogs have been around for a few years, but adoption is still lagging. Find out why an enterprise...

• Alation brings data catalog technology to the public cloud

Alation's data intelligence technology is getting easier for organizations to use, with a new managed service that can help ...

• Yellowbrick Manager embraces Kubernetes for data warehouse

Yellowbrick is building out a new unified control plane to help users manage distributed cloud data warehouse deployments. The ...

• Air Force's data overhaul makes analytics a priority

With its data locked in legacy systems that didn't communicate with one another, in 2017 the Air Force started to overhaul of its...

• 6 reasons you may need data science as a service

There are plenty of reasons to outsource all or part of a data science project to a service. Find out how enterprises are using ...

• Logi Analytics acquired by ERP vendor Insightsoftware

Logi Analytics, a BI vendor specializing in embedded business intelligence, was acquired on April 7 by an ERP vendor specializing...

SearchSAP

• Unpatched applications threaten SAP security

Cyberattacks are a significant threat to unpatched, unprotected SAP applications, according to a new threat intelligence report ...

• Understand your S/4HANA deployment options

SAP deployment options can be confusing, particularly with name changes. Learn which SAP program is right for your company based ...

• MSC Industrial Supply transforms to SAP S/4HANA private cloud

MSC Industrial Supply needed to cut costs and modernize its ERP system, so it first moved to S/4HANA on a hybrid cloud and has ...

SearchSQLServer

• SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

TheServerSide.com

• Microsoft previews OpenJDK distro to the delight of devs

In a move meant to attract more Java developers to its Azure cloud and further support the Java community, Microsoft launched a ...

• Supreme Court ruling on Java APIs eases developer worries

Now that the Supreme Court has ruled for Google over Oracle in their high-stakes copyright battle over Java APIs, developers can ...

• Genuitec's CodeTogether 4.0 promotes pair programming

With Genuitec CodeTogether 4.0, development teams can work collaboratively even when remote. The product supports pair ...

SearchDataCenter

• New Intel Ice Lake processors boost performance, security

Intel launches third-generation Xeon Scalable processors that bolster security, accelerate common data center workloads by 46% on...

• IBM tools speed mainframe application modernization projects

IBM has released new versions of its application modernization tools designed to bring its Z series of mainframe applications in ...

• Nvidia vs. AMD: Compare GPU offerings

Chip vendors Nvidia and AMD each offer GPUs optimized for large data centers. Compare the two to decide which best suits your ...

SearchContentManagement

As the pandemic disrupts paper workflows, Adobe courts small business users with simple webforms, digital signatures and payments...

• Know when to use a headless CMS and when it's not worth it

Headless CMSes aren't a silver bullet for every circumstance. Evaluate three solid use cases for a headless CMS and three ...

• 8 business benefits and challenges of video conferencing

Remote work necessitates software such as video conferencing software. Consider both the business benefits and the challenges ...

SearchHRSoftware

• Hybrid workplace may give employers hiring edge

The post-pandemic office is leaning strongly in the direction of hybrid workplaces. Job candidates view working from home as a ...

• Biden infrastructure plan may deliver huge training boost

President Biden's \$2T infrastructure plan will likely result in training programs for rapid reskilling, especially in the ...

• 10 learning and development trends for the next normal

Today's companies need to upskill and reskill their workforces to stay competitive. Here's a look some of the most important ...

Close