Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
3  
I'd suggest creating a derived table that contains all of the ValueRanges, then you can LEFT JOIN to make sure that you return all of the ranges. –  bluefeet Aug 29 at 14:39

1 Answer 1

up vote 12 down vote accepted
DECLARE @ranges TABLE
(
  Label VARCHAR(20),
  LBound INT,
  UBound INT
);

INSERT @ranges(Label,LBound,UBound)
VALUES(...),
('60-65', 60, 65),
('70-75', 70, 75),
(...);

SELECT ValueRange = r.Label, [Count] = COUNT(o.key)
  FROM @ranges AS r
  LEFT OUTER JOIN dbo.othertable AS o
  ON o.value1 > r.LBound
  AND o.value1 <= r.UBound
  -- AND other filters for dbo.othertable
GROUP BY r.Label;
share|improve this answer
1  
+1 because I was too lazy to write an answer :) –  bluefeet Aug 29 at 14:53
    
You could also replace the @ranges in the query with the values statement. eg FROM (VALUES (...),(...)) AS r (Label1, LBound, UBound) LEFT OUTER JOIN ... –  MickyT Aug 29 at 22:36
    
@MickyT yep, I'd rather go the other way though, if these ranges are always the ones used, why not simplify the query and put them in a real table? –  Aaron Bertrand Aug 29 at 22:40
    
@Aaron Bertrand Ranges is constant but the table that contains value1 is dynamic table(adding New values per an hour) So we cant use real table –  Bünyamin Atik Aug 30 at 23:40
    
@BünyaminAtik I'm not quite sure I understand the problem. You can certainly use a real table for the ranges, regardless of whether you left join that to a table variable, temporary table, or a "dynamic" table in dynamic SQL. In fact in the dynamic SQL case a permanent table for the ranges is going to make your life easier, not harder. –  Aaron Bertrand Aug 30 at 23:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.