Get a Unique list with a summary of numbers in another column

[POSAPAY]

Hi,
I have a sales forecast table called tblSalesForecasts
which amongst many fields includes
SalesForecastEncID(Unique ID to this table.)
ModelEncID(unique ID to model, not unique to this table)
BaseModel(the name of the product)

In a second table I have the forecasted month data.
tblSalesForecastsQty
SalesForecastEncID - to reference back with
MonthID - digit of the month
YearID - the 4 digit for the year
ForecastQty - a number sales enters for their forecast.

How could I create a SQL statement to endup with a record set to be dumped to a report to show the list of BaseModels, Discription of each model looked up from tblModels using ModelEncID, and then a column of the total of forecast qty.

Each BaseModel name should show only once, and if one record's forecast is 10 and the next record's forecast's value is 20, then it should display 30 as the qty forecast for that product.

Thanks for any help!
-P

 

[markros]

select M.Description, S.BaseModel, ISNULL(T.Qty,0) as Qty, T.MonthID, T.YearID from tblModels M inner join  tblSalesForecasts S on M.ModelEncID = S.ModelEncID 
left join (select SalesForecastEncID, MonthID, YearID, sum(ForecastQty) as Qty from tblSalesForecastsQty group by SalesForecastEncID, MonthID, YearID) T on S.SalesForecastEncID = T.SalesForecastEncID

 

[POSAPAY]

Hi markros,
Thanks! It did run, but besides a couple of namings that I forgot about which I just corrected, it doesn't return the data I was hoping for.

I think I tried to over-simplify it in my original details... so here is what I've got literally:

tblSalesForecasts (from each sales person, one record for each customer and product sold to them. So if SalesPersonA, sells to CustomerA productA and ProductB, that is two records. They fill this out each month, so we have a report per sales person per month.)
Fields in here:
- SalesForecastID (integer, counting up by 1)
- CustomerID (int)
- ModelID (int)
- ForecastMonth (int)
- ForecastYear (int)

tblSalesForecastsQtyData ( for each record in tblSalesForecasts, here there are 6 records for 6 months of forecast for each customer and each product to that customer.)
- SalesForecastID (int)
- ForecastQty (int)
- Probability (int)
- MonthMM (int)
- YearYYYY (int)

tblModels (a table of products)
- ModelID (int counting up by 1)
- BaseModel (Name of a product)
- Description (It's details.)


So my goal is to have a table showing each product (meaning one record per product, only products in tblSalesForecasts) and showing the quantity forecast for each month. One of these reports would be generated for each month(tblSalesForecasts ForecastMonth and ForecastYear.)

So I would pass in a specific Month and year.. example: 8 (for August) and 2009 (for year)
Should get a table showing columns:
- BaseModel
- Description
- Month1Qty (A total for that product, that month and year from all sales people for all customers)
- Month2Qty
- Month3Qty
- Month4Qty
- Month5Qty
- Month6Qty

Month 1 - 6 in tblSalesForecastsQtyData relates the following way to the tblSalesForecasts:

Month1 in tblSalesForecastsQtyData field MonthMM is same as tblSalesForecasts field ForecastMonth - 1 (and YearYYYY would match ForecastYear(minus 1 if ForecastMonth was =1 )

Month2 in tblSalesForecastsQtyData field MonthMM is same as tblSalesForecasts field ForecastMonth (exactly the same) (YearYYYY is same as ForecastYear)

Month3 in tblSalesForecastsQtyData field MonthMM is same as tblSalesForecasts field ForecastMonth + 1 (YearYYYY is same as ForecastYear unless ForecastMonth = 12 then plus 1)

Month4 in tblSalesForecastsQtyData field MonthMM is same as tblSalesForecasts field ForecastMonth + 2 (YearYYYY is same as ForecastYear unless ForecastMonth = 11 or 12 then plus 1)

Month5 in tblSalesForecastsQtyData field MonthMM is same as tblSalesForecasts field ForecastMonth + 3 (YearYYYY is same as ForecastYear unless ForecastMonth = 10 or 11 or 12 then plus 1)

Month6 in tblSalesForecastsQtyData field MonthMM is same as tblSalesForecasts field ForecastMonth + 4 (YearYYYY is same as ForecastYear unless ForecastMonth = 9 or 10 or 11 or 12 then plus 1)

So table would be something like:
ProductA - DetailsOfProductA - LastMonthsQty - ThisMonthsQty - NextMonthsQty - 2MonthsFromNowQty - 3MonthsFromNowQty - 4MonthsFromNowQty

Each Month's Qty is the sum for A product from all sales people and customers for that month's forecast multiplied by the Probability, which is a 0 - 100 integer, so multiply by value in Probability/100 and then sum those up.

Sorry for this becoming so complex, but that is why I got stuck, because it is this complicated. I wonder if I'd need to take a SQL course even when I only need to do this once a year or so. I'm dumping it to classic ASP, since the system is using that internally.

Thanks for any and all help!
-Peter

 

[markros]

I see, it's actually not hard at all also.

We would need to have our 6 months/6 years variables ( a bit excessive, but OK) and then use a bit of trick to get our sums
(we would just group based on BaseModel, Description fields).

The trick to get our data is in using CASE statement for our sums, e.g.

SUM(case when [Year] = @Year1 and [Month] = @Month1 then ForecastQty end) as LastMonthQty, ...

So, you would be using the select statement very similar to what I posted, but group based on BaseModel, Description and use SUM(case .. ) statements.

I let for you the little exercise of IF ELSE IF statements at the top of your code to get @Month1, @Year1, etc. correctly created.

Sorry for not providing the exact query you want, but I believe using the information I provided you would be able to finish this task.

Let me know if you would need more help here, I may look at this thread on Saturday's night again.

 

[POSAPAY]

Hi Markros,

Thanks for your thoughts and ideas.
I'm pretty good in VBScript, in classic ASP, but when it comes to SQL, I've only mastered the understandning of simple Select statements. I usually get errors when I try to use DISTINCT, GROUP BY, etc.
So forgive me as I'm having a hard time catching on with these as I'm learning it.

In classic ASP I create a variable that contains the SQL statement as a string. Then I create an object(recordset) and open it with the variable as the source of the data.

So I'm not sure when you mention CASE and IF ELSE IF, if you mean stored procedures, or if this could be put in my VBScript as the data source's single SQL command.

If you can further help, that would be great, ast for me to understand this fully will probably take some time, and I'd like to have this one report up much sooner than that.

Thanks again for taking the time, and if you take time on Saturday as well!
-Peter

 

[markros]

Ok, in your VBScript set up 6 years and 6 months (do calculation for them in VBScript).

Then

select M.Description, S.BaseModel, SUM (case when Month = @Month1 and Year = @Year1 then T.Qty end) as LastMonthsQty, 
SUM (case when Month = @Month2 and Year = @Year2 then T.Qty end) as ThisMonthsQty, etc. for other Qty values

from tblModels M inner join  tblSalesForecasts S on M.ModelEncID = S.ModelEncID
INNER join tblSalesForecastsQty T on S.SalesForecastEncID = T.SalesForecastEncID group by M.Description, S.BaseModel where
T.Year between @Year1 and @Year6

You would need to pass 6 months and 6 years as parameters to your query.

Alternatively you can only pass one month/year and try incorporating logic of calculating next prev months in the query - but for me the query would be a bit harder to construct this way.

 

[POSAPAY]

Sure, that could work!

Recordset1:
So outside filter for the report is on:
- Report Month (from tblSalesForecasts ForecastMonth)
- Report Year (from tblSalesForecasts ForecastYear)

Recordset2:
One layer in this, from that set of records, we'd want the distinct list of Models. This is the number of record to be displayed, cross referenced from tblModels to get BaseModel and Description.
DISTINCT ModelID (from recordset 1)

For each record in Recordset2, we'd want to get for each of the 6 months(so calling this 6 times per record) and displaying the quantity in 6 columns basically.
The Total Qty for one record in recordset2 and one month is:

Recordset3:
Selecting all records in Recrodset1 that have the ModelID match the record we are on in Recordset2

Recordset4:
Taking Recordset3 and for each record in this set look up the forecast quantities from tblSalesForecastsQtyData with MonthMM being the original ForecastMonth in Recodset1 specs... but for each column we'd call it with either -1 or .. incrementally to +4. and summing the Qty for that one month and SalesForecastID.


I know I can do this with like 4..5 separate calls and loops in VBScript, it would be great to reduce it to less SQL calls.

 

[markros]

I'm sorry, you confused me now a bit. Do you have SQL Server installed on your computer with SSMS where you can play a bit?

 

[POSAPAY]

I develop on a separate database and sepearate virtual web.. so yeah.. I can play around.

Sorry about confusing you... let me see if I can simplify:

So I'm thinking nested loops in VBScript, where some loops could be avoided if we can combine SQL statements.

The final table would have a unique list of products and for each product 6 columns for 6 summed up forecast quantities.

If I understood you correctly, for each of the 6 month summary quantities we would run a separate call to SQL from VBscript.

So.. essentially looking for two different SQL statements:

#1 - gets Distinct ModelID from tblSalesForecasts Where specific ForecastMonth and ForecastYear are passd in, and (is it joins?) with table tblModels to get BaseModel name, and Description of products.

#2 - would be called for each product 6 times in each record.
Input is the ModelID and Month and Year for a specific ForecastMonth and ForecastYear report.
Should get A total summed up Qty back.

This I guess is the most complicated for me, as it is pulling data from two tables, having separate filters on both.
tblSalesForecasts - Specific ModelID and specific ForecastMonth and specific ForecastYear. (inner join?) with tbale tblSalesForecastsQtyData (using SalesForecastID) and filtering on MonthMM and YearYYYY filters, getting Quantity and summing that for the entire set.

I hope this sounds clearer...but I do realize it is still complex. If it wasn't so complex I would have been able to solve it probably.
-Peter

 

[POSAPAY]

Well, I've created a couple of loops in VBScript.
First I get a recordset of just the Distinct BaseModels ordered by BaseModels Asc.

As I go down the list of these and displaying them, one at a time I lookup their ModelID, and using the ModelID I do another SQL SELECT to get the descriptions.

Then a couple of columns over, I get the previous month's records that are reported in this months report, and filtered by the ModelID. I loop through all these records adding up what ever number it finds in the Qty field. and displays it.

Then I repeat this 5 more times for the other 5 months.

So I'm really just issueing simple SELECT statements, and handling all the data processing in VBScript in my classic ASP.

It works.. but it does take a while to process.

 

[markros]

Post what you have now and we'll try to change it. My original idea was to use select statement with 12 parameters (for days and months) and use calculations with CASE statements.

 

[POSAPAY]

Hi,
Sorry for not responding sooner, I still don't have it resolved, but I had to finish another thread to close off this quarter.

So I guess I should break this down to simplify it.
Currently I got a long-way around it to get it to do what I needed.
Basically taking two tables for Forecast Data, and taking one table to reference product description.

Forecast data is by customers, and products.
The second table for forecasts has for each forecast customer and product 6 different records for 6 months out quantity forecasts and a probability in a second field of that record.

My goal is to have a table that has one of each product listed, and in separate columns next to them for each of the 6 months the total qty of forecasted sales data, where before summing it up, each is multiplied with the probability value/100.

I'll post the code later today.

 

[markros]

Ok, thanks.

 

[POSAPAY]

Currently I make 8 database calls and some of them are called over and over in a loop to get the job done.

 <%  set GetForecastModels = Server.CreateObject("ADODB.Recordset")
             strSQL_GetForecastModels = "SELECT Distinct ModelID, BaseModel FROM tblSalesForecasts WHERE Active = 'T' AND Deleted = 'F' AND" & _
                                        " TeamID like '" & Session("TeamID") & "' AND" & _
                                        " ForecastMonth Like '" & ForecastMonth & "' AND ForecastYear Like '" & ForecastYear & "'" & _
                                        " ORDER BY BaseModel"
             GetForecastModels.Open strSQL_GetForecastModels ,ConnObj',adOpenKeyset',adCmdTable
             If NOT GetForecastModels.EOF then
                 GetForecastModels.MoveFirst %>
                 
                 <%do while not GetForecastModels.EOF %>
                          <%' ------ LOOK UP Number of records for each model for this month ---- %>
                          <%  set GetForecastModelCount = Server.CreateObject("ADODB.Recordset")
                              strSQL_GetForecastModelCount = "SELECT SalesForecastID FROM tblSalesForecasts WHERE Active = 'T' AND Deleted = 'F' AND" & _
                                                             " TeamID like '" & Session("TeamID") & "' AND" & _
                                                             " ForecastMonth Like '" & ForecastMonth & "' AND ForecastYear Like '" & ForecastYear & "'" & _
                                                             " AND ModelID LIKE '" & GetForecastModels.Fields("ModelID") & "'"
                              GetForecastModelCount.Open strSQL_GetForecastModelCount ,ConnObj,adOpenKeyset',adCmdTable
                              BaseModelRecordCount = 0
                              If not GetForecastModelCount.EOF then
                                 GetForecastModelCount.MoveFirst
                                 BaseModelRecordCount = GetForecastModelCount.RecordCount
                              Else
                                 BaseModelRecordCount = 0
                              end if
                              GetForecastModelCount.close
                              set GetForecastModelCount = nothing
                          %>
                          
                     <tr><td align="left" bgcolor="#F0F0F0" nowrap><%=trim(GetForecastModels.Fields("BaseModel")) %>&nbsp;(<%=BaseModelRecordCount %>)</td>
                          <%' ------ LOOK UP Model Descriptions ---- %>
                          <%  set GetModelDescript = Server.CreateObject("ADODB.Recordset")
                              strSQL_GetModelDescript = "SELECT ModelID, ModelEncID, BaseModel, Description FROM tblModels WHERE " & _
                                                             " TeamID like '" & Session("TeamID") & "'" & _
                                                             " AND ModelID LIKE '" & GetForecastModels.Fields("ModelID") & "'"
                              GetModelDescript.Open strSQL_GetModelDescript ,ConnObj,adOpenKeyset',adCmdTable
                              BaseModelDescription = ""
                              BaseModelDescriptionOriginal = ""
                              ModelEncID = ""
                              BaseModel = ""
                              ModelID = ""
                              If not GetModelDescript.EOF then
                                 GetModelDescript.MoveFirst
                                   ModelID = trim(GetModelDescript.Fields("ModelID"))
                                   ModelEncID = trim(GetModelDescript.Fields("ModelEncID"))
                                   BaseModel = trim(GetModelDescript.Fields("BaseModel"))
                                   BaseModelDescription = trim(GetModelDescript.Fields("Description"))
                                   BaseModelDescriptionOriginal = BaseModelDescription
                              Else
                                 BaseModelDescription = ""
                              end if
                              GetModelDescript.close
                              set GetModelDescript = nothing
                              If len(BaseModelDescription) > 15 then
                                dotdotdot = "..."
                              Else
                                dotdotdot = ""
                              End if
                              
                          %>
                          
                         <td align="left" bgcolor="#F0F0F0" nowrap><%=left(BaseModelDescription,15)%><%=dotdotdot %></td>
                         <td width="2" bgcolor="#C0C0C0"></td>
                          <%' ------ LOOK UP Model Forecast for this month ---- %>
                          <% Next1MonthQty = 0 
                             Next2MonthQty = 0 
                             Next3MonthQty = 0 
                             Next4MonthQty = 0 
                             Next1MonthQtyAlternate = 0%>
                          <%  set GetForecastsForModel = Server.CreateObject("ADODB.Recordset")
                              strSQL_GetForecastsForModel = "SELECT SalesForecastID FROM tblSalesForecasts WHERE Active = 'T' AND Deleted = 'F' AND" & _
                                                             " TeamID like '" & Session("TeamID") & "'" & _
                                                             " AND ForecastMonth Like '" & ForecastMonth & "' AND ForecastYear Like '" & ForecastYear & "'" & _
                                                             " AND BaseModel LIKE '" & GetForecastModels.Fields("BaseModel") & "'"
                              GetForecastsForModel.Open strSQL_GetForecastsForModel ,ConnObj,adOpenKeyset',adCmdTable
                              If not GetForecastsForModel.EOF then
                                 GetForecastsForModel.MoveFirst
                                 do while not GetForecastsForModel.EOF
                                      '=-=-=-=-=- beginning of loop -=-=-=-=-=-=-
                                      'Next1Month---GetForecasts for 
                                      Next1Month = ForecastMonth + 1
                                      Next1MonthYear = ForecastYear
                                      If Next1Month > 12 then
                                        Next1Month = 1
                                        Next1MonthYear = Next1MonthYear + 1
                                      End if
                                      set GetNext1MonthQtyForModel = Server.CreateObject("ADODB.Recordset")
                                      strSQL_GetNext1MonthQtyForModel = "SELECT * FROM tblSalesForecastsQtyData WHERE Active = 'T' AND Deleted = 'F' AND" & _
                                                             " SalesForecastID like '" & GetForecastsForModel.Fields("SalesForecastID") & "'" & _
                                                             " AND MonthMM LIKE '" & Next1Month & "'" & _
                                                             " AND YearYYYY LIKE '" & Next1MonthYear & "'"
                                      GetNext1MonthQtyForModel.Open strSQL_GetNext1MonthQtyForModel ,ConnObj,adOpenKeyset',adCmdTable
                                      If not GetNext1MonthQtyForModel.EOF then
                                         GetNext1MonthQtyForModel.MoveFirst
                                            Next1MonthQty = Next1MonthQty + (GetNext1MonthQtyForModel.Fields("ForecastQty") * (GetNext1MonthQtyForModel.Fields("Probability")/100))
                                            ''--- alternate calcuation method
                                            ' If GetNext1MonthQtyForModel.Fields("Probability") > 80 then
                                            '  Next1MonthQtyAlternate = Next1MonthQtyAlternate + (GetNext1MonthQtyForModel.Fields("ForecastQty"))
                                            ' End if
                                      End if
                                      GetNext1MonthQtyForModel.close
                                      set GetNext1MonthQtyForModel = nothing
                                      
                                     'Next2Month---GetForecasts for 
                                      Next2Month = ForecastMonth + 2
                                      Next2MonthYear = ForecastYear
                                      Select case Next2Month
                                      Case 13
                                        Next2Month = 1
                                        Next2MonthYear = Next2MonthYear + 1
                                      Case 14
                                        Next2Month = 2
                                        Next2MonthYear = Next2MonthYear + 1
                                      End Select
                                      set GetNext2MonthQtyForModel = Server.CreateObject("ADODB.Recordset")
                                      strSQL_GetNext2MonthQtyForModel = "SELECT * FROM tblSalesForecastsQtyData WHERE Active = 'T' AND Deleted = 'F' AND" & _
                                                             " SalesForecastID like '" & GetForecastsForModel.Fields("SalesForecastID") & "'" & _
                                                             " AND MonthMM LIKE '" & Next2Month & "'" & _
                                                             " AND YearYYYY LIKE '" & Next2MonthYear & "'"
                                      GetNext2MonthQtyForModel.Open strSQL_GetNext2MonthQtyForModel ,ConnObj,adOpenKeyset',adCmdTable
                                      If not GetNext2MonthQtyForModel.EOF then
                                         GetNext2MonthQtyForModel.MoveFirst
                                            Next2MonthQty = Next2MonthQty + (GetNext2MonthQtyForModel.Fields("ForecastQty") * (GetNext2MonthQtyForModel.Fields("Probability")/100))
                                      End if
                                      GetNext2MonthQtyForModel.close
                                      set GetNext2MonthQtyForModel = nothing
                                      
                                      
                                     'Next3Month---GetForecasts for 
                                      Next3Month = ForecastMonth + 3
                                      Next3MonthYear = ForecastYear
                                      Select case Next3Month
                                      Case 13
                                        Next3Month = 1
                                        Next3MonthYear = Next3MonthYear + 1
                                      Case 14
                                        Next3Month = 2
                                        Next3MonthYear = Next3MonthYear + 1
                                      Case 15
                                        Next3Month = 3
                                        Next3MonthYear = Next3MonthYear + 1
                                      End Select
                                      set GetNext3MonthQtyForModel = Server.CreateObject("ADODB.Recordset")
                                      strSQL_GetNext3MonthQtyForModel = "SELECT * FROM tblSalesForecastsQtyData WHERE Active = 'T' AND Deleted = 'F' AND" & _
                                                             " SalesForecastID like '" & GetForecastsForModel.Fields("SalesForecastID") & "'" & _
                                                             " AND MonthMM LIKE '" & Next3Month & "'" & _
                                                             " AND YearYYYY LIKE '" & Next3MonthYear & "'"
                                      GetNext3MonthQtyForModel.Open strSQL_GetNext3MonthQtyForModel ,ConnObj,adOpenKeyset',adCmdTable
                                      If not GetNext3MonthQtyForModel.EOF then
                                         GetNext3MonthQtyForModel.MoveFirst
                                            Next3MonthQty = Next3MonthQty + (GetNext3MonthQtyForModel.Fields("ForecastQty") * (GetNext3MonthQtyForModel.Fields("Probability")/100))
                                      End if
                                      GetNext3MonthQtyForModel.close
                                      set GetNext3MonthQtyForModel = nothing
                                      
                                      
                                     'Next4Month---GetForecasts for 
                                      Next4Month = ForecastMonth + 4
                                      Next4MonthYear = ForecastYear
                                      Select case Next4Month
                                      Case 13
                                        Next4Month = 1
                                        Next4MonthYear = Next4MonthYear + 1
                                      Case 14
                                        Next4Month = 2
                                        Next4MonthYear = Next4MonthYear + 1
                                      Case 15
                                        Next4Month = 3
                                        Next4MonthYear = Next4MonthYear + 1
                                      Case 16
                                        Next4Month = 4
                                        Next4MonthYear = Next4MonthYear + 1
                                      End Select
                                      set GetNext4MonthQtyForModel = Server.CreateObject("ADODB.Recordset")
                                      strSQL_GetNext4MonthQtyForModel = "SELECT * FROM tblSalesForecastsQtyData WHERE Active = 'T' AND Deleted = 'F' AND" & _
                                                             " SalesForecastID like '" & GetForecastsForModel.Fields("SalesForecastID") & "'" & _
                                                             " AND MonthMM LIKE '" & Next4Month & "'" & _
                                                             " AND YearYYYY LIKE '" & Next4MonthYear & "'"
                                      GetNext4MonthQtyForModel.Open strSQL_GetNext4MonthQtyForModel ,ConnObj,adOpenKeyset',adCmdTable
                                      If not GetNext4MonthQtyForModel.EOF then
                                         GetNext4MonthQtyForModel.MoveFirst
                                            Next4MonthQty = Next4MonthQty + (GetNext4MonthQtyForModel.Fields("ForecastQty") * (GetNext4MonthQtyForModel.Fields("Probability")/100))
                                      End if
                                      GetNext4MonthQtyForModel.close
                                      set GetNext4MonthQtyForModel = nothing
                                   '=-=-=-=-= end of loop =-=-=-=-=-=-=-   
                                   GetForecastsForModel.MoveNext
                                 loop
                              end if
                              GetForecastsForModel.close
                              set GetForecastsForModel = nothing
                          %>
                         <td align="Right" bgcolor="#F0F0F0"><%=FormatNumber(Next1MonthQty,0) %>&nbsp;</td>
                         <td align="Right" bgcolor="#F0F0F0"><%=FormatNumber(Next2MonthQty,0) %>&nbsp;</td>
                         <td align="Right" bgcolor="#F0F0F0"><%=FormatNumber(Next3MonthQty,0) %>&nbsp;</td>
                         <td align="Right" bgcolor="#F0F0F0"><%=FormatNumber(Next4MonthQty,0) %>&nbsp;</td>
                      </tr>

                   <% GetForecastModels.MoveNext
                   loop %>
              <%
              End if
              GetForecastModels.close
              set GetForecastModels = nothing%>

 

[JeffModen]

Holy Moly... that's a lot of code. Take a look at the following URL to see how to do dynamic cross-tabs...

http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[POSAPAY]

Dynamic cross-tabs sounds great, but it got me lost on the first couple of paragraphs. All in all that seemed to have a longer code, but maybe just because I got lost in it quickly.

But perhaps there are no easier options, in which case sooner or later I'll just have to get down to actually understanding every nut and bolt.

 

[JeffModen]

I believe that once you get the hang of it, you'll find the dynamic cross-tab code is much shorter. And, for batch jobs such as this, I believe you also find that dynamic SQL taking longer is pretty much a myth.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[POSAPAY]

Sounds good, I guess I'll need to set some time aside and learn it, try it, test it, play with it to become friends with it.