SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Perform Insert taking care Local-Solar days		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ciupaz
Posting Yak Master

Italy
176 Posts
Posted - 11/08/2012 :  05:44:09  Show Profile  Reply with Quote
Hello all,
I have a simple query that make an Insert of one record in one table, in this way:


Declare @MyDate datetime
set @MyDate = '2010-06-06 22:00:00.000'

INSERT INTO T_TaskQueue (NameTask, ReferenceDate, IdObject)
VALUES ('MyTask1', @MyDate, 0)

Now I should make this script able to accept a StartDate and an EndDate
and make these Insert for every day between this dates.
But I have to insert hour=22 for days "legal" (from last sunday in March to last sunday in October)
and hour=23 for the other days.

How can I make this?

Thanks in advance.

Luigi

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/08/2012 :  06:13:07  Show Profile  Reply with Quote
Probably the simplest and most straightforward way is to create a calendar table and join with that table. Create the calendar table with two columns, one for the date and the other for the hours - like shown below
CREATE TABLE #Calendar(dt DATETIME, hourstoInsert INT);

Now populate this table for the date ranges that are of interest to you - for example, to do this for all of 2012, you would do the following:
;WITH N(n) AS
(
    SELECT 1 UNION ALL SELECT n + 1
                       FROM   N
                       WHERE  n < 366
)
INSERT INTO #Calendar
SELECT DATEADD(dd, n -1, '20120101'), 23
FROM   N
       OPTION(MAXRECURSION 0);
I inserted 23 for all hours, you can run an update to change that to 22 for the dates of your interest.

Once you have this table, insert into your destination table by selecting from this table
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
176 Posts
Posted - 11/08/2012 :  08:13:47  Show Profile  Reply with Quote
Yes I have a table T_Date, with fields DateTime and DateTimeUTC.
So how can I change my query?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/08/2012 :  08:39:58  Show Profile  Reply with Quote
Would be something like this, assuming the date column in T_Date is "DateColumn"
Declare @MyStartDate DATETIME, @MyEndDate DATETIME;
set @MyStartDate = '20100626';
SET @MyEndDate = '2010731';

INSERT INTO T_TaskQueue (NameTask, ReferenceDate, IdObject) 
SELECT
	'MyTask1',
	Datecolumn,
	0
FROM
	T_Date
WHERE
	DateColumn BETWEEN  @MyStartDate AND @MyEndDate;
Go to Top of Page

Ciupaz
Posting Yak Master

Italy
176 Posts
Posted - 11/08/2012 :  08:48:22  Show Profile  Reply with Quote
Perfect, thank you very much Sunita.

Luigi
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000