sql syntax

[Former Member]

i have a sp that inserts new rows if the data meets my specific criteria-(create or change date in @parameter)

see below

INSERT INTO item_hst_tbl

SELECT en_item_key, en_item_sdesc, en_item_desc,

en_item_orgin, en_item_trdnm, sa_user_key, en_item_wgt,

en_item_wuom, en_stats_key, en_item_pltqt, en_item_chgdt,

en_item_crtdt, en_item_cmnts,

CASE wHEN en_item_chgdt >= CONVERT(datetime,CONVERT(char(10),'07/01/2008',101))

and en_item_chgdt <= CONVERT(datetime,CONVERT(char(10),'07/31/2008',101)) AND en_stats_key='INACTIVE' THEN 'I'

WHEN en_item_crtdt >= CONVERT(datetime,CONVERT(char(10),'07/01/2008',101)) and en_item_crtdt <= CONVERT(datetime,CONVERT(char(10),'07/31/2008',101))THEN 'A'

WHEN en_item_chgdt >= CONVERT(datetime,CONVERT(char(10),'07/01/2008',101)) and en_item_chgdt <= CONVERT(datetime,CONVERT(char(10),'07/31/2008',101)) THEN 'U' END Mod_Type,

getdate() timestamp

FROM en_item_tbl

WHERE CONVERT(datetime,CONVERT(char(10),en_item_crtdt,101))>= CONVERT(datetime,CONVERT(char(10),'07/01/2008',101)) and CONVERT(datetime,CONVERT(char(10),en_item_crtdt,101))

<= CONVERT(datetime,CONVERT(char(10),'07/31/2008',101))

or CONVERT(datetime,CONVERT(char(10),en_item_chgdt,101))>= CONVERT(datetime,CONVERT(char(10),'07/01/2008',101))and CONVERT(datetime,CONVERT(char(10),en_item_chgdt,101))<= CONVERT(datetime,CONVERT(char(10),'07/31/2008',101))

this sp will be run monthly in a batch process. i dont want to have to modify the dates every month.

what i am trying to accomplish is to retrieve the maximum date from the item_hst_tbl (which has been populated from the prior month.)

then in my where statement i want to pull the records that are between the max date from the item_hst_tbl table and the getdate()

i dont know the syntax or placement of this.

i hope this makes sense. i tend to confuse myself sometimes.

[Former Member · Accepted solution]

Hi Sharon

Edit your procedure and follow these steps:

1. Declare a variable at the start of the procedure as:

Declare @Max_Date datetime

2. Then select the max date from the item_hst_tbl as:

Set @Max_Date = (Select max(<Your Date Field>) from item_hst_tbl)

3. Now your procedure code

3. Use the variable @Max_Date and the getdate() function in your where clause to set the dates as you want.

Hope this helps.

Regards

Nikhil

[Former Member · Accepted solution]

thats it!!!

thank you very much