Remove From My Forums

Inner Join

Soru
0

Oylamak İçin Oturum Aç

Merhaba,

Bir sorguda yardımlarınızı rica edeceğim.

3 tablodan 3 tane basit sorgum var.

1.sorgu; DBT.DEBIT_AUTHORIZATION işlem sayısı ve işlem yapan kart sayısı

2.sorgu; POS.TERMINAL_TRN_LOG işlem sayısı ve işlem yapan kart sayısı

3.sorg; DBT.DEBIT_AUTHORIZATION_NON_FINANCIAL işlem sayısı ve işlem yapan kart sayısı

Ve bunları PRODUCT_NUMBER bazında grupladım

Amacım, bu 3 tablodan da birden işlem yapan SHADOW_CARD_NUMBER’ların işlem sayısı ve kart sayısına ihtiyacım var, kendim yazmaya çalıştım ama doğru olduğundan emin olamadım, sizlere danışmak istedim, yardımcı olabilirseniz çok sevinirim. Teşekkürler.

1.

use CardPayment

select PRODUCT_NUMBER, count(SHADOW_CARD_NUMBER) as 'İşlem Adeti',count(DISTINCT SHADOW_CARD_NUMBER) as 'Kart Adeti'

from DBT.DEBIT_AUTHORIZATION (NOLOCK)

WHERE INSERT_DATE BETWEEN '2019-04-01 00:00:00.000' AND '2019-04-30 23:59:59.000' AND

TRN_CODE '05' and RECORD_STATUS='A' and RESPONSE_CODE='00'

GROUP BY PRODUCT_NUMBER

2.

use CardPayment

select b.PRODUCT_NUMBER, count(a.SHADOW_CARD_NUMBER) as 'İşlem Adeti',count(DISTINCT a.SHADOW_CARD_NUMBER) as 'Kart Adeti'

from POS.TERMINAL_TRN_LOG a (NOLOCK)

left join DBT.DEBIT_CARD_MASTER b (Nolock) on a.SHADOW_CARD_NUMBER=b.SHADOW_CARD_NUMBER

WHERE a.INSERT_DATE BETWEEN '2019-04-01 00:00:00.000' AND '2019-04-30 23:59:59.000' AND

a.RESPONSE_CODE='00' and RECORD_STATUS='A'

GROUP BY b.PRODUCT_NUMBER

3.

use CardPayment

select b.PRODUCT_NUMBER, count(a.SHADOW_CARD_NUMBER) as 'İşlem Adeti',count(DISTINCT a.SHADOW_CARD_NUMBER) as 'Kart Adeti'

from DBT.DEBIT_AUTHORIZATION_NON_FINANCIAL a (NOLOCK)

left join DBT.DEBIT_CARD_MASTER b (Nolock) on a.SHADOW_CARD_NUMBER=b.SHADOW_CARD_NUMBER

WHERE a.INSERT_DATE BETWEEN '2019-04-01 00:00:00.000' AND '2019-04-30 23:59:59.000' AND

a.RESPONSE_CODE='00' and a.INSERT_CHANNEL_CODE='ATM' and RECORD_STATUS='A'

GROUP BY b.PRODUCT_NUMBER

Deneme amaçlı yapmaya çalıştığım sorgu

use CardPayment

select b.PRODUCT_NUMBER, count(b.SHADOW_CARD_NUMBER) as 'İşlem Adeti',count(DISTINCT b.SHADOW_CARD_NUMBER) as 'Kart Adeti'

from DBT.DEBIT_CARD_MASTER b (nolock)

inner join POS.TERMINAL_TRN_LOG c (nolock) on b.SHADOW_CARD_NUMBER=c.SHADOW_CARD_NUMBER

inner join DBT.DEBIT_AUTHORIZATION_NON_FINANCIAL d (nolock) on b.SHADOW_CARD_NUMBER=d.SHADOW_CARD_NUMBER

inner join DBT.DEBIT_AUTHORIZATION a (Nolock) on a.SHADOW_CARD_NUMBER=b.SHADOW_CARD_NUMBER

WHERE a.INSERT_DATE BETWEEN '2019-04-01 00:00:00.000' AND '2019-04-30 23:59:59.000' and

c.INSERT_DATE BETWEEN '2019-04-01 00:00:00.000' AND '2019-04-30 23:59:59.000' and

d.INSERT_DATE BETWEEN '2019-04-01 00:00:00.000' AND '2019-04-30 23:59:59.000' and

a.TRN_CODE= '05' and a.RECORD_STATUS='A' and a.RESPONSE_CODE='00' and

c.RESPONSE_CODE='00' and c.RECORD_STATUS='A' and

d.RESPONSE_CODE='00' and d.INSERT_CHANNEL_CODE='ATM' and d.RECORD_STATUS='A'

GROUP BY b.PRODUCT_NUMBER

13 Mayıs 2019 Pazartesi 12:44

Yanıt

|

Alıntı

Yanıtlar

Oylamak İçin Oturum Aç

Evet dogru degil. MS SQL server'da DateTime aralıkları BETWEEN kullanılarak sorgulanmaz, hataya acıktır. Onun yerine >= ve < kullanmanız lazim, BETWEEN ise >= ve <= anlamındadır (ust sinir hariç olmalı sorguda).

Ikincisi "use DbName" kullanacaksaniz bunun ayni bir batch olması gerekir. Yani sonrasında "GO" olmalıydı. Bu sekilde calistigina gore, zaten baglandiginiz database CardPayment.

Henüz hepsine bakmadım ama sanki 2.sorgudaki join tamamen gereksiz. Ilkinde de syntax hatası var ama herhalde buraya yazarken yapmissinizdir.

DECLARE @baslangic DATETIME = '20190401';
DECLARE @bitis DATETIME = '20190501';

-- 1.Sorgu
SELECT PRODUCT_NUMBER,
       COUNT(SHADOW_CARD_NUMBER) AS 'İşlem Adeti',
       COUNT(DISTINCT SHADOW_CARD_NUMBER) AS 'Kart Adeti'
FROM DBT.DEBIT_AUTHORIZATION (NOLOCK)
WHERE INSERT_DATE >= @baslangic
      AND INSERT_DATE < @bitis
      AND TRN_CODE = '05'
      AND RECORD_STATUS = 'A'
      AND RESPONSE_CODE = '00'
GROUP BY PRODUCT_NUMBER;

-- 2.Sorgu
SELECT PRODUCT_NUMBER,
       COUNT(SHADOW_CARD_NUMBER) AS 'İşlem Adeti',
       COUNT(DISTINCT SHADOW_CARD_NUMBER) AS 'Kart Adeti'
FROM POS.TERMINAL_TRN_LOG b (NOLOCK)
WHERE EXISTS
(
    SELECT *
    FROM DBT.DEBIT_CARD_MASTER a (NOLOCK)
    WHERE a.SHADOW_CARD_NUMBER = b.SHADOW_CARD_NUMBER
          AND a.INSERT_DATE >= @baslangic
          AND a.INSERT_DATE < @bitis
          AND a.RESPONSE_CODE = '00'
          AND a.RECORD_STATUS = 'A'
)
GROUP BY PRODUCT_NUMBER;

-- 3.Sorgu, soru isareti
SELECT b.PRODUCT_NUMBER,
       COUNT(a.SHADOW_CARD_NUMBER) AS 'İşlem Adeti',
       COUNT(DISTINCT a.SHADOW_CARD_NUMBER) AS 'Kart Adeti'
FROM DBT.DEBIT_AUTHORIZATION_NON_FINANCIAL a (NOLOCK)
    LEFT JOIN DBT.DEBIT_CARD_MASTER b (NOLOCK)
        ON a.SHADOW_CARD_NUMBER = b.SHADOW_CARD_NUMBER
WHERE a.INSERT_DATE >= @baslangic
      AND a.INSERT_DATE < @bitis
      AND a.RESPONSE_CODE = '00'
      AND a.INSERT_CHANNEL_CODE = 'ATM'
      AND a.RECORD_STATUS = 'A'
GROUP BY b.PRODUCT_NUMBER;


--
SELECT b.PRODUCT_NUMBER,
       COUNT(b.SHADOW_CARD_NUMBER) AS 'İşlem Adeti',
       COUNT(DISTINCT b.SHADOW_CARD_NUMBER) AS 'Kart Adeti'
FROM DBT.DEBIT_CARD_MASTER b (NOLOCK)
WHERE EXISTS
(
    SELECT *
    FROM POS.TERMINAL_TRN_LOG c (NOLOCK)
    WHERE b.SHADOW_CARD_NUMBER = c.SHADOW_CARD_NUMBER
          AND c.INSERT_DATE >= @baslangic
          AND c.INSERT_DATE < @bitis
          AND c.RESPONSE_CODE = '00'
          AND c.RECORD_STATUS = 'A'
)
      AND EXISTS
(
    SELECT *
    FROM DBT.DEBIT_AUTHORIZATION_NON_FINANCIAL d (NOLOCK)
    WHERE b.SHADOW_CARD_NUMBER = d.SHADOW_CARD_NUMBER
          AND d.INSERT_DATE >= @baslangic
          AND d.INSERT_DATE < @bitis
          AND d.RESPONSE_CODE = '00'
          AND d.INSERT_CHANNEL_CODE = 'ATM'
          AND d.RECORD_STATUS = 'A'
)
      AND EXISTS
(
    SELECT *
    FROM DBT.DEBIT_AUTHORIZATION a (NOLOCK)
    WHERE a.SHADOW_CARD_NUMBER = b.SHADOW_CARD_NUMBER
          AND a.INSERT_DATE >= @baslangic
          AND a.INSERT_DATE < @bitis
          AND a.TRN_CODE = '05'
          AND a.RECORD_STATUS = 'A'
          AND a.RESPONSE_CODE = '00'
)
GROUP BY b.PRODUCT_NUMBER;

Yanıt Olarak İşaretleyen ErsinBicer 14 Mayıs 2019 Salı 06:28

13 Mayıs 2019 Pazartesi 13:18

Tüm Yanıtlar