views:

32

answers:

1

What I'm trying to do is create a table that has 2 fields, one being the status and one being a type of equipment. I then need a list of how long these particular types of equipment have been being repaired. There can be multiples of each equipment, so I would like something like this:

Equipment Type  |  Status | 0-7 days | 8-15 days | 16-30 days | ...
Type A          |    B    |     3    |     2     |     0      | ...
Type B          |    B    |     1    |     0     |     13     | ...

etc. Here is what I have code wise in attempt to do this:

SELECT  EQP_STAT_EQP, EQP_TYP_EQP,
count(case [Days_Outstanding] when 'Less than 7 Days Outstanding     ' then 1 else null end) as [0_to_7_Days_Outstanding],
count(case [Days_Outstanding] when '8 to 14 Days Outstanding         ' then 1 else null end) as [8_to_14_Days_Outstanding],
count(case [Days_Outstanding] when '15 to 30 Days Outstanding        ' then 1 else null end) as [15_to_30_Days_Outstanding],
count(case [Days_Outstanding] when '31 to 60 Days Outstanding        ' then 1 else null end) as [31_to_60_Days_Outstanding],
count(case [Days_Outstanding] when '61 to 90 Days Outstanding        ' then 1 else null end) as [61_to_90_Days_Outstanding],
count(case [Days_Outstanding] when '91 to 120 Days Outstanding       ' then 1 else null end) as [91_to_120_Days_Outstanding],
count(case [Days_Outstanding] when 'Greater than 120 Days Outstanding' then 1 else null end) as [120_Plus_Days_Outstanding]
INTO Repair_Status_Summary
FROM Total_Equipment 
WHERE EQP_STAT_EQP='B'
GROUP BY EQP_STAT_EQP, EQP_TYP_EQP

However, this keeps giving me the error:

'Msg 245, Level 16, State 1, Line 143
Conversion failed when converting the varchar value ''Less than 7 Days Outstanding     '' to data type int.'

I have done this type of table before, exactly like this, and even copy and pasted that in and changed the names of the fields thinking that might solve the problem to no avail, the only difference is the WHERE statement which I added.

Something else I don't understand is why it's saying it's a varchar, when I have Days_Outstanding defined as a char(35). So I even went as far as dropping the Total_Equipment table and repopulating it again. So if you have any ideas, it's appreciated.

Thanks!

EDIT

@Mark Bannister - Thank you! I feel so dumb now, but I was just not seeing it. I should have had a field [Aging_Bucket], which I use [Days_Outstanding] to define. Sorry to everyone for the waste of time, I just was not seeing it.

+3  A: 
then 1

needs to be

then '1'

1 is an integer and '1' is a varchar

SELECT  EQP_STAT_EQP, EQP_TYP_EQP,
count(case [Days_Outstanding] when 'Less than 7 Days Outstanding     ' then '1' else null end) as [0_to_7_Days_Outstanding],
count(case [Days_Outstanding] when '8 to 14 Days Outstanding         ' then '1' else null end) as [8_to_14_Days_Outstanding],
count(case [Days_Outstanding] when '15 to 30 Days Outstanding        ' then '1' else null end) as [15_to_30_Days_Outstanding],
count(case [Days_Outstanding] when '31 to 60 Days Outstanding        ' then '1' else null end) as [31_to_60_Days_Outstanding],
count(case [Days_Outstanding] when '61 to 90 Days Outstanding        ' then '1' else null end) as [61_to_90_Days_Outstanding],
count(case [Days_Outstanding] when '91 to 120 Days Outstanding       ' then '1' else null end) as [91_to_120_Days_Outstanding],
count(case [Days_Outstanding] when 'Greater than 120 Days Outstanding' then '1' else null end) as [120_Plus_Days_Outstanding]
INTO Repair_Status_Summary
FROM Total_Equipment 
WHERE EQP_STAT_EQP='B'
GROUP BY EQP_STAT_EQP, EQP_TYP_EQP
SQLMenace
Right, but wouldn't I need to have an int, since I'm trying to get the total count how many times each occurs?
COUNT counts records, and doesn't sum up the 1's. Thatfor you would use SUM and would have to replace the `null`'s with a 0.
MicSim