MS SQL ile When Case karar yapısı kullanımı

14.10.2015 3541 defa okundu

SQL ile When Case karar yapısını kullanarak bir alana göre göre boş ve dolu kayıtları sayma ve kayıtların dolu ve boş olma durumunu toplam kayıt sayısına göre orantısını bulmak için,

Veri kaynağı için geçici tablo oluşturma

SELECT 1 AS ID,'AHMET1' AS ADI,'DENEME1' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-08-19 12:52:03.000' AS TARIH
INTO #GECICI_TABLO
UNION
SELECT 2 AS ID, 'AHMET2' AS ADI,'DENEME2' AS SOYADI,'ANKARA' AS SEHIR,'2014-01-31 12:00:00.000' AS TARIH
UNION
SELECT 3 AS ID, 'AHMET3' AS ADI,'DENEME3' AS SOYADI,NULL AS SEHIR,'2014-08-31 10:51:22.000' AS TARIH
UNION
SELECT 4 AS ID,'AHMET4' AS ADI,'DENEME4' AS SOYADI,'ANKARA' AS SEHIR,'2014-08-21 12:29:45.000' AS TARIH
UNION
SELECT 5 AS ID,'AHMET5' AS ADI,'DENEME5' AS SOYADI,'İZMİR' AS SEHIR,'2014-08-20 09:22:00.000' AS TARIH
UNION
SELECT 6 AS ID,'AHMET6' AS ADI,'DENEME6' AS SOYADI,NULL AS SEHIR,'2014-08-13 10:38:20.000' AS TARIH
UNION
SELECT 7 AS ID,'AHMET7' AS ADI,'DENEME7' AS SOYADI,NULL AS SEHIR,'2014-06-25 12:00:00.000' AS TARIH
UNION
SELECT 8 AS ID,'AHMET8' AS ADI,'DENEME8' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-08-22 02:39:00.000' AS TARIH
UNION
SELECT 9 AS ID,'AHMET9' AS ADI,'DENEME9' AS SOYADI,'İSTANBUL' AS SEHIR,'2013-12-22 10:01:20.000' AS TARIH
UNION
SELECT 10 AS ID,'AHMET10' AS ADI,'DENEME10' AS SOYADI,'İZMİR' AS SEHIR,'2014-09-06 11:07:23.000' AS TARIH
UNION
SELECT 11 AS ID,'AHMET11' AS ADI,'DENEME11' AS SOYADI,NULL AS SEHIR,'2014-01-18 22:52:03.000' AS TARIH
UNION
SELECT 12 AS ID,'AHMET12' AS ADI,'DENEME12' AS SOYADI,'İZMİR' AS SEHIR,'2014-03-29 16:50:04.000' AS TARIH
UNION
SELECT 13 AS ID,'AHMET13' AS ADI,'DENEME13' AS SOYADI,NULL AS SEHIR,'2014-06-05 10:22:23.000' AS TARIH
UNION
SELECT 14 AS ID,'AHMET14' AS ADI,'DENEME14' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-06-15 20:25:00.000' AS TARIH
UNION
SELECT 15 AS ID,'AHMET15' AS ADI,'DENEME15' AS SOYADI,'İSTANBUL' AS SEHIR,'2014-02-05 02:45:55.000' AS TARIH
UNION
SELECT 16 AS ID,'AHMET15' AS ADI,'DENEME16' AS SOYADI,NULL AS SEHIR,'2014-05-03 11:34:16.000' AS TARIH

Oluşturduğumuz geçici tablomuzun içeriği aşağıdaki gibidir.

SELECT * FROM #GECICI_TABLO

ID	ADI	SOYADI	SEHIR	TARIH
1	AHMET1	DENEME1	İSTANBUL	2014-08-19 12:52:03.000
2	AHMET2	DENEME2	ANKARA	2014-01-31 12:00:00.000
3	AHMET3	DENEME3	NULL	2014-08-31 10:51:22.000
4	AHMET4	DENEME4	ANKARA	2014-08-21 12:29:45.000
5	AHMET5	DENEME5	İZMİR	2014-08-20 09:22:00.000
6	AHMET6	DENEME6	NULL	2014-08-13 10:38:20.000
7	AHMET7	DENEME7	NULL	2014-06-25 12:00:00.000
8	AHMET8	DENEME8	İSTANBUL	2014-08-22 02:39:00.000
9	AHMET9	DENEME9	İSTANBUL	2013-12-22 10:01:20.000
10	AHMET10	DENEME10	İZMİR	2014-09-06 11:07:23.000
11	AHMET11	DENEME11	NULL	2014-01-18 22:52:03.000
12	AHMET12	DENEME12	İZMİR	2014-03-29 16:50:04.000
13	AHMET13	DENEME13	NULL	2014-06-05 10:22:23.000
14	AHMET14	DENEME14	İSTANBUL	2014-06-15 20:25:00.000
15	AHMET15	DENEME15	İSTANBUL	2014-02-05 02:45:55.000
16	AHMET15	DENEME16	NULL	2014-05-03 11:34:16.000

Dolu kayıt sayısını bulan sorgu

SELECT DURUM,COUNT(DURUM) AS SAYI,ROUND(CONVERT(FLOAT,COUNT(DURUM))/
(SELECT COUNT(*) FROM #GECICI_TABLO)*100,2) AS ORAN
FROM
(SELECT DURUM=CASE WHEN SEHIR IS NOT NULL THEN 'DOLU' ELSE 'BOS' END
FROM #GECICI_TABLO) AS A1
GROUP BY DURUM

Sonuç :