April 27, 2010 at 7:12 am
Hi, I have a stored procedure that currenly has one parameter in it. I want to add a @StartDate, and an @EndDate parameter to it, however I am unsure how to add the parameter to the query and define the where clause.
Here is the StoredPocedure;
USE [NMS_RT]
GO
/****** Object: StoredProcedure [dbo].[Trending] Script Date: 04/27/2010 13:02:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Trending]
@WaySide VARCHAR(50),
@StartDate Date,
@EndDate Date
AS
BEGIN
SET NOCOUNT ON
SELECT ( Grp.name + ',' + Grp.state ) AS Wayside,
( SELECT 'G'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 3, 3) + '/'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 6, 3)
) AS 'Group ID',
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + ' ( ' + [base1] + ')' AS 'Primary',
[SSI1],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + ')' AS 'Secondary',
[SSI2],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base3])
)
) + ' ( ' + [base3] + ')' AS 'Tertiary',
[SSI3],
CAST([Date_Time] AS DATE) AS Date
FROM [NMS_RT].[dbo].[RT_Group_Coverage] AS Cov
INNER JOIN [nms_cfg].[dbo].[ATCS_Group] AS Grp
ON Grp.Group_Address = Cov.[WEA]
WHERE Grp.name = @WaySide
ORDER BY WEA, Date_Time
END
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
April 27, 2010 at 7:34 am
brian.cook (4/27/2010)
Hi, I have a stored procedure that currenly has one parameter in it. I want to add a @StartDate, and an @EndDate parameter to it, however I am unsure how to add the parameter to the query and define the where clause.Here is the StoredPocedure;
USE [NMS_RT]
GO
/****** Object: StoredProcedure [dbo].[Trending] Script Date: 04/27/2010 13:02:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Trending]
@WaySide VARCHAR(50),
@StartDate Date,
@EndDate Date
AS
BEGIN
SET NOCOUNT ON
SELECT ( Grp.name + ',' + Grp.state ) AS Wayside,
( SELECT 'G'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 3, 3) + '/'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 6, 3)
) AS 'Group ID',
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + ' ( ' + [base1] + ')' AS 'Primary',
[SSI1],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + ')' AS 'Secondary',
[SSI2],
( SELECT [Name] + ',' + [State]
FROM [NMS_CFG].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [NMS_CFG].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base3])
)
) + ' ( ' + [base3] + ')' AS 'Tertiary',
[SSI3],
CAST([Date_Time] AS DATE) AS Date
FROM [NMS_RT].[dbo].[RT_Group_Coverage] AS Cov
INNER JOIN [nms_cfg].[dbo].[ATCS_Group] AS Grp
ON Grp.Group_Address = Cov.[WEA]
WHERE Grp.name = @WaySide
AND Date_Time >= @StartDate
AND Date_Time < DateAdd(day, 1, @EndDate)
ORDER BY WEA, Date_Time
END
I added to the end of your query above what I think you're looking for.
Note that I increment the @EndDate by 1, and look for dates less than that value. Since the Date_Time field is named the way it is, I'm assuming that it has both a date and time value. With @EndDate being a Date data type, it will be converted to a datetime with a time of 00:00:00.000 to be compatible with the Date_Time field. So, when you pass in a value to the EndDate parameter, if you were to use between then you would only get the values in Date_Time that match up to a time of 00:00:00.000. Any other values for that day (00:00:01) would NOT be included. So, assuming that you want all values for the EndDate passed in, I add a single day to it, and look for the Date_Time value being less than the value.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 27, 2010 at 7:55 am
Thank you. I know it had to be something simple!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply