MS SQL ile When Case karar yapısı kullanımı
4.396 defa okundu
Konu: Raporlama
İçindekiler
SQL ile When Case karar yapısını kullanarak bir alana 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
SQL
- 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.
SQL
- 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
SQL
- 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' ENDFROM #GECICI_TABLO) AS A1GROUP BY DURUM
Sonuç :
| DURUM | SAYI | ORAN |
| BOS | 6 | 37,5 |
| DOLU | 10 | 62,5 |
Teşekkürler, geri bildiriminiz alındı.
