Date Parameters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Parameters

Author

Topic

gualm
Starting Member

11 Posts

Posted - 2008-03-20 : 15:34:05

See Code below -->> I need help with date parameters. I was told to pass in parameters for the meters date >>see underlined<<. I originally had specific years there but they do not want the years to be hard coded into the script. So I tried to replace with a parameter but need help. I have placed the years next to the parameters that were originally in the 'where' clause where the underline is.

SET NOCOUNT ON

DECLARE
@mtr_ageA datetime, --<=1/1/94
@mtr_ageB datetime, --<=1/1/07
@mtr_ageC datetime; --<=1/1/07

set @mtr_ageA = (getdate()); (this should be a year not current date)
set @mtr_ageB = (getdate());
set @mtr_ageC = (getdate());

create table #aged_a
(
Entno varchar (4),
Customer_Name varchar(100),
Loc_Nbr varchar (10),
--Contract_Nbr varchar (10),
Ref_No varchar(20),
Meter_No varchar(20),
Service_Area varchar(10),
Pol_Sub varchar(10),
Kind_Code varchar(10),
Mtr_Type varchar(10), --group a,b,c
Set_Date datetime,
Mtr_Status varchar (1),
Meter_Loc varchar(20),
Address varchar(100),
City varchar(30),
State varchar(2),
Zip varchar(10)
)

Insert Into #aged_a
(Entno, Loc_Nbr, Meter_No, Service_Area, Pol_Sub, Kind_Code, Mtr_Type, Set_Date, Mtr_Status,
Meter_Loc, Address, City, State, Zip)

select
locn_entno,locn_locno, mplm_metno, locn_serv_area, locn_polsub_id, winv_subclass, winv_inv_type, mplm_set_date, mplm_meter_status,
ISNULL(mprl_rloc_desc,''), ISNULL(locn_addr, '') + ' ' + ISNULL(locn_addr2, ''), locn_city, locn_state,
locn_postal
FROM rs3_prod.dbo.MP_Location_Meter (NOLOCK)
INNER JOIN rs3_prod.dbo.WO_Inventory (NOLOCK)
ON mplm_entno = winv_entno
and mplm_metno = winv_invno
INNER JOIN rs3_prod.dbo.RS_Location (NOLOCK)
ON winv_entno = locn_entno
and winv_locno = locn_locno
Left outer join rs3_prod.dbo.MP_Read_Location (NOLOCK)
on mprl_rloc_code = mplm_rloc_code
WHERE (winv_inv_type = 'GROUP A' and mplm_set_date <=@mtr_ageA )
or (winv_inv_type = 'Group B'and mplm_set_date <=@mtr_ageB )
or (winv_inv_type ='Group C' and mplm_set_date <=@mtr_ageC )

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-20 : 16:11:42

If "mplm_set_date" is a date time you can use less than Jan-1- of the next year. For example:

DECLARE 
@mtr_ageA datetime, --<=1/1/94
@mtr_ageB datetime, --<=1/1/07
@mtr_ageC datetime; --<=1/1/07

set @mtr_ageA = DATEADD(YEAR, YEAR(GETDATE()) - 1899, 0)
set @mtr_ageB = DATEADD(YEAR, YEAR(GETDATE()) - 1899, 0)
set @mtr_ageC = DATEADD(YEAR, YEAR(GETDATE()) - 1899, 0)



create table #aged_a
( 
Entno varchar (4),
Customer_Name varchar(100),
Loc_Nbr varchar (10),
--Contract_Nbr varchar (10), 
Ref_No varchar(20),
Meter_No varchar(20),
Service_Area varchar(10),
Pol_Sub varchar(10),
Kind_Code varchar(10),
Mtr_Type varchar(10), --group a,b,c 
Set_Date datetime,
Mtr_Status varchar (1), 
Meter_Loc varchar(20),
Address varchar(100),
City varchar(30),
State varchar(2),
Zip varchar(10)
)


Insert Into #aged_a
(Entno, Loc_Nbr, Meter_No, Service_Area, Pol_Sub, Kind_Code, Mtr_Type, Set_Date, Mtr_Status,
Meter_Loc, Address, City, State, Zip) 

select 
locn_entno,locn_locno, mplm_metno, locn_serv_area, locn_polsub_id, winv_subclass, winv_inv_type, mplm_set_date, mplm_meter_status,
ISNULL(mprl_rloc_desc,''), ISNULL(locn_addr, '') + ' ' + ISNULL(locn_addr2, ''), locn_city, locn_state, 
locn_postal
FROM rs3_prod.dbo.MP_Location_Meter (NOLOCK) 
INNER JOIN rs3_prod.dbo.WO_Inventory (NOLOCK)
ON mplm_entno = winv_entno
and mplm_metno = winv_invno
INNER JOIN rs3_prod.dbo.RS_Location (NOLOCK)
ON winv_entno = locn_entno
and winv_locno = locn_locno
Left outer join rs3_prod.dbo.MP_Read_Location (NOLOCK)
on mprl_rloc_code = mplm_rloc_code
WHERE (winv_inv_type = 'GROUP A' and mplm_set_date < @mtr_ageA )
or (winv_inv_type = 'Group B'and mplm_set_date < @mtr_ageB ) 
or (winv_inv_type ='Group C' and mplm_set_date < @mtr_ageC )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources