Inserting records certain criteria

[SqlHunter]

Emp_Details:

ref_id emp_id Class1 Class2 Address
756 23456 Level1 Stage1 erteryey
456 65789 Level1 fghghjgj
789 76542 Stage6 tyutyutu



tracking_Details:

Inc_id emp_id ref_id Dept start_date
1 23456 756 PT 12/6/04
2 65789 456 SC 23/6/04
3 76542 789 PT 11/5/04


I have 2 tables one table is the emp_details table and the other is the tracking table.Now emp_details table has the data inside.I have to transfer certain details to the tracking_details.These are the constraints

1. corressponding emp_id and ref_id should be transfered
2. the record is pulled is like if there is data in Class1 and
Class2 OR if there is only data in class2 then "Dept" in
tracking_details should be "PT".
3. If there is data only in Class1 then "Dept" in
tracking_details should be "SC"
4. Start_date is the todays date

please show me how to tackle this problem using T-SQl

 

[Krickles]

If the missing data is actually a blank space try:

INSERT INTO tracking_details (emp_id, ref_id, dept, start_date)
SELECT emp_id, ref_id,
   CASE
      WHEN class1 <> '' and class2 = ''
      THEN 'SC'
      ELSE 'PT'
   END,
getdate()
FROM emp_details

If the missing data is actually NULL try:

INSERT INTO tracking_details (emp_id, ref_id, dept, start_date)
SELECT emp_id, ref_id,
   CASE
      WHEN class1 IS NOT NULL and class2 IS NULL
      THEN 'SC'
      ELSE 'PT'
   END,
getdate()
FROM emp_details

The only problem I see moving forward is how to know which records to update from the emp_details table. Hopefully you have a datetime stamp in the emp_details table that you can use as a parameter when picking which values to insert into the tracking_details table.

Good luck.

Krickles | 1.6180