May 13, 2008 at 12:52 pm
I have a machine which has 6 independant tracks.
In my SQL DB I have a table which holds information on whether the tracks are active or not.
When the "Active/Not Active" bit on the machine changes for any track a new record is created in the table with Active = 1 and Not Active = 0.
The table is as follows:
USE [BC_CP12_Statistical]
GO
/****** Object: Table [dbo].[tracks] Script Date: 05/13/2008 19:46:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tracks](
[DateTime] [datetime] NULL,
[Track_1] [bit] NULL,
[Track_2] [bit] NULL,
[Track_3] [bit] NULL,
[Track_4] [bit] NULL,
[Track_5] [bit] NULL,
[Track_6] [bit] NULL
) ON [PRIMARY]
I want to setup a function that given the track number and datetime will return 1 if the track was active or 0 if the track was not active.
I can write 6 different queries/functions to perform this task but I would have thought it would be possible with 1.
Thanks for any help
Mike Atkinson
May 13, 2008 at 1:15 pm
Something like this should do it:
CREATE TABLE #Tracks(
[DateTime] [datetime] NULL,
[Track_1] [bit] NULL,
[Track_2] [bit] NULL,
[Track_3] [bit] NULL,
[Track_4] [bit] NULL,
[Track_5] [bit] NULL,
[Track_6] [bit] NULL
)
insert into #tracks ([datetime], track_1, track_2, track_3,
track_4, track_5, track_6)
select '5/13/2008 1 pm', 1, 0, 0, 0, 0, 0
union all
select '5/13/2008 2 pm', 0, 1, 0, 0, 0, 0
union all
select '5/13/2008 3 pm', 1, 0, 0, 0, 0, 0
declare @Time_in datetime, @Track_in tinyint --Input params
select @Time_in = '5/13/2008 2:30 pm', @track_in = 1 --Remove as will be
-- assigned by input param values
-- Finds the last time the track was active before the time param
declare @ActiveTime datetime
select @activetime = (
select max([datetime])
from #tracks
where
(@track_in = 1 and track_1 = 1
or
@track_in = 2 and track_2 = 1
or
@track_in = 3 and track_3 = 1
or
@track_in = 4 and track_4 = 1
or
@track_in = 5 and track_5 = 1
or
@track_in = 6 and track_6 = 1)
and [datetime] < @time_in)
-- Detects if there is a row where that track is inactive
-- after that, but before the time selected.
-- This should indicate the row was inactive at the time
-- requested.
select case
when exists
(select 1
from #tracks
where [datetime] < @time_in
and [datetime] > @activetime
and
(@track_in = 1 and track_1 = 0
or
@track_in = 2 and track_2 = 0
or
@track_in = 3 and track_3 = 0
or
@track_in = 4 and track_4 = 0
or
@track_in = 5 and track_5 = 0
or
@track_in = 6 and track_6 = 0))
then 'Not Active'
else 'Active'
end
Remove the table creation and sample data (I used those for my tests, you might want them to test this out). Use the variables declared with "_in" on them as the input parameters of the proc/function. Modify further as needed. Should get you what you need.
Could be consolidated further into a single statement, but I think it's more maintainable this way. Easier to read what it does.
Note that separate procs for each track would definitely take better advantage of indexes and table statistics. Might be more of a pain to maintain, but might be worth doing for the speed it will give you.
For that, you would create six versions of this:
create proc dbo.TrackActive_1
(@Time_in datetime)
as
declare @ActiveTime datetime
select @activetime = (
select max([datetime])
from #tracks
where track_1 = 1
and [datetime] < @time_in)
select case
when exists
(select 1
from #tracks
where [datetime] < @time_in
and [datetime] > @activetime
and track_1 = 0)
then 'Not Active'
else 'Active'
end
And one version of this:
create proc TrackActive
(@Track_in tinyint,
@Time_in datetime)
as
if @track_in = 1
exec dbo.TrackActive_1 @Time_in
else
if @track_in = 2
exec dbo.TrackActive_2 @Time_in
... (and so on)
If you have the right indexes, and the table is big enough for it to matter, the second solution will almost certainly be much faster, since each sub-proc (TrackActive_1, etc.) can have an execution plan stored that will be right for what it's doing, and can use indexes (if you have them).
I recommend the second solution (master and sub-procs).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply