Return value for null rows

Question

Return value for null rows

ggarmon

Grasshopper

Points: 18
More actions
July 19, 2010 at 12:07 pm

#97238

I have the following query:
select
distinct(convert(varchar(5),DATEADD(minute,(DATEDIFF(minute,DATEDIFF(dd,0,entereddate),entereddate)/15)*15,DATEADD(day,DATEDIFF(dd,0,entereddate),0)),108)) as RealHour,
round(avg(cast(substring(convert(varchar(10),(out_time - in_time),108),4,2) as numeric)),2) as RPhTurnaround, '30' as HelpLine, '25' as Caution
,round(avg(cast(substring(convert(varchar(10),((convert(varchar(12),entereddate,114)) - out_time),108),4,2) as numeric)),2) as OETTurn
,round(avg(cast(substring(convert(varchar(10),((convert(varchar(12),entereddate,114)) - in_time),108),4,2) as numeric)),2) as TotalTurn
,count(*) as TotalOrders
from order_turnaround
where entereddate >= dateadd(hh,-4,getdate())
and datepart(day,entereddate) = datepart(day,getdate())
and cast(substring(convert(varchar(10),(out_time - in_time),108),4,2) as numeric) <> cast(substring(convert(varchar(10),('59:00'),108),4,2) as numeric)
group by
convert(varchar(5),DATEADD(minute,(DATEDIFF(minute,DATEDIFF(dd,0,entereddate),entereddate)/15)*15,DATEADD(day,DATEDIFF(dd,0,entereddate),0)),108)
It basically returns summary info in 15 minute increments. There are some increments that do not have any data. How can I display those increments?
thanks

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 740103 More actions · Answer 1

I believe this is a place where you'd need a tally table the calculates the 15 minute increments and then outer join with that.

Essentially build a table of 15 minute increments for your time period and then join with that.

ggarmon Grasshopper Points: 18 More actions · Answer 2

So, do you mean a table that is populated by a DTS package that runs every 15mins to pull the data or did you have somehting else in mind?

thanks for the suggestion!

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 3

Nope... not a DTS package...

Here's 24 hours of 15 minute segments...

SELECT CONVERT(CHAR(5),DATEADD(mi,(t.N-1)*15,0),108)

FROM dbo.Tally t

WHERE t.N BETWEEN 1 AND 96

Of course, for that to work, you'll need a Tally table. Please see the following article for that...

http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)