Calculate Trigger Date

Question

Post reply

Calculate Trigger Date

2Tall

SSChampion

Points: 14644
More actions
June 19, 2009 at 6:42 am

#131268

I have been asked to create a Trigger to email suppliers 7 days prior to order due date (I assume an SSIS package would achieve this).
First step is to get the query right. I have the following query:
DECLARE @TriggerDate smalldatetime
SET @TriggerDate = Getdate()-7
SELECT s.SupplierId,s.email, po.PurchaseOrderId, poi.DueDate
FROM PurchaseOrderItems AS poi
INNER JOIN PurchaseOrders AS po ON poi.PurchaseOrder = po.PurchaseOrder
INNER JOIN Suppliers AS S ON po.Supplier = s.Supplier
WHERE poi.DueDate = @TriggerDate
No matter what I set 'SET @TriggerDate = Getdate()-7 ' to the query returns no results so I can only assume my query is incorrect.
Can anyone spot my mistake?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Does due date contain a time ? You cant separate out the time portion, try between 00:00 and 23:59.

Try this link for more info http://www.karaszi.com/SQLServer/info_datetime.asp

Clear Sky SQL
My Blog[/url]

ss-457805 SSCertifiable Points: 5872 More actions · Answer 2

What datatype is duedate?

blog: http://sarveshsingh.com Twitter: @sarveshsing

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Change: WHERE poi.DueDate = @TriggerDate

To: WHERE poi.DueDate = dateadd(dd, datediff(dd,0,@TriggerDate),0)

Does that help?

2Tall SSChampion Points: 14644 More actions · Answer 4

Sarvesh the data type was smalldatetime.

Lynn that worked a treat many thanks.

Thanks to all for taking time to review my post, as always very much appreciated.......now on to SSIS!

Kind Regards,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper