Parameters
-
I would like to give my user the opportunity to use a filter that would allow them to put in a machine number range 5 - 10 and find machines with unused numbers 'within' that number range. My thought is a parameter. But I am not sure how to write it. Note the report already has a long list of parameters.
So I assume I add it to that list an set it to something. Can anyone help? Here's the code thus far w/ out the above mentioned param:
DECLARE @NameGUID uniqueidentifier
DECLARE @DivisionGUID uniqueidentifier
DECLARE @Name varchar(50)
DECLARE @RegionName varchar(50)
DECLARE @CCNumber int
DECLARE @CCName varchar(50)
SELECT @RegionGUID = RegionGUID , CostCenterName = ISNULL(dbo.CC.Name1, '') + ' ' + ISNULL(dbo.CC.Name2, ''), @CCNumber = CCID FROM dbo.CC WHERE CCID = @CCNumber
SELECT @DivisionGUID = DivisionGUID, @RegionName = @RegionName FROM dbo.R WHERE RegionGUID = @RegionGUID
SELECT @DivisionName = [Name] FROM dbo.D WHERE DivisionGUID = @DivisionGUID
DECLARE @v85nvarchar(10)
DECLARE @FromRangeint
DECLARE @ToRangeint
DECLARE @Begin int
DECLARE @End int
SET @Begin = @FromRange
SET @End= @ToRange
------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #UnusedNumberTable (UnusedNumber int)
WHILE (@begin <= @End)
BEGIN
SET @v85 = @begin
if SUBSTRING(@v85,1,2) <> 85
INSERT INTO #UnusedNumberTable(UnusedNumber)Values(@begin)
SET @Begin = @Begin + 1
END
--select * from #UnusedNumberTable
-------------------------------------------------------------------------------------------------------------------------
EXEC
('
SELECT
UnusedNumber--, ' + @ls_SingleQuote + @DivisionName + @ls_SingleQuote + ' , ' + @ls_SingleQuote + @RegionName + @ls_SingleQuote + ' , ' + @ls_SingleQuote + @CostCenterName + @ls_SingleQuote + ' , ' + @ls_SingleQuote + @CostCenterNumber + @ls_SingleQuote + '
FROM
#UnusedNumberTable
WHERE
UnusedNumber
NOT IN
(
SELECT DISTINCT
dbo.Machine.Number
FROM
dbo.Machine
INNER JOIN dbo.Cost_Center ON dbo.Machine.CostCenterID= dbo.CC.CostCenterID
INNER JOIN dbo.Region ON dbo.CC.RegionGUID = dbo.Region.RegionGUID
INNER JOIN dbo.Division ON dbo.Region.DivisionGUID = dbo.Division.DivisionGUID
WHERE
1 = 1
'+ @ls_UserFilter + '
)
')
-
Not sure I fully understand what you want to achieve, but how about this.
Create a temp table with an identity field that has a seed value of the first number in the range. Then insert the number of records covered by the range. Then a left join to the temp table should give you the missing numbers.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply