drei22
asked on
SQL 2000 Query help
Can the following query be streamlined by CONVERT instead of CAST? If so how? No rush on this I'm in a learning phase with SQL and I'm getting quite confused with aggregation and datetime functions.
select cast(
cast(yy as nvarchar(4))+'-'+
cast(mm as nvarchar(2))+'-'+
cast(dd as nvarchar(2))+' '+
cast(hh as nvarchar(2))+':'+
cast(mi15*15 as nvarchar(2))
as datetime) dtCompleted, counts
into #T
from
(select
datepart(yy, dtcompleted) yy,
datepart(mm, dtcompleted) mm,
datepart(dd, dtcompleted) dd,
datepart(hh, dtcompleted) hh,
datepart(mi,dtcompleted)/1 5 mi15,
count(dtcompleted) counts
from sds (nolock)
where dtcompleted between '2008-08-01' and '2008-08-08'
group by
datepart(yy, dtcompleted),
datepart(mm, dtcompleted),
datepart(dd, dtcompleted),
datepart(hh, dtcompleted),
datepart(mi,dtcompleted)/1 5
)
order by 1
select * from #t
select min(counts),max(counts),av g(counts), sum(counts ) from #t
drop table #t
select cast(
cast(yy as nvarchar(4))+'-'+
cast(mm as nvarchar(2))+'-'+
cast(dd as nvarchar(2))+' '+
cast(hh as nvarchar(2))+':'+
cast(mi15*15 as nvarchar(2))
as datetime) dtCompleted, counts
into #T
from
(select
datepart(yy, dtcompleted) yy,
datepart(mm, dtcompleted) mm,
datepart(dd, dtcompleted) dd,
datepart(hh, dtcompleted) hh,
datepart(mi,dtcompleted)/1
count(dtcompleted) counts
from sds (nolock)
where dtcompleted between '2008-08-01' and '2008-08-08'
group by
datepart(yy, dtcompleted),
datepart(mm, dtcompleted),
datepart(dd, dtcompleted),
datepart(hh, dtcompleted),
datepart(mi,dtcompleted)/1
)
order by 1
select * from #t
select min(counts),max(counts),av
drop table #t
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.