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%
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. Thank - Gallop 2012-04-04 08:25
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