June 11, 2004 at 11:56 pm
I have a dts package that attaches the results from a stored procedure to an excel sheet and emails it to users. In the store procedure I will only like to capature the records for an up result_date an email the results in the attached spreadsheet.
Here is the proc
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure bcc_sp_inspection_result_codes
as
select
result_date,
result_code,
permit_nbr,
insp_code,
insp_class,
result_insp,
result_date -1 as report_date
from permit_insp
where insp_code <'800'
and result_code not
in('70','74','75','76','77','80','81','82','83','84','90','91','92','93'
,'99')
--Here I will like to say "give me only records with the result_date is one day before the result_date
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Any help or advice will be greatly appreciated
June 14, 2004 at 8:00 am
This was removed by the editor as SPAM
June 14, 2004 at 8:20 am
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure bcc_sp_inspection_result_codes
as
select
result_date,
result_code,
permit_nbr,
insp_code,
insp_class,
result_insp,
result_date -1 as report_date
from permit_insp
where insp_code <'800'
and result_code not
in('70','74','75','76','77','80','81','82','83','84','90','91','92','93'
,'99')
where cast(convert(varchar(10), result_date, 101) as datetime) = cast(convert(varchar(10), dateadd(d, -1, getdate()), 101) as datetime)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
June 14, 2004 at 8:26 am
Or another alternative is
where dateadd(d,datediff(d,0,result_date),0) = dateadd(d,datediff(d,0,getdate()),0)
June 15, 2004 at 2:50 pm
slight correction in Nicoloas Kain's answer...
it should be
where cast(convert(varchar(10), result_date, 101) as datetime) = cast(convert(varchar(10), dateadd(d, -1, result_date), 101) as datetime)
not
where cast(convert(varchar(10), result_date, 101) as datetime) = cast(convert(varchar(10), dateadd(d, -1, getdate()), 101) as datetime)
because Jaime is asking for
"....only records with the result_date is one day before the result_date" ...getdate() should not come
Cheers!!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply