Changing DATES - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Changing DATES

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-04-03 : 11:26:29

I have a script that is run against our Database every year to extract data into a text file. I would Like to build a DTS package for this script and also automate it's schedule. However, In the query below, I always have to go in and manually change the date in the "where" clause to reflect the appropriate year. This defeats the purpose of putting it on an automated schedule.

WHERE property.id = prop_valuation.property_id
AND prop_valuation.tax_year = '2008'
AND property_char.tax_year = '2008'
AND property_char.tax_year = '2008'

Is there a way I can automate this so that I don't have to change it every single year? I'm curently using SQL Server 2005.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 11:35:35

you can pout placeholders for parameters and map them in parameters mapping tab of Execute SQL task

so above becomes
WHERE property.id = prop_valuation.property_id
AND prop_valuation.tax_year = ?
AND property_char.tax_year =?
AND property_char.tax_year = ?
and map your parameter to each of these placeholders

and to schedule it create a SQL server agent job to call this DTS

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-04-03 : 12:24:03

Hi,

Thanks for the reply. Are you able to explain the concept of this? For example, "property_char.tax_year" has a mixture of 2006, 2007, and 2008. Next year it will have 2009. How would it automatically know to choose all of 2009s' data next year?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-03 : 12:35:41

You need to define a parameter for your package. The parameter must get its value from some table of file. Then whereaver you want its value put ? as place holder and map the parameter position to your variable in parameter mapping tab of your task.

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-04-03 : 19:47:40

Is there a way to write this code without mapping parameters?

For example:

Select * from ABC

where date = "current year"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources