April 15, 2011 at 2:27 pm
I have the following stored procedure that was created by someone else:
CREATE PROCEDURE [dbo].[oncalladd3]
@subschedule varchar(50),
@lookup_time datetime = NULL,
@no_oc_value varchar(50) = 'No o/c listed'
AS
DECLARE @temptable TABLE (
Schedname nvarchar(50),
FirstListing nvarchar(50),
Oncall_Start datetime,
Oncall_end datetime,
StartOnCallDate int,
StartOnCallTime int,
Duration decimal(18,0)
)
DECLARE @count int
BEGIN
IF (@lookup_time IS NULL)
SET @lookup_time = CURRENT_TIMESTAMP
INSERT into @temptable SELECT *
FROM (SELECT Schedname,
FirstListing,
CONVERT(datetime, DATEADD(mi, StartOnCallTime, CONVERT(datetime, StartOnCallDate - 1))) AS Oncall_Start,
CONVERT(datetime, DATEADD(mi, StartOnCallTime + Duration, CONVERT(datetime, StartOnCallDate - 1))) AS OnCall_end,
StartOnCallDate,
StartOnCallTime,
Duration
FROM mdr.dbo.mOnCallAdd
WHERE SchedName = @subschedule) Oc
WHERE DATEDIFF(mi, OnCall_start, @lookup_time) >= 0
AND DATEDIFF(mi, @lookup_time, Oncall_end) > 0
AND NOT EXISTS (SELECT *
FROM mdr.dbo.mOnCallDelete del_oc
WHERE oc.SchedName = del_oc.SchedName
AND oc.FirstListing = del_oc.FirstListing
AND oc.StartOnCallDate = del_oc.StartOnCallDate
AND oc.StartOnCallTime = del_oc.StartOnCallTime
AND oc.Duration = del_oc.Duration)
SET @count = (SELECT count(*) FROM @temptable)
IF (@count > 0)
BEGIN
SELECT * FROM @temptable
ORDER BY OnCall_start
END
ELSE
BEGIN
SELECT @no_oc_value,NULL,NULL,NULL,NULL,NULL,NULL
END
RETURN @count
END
and it's producing too many results for what I need it to do. I don't need the columns StartOnCallDate, StartOnCallTime, Duration. All I need is those results removed. Can someone please assist.
Thank you
April 15, 2011 at 2:33 pm
Change the
SELECT * FROM @temptable
ORDER BY OnCall_start
section to return the columns you need by explicitly naming the columns instead of *.
Furthermore, you should reduce the following statement to the number of columns returned by the query you just changed.
SELECT @no_oc_value,NULL,NULL,NULL,NULL,NULL,NULL
April 15, 2011 at 2:35 pm
Should be as simple as removing the columns you don't want. Or am I missing something?
It might have seemed more difficult because all kinds of shortcuts are being taken here. No column lists on the insert, select *'s, etc. Just removing it below and not removing it from the table variable would have thrown an error (which I'm guessing is what you were running into?)
CREATE PROCEDURE [dbo].[oncalladd3]
@subschedule varchar(50),
@lookup_time datetime = NULL,
@no_oc_value varchar(50) = 'No o/c listed'
AS
DECLARE @temptable TABLE (
Schedname nvarchar(50),
FirstListing nvarchar(50),
Oncall_Start datetime,
Oncall_end datetime --,
-- StartOnCallDate int,
-- StartOnCallTime int,
-- Duration decimal(18,0)
)
DECLARE @count int
BEGIN
IF (@lookup_time IS NULL)
SET @lookup_time = CURRENT_TIMESTAMP
INSERT into @temptable SELECT *
FROM (SELECT Schedname,
FirstListing,
CONVERT(datetime, DATEADD(mi, StartOnCallTime, CONVERT(datetime, StartOnCallDate - 1))) AS Oncall_Start,
CONVERT(datetime, DATEADD(mi, StartOnCallTime + Duration, CONVERT(datetime, StartOnCallDate - 1))) AS OnCall_end --,
-- StartOnCallDate,
-- StartOnCallTime,
-- Duration
FROM mdr.dbo.mOnCallAdd
WHERE SchedName = @subschedule) Oc
WHERE DATEDIFF(mi, OnCall_start, @lookup_time) >= 0
AND DATEDIFF(mi, @lookup_time, Oncall_end) > 0
AND NOT EXISTS (SELECT *
FROM mdr.dbo.mOnCallDelete del_oc
WHERE oc.SchedName = del_oc.SchedName
AND oc.FirstListing = del_oc.FirstListing
AND oc.StartOnCallDate = del_oc.StartOnCallDate
AND oc.StartOnCallTime = del_oc.StartOnCallTime
AND oc.Duration = del_oc.Duration)
SET @count = (SELECT count(*) FROM @temptable)
IF (@count > 0)
BEGIN
SELECT * FROM @temptable
ORDER BY OnCall_start
END
ELSE
BEGIN
SELECT @no_oc_value,NULL,NULL,NULL,NULL,NULL,NULL
END
RETURN @count
END
April 15, 2011 at 2:35 pm
I suggest changing this:
SET @count = (SELECT count(*) FROM @temptable)
to
SET @count = @@ROWCOUNT
That way you aren't reading the table variable for no reason.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 18, 2011 at 8:59 am
Seth,
I changed my procedure to reflect your edits and still get an error:
CREATE PROCEDURE [dbo].[getoncall2]
@subschedule varchar(50),
@lookup_time datetime = NULL,
@no_oc_value varchar(50) = 'No o/c listed'
AS
DECLARE @temptable TABLE (
Schedname nvarchar(50),
FirstListing nvarchar(50),
Oncall_Start datetime,
Oncall_end datetime
-- StartOnCallDate int,
-- StartOnCallTime int,
-- Duration decimal(18,0)
)
DECLARE @count int
BEGIN
IF (@lookup_time IS NULL)
SET @lookup_time = CURRENT_TIMESTAMP
INSERT into @temptable SELECT *
FROM (SELECT Schedname,
FirstListing,
CONVERT(datetime, DATEADD(mi, StartOnCallTime, CONVERT(datetime, StartOnCallDate - 1))) AS Oncall_Start,
CONVERT(datetime, DATEADD(mi, StartOnCallTime + Duration, CONVERT(datetime, StartOnCallDate - 1))) AS OnCall_end
-- StartOnCallDate,
-- StartOnCallTime,
-- Duration
FROM mdr.dbo.mOnCallAdd
WHERE SchedName = @subschedule) Oc
WHERE DATEDIFF(mi, OnCall_start, @lookup_time) >= 0
AND DATEDIFF(mi, @lookup_time, Oncall_end) > 0
AND NOT EXISTS (SELECT *
FROM mdr.dbo.mOnCallDelete del_oc
WHERE oc.SchedName = del_oc.SchedName
AND oc.FirstListing = del_oc.FirstListing
AND oc.StartOnCallDate = del_oc.StartOnCallDate
AND oc.StartOnCallTime = del_oc.StartOnCallTime
AND oc.Duration = del_oc.Duration)
SET @count = @@ROWCOUNT
IF (@count > 0)
BEGIN
SELECT * FROM @temptable
ORDER BY OnCall_start
END
ELSE
BEGIN
SELECT @no_oc_value,NULL,NULL,NULL,NULL,NULL,NULL
END
RETURN @count
END
GO
Error 207, Invalid column name 'StartOncallDate, StartoncallTime and Duration.
April 18, 2011 at 9:55 am
You are referencing those columns in the where clause of the not exists subquery and you have removed them from the derived table in your from clause. If you are going to reference them outside the derived table you have to include them in the derived table. The derived table is this section:
(
SELECT
Schedname,
FirstListing,
CONVERT(DATETIME, DATEADD(mi, StartOnCallTime,
CONVERT(DATETIME, StartOnCallDate - 1))) AS Oncall_Start,
CONVERT(DATETIME, DATEADD(mi, StartOnCallTime + Duration,
CONVERT(DATETIME, StartOnCallDate - 1))) AS OnCall_end
-- StartOnCallDate,
-- StartOnCallTime,
-- Duration
FROM
mdr.dbo.mOnCallAdd
WHERE
SchedName = @subschedule
)
In essence you have to uncomment those columns for the current query to work.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 18, 2011 at 12:02 pm
I've made further edits to this query and now I'm getting "No o/c listed" as a result when I know that there should be several listings for this time period. Here is how the sp is now edited:
CREATE PROCEDURE [dbo].[getoncall2]
@subschedule varchar(50),
@lookup_time datetime = NULL,
@no_oc_value varchar(50) = 'No o/c listed'
AS
DECLARE @temptable TABLE (
Schedname nvarchar(50),
FirstListing nvarchar(50),
Oncall_Start datetime,
Oncall_end datetime
--StartOnCallDate int,
--StartOnCallTime int,
--Duration decimal(18,0)
)
DECLARE @count int
BEGIN
IF (@lookup_time IS NULL)
SET @lookup_time = CURRENT_TIMESTAMP
INSERT into @temptable SELECT *
FROM (SELECT Schedname,
FirstListing,
CONVERT(datetime, DATEADD(mi, StartOnCallTime, CONVERT(datetime, StartOnCallDate - 1))) AS Oncall_Start,
CONVERT(datetime, DATEADD(mi, StartOnCallTime + Duration, CONVERT(datetime, StartOnCallDate - 1))) AS OnCall_end
--StartOnCallDate,
--StartOnCallTime,
--Duration
FROM mdr.dbo.mOnCallAdd
WHERE SchedName = @subschedule) Oc
WHERE DATEDIFF(mi, OnCall_start, @lookup_time) >= 0
AND DATEDIFF(mi, @lookup_time, Oncall_end) > 0
AND NOT EXISTS (SELECT *
FROM mdr.dbo.mOnCallDelete del_oc
WHERE oc.SchedName = del_oc.SchedName
AND oc.FirstListing = del_oc.FirstListing)
-- AND oc.StartOnCallDate = del_oc.StartOnCallDate
-- AND oc.StartOnCallTime = del_oc.StartOnCallTime
-- AND oc.Duration = del_oc.Duration)
SET @count = @@ROWCOUNT
IF (@count > 0)
BEGIN
SELECT * FROM @temptable
ORDER BY OnCall_start
END
ELSE
BEGIN
SELECT @no_oc_value,NULL,NULL,NULL,NULL,NULL,NULL
END
RETURN @count
End
GO
April 18, 2011 at 12:55 pm
doug 40899 (4/18/2011)
I've made further edits to this query and now I'm getting "No o/c listed" as a result when I know that there should be several listings for this time period. Here is how the sp is now edited:
CREATE PROCEDURE [dbo].[getoncall2]
@subschedule varchar(50),
@lookup_time datetime = NULL,
@no_oc_value varchar(50) = 'No o/c listed'
AS
DECLARE @temptable TABLE (
Schedname nvarchar(50),
FirstListing nvarchar(50),
Oncall_Start datetime,
Oncall_end datetime
--StartOnCallDate int,
--StartOnCallTime int,
--Duration decimal(18,0)
)
DECLARE @count int
BEGIN
IF (@lookup_time IS NULL)
SET @lookup_time = CURRENT_TIMESTAMP
INSERT into @temptable SELECT *
FROM (SELECT Schedname,
FirstListing,
CONVERT(datetime, DATEADD(mi, StartOnCallTime, CONVERT(datetime, StartOnCallDate - 1))) AS Oncall_Start,
CONVERT(datetime, DATEADD(mi, StartOnCallTime + Duration, CONVERT(datetime, StartOnCallDate - 1))) AS OnCall_end
--StartOnCallDate,
--StartOnCallTime,
--Duration
FROM mdr.dbo.mOnCallAdd
WHERE SchedName = @subschedule) Oc
WHERE DATEDIFF(mi, OnCall_start, @lookup_time) >= 0
AND DATEDIFF(mi, @lookup_time, Oncall_end) > 0
AND NOT EXISTS (SELECT *
FROM mdr.dbo.mOnCallDelete del_oc
WHERE oc.SchedName = del_oc.SchedName
AND oc.FirstListing = del_oc.FirstListing)
-- AND oc.StartOnCallDate = del_oc.StartOnCallDate
-- AND oc.StartOnCallTime = del_oc.StartOnCallTime
-- AND oc.Duration = del_oc.Duration)
SET @count = @@ROWCOUNT
IF (@count > 0)
BEGIN
SELECT * FROM @temptable
ORDER BY OnCall_start
END
ELSE
BEGIN
SELECT @no_oc_value,NULL,NULL,NULL,NULL,NULL,NULL
END
RETURN @count
End
GO
That's because you commented out the correlated columns
-- AND oc.StartOnCallDate = del_oc.StartOnCallDate
-- AND oc.StartOnCallTime = del_oc.StartOnCallTime
-- AND oc.Duration = del_oc.Duration)
rather than the output columns as suggested by Jack. If you find EXISTS confusing, try it with LEFT JOIN instead:
CREATE PROCEDURE [dbo].[oncalladd3]
@subschedule varchar(50),
@lookup_time datetime = NULL,
@no_oc_value varchar(50) = 'No o/c listed'
AS
BEGIN
IF @lookup_time IS NULL
SET @lookup_time = CURRENT_TIMESTAMP
SELECT
d.Schedname,
d.FirstListing,
d.Oncall_Start,
d.OnCall_end,
d.StartOnCallDate,
d.StartOnCallTime,
d.Duration
INTO #temptable
FROM (
SELECT
oc.Schedname,
oc.FirstListing,
CONVERT(datetime, DATEADD(mi, oc.StartOnCallTime, CONVERT(datetime, oc.StartOnCallDate - 1))) AS Oncall_Start,
CONVERT(datetime, DATEADD(mi, oc.StartOnCallTime + oc.Duration, CONVERT(datetime, oc.StartOnCallDate - 1))) AS OnCall_end,
oc.StartOnCallDate,
oc.StartOnCallTime,
oc.Duration
FROM mdr.dbo.mOnCallAdd oc
----
LEFT JOIN mdr.dbo.mOnCallDelete del_oc
ON oc.SchedName = del_oc.SchedName
AND oc.FirstListing = del_oc.FirstListing
AND oc.StartOnCallDate = del_oc.StartOnCallDate
AND oc.StartOnCallTime = del_oc.StartOnCallTime
AND oc.Duration = del_oc.Duration)
----
WHERE oc.SchedName = @subschedule
AND del_oc.SchedName IS NULL
) d
WHERE DATEDIFF(mi, d.OnCall_start, @lookup_time) >= 0
AND DATEDIFF(mi, @lookup_time, d.Oncall_end) > 0
-- It's usually far easier to do this bit client-side and skip the temp table (or variable) altogether.
-- comment out unwanted columns
SET @count = @@ROWCOUNT
IF @count = 0
SELECT
Schedname = @no_oc_value,
FirstListing = CAST(NULL AS BIT), -- datatypes guessed
Oncall_Start = CAST(NULL AS DATETIME),-- datatypes guessed
OnCall_end = CAST(NULL AS DATETIME),-- datatypes guessed
StartOnCallDate = CAST(NULL AS DATETIME),-- datatypes guessed
StartOnCallTime = CAST(NULL AS DATETIME),-- datatypes guessed
Duration = CAST(NULL AS INT)-- datatypes guessed
ELSE
SELECT
Schedname,
FirstListing,
Oncall_Start,
OnCall_end,
StartOnCallDate,
StartOnCallTime,
Duration
FROM #temptable
ORDER BY OnCall_start
RETURN @count
END
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 19, 2011 at 8:47 am
Yeah, my bad on that one, read this too fast and missed the way the exists referred to the derived table. They can be removed from the base level though.
(Where you have Select * right after the insert, use a column list instead without those in it).
ie. Replace that Select * with:SELECT Schedname, FirstListing, Oncall_Start, OnCall_end
All of the other methods already mentioned of doing it will also work handily.
As Lutz mentioned in the beginning, whichever method you go with, you probably also want to fix your SELECT @no_oc_value,NULL,NULL,NULL... statement at the bottom to match your new number of returned columns.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply