Discuss "Create History Table / Outcome results" in the Database Design Help forum.

Rebelle · #1 (**permalink**) March 25th, 2008, 10:21 AM

All,

Ok, I have 3 tables right now that I have created a SQL view to display. Only thing is....I update one of the tables called rollout once a month. I take a backup copy of the table and then delete the data and put in new month data. Well, I would like to take all the backup copies and put into a new history table but would like to know if this is ok to have setup like below. The MoID is the month ID and is the only way to tell the month of the records. Should I be using a datefield...MoID is like 1 for Jan., etc.

History Table:
ToolID
RegionID
DistrictID
Requested
Committed
Shipped
MoID

I want to make sure the above is ok because I will need the following outcome. After I have several months in the table, I would like to display monthly and sum qtrly eventually.

Example:
Tool Name --- Jan Shipped---Feb Shipped --- Mar Shipped --- QtrTot
Hammer...............2.................1.......... ..........0....................3

Thanks in advance.

jmurrayhead · #2 (**permalink**) March 25th, 2008, 10:29 AM

I think using a datetime datatype would be beneficial as far as searching is concerned. If you are using SQL Server, for example, and you want to get everything for January 2008, you could use the DatePart function.

Hope this helps.

Wolffy · #3 (**permalink**) March 25th, 2008, 10:33 AM

Couple of thoughts here. I'm curious as to why you delete records from the table (let's call it the Shipped table). Why not just leave the data in this table and modify the view to return only the current (or perhaps the previous month) data?

Since you have only months in the table, what happens when you start a new year? Have you no need for historical data from previous years?

Finally, I don't see any real problem with doing what you intend. By year's end, you will have 12 records for each ToolID. I don't think you will be able to retrieve the data in the format you want using a simple query (actually, you can and I have written a 'pivot' query like this before, but it really ugly), so some kind of formatting logic will be required -- just ensure that you can handle months that may not have records, unless you include a 0 shipped record in your history.

One other idea to chew on would be to create a "Summary History" table that you update each month that has a column for each month. Just update this table with the SUM of the shipped values for the month.

Rebelle · #4 (**permalink**) March 25th, 2008, 10:36 AM

Thanks JMurrayhead,

I have been playing/testing around a bit....but is the below equal to or similar to the datepart function you mentioned? I am using SQL2000 and the below I was testing in SQL analyzer.

Code:

select tools,onorder1,
  JAN08= IsNull((Select Sum(IsNull([RolloutShipped],0)) from vwRollout where MoID = 2 and tools = Q.tools and onorder1 = Q.onorder1),0),
  FEB08= IsNull((Select Sum(IsNull([RolloutShipped],0)) from vwRollout where MoID = 3 and tools = Q.tools and onorder1 = Q.onorder1),0),
  MAR08= IsNull((Select Sum(IsNull([RolloutShipped],0)) from vwRollout where MoID = 4 and tools = Q.tools and onorder1 = Q.onorder1),0)
from vwRollout Q
group by tools,onorder1
order by tools

jmurrayhead · #5 (**permalink**) March 25th, 2008, 11:31 AM

The DatePart function returns an Integere value from the specified datepart of the specified Date.

For example:

Code:

SELECT DATEPART(month, '3/25/2008') AS 'Month Number'

The above would return 3. Make sense? You could tell it to return the year only, for example, and the above would return 2008.

Rebelle · #6 (**permalink**) March 26th, 2008, 08:22 AM

Hi Wolffy and JMurrayhead,

Thanks for the info and suggestions. Yeah, this one is giving me a big headache. The one table I delete from is because I get a fresh snapshot of data from another system once a month. The table that holds the shipped quantities stays put but once a month I update the MoID after I've taken a copy of the table.

Wolffy since I only have MoID right now, I will eventually need something to distinguish for the year.

I will see what I can do with your advice to get the quarterly sum implemented but eventually I think this process / tables will need to be redone.

I do populate a record for every tool/district, so in the shipped data table and the snapshot table it has one record for each unique tool/district.

This will drive me to.....

Rebelle · #7 (**permalink**) May 15th, 2008, 10:47 AM

I created a new table called "tblRolloutAddHistory" where I have a datetime
field (CurrentMoYr), but wondering how I can incorporate your suggestions
into my mess below. Do I use the datepart function in my SQL view or in my asp?
After I take a snapshot of the month, I am placing the data in tblRolloutAddHistory.
Right now it works only because I have the first 3 months in the table....need help as I will need to add future months and put those numbers in the appropriate SUM/Shipped QTR Column (Q2 Shipped-Q3 Shipped-Q4 Shipped- haven't yet added them to the code below).

This is the SQLview I'm pulling from:

Code:

SELECT     dbo.tblTools.Tools, dbo.tblTools.OnOrder1, dbo.tblTools.OnOrder2, dbo.tblTools.OnOrder3, dbo.tblTools.OnOrder4, 
                      dbo.tblRolloutAddHistory.CurrentMoYr, SUM(dbo.tblRolloutAddHistory.RolloutShipped) AS SumShipped, dbo.tblTools.ToolID, 
                      dbo.tblRolloutAddHistory.RolloutShipped, dbo.tblRolloutAddHistory.DistrictID, dbo.tblRolloutAddHistory.RegionID, dbo.tblTools.MeetingID, 
                      dbo.tblRegion.RegionName, dbo.tblDistricts.District_Location
FROM         dbo.tblRolloutAddHistory INNER JOIN
                      dbo.tblRegion ON dbo.tblRolloutAddHistory.RegionID = dbo.tblRegion.RegionID INNER JOIN
                      dbo.tblDistricts ON dbo.tblRolloutAddHistory.DistrictID = dbo.tblDistricts.DistrictID FULL OUTER JOIN
                      dbo.tblTools ON dbo.tblRolloutAddHistory.ToolID = dbo.tblTools.ToolID
GROUP BY dbo.tblTools.Tools, dbo.tblTools.OnOrder1, dbo.tblTools.OnOrder2, dbo.tblTools.OnOrder3, dbo.tblTools.OnOrder4, 
                      dbo.tblRolloutAddHistory.CurrentMoYr, dbo.tblTools.ToolID, dbo.tblRolloutAddHistory.RolloutShipped, dbo.tblRolloutAddHistory.DistrictID, 
                      dbo.tblRolloutAddHistory.RegionID, dbo.tblTools.MeetingID, dbo.tblRegion.RegionName, dbo.tblDistricts.District_Location
HAVING      (dbo.tblTools.MeetingID = 1) OR
                      (dbo.tblTools.MeetingID = 2)

On my asp page I have this:
SQL statement

Code:

sSQL = "SELECT ToolID,Tools, onOrder1, OnOrder2, OnOrder3, OnOrder4, Sum(IsNull([RolloutShipped],0)) As SumShipped FROM vwShippedHistory WHERE 1=1 GROUP BY ToolID,Tools,OnOrder1,OnOrder2,OnOrder3,OnOrder4 ORDER BY " & sOrderBy & " " & sOrderByDir

then

Code:

<TABLE border="3" width=800>
      <TR bgcolor="gray">
<TD>Tools</TD>
<td><b>Q1-08</b></td>
<td><b>Q1 Shipped</b></td>
<td><b>Q2-08</b></td>
<td><b>Q3-08</b></td>
<td><b>Q4-08</b></td>
</TR>

</CENTER>


<%
	do while not rs.EOF
		if i > iStopRec then
			exit do
		end if
	strLastTools = rs("ToolID")
	strTools = rs("Tools")
	strQ1 = rs("OnOrder1")
	strQ1Shipped = strQ1Shipped + rs("SumShipped")
	strQ2 = rs("OnOrder2")
	strQ3 = rs("OnOrder3")
	strQ4 = rs("OnOrder4")

	strCurrentTools = rs("Tools")


		Response.Write("<tr><td colwidth=""150""><b>" & strTools & "</b></a></td>")
		Response.Write("<td align=center colwidth=""50""><b>" & strQ1 & "</b></td>")
		strQ1 = 0 
			Response.Write("<td align=center colwidth=""50""><b>" & strQ1Shipped & "</b></td>")
		strQ1Shipped = 0
			Response.Write("<td align=center colwidth=""50""><b>" & strQ2 & "</b></td>")
		strQ2 = 0
			Response.Write("<td align=center colwidth=""50""><b>" & strQ3 & "</b></td>")
		strQ3 = 0
			Response.Write("<td align=center colwidth=""50""><b>" & strQ4 & "</b></td></tr>")
		strQ4 = 0
	

%>


<%
	i = i + 1
  rs.MoveNext
loop
%>
<%
rs.Close
end if
end if
%>

    </TABLE>

Rebelle · #8 (**permalink**) June 24th, 2008, 01:45 PM

Quote from Wolffy ---One other idea to chew on would be to create a "Summary History" table that you update each month that has a column for each month. Just update this table with the SUM of the shipped values for the month.

Trying this suggestion, so I have a summary history tables that i want to sum, but need help/direction please.

Sorry, I'm still trying to sort this out....what I have so far is something that looks like below on my webpage:

Code:

ToolName----Q1Build----Q1Shipped---- Q2Build---- Q3Build---- Q4Build
Tool1----------2----------4-------------0-----------3----------2
Tool2----------1----------2-------------2-----------3----------1
Tool3----------3----------2-------------3-----------3----------2
Tool4----------3----------0-------------2-----------1----------1

from the above I have Q1Shipped is rsfield "SumShipped" (summing field ShippedQty) from my sqlview which is currently summing correct only because the table has the first 3 months of data in place.....

....I want to still add to my table for other months but how would I tell it to only sum for months 1-3 for Q1Shipped, then 4-6 for Q2Shipped, 7-9 for Q3Shipped, 10-12 for Q4Shipped, if they are all rsfield("SumShipped")?

Code:

ex:
History info (haven't added data for > 4/1/2008 because then my numbers on webpage will be wrong, would need to put those in another column for Q2Shipped, and so on).

ToolID      RegionID        DistrictID      ShippedQty       CurrentMoYr     MoID
1------------1-------------1-------------1--------------2/1/2008-----2
1------------1-------------1-------------0--------------3/1/2008-----3
1------------1-------------1-------------0--------------4/1/2008-----4
1------------1-------------1-------------0--------------5/1/2008-----5
1------------1-------------1-------------3--------------6/1/2008-----6
1------------1-------------1-------------3--------------7/1/2008-----7
1------------1-------------2-------------1--------------2/1/2008-----2
1------------1-------------2-------------2--------------3/1/2008-----3
1------------1-------------2-------------0--------------4/1/2008-----4
1------------1-------------2-------------3--------------5/1/2008-----5
1------------1-------------2-------------3--------------6/1/2008-----6
1------------1-------------2-------------3--------------7/1/2008-----7
2------------1-------------1-------------1--------------2/1/2008-----2
2------------1-------------1-------------0--------------3/1/2008-----3
2------------1-------------1-------------2--------------4/1/2008-----4
2------------1-------------1-------------3--------------5/1/2008-----5
2------------1-------------1-------------3--------------6/1/2008-----6
2------------1-------------1-------------3--------------7/1/2008-----7
2------------1-------------2-------------1--------------2/1/2008-----2
2------------1-------------2-------------0--------------3/1/2008-----3
2------------1-------------2-------------2--------------4/1/2008-----4
2------------1-------------2-------------3--------------5/1/2008-----5
2------------1-------------2-------------4--------------6/1/2008-----6
2------------1-------------2-------------3--------------7/1/2008-----7

Wolffy · #9 (**permalink**) June 25th, 2008, 04:32 PM

I thought this could be done with CASE statements, and I was right. Try the following query (that 'quarterizes') QTY Shipped by ToolID

Code:

select toolid,
  sum(case 
		when CurrentMonth between 1 and 3 then ShippedQty
	  end) as ShippedQTR1,
  sum(case
		when CurrentMonth between 4 and 6 then ShippedQty
	  end) as ShippedQTR2,
  sum(case
		when CurrentMonth between 7 and 9 then ShippedQty
	  end) as ShippedQTR3,
  sum(case
		when CurrentMonth between 10 and 12 then ShippedQty
      end) as ShippedQTR4
from Tools
where CurrentYear = 2008
group by toolid

Rebelle · #10 (**permalink**) July 1st, 2008, 08:33 AM

Thanks Wolffy.....I have my months screwed up so when I put the data into the history table, I'll have to change them....because the way I have it right now, Id have to do between moID 2 and 4, 5 and 7, 8 and 10, then will have a problem for 11-12 and 1.

Thanks again for this info, this looks like it's going to work out well.....I had some issues with the asp part but mehere helped me sort that all out. I put the sum(case) in the sql statement on my asp page.

*howls*

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

The Following User Says Thank You to Wolffy For This Useful Post:
Rebelle (July 1st, 2008)

Discuss "Create History Table / Outcome results" in the Database Design Help forum.

Database Design Help - Database design is important to build fast and efficient applications. Discuss the best practices such as naming conventions and relational database schemes here.