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 |
|
Gavr0s
Starting Member
2 Posts |
 Posted - 2011-01-27 : 07:01:46
|
| I need help in applying some database logic. I have 2 tables(see below), 1 is a lookup and the other contains imported data. The imported data table needs to be assigned a Car based on the site and code matching and then depends on the % of allocation in the allocation table.Allocation Table:Site - Code - Car - AllocationLondon - 123 - Fiesta - 40%London - 123 - Astra - 30%London - 123 - Mondeo - 20%London - 123 - Alpha - 10%Imported data table:Site - Code - CarLondon - 123 - ??London - 123 - ??London - 123 - ??London - 123 - ??I need to write a query which will output the data like this:Imported data table:Site - Code - CarLondon - 123 - FiestaLondon - 123 - FiestaLondon - 123 - AstraLondon - 123 - MondeoAny help would be greatfully received.Thanks |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-01-27 : 07:12:32
|
| So far so good and understandable the following: it seems that you want to update the tableB (imported Data table) having missing Car info from tableA(Allocation Table) using the Site and Code match. However you said and then depends on the % of allocation in the allocation table.1) what does this mean as the required output is showing fiesta twice However it is once in the Allocation Table. 2) Is there any column in the imported Data table representing a unique row in the Allocation or Vice versa - which could be used as joining condition along with Site and Code (since these two have repetition) |
 |
|
|
Gavr0s
Starting Member
2 Posts |
Posted - 2011-01-27 : 07:21:23
|
| In the allocation table we are saying that 40% of all records in the imported data table that match on Site and Code should be allocated the ASTRA car.My results are based on 40% of 4 records is 1.6 but rounded up to 2.Thanks |
|
|
|
|
|
|
Query logic help