Update/insert table A based on table A and B

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 2005 Forums

Transact-SQL (2005)

Update/insert table A based on table A and B

Author

Topic

briankind
Starting Member

17 Posts

Posted - 2009-01-21 : 17:24:12

Update
Update/insert table A based on table A and B

3 tables
A and B and C

table A has the Date_category field in text/string
Date_category

table B has the Coll_rec_date in smalldatetime

table C has the Date_category_OLD in text/string

I want to populate table A based on information on table,B and C

the first thing is
if Coll_rec_date date range of table B is from July 1 2008 to june 30 2009 then insert 09
if Coll_rec_date date range of table B is from July 1 2007 to june 30 2008
insert 08
if Coll_rec_date date range of table B is from July 1 2006 to june 30 2007
insert 07
if Coll_rec_date date range of table B is from July 1 2005 to june 30 2006
insert 06

and if there are any nulls in Coll_rec_date date then populate it with with the value of table C has the Date_category_OLD ( if it is not null).

can you help me with the code thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-21 : 17:27:50

See http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.63"
N 56°04'39.26"

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-22 : 00:06:34

Hi Dear,

Try this below Query:

Query
INSERT INTO A(Date_category)
SELECT CASE
WHEN isnull(B.Coll_rec_date,C.Coll_rec_date) BETWEEN 'July 1 2008' AND 'june 30 2009'
THEN '09'
WHEN isnull(B.Coll_rec_date,C.Coll_rec_date) BETWEEN 'July 1 2007' AND 'june 30 2008'
THEN '08'
WHEN isnull(B.Coll_rec_date,C.Coll_rec_date) BETWEEN 'July 1 2006' AND 'june 30 2007'
THEN '07'
WHEN isnull(B.Coll_rec_date,C.Coll_rec_date) BETWEEN 'July 1 2005' AND 'june 30 2006'
THEN '06'
ELSE '' END
FROM B INNER JOIN C ON B.ID = C.ID

Here in the above query i used an ID field which helps you to make join between B and C. I hope the ID column will be there. If not use row_number function to generate and use it. Here i dont have SQL 2005 to use that..

I hope it will help you a bit.

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

briankind
Starting Member

17 Posts

Posted - 2009-01-22 : 11:00:43

What happen to
Table C has the Date_category_OLD

if there are nulls in table A Coll_rec_date date then us ethe value of Table C has the Date_category_OLD to populate A table
----A has the Date_category field in text/string
----Date_category

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 11:12:19

why are you using text fields for storing dates? always try to use proper datatype for fields

briankind
Starting Member

17 Posts

Posted - 2009-01-23 : 10:28:13

I am not storing date values in a text

i am trying to store text values in table A based on the range date value from table B another field.

-----------------------------------------------

table A has the Date_category field in text/string
Date_category

table B has the Coll_rec_date in smalldatetime

table C has the Date_category_OLD in text/string

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources