Need Urgent help in a SQL Query

[Nikita11011]

I have a table with column id, segment, external_id ,import_date and sku. so based on a week of import date i have to calculate the no of unique external ids and as per sku and segment.
ther is another table with columns segment ExternalID import_date ID and code. here i have to calculate the count of code based on week of a import date and as per segment and code itself. the complex part is i want the two metric no of unique external id and no of codes in one consolidated table based on join on externalIds.

here one code in the table 2 can have multiple external ids.
Attached below an example(i want the consolidated final table)

[harishgg1]

hi

i tried to do this
hope this helps !!!

please click arrow to the left for "drop create data" script

drop table dbo.table1
go 

create table dbo.table1
(
id int ,
segment varchar(1),
external_id varchar(10),
repair_date date ,
sku varchar(5)
)
go 

set dateformat dmy 
go 

insert into dbo.table1 select 1,'A','BCFG','1/07/2019','GHIJJ'
insert into dbo.table1 select 2,'A','DEFG','2/07/2019','GHIJJ'
go 

select * from dbo.table1
go 

drop table dbo.table2
go 


create table dbo.table2
(
segment varchar(1),
external_id varchar(10),
repair_date date ,
id int,
code varchar(5)
)
go 

insert into dbo.table2 select 'A','BCFG','1/07/2019',1,'TRIT'
insert into dbo.table2 select 'A','BCFG','2/07/2019',2,'TRIT'
insert into dbo.table2 select 'A','BCFG','3/07/2019',3,'TRIT'
insert into dbo.table2 select 'A','DEFG','1/07/2019',2,'TRIT'
go 

select * from dbo.table2
go

please click arrow to the left for "SQL" script

; WITH cte 
     AS (SELECT Dateadd(dd, -( Datepart(dw, repair_date) - 1 ), repair_date) AS 
                [WeekStart], 
                Count(code)                                                  AS 
                   no_of_code 
         FROM   dbo.table2 
         GROUP  BY Dateadd(dd, -( Datepart(dw, repair_date) - 1 ), repair_date)) 
, 
     cte1 
     AS (SELECT Dateadd(dd, -( Datepart(dw, a.repair_date) - 1 ), a.repair_date) 
                [WeekStart], 
                a.segment, 
                a.sku, 
                b.code, 
                Count(DISTINCT a.external_id) 
                AS 
                   no_of_externalid 
         FROM   dbo.table1 a 
                JOIN dbo.table2 b 
                  ON Datepart(week, a.repair_date) = 
                     Datepart(week, b.repair_date) 
                     AND a.id = b.id 
         GROUP  BY Dateadd(dd, -( Datepart(dw, a.repair_date) - 1 ), 
                   a.repair_date), 
                   a.segment, 
                   a.sku, 
                   b.code) 
SELECT a.weekstart AS repair_week, 
       a.segment, 
       a.sku, 
       a.code, 
       a.no_of_externalid, 
       b.no_of_code 
FROM   cte1 a 
       JOIN cte b 
         ON a.weekstart = b.weekstart 

go

[Nikita11011]

Thanks harish but its resulting in lot of duplicates being added.