Count and Case statement help 1

[mongol648]

I have the following query I am trying to run:

select datefilled,
case len(timefilled)
when 7 then SUBSTRING(CAST(timefilled as varchar),1,1)
else SUBSTRING(CAST(timefilled as varchar),1,2)
end A
from ScriptHistory
where datefilled > getdate()-7
group by timefilled,datefilled

I basically want to count the number of fills by hour. the timefilled column is an INT field with data similar to this 9054035. I am trying to pull out the first or first and second characters depending on the length of the data. I can get everything but grouping and getting a count other than 1. Any suggestions??

Thanks

 

[markros]

Try

select DateFilled, count(cast(Fill as int)) as FillPerDate from (select datefilled,   case len(timefilled)   when 7 then SUBSTRING(CAST(timefilled as varchar),1,1)   else SUBSTRING(CAST(timefilled as varchar),1,2)   end as Fill
from ScriptHistorywhere datefilled > dateadd(day,-7, getdate())) X group by DateFilled

 

[mongol648]

Awesome! thank you!