• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Need some help writing a SQL Query

So i am working on a query for summarizing machine delay instances.

What i have now (code attached) shows a query that summarizes delay by Maincategory for a set time period. In the example its 8 hours.

What i would like to do is add another column that shows the cumulative for the week. I would guess i need to run another select statement for another time range and somehow union the tables together.

Any advice would be greatly appreciated. Thanks
declare @Starttime datetime
declare @Endtime datetime
declare @PrevHours int

--set variables here
set @EndTime = '2011-10-26 23:00:00'
set @PrevHours = -8
set @StartTime = dateadd(hh,@PrevHours,@EndTime)

SELECT ISNULL(MainCategoryDescription,'Not Classified') as 'Delay Type', round(sum(DelayHours),2) as Hours
FROM [RKBBackEnd].[dbo].[v_Delay]
where timedelaybegin > @starttime and timedelayend < @Endtime and LineID = 1
group by MainCategoryDescription order by Hours desc

Open in new window

1 Solution
what are you trying to accumulated for the week?

declare @Starttime datetime
,@Endtime datetime
, @PrevHours int
,@Startweek dateweek

--set variables here
select @EndTime = '2011-10-26 23:00:00'
, @PrevHours = -8
, @StartTime = dateadd(hh,@PrevHours,@EndTime)

;with cte as (select
coalesce(MainCategoryDescription,'Not Classified') as [Delay Type]
     , DelayHours
FROM [RKBBackEnd].[dbo].[v_Delay]
where timedelaybegin > @startweek
  and timedelayend < @Endtime
  and LineID = 1

SELECT [Delay Type]
       , round(sum(case when timedelaybegin > @starttime then DelayHours end),2) as Hours
       , count(*) as [delays lastweek]
       , round(sum(DelayHours),2) as [delay Hours last week]
FROM cte

group by [delay type]
order by 1,2 desc
michael_krellAuthor Commented:
what you gave me seems to do the trick. Nice!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now