Make sure they are stores along the x (horizontal axis) and months along the y (vertical). If necessary use the Excel transpose function
Create a table with them all in
create table #temp(
mnth varchar(10), det_id smallint, branch_name varchar(30), site_identifer smallint, target dec(9,2)) insert into #temp (mnth, branch_name,target) values ('April','Osterley',35520.71)
Update the branch_no and det_id (from the calendar2 table where calendar_id = 3)
update t set det_id = case t.mnth when 'April' then 1713 when 'may' then 1714 when 'june' then 1715 when 'july' then 1716 when 'august' then 1717 when 'september' then 1718 when 'october' then 1719 when 'november' then 1720 when 'december' then 1721 when 'January' then 2886 when 'February' then 2887 when 'March' then 2888 end, site_identifer = b.branch_no from #temp t inner join branch b on b.branch_name collate Latin1_General_CI_AS = t.branch_name collate Latin1_General_CI_AS
Confirm that everything has been updated.
select * from #temp where site_identifer is null select * from #temp where det_id is null
Insert into the target table
begin transaction insert into target_det (hdr_id, det_id, site_identifier, target_grouping, target_value1, target_value2) select 1,det_id, site_identifer,null,target,null from #temp --commit transaction
Check costs (ex vat if necessary)
SELECt branch_name, SUM(sale_val-vat_val) FROM invoice_hdr H INNER JOIN vw_TerminalBranch T ON T.terminal_id = H.terminal_id WHERE date_time > '2017-05-01' and branch_no in (3,4,6,7,8,9,10,16,17,20,26,31,44,45,46,159,168) GROUP BY branch_name