Srijon

select distinct jobTypeID='1',j.InvoiceDate as IssueDate,j.InvoiceDate as EntryDateTime, 'New Installation Request' as Subject,
j.CustomerID,bp.Name CustomerName,bp.OwnerMobile CustomerPhone,bp.DateOfBirth CustomerDOB,
bp.NationalID CustomerNID,bp.Gender,j.ShowroomID DealerID,j.LandMark,j.RoadNo,j.HouseNo,j.CPE,j.PackageId servicePackageID,
j.SubscriberId,sss.SerialNo STBnumber,j.InvoiceID ReferenceID,1 as ReferenceType,j.CreatedBy,
j.InvoiceDate CreatedDate,T.DistributorID
 from SalesInvoice j
  INNER JOIN
     (Select ThanaID,DistributorID=MAX(DistributorID) from BusinessPartnerLocation bpl Group by ThanaID) T ON j.ThanaId=T.ThanaID
  INNER  JOIN InvoiceLineItem AS ili ON j.InvoiceID= ili.InvoiceID
  INNER JOIN InvoiceLineItemSerial AS ilis ON j.InvoiceID=ilis.InvoiceID  AND ili.InvoiceLineItemID=ilis.InvoiceLineItemID
  INNER JOIN skubatchstockserial AS sss ON sss.SKUSerialID=ilis.SerialID AND sss.SKUBatchID=ilis.SKUBatchID
  LEFT Join  BusinessPartner bp on j.CustomerID=bp.BPID and bp.Category=11
where cast(j.CreatedDate as Date)>=cast('19 Jan 2020' as Date)
and j.SubscriberId not in (
select distinct j.SubscriberId from job  j where  cast(j.CreatedDate as Date)>=cast('19 Jan 2020' as Date)
and j.SubscriberId is not null)
and T.DistributorID>0
and sss.SerialStatus=1