Rx_
Nothing In Moderation
- Local time
- Yesterday, 17:48
- Joined
- Oct 22, 2009
- Messages
- 2,803
Any suggestions, comments, or improvements on this approach or examples would be appreciated (tables are under 100,000 records)
After importing thousands of Excel templates into a table, the emission data date types may need to be converted for efficient SQL.
Will write a formula to convert the strings Jan, Feb, Mar, ... into a new Mo column with 1, 2, 3, ...
The SQL based report is intended to create a Total(Value) for each Site for the previous 12 months from a Year + Mo Point-in-time. Sometimes there will not be 12 months for example if it is a new location with only 3 months of data. Values are entered once a month.
Thinking of sorting by Year, Mo then taking a Top 12 query (then Totals)
Would a Parameter for a Where Date is < [Year & Mo + 3 days] be the solution?
Year is currently of type Text, going back 0 to 5 years.
Site Value Year Month| MO
S007 1.222 2012 Sep | 9
S007 1.444 2012 Aug | 8
S007 1.449 2012 Jul | 7
After importing thousands of Excel templates into a table, the emission data date types may need to be converted for efficient SQL.
Will write a formula to convert the strings Jan, Feb, Mar, ... into a new Mo column with 1, 2, 3, ...
The SQL based report is intended to create a Total(Value) for each Site for the previous 12 months from a Year + Mo Point-in-time. Sometimes there will not be 12 months for example if it is a new location with only 3 months of data. Values are entered once a month.
Thinking of sorting by Year, Mo then taking a Top 12 query (then Totals)
Would a Parameter for a Where Date is < [Year & Mo + 3 days] be the solution?
Year is currently of type Text, going back 0 to 5 years.
Site Value Year Month| MO
S007 1.222 2012 Sep | 9
S007 1.444 2012 Aug | 8
S007 1.449 2012 Jul | 7
