CODEWITHSUNDEEP
sqlsql-server-2008ssis
Gallop
Gallop

Reputation: 1405

Update column in SQL Server 2008 - SSIS

If the Customer Transaction type is not mentioned in the database
If the model year is less than or equal to 2 years than the transaction type should be updated as a Warranty.

Remaining 60 % of the customer data should be updated as Customer pay and the 40% of the customer data should be updated as Warranty randomly on each dealer.

I have a table for Model year table of this structure:

SlNo  VehicleNo  ModelYear
----  ---------  ---------
1     AAAD1234   2012
2     VVV023333  2008
3     CRT456     2011
4     MTER6666   2010

Is it possible to achieve this using SSIS??

I have tried a query. Please help fix it

select 
  vehicleNo, Modelyear,
  case 
      when DATEDIFF(year, ModelYear, GETDATE()) <= 2 then 'Warranty' END,
  case 
      when COUNT(modelyear) * 100 / (select COUNT(*) from VehicleModel) > 2 then '100%' end,
  case 
      when COUNT(modelyear) * 40 / (select COUNT(*) from VehicleModel) > 2  then '40%' end
from 
    vehiclemodel
group by 
    vehicleNo, Modelyear

Output

vehicleNo  Modelyear  (No column name)  (No column name)  (No column name) 
---------  ---------  ----------------  ----------------  ----------------
AAAD1234   2008       NULL              100%              40% 
VVV023333  2010       Warranty          100%              40% 
CRT456     2011       Warranty          100%              40%
MTER6666   2012       Warranty          100%              40%

Upvotes: 0

Views: 519

Answers (1)

Diego
Diego

Reputation: 36176

what exactly are you trying to do with SSIS? Where are you moving data from and where are you inserting it to?

If you only need to run this query you don't need SSIS. You can do this logic in SQL. If you need to insert this into another table or database I would also do the calculation on SQL (like you just did) and use it as source to an OleDBSourtce component and then insert it into your destination.

I think you have to provide more information so we can help you

Upvotes: 1

Related Questions