| Author |
Topic |
|
VeselaApo
Posting Yak Master
114 Posts |
 Posted - 2010-12-01 : 13:58:10
|
| Hi,I create a new table and declare its 2 columns and variables. The first columns of the table will be populated with dates. The second column is with money. Next, I would like to insert values in the table and I use the INSERT INTO statement. I insert the dates in the first column. In the second column I would like to insert one value on certain dates and not in the entire column. How and where do I specify on which dates I want to insert the value? The desired result should look like that:Date Fee01-01-2011 , 40001-02-2011, 001-03-2011, 0 01-04-2011, 001-05-2011, 400Currently my code looks like that:DECLARE @gencalendar TABLE (cal_date DATETIME PRIMARY KEY, Fees float)DECLARE @beginning_date SMALLDATETIMEDECLARE @end_date SMALLDATETIMEDECLARE @Fee intSET @beginning_date = '01-01-2011'set @end_date = @beginning_date WHILE @end_date <= '01-01-2013'BeginINSERT INTO @gencalendar(cal_date, Fees )VALUES(@end_date, @fees)SET @end_date = dateadd(DD, 1, @end_date)set @fee = 400Endselect *into #All_Datesfrom @gencalendar |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-12-01 : 14:00:29
|
| how do you determine which all dates you want to insert value?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
VeselaApo
Posting Yak Master
114 Posts |
Posted - 2010-12-01 : 14:10:53
|
| At this stage I would like to insert the FEE only on the beginning date of the calendar. If I do the if statement, it will also be valid for the insert dates part so I dont think this will work.. I am not sure how to use the select statement - can you please elaborate on that? Should it be after set @fee = ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-12-01 : 14:16:19
|
you can simply do likeINSERT INTO YourTableSELECT Date,CASE WHEN Date = @beginning_date THEN @fee ELSE 0 ENDFROM dbo.CalendarTable(@beginning_date,@end_date,0)see the function definition herehttp://visakhm.blogspot.com/2010/02/generating-calendar-table.htmlalso set correct values for beginning end date variables and fee variable ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
VeselaApo
Posting Yak Master
114 Posts |
Posted - 2010-12-01 : 14:22:26
|
| I dont have any CalendarTable database - thats why I am not sure that the select statement will work...any other ideas? Thanks for your input! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
VeselaApo
Posting Yak Master
114 Posts |
Posted - 2010-12-01 : 14:34:24
|
| So what you both are saying is that the only way I can insert values on specific dates in my table is through select statement from another table/database? I am trying to simplify my code as much as possible - is there another way?...Thanks much for your input! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
VeselaApo
Posting Yak Master
114 Posts |
Posted - 2010-12-01 : 15:16:53
|
| well the problem is that I am creating my table within a procedure and i define the dates in that procedure. It is not possible to use an external calendar at this stage.. |
|
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-12-01 : 15:21:25
|
| What table are you creating? |
|
|
|
VeselaApo
Posting Yak Master
114 Posts |
Posted - 2010-12-01 : 15:50:46
|
| I am declaring a table in the beginning of my procedure. Then I insert values in that table. The first column of the table is dates so I specify begin and end date in the procedure parameters. |
|
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-12-01 : 15:58:12
|
| I don't think I understand what you want to do, so bear with me.You want to create a table with a begin date and an end date, and a fee value for each of those. The begin and end dates and the fee value are all provided externally.Then you want to update another table with the fee where the other table's date matches the begin date. Is it just begin date, end date, or both? Or is it another date altogether? |
|
|
|
VeselaApo
Posting Yak Master
114 Posts |
Posted - 2010-12-01 : 16:17:53
|
| "You want to create a table with a begin date and an end date, and a fee value for each of those. The begin and end dates and the fee value are all provided externally"correct up top here.Then I populate the table with all the dates from begin date to end date in the first column (see code above). What I want to do next is to update this same table with the fee value but only on certain dates (such as the begin date) not throughout the whole column 2. How do I do that? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-12-01 : 17:06:00
|
| Well, how do you know what those "certain dates" are? Is there another table elsewhere? How are you populating the other dates? |
|
|
|
|
Insert values in a new table with clauses