con.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=report;PASSWORD=******;Auto translate=false;" + "Initial Catalog=" + XXXXX+ ";Data Source=" + SERVER
con.Open
Set rc = Nothing

sTmp = " SELECT DISTINCT HED.PLASIYER_KODU  + '    ' + HED.PLASIYER_ACIKLAMA [TEMSILCI],HED.SUPERVAYZER ,HED.STOK_GRUP ,ROUND(HED.HEDEF_QUTU,0) [HEDEF_QUTU],ROUND(SUM(CASE WHEN STH.STHAR_HTUR = 'J' THEN STH.STHAR_GCMIK /SB.PAYDA2 ELSE 0 END ),0)- ROUND(SUM(CASE WHEN STH.STHAR_HTUR = 'L' THEN STH.STHAR_GCMIK /SB.PAYDA2 ELSE 0 END ),0) [NET_SATIS_QUTU]," _
& " ROUND(SUM(CASE WHEN STH.STHAR_HTUR = 'J' AND STH.STHAR_TARIH = '" & TARIH2 & "' THEN STH.STHAR_GCMIK /SB.PAYDA2 ELSE 0 END ),0)-ROUND(SUM(CASE WHEN STH.STHAR_HTUR = 'L' AND STH.STHAR_TARIH = '" & TARIH2 & "' THEN STH.STHAR_GCMIK /SB.PAYDA2 ELSE 0 END ),0) [GUN_QUTU], " _
& " ROUND(HED.HEDEF_AZN,2) [HEDEF_AZN], ROUND(SUM(CASE WHEN STH.STHAR_HTUR = 'J' THEN STH.STHAR_GCMIK * STH.STHAR_NF  ELSE 0 END ),0)-ROUND(SUM(CASE WHEN STH.STHAR_HTUR = 'L' THEN STH.STHAR_GCMIK * STH.STHAR_NF ELSE 0 END ),0) [SATIS_AZN],ROUND(SUM(CASE WHEN STH.STHAR_HTUR = 'J' AND STH.STHAR_TARIH = '" & TARIH2 & "' THEN STH.STHAR_GCMIK * STH.STHAR_NF  ELSE 0 END ),0)-ROUND(SUM(CASE WHEN STH.STHAR_HTUR = 'L' AND STH.STHAR_TARIH = '" & TARIH2 & "' THEN STH.STHAR_GCMIK * STH.STHAR_NF ELSE 0 END ),0) [GUN_AZN], " _
& " (CASE WHEN CAPLA.GRUPKODU LIKE '90VIP%' THEN 'VIP' WHEN CAPLA.GRUPKODU LIKE '90ERZ%' THEN 'ERZAQ' WHEN CAPLA.GRUPKODU LIKE '90TOP%' THEN 'TOPDAN'  WHEN CAPLA.GRUPKODU LIKE '90HOR%' THEN 'TOPDAN' WHEN CAPLA.GRUPKODU LIKE '09ERZ%' THEN 'BERDE' WHEN CAPLA.GRUPKODU LIKE '18%' THEN 'SHIRVAN' WHEN CAPLA.GRUPKODU LIKE '20%' THEN 'GENCE' WHEN CAPLA.GRUPKODU LIKE '23%' THEN 'GOYCAY' WHEN CAPLA.GRUPKODU LIKE '40%' THEN 'QUBA' WHEN CAPLA.GRUPKODU LIKE '44%' THEN 'LENKARAN' WHEN CAPLA.GRUPKODU LIKE '62%' THEN 'ZAQATALA' ELSE '' END)  [KANAL] ,(CASE WHEN SB.GRUP_KODU IN ('01')THEN 'BISMAK' WHEN SB.GRUP_KODU IN ('02','03','05') THEN 'KRISTAL' WHEN SB.GRUP_KODU IN ('10') THEN 'YENI ZELLANDIYA' WHEN SB.GRUP_KODU IN ('04','07') THEN 'AMERLAND' ELSE 'DIGER' END )[GRUP], HED.SOBE [BOLGE] ,COUNT(DISTINCT STH.STHAR_CARIKOD )[MUSTERI] " _
& " FROM HEDEFLER HED  WITH (NOLOCK)" _
& " LEFT OUTER JOIN TBLSTSABITEK EK WITH (NOLOCK) ON  EK.KULL8S =HED.STOK_GRUP " _
& " LEFT OUTER JOIN TBLSTHAR STH WITH (NOLOCK) ON  EK.STOK_KODU = STH.STOK_KODU AND HED.PLASIYER_KODU = STH.PLASIYER_KODU AND STH.STHAR_HTUR IN ('J','L') AND STH.STHAR_TARIH between '" & TARIH1 & "' and '" & TARIH2 & "' " _
& " LEFT OUTER JOIN TBLSTSABIT SB ON SB.STOK_KODU = EK.STOK_KODU " _
& " LEFT OUTER JOIN TBLCARIPLASIYER CAPLA WITH (NOLOCK) ON CAPLA.PLASIYER_KODU = HED.PLASIYER_KODU " _
& " WHERE  HED.AY_TARIX = '" & TARIX5 & "' AND HED.PLASIYER_KODU  IN  (SELECT PLA.PLASIYER_KODU  FROM TBLCARIPLASIYER PLA WHERE PLA.GRUPKODU LIKE '90%' OR PLA.GRUPKODU LIKE '09ERZ%' OR PLA.GRUPKODU LIKE '18%' OR PLA.GRUPKODU LIKE '23%' OR PLA.GRUPKODU LIKE '40%' OR PLA.GRUPKODU LIKE '44%' OR PLA.GRUPKODU LIKE '20%' OR PLA.GRUPKODU LIKE '62%' ) " _
& " GROUP BY HED.PLASIYER_KODU ,HED.STOK_GRUP , HED.HEDEF_QUTU,HED.PLASIYER_ACIKLAMA ,HED.SUPERVAYZER ,CAPLA.GRUPKODU ,HED.SOBE , HED.HEDEF_AZN ,SB.GRUP_KODU"





rc.Open sTmp, con, adOpenDynamic, adLockBatchOptimistic

Set rc = Nothing
rc.Open sTmp, con, adOpenDynamic, adLockBatchOptimistic
         
         i = 4
         A = 1
          
 Do While rc.EOF = False
   Sheet11.Cells(i, 1) = A
   Sheet11.Cells(i, 2) = rc("TEMSILCI").Value
   Sheet11.Cells(i, 3) = rc("SUPERVAYZER").Value
   Sheet11.Cells(i, 4) = rc("STOK_GRUP").Value
   Sheet11.Cells(i, 5) = rc("HEDEF_QUTU").Value
   Sheet11.Cells(i, 6) = rc("NET_SATIS_QUTU").Value
   Sheet11.Cells(i, 7) = rc("GUN_QUTU").Value
   Sheet11.Cells(i, 8) = rc("HEDEF_AZN").Value
   Sheet11.Cells(i, 9) = rc("SATIS_AZN").Value
   Sheet11.Cells(i, 10) = rc("GUN_AZN").Value
      Sheet11.Cells(i, 11) = rc("KANAL").Value
       Sheet11.Cells(i, 12) = rc("GRUP").Value
        Sheet11.Cells(i, 13) = rc("BOLGE").Value
   Sheet11.Cells(i, 14) = rc("MUSTERI").Value

      
     
         
     rc.MoveNext
  A = A + 1
   i = i + 1
  
   Loop
   
con.Close