Simple Table update
-
Can anyone help with this?????? Two tables and their fields......
Table 1 -
CustNumber
Shop
Branch
Week
Table 2 - Shop
Branch
Week
Table1 contains a list of customers and the shops and branches which they belong to aswell as week numbers.
Table2 contains all a list of all different branches in ONE shop only and Week numbers.
One shop consists of many branches, e.g. Shop1 can include branch1, branch2, branch3 etc......
There may be many customers in any one shop/branch combination, e.g. Shop1,Branch1 may contain many customers
Need to populate a 'TotalCustomers' field in Table2 which gives the total number of customers in each shop/branch combination at any given week.
The code below only returns the total number of customers in Table1 for the shop in table2 (remember table two only consists of ONE shop containing many branches, whereas
Table1 contains details of all customers for all shops/branches)
update Table2
set TotalCustomers= (select count(T1.CustNumber)
from Table1.T1,
Table2.T2
where T1.Shop = T2.Shop
and T1.Branch = T2.Branch
and T1.Week = T2.Week
Any ideas how to change this to get the total number of customers in each shop/branch combination at any given week?
Thanks for your help guys.
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive! -
This was removed by the editor as SPAM
-
- Update t2
- set TotalCustomers = custCount
- from table2 t2 join (select count(CustNumber) custCount,Shop,Branch,week
- from table1
- group by shop,branch,week) t1
- on t2.Shop = t1.shop
- where t1.week = t2.week and t1.branch = t2.branch
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply