Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dosteroid
Starting Member
29 Posts |
 Posted - 2012-01-12 : 05:34:01
|
| Hi AllI have two tables, one is Sales which contains Sales Amount for our Companies/Sites likeInvoiceDate | Company | SalesManager | ShipTo | Sales USD | MnemonicKey | PeriodCode for this (simplified) isselect InoviceDate, Company,SalesManager,ShipTo,SalesUSD,MnemonicKey,Period from SalesMnemonicKey is a Concatenation of several columns, however it is not entirely unique as there can beSales on the same MnemonicKey more than once in a given periodPeriod is 1-12 (january, february...December)So an output from Sales could look like this:InvoiceDate|Company|SalesManager|ShipTo|Sales USD|MnemonicKey...........|Period----------------------------------------------------------------------------11-01-2011 |XXX..... |Dr.Phil..........|Latex..|1.000.000|Xenious_DE10435004 |123-01-2011 |XXX..... |Dr.Phil..........|Latex..|1.250.000|Xenious_DE10435004 |1The other table is called Forecast (our estimated Sales for the future 12 months ahead)which look like this:MnemonicKey | Forecast | PeriodCode for this is just:select MnemonicKey,Forecast,Period from ForecastThis table has a Forecast on one MnemonicKey for a period like this:(so MnemonicKey is Unique in this table)Xenious_DE10435004 | 3.500.000 | 1How can I join or Union or... do in order to get an output likeInvoiceDate|Company|SalesManager|ShipTo|SalesUSD |MnemonicKey.........|Period|Forecast----------------------------------------------------------------------------xxxxxxxxxxx |XXX......|Dr.Phil..........|Latex.|0.000.000|Xenious_DE10435004|1......|3.500.00011-01-2011 |XXX..... |Dr.Phil..........|Latex.|1.000.000|Xenious_DE10435004|1......|23-01-2011 |XXX..... |Dr.Phil..........|Latex.|1.250.000|Xenious_DE10435004|1......|The xxxxxxxxxx in InvoiceDate is because it's a forecast in the future, actually it would be for January 2012, butIm not sure how I can sort that either...Any Help appreciated [list] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-12 : 06:27:15
|
| how do you associate the values of SalesManager|ShipTo|SalesUSD etc for forecast data? you dont have any related in Forecast table. or is it like you forecast it for each unique set of values which exists in Sales table for previous year?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
dosteroid
Starting Member
29 Posts |
Posted - 2012-01-12 : 09:17:40
|
| The only relation I have between the Sales table and the Forecast table is the MnemonicKey, however it is not unique in the Sales table, as there can be several sales in the same period... in the forecast it's unique if thats what you mean? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-12 : 12:54:16
|
quote:
Originally posted by dosteroid
The only relation I have between the Sales table and the Forecast table is the MnemonicKey, however it is not unique in the Sales table, as there can be several sales in the same period... in the forecast it's unique if thats what you mean?
nope...i was asking on output. How do you determine the values that you're currently displaying for SalesManager|ShipTo|SalesUSD fields in forecast row?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Join/union of Sales and Forecast Tables