TSQL for SQL Agent - 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

TSQL for SQL Agent

Author

Topic

zhtway1
Starting Member

10 Posts

Posted - 2010-08-21 : 02:47:35

Hi,
I am very new and never write a TSQL code before. I started learning from yesterday.
I have those tables.
Holiday
- HolidayDate
All the Holiday dates in a year will be populated.
Routes
- RouteID(Identity), StaffID, SatDayDelivery(bit)
Pickup
- PickUpID(Identity), RouteID, StaffID, PickupDate

What I want to do is ---
1) Copy all records from Routes to Pickup in every weekdays.
2) And copy the (SatDayDelivery = True) records on Friday or the day before Holiday.

After learning I know how to copy the data but I don't know how to do complex things. Still learning..

Any help will be greatly appreciated.

rgds,
zhtway

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 04:19:40

Can we see your used query till now?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zhtway1
Starting Member

10 Posts

Posted - 2010-08-21 : 04:35:10

Hi,
I just copy the data to pickup table..I don't know how to do.
use MobilePOD
Insert into Pickup (StaffID, RouteID, PickupDate)
select StaffID, RouteID, (getdate()) from Routes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 13:09:28

so you want transfer to happen everyday?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zhtway1
Starting Member

10 Posts

Posted - 2010-08-21 : 14:21:32

Hi,

Thanks for your replies.

Let me explain to you what I want to do.

Routes is the Master table for all routes.
Pickup is the table for a day where all the staffs have to pickup for their routes.
And All the data in the Pickup will be copied to PickupHistory when all the packages are delivered (may be in the same day or the next days).
Saturday Delivery has to be prepared on Friday. If the Holiday is Friday, Saturday Delivery will has to prepared on Thursday.
As holiday, records will not be copied to Pickup.

That TSQL will be in the SQL Agent Job and will schedule for weekdays.
Basically my logic is like that..

if today <> holiday then
copy all the records from routes to pickup
if today = friday or today= holiday-1 then
copy all the SatDelivery records to pickup
endif
endif

Actually, I can write code in VB.net and run as job schedule or windows service, yet I am not expert.
But I know there is something I can do with TSQL and I want to learn TSQL a bit.

Thanks
zhtway

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 14:27:39

[code]
INSERT INTO PickUp (RouteID, StaffID, PickupDate)
SELECT RouteID, StaffID, GETDATE()
FROM Routes t
WHERE SatDayDelivery = 1
OR (DATENAME(dw,GETDATE()) <> 'Friday'
AND DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) NOT IN (SELECT Date IN HolidayDate))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources