I create a view to display ValueRange like this:
...
ValueRange=case when Value1>60 and Value1<=65 then '60-65'
when Value1>65 and Value1<=70 then '65-70'
when Value1>70 and Value1<=75 then '70-75' END
...
But I don't have any data for Value1>65 and Value1<=70… so I can't see the these rows in result.
My result is :
ValueRange Count
60-65 5
70-75 3
But my result should be:
ValueRange Count
60-65 5
65-70 0
70-75 3
Edit:As i said before, full code is more complex. Actually this is the two dimensional pivot table and your answers are invalid for my sql. So i publish all codes.
WITH RangeTable(YukRange,FiyatRange) AS (SELECT
YukRange=case when Yük<=20000 or Yük IS NULL then '0-20000'
when Yük>20000 and Yük<=21000 then '20000-21000'
when Yük>21000 and Yük<=22000 then '21000-22000'
when Yük>22000 and Yük<=23000 then '22000-23000'
when Yük>23000 and Yük<=24000 then '23000-24000'
when Yük>24000 and Yük<=25000 then '24000-25000'
when Yük>25000 and Yük<=26000 then '25000-26000'
when Yük>26000 and Yük<=27000 then '26000-27000'
when Yük>27000 and Yük<=28000 then '27000-28000'
when Yük>28000 and Yük<=29000 then '28000-29000'
when Yük>29000 and Yük<=30000 then '29000-30000'
when Yük>30000 and Yük<=31000 then '30000-31000'
when Yük>31000 and Yük<=32000 then '31000-32000'
when Yük>32000 and Yük<=33000 then '32000-33000'
when Yük>33000 and Yük<=34000 then '33000-34000'
when Yük>34000 and Yük<=35000 then '34000-35000'
when Yük>35000 and Yük<=36000 then '35000-36000'
when Yük>36000 and Yük<=37000 then '36000-37000'
when Yük>37000 and Yük<=38000 then '37000-38000'
when Yük>38000 and Yük<=39000 then '38000-39000'
when Yük>39000 and Yük<=40000 then '39000-40000'
when Yük>40000 and Yük<=41000 then '40000-41000' END
, FiyatRange=case when GunlukParams_SGOF<=60 or GunlukParams_SGOF IS NULL then '0-60'
when GunlukParams_SGOF>60 and GunlukParams_SGOF<=65 then '60-65'
when GunlukParams_SGOF>65 and GunlukParams_SGOF<=70 then '65-70'
when GunlukParams_SGOF>70 and GunlukParams_SGOF<=75 then '70-75'
when GunlukParams_SGOF>75 and GunlukParams_SGOF<=80 then '75-80'
when GunlukParams_SGOF>80 and GunlukParams_SGOF<=85 then '80-85'
when GunlukParams_SGOF>85 and GunlukParams_SGOF<=90 then '85-90'
when GunlukParams_SGOF>90 and GunlukParams_SGOF<=95 then '90-95'
when GunlukParams_SGOF>95 and GunlukParams_SGOF<=100 then '95-100'
when GunlukParams_SGOF>100 and GunlukParams_SGOF<=105 then '100-105'
when GunlukParams_SGOF>105 and GunlukParams_SGOF<=110 then '105-110'
when GunlukParams_SGOF>110 and GunlukParams_SGOF<=115 then '110-115'
when GunlukParams_SGOF>115 and GunlukParams_SGOF<=120 then '115-120'
when GunlukParams_SGOF>120 and GunlukParams_SGOF<=125 then '120-125'
when GunlukParams_SGOF>125 and GunlukParams_SGOF<=130 then '125-130'
when GunlukParams_SGOF>130 and GunlukParams_SGOF<=135 then '130-135'
when GunlukParams_SGOF>135 and GunlukParams_SGOF<=140 then '135-140'
when GunlukParams_SGOF>140 and GunlukParams_SGOF<=145 then '140-145'
when GunlukParams_SGOF>145 and GunlukParams_SGOF<=150 then '145-150'
when GunlukParams_SGOF>150 and GunlukParams_SGOF<=155 then '150-155'
when GunlukParams_SGOF>155 and GunlukParams_SGOF<=160 then '155-160'
when GunlukParams_SGOF>160 and GunlukParams_SGOF<=165 then '160-165'
when GunlukParams_SGOF>165 and GunlukParams_SGOF<=170 then '165-170'
when GunlukParams_SGOF>170 and GunlukParams_SGOF<=175 then '170-175'
when GunlukParams_SGOF>175 and GunlukParams_SGOF<=180 then '175-180'
when GunlukParams_SGOF>180 and GunlukParams_SGOF<=185 then '180-185'
when GunlukParams_SGOF>185 and GunlukParams_SGOF<=190 then '185-190'
when GunlukParams_SGOF>190 and GunlukParams_SGOF<=195 then '190-195'
when GunlukParams_SGOF>195 and GunlukParams_SGOF<=200 then '195-200'
when GunlukParams_SGOF>200 and GunlukParams_SGOF<=205 then '200-205'
when GunlukParams_SGOF>205 and GunlukParams_SGOF<=210 then '205-210'
when GunlukParams_SGOF>210 and GunlukParams_SGOF<=215 then '210-215'
when GunlukParams_SGOF>215 and GunlukParams_SGOF<=220 then '215-220'
when GunlukParams_SGOF>220 and GunlukParams_SGOF<=225 then '220-225'
when GunlukParams_SGOF>225 and GunlukParams_SGOF<=230 then '225-230'
when GunlukParams_SGOF>230 and GunlukParams_SGOF<=235 then '230-235'
when GunlukParams_SGOF>235 and GunlukParams_SGOF<=240 then '235-240'
when GunlukParams_SGOF>240 and GunlukParams_SGOF<=245 then '240-245'
when GunlukParams_SGOF>245 and GunlukParams_SGOF<=250 then '245-250'
when GunlukParams_SGOF>250 and GunlukParams_SGOF<=255 then '250-255'
when GunlukParams_SGOF>255 and GunlukParams_SGOF<=260 then '255-260'
when GunlukParams_SGOF>260 and GunlukParams_SGOF<=265 then '260-265'
when GunlukParams_SGOF>265 and GunlukParams_SGOF<=270 then '265-270'
when GunlukParams_SGOF>270 and GunlukParams_SGOF<=275 then '270-275'
when GunlukParams_SGOF>275 and GunlukParams_SGOF<=280 then '275-280'
when GunlukParams_SGOF>280 and GunlukParams_SGOF<=285 then '280-285'
when GunlukParams_SGOF>285 and GunlukParams_SGOF<=290 then '285-290'
when GunlukParams_SGOF>290 and GunlukParams_SGOF<=295 then '290-295'
when GunlukParams_SGOF>295 and GunlukParams_SGOF<=300 then '295-300'
when GunlukParams_SGOF>300 then '300+' END
FROM YukFiyat_View),
MainTable AS (
SELECT * FROM RangeTable
PIVOT(
COUNT(YukRange)
FOR YukRange IN ([0-20000], [20000-21000], [21000-22000], [22000-23000], [23000-24000], [24000-25000], [25000-26000], [26000-27000]
, [27000-28000], [28000-29000], [29000-30000], [30000-31000], [31000-32000], [32000-33000], [33000-34000], [34000-35000]
, [35000-36000], [36000-37000], [37000-38000], [38000-39000], [39000-40000], [40000-41000])
) AS Pvt)
select * from MainTable;
Explain:I am getting the values from YukFiyat_View table. According to two values in this table i create temporary table for two different ranges.
Original Table(YukFiyat_View):
Date Yük GunlukParams_SGOF
2009-12-30 00:00:00.000 21590 140
2009-12-30 01:00:00.000 20208 100
2009-12-30 02:00:00.000 19313 70
2009-12-30 03:00:00.000 18866 57
2009-12-30 04:00:00.000 18812 57
2009-12-30 05:00:00.000 18974 58
2009-12-30 06:00:00.000 19791 69
2009-12-30 07:00:00.000 20630 69
2009-12-30 08:00:00.000 23314 69
2009-12-30 09:00:00.000 25318 69
My temporary table:
Date Yük GunlukParams_SGOF YukRange FiyatRange
2009-12-30 00:00:00.000 21590 140 [21000-22000] [140-145]
2009-12-30 01:00:00.000 20208 100 [20000-21000] [100-105]
2009-12-30 02:00:00.000 19313 70 [0-20000] [70-75]
2009-12-30 03:00:00.000 18866 57 [0-20000] [0-60]
2009-12-30 04:00:00.000 18812 57 [0-20000] [0-60]
2009-12-30 05:00:00.000 18974 58 [0-20000] [0-60]
2009-12-30 06:00:00.000 19791 69 [0-20000] [65-70]
2009-12-30 07:00:00.000 20630 69 [20000-21000] [65-70]
2009-12-30 08:00:00.000 23314 69 [23000-24000] [65-70]
2009-12-30 09:00:00.000 25318 69 [25000-26000] [65-70]
After that, I use pivot table to create two dimensional range table. Result is:
FiyatRange [0-20000] [20000-21000] [21000-22000] ...
[0-60] 3 0 0
[65-70] 1 1 0
[70-75] 1 0 0
[100-105] 0 1 0
[140-145] 0 0 1
.
.
My problem is: Some ranges for FiyatRange doesn't contain in my table yet. Because of this, i cant see all FiyatRange values that i created doesn't display in result.
So result is I want:
FiyatRange [0-20000] [20000-21000] [21000-22000] ...
[0-60] 3 0 0
[60-65] 0 0 0
[65-70] 1 1 0
[70-75] 1 0 0
[75-80] 0 0 0
.
.
[100-105] 0 1 0
.
.
[140-145] 0 0 1
.
.
So if the row value is all '0', i want to see this row in the result.
Best Answer