Writing conditions in Conditional Split Transformation Editor

I'm a novice to SSIS and one transformation I've never worked with before is The Conditional Split Transformation. I have a scenario where I need to split payments in 2 separated tables, where certain conditions are met. I've been fighting to get some logic to the syntax and how to write the conditions but it always me errors.

The code I attached is how I would like to split the payments. Is there any way to write those select statements as a condition in the Conditional Split Transformation Editor?
SELECT SUM(19-DATEPART("dd",gsdgsvfr)) as payment1,
SUM(gsfjodag- (19-DATEPART("dd",gsdgsvfr))) as  payment2
FROM [Bal].[dbo].[payments]
Group by [gsnumrec]

the type of those fields used:
[gsdgsvfr] [char](10) NULL
[gsdgsvti] [char](10) NULL
[gsfjodag] [smallint] NULL

Open in new window

gosi75Business Intelligence ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Reza RadConsultant, TrainerCommented:
the logic of why you want to use conditional transformation is not clear to me yet.
if you want to put payment1 into one table , and payment2 into another table. so add a multi cast transformation after your data source, this will make double the result, then put payment1 from first result to first table. and payment2 from second result to table2.

Am I understood your problem correctly?
let me know if you mean anything else.
0
gosi75Business Intelligence ConsultantAuthor Commented:
Ok maybe Conditional transformation is not what I'm looking for but maybe you could give me some advice what would be the best approach to solve my problem.

In the table I'm working with there are some rows that need to be split to 2 rows if certain condition are met.
There are many columns in the table but all of them will be transfered to the new table/tables. Two of the columns include dates, the column [gsdgsvfr] is the date from and the column [gsdgsvti] is the date to. The third column, [gsfjodag] is a calculation of how many payment days are in the range from [gsdgsvfr]:[gsdgsvti].

I will only take the ranges where the 20th of the month is included in the range, as the payment period 1 is 1st-19th and payment period 2 is the =>20th of the month.

F.exs. if the range is [2010-17-01]:[2010-22-01] then I want to split the transaction to the destination table so there will be two rows. The first transaction will have the value 2 in the payment table and the second transaction will have the value 3 in the payment table, and sum of those two values will be equal to the value in [gsfjodag].

What I had in mind was to make some transformation to two temp tables, one with the data from 1st to the 19th and another with the data from 20th to end of month. I then wanted to merge those two temp tables to one destination table and link the new payment fields to the [gsfjodag] field so that I now have a new set of rows, where [gsfjodag] has the value 2 for row one and  the  [gsfjodag] has the value 3 for the second row.

I hope you understand my problem, if some things are still unclear then please let me know.

0
Reza RadConsultant, TrainerCommented:
I think it would be more understandable if you bring sample row of data from payments table.
I got something in your explanation , but sample data rows will be helpful.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

gosi75Business Intelligence ConsultantAuthor Commented:
Here are some sample data
SampleData-EE.rpt
0
Reza RadConsultant, TrainerCommented:
As I see in your sample data gsfjodag should be amount of days between two date columns. but it is not!
do you calculate only working days? or logic is something else?

gsdgsvfr          gsdgsvti               gsfjodag
----------          ----------               --------
2001.09.17     2001.09.24           5      
2001.10.08     2001.10.22          10      
2001.11.19     2001.11.30          9      
2001.12.17     2001.12.31          10      
0
gosi75Business Intelligence ConsultantAuthor Commented:
It is only working days, weekends are excluded. But that is another problem which I'm trying to solve. The reason I didn't mention it in my first post was that I wanted to start by getting the transformation to work, without considering the weekends, but the problems with the weekends are that the gsfjodag is getting the amount of working days manually from the client input. To split it right, then I would need to know if there is a weekend on the period and exlude it in the calculations.

Example:
The range [2001.09.17] to  [2001.09.24] should have 1 day in period one and 4 days in period two as the 17.09.2001 is friday and the 19th is the last day of the period one. Is there any way in SSIS to do that?

This is maybe another problem and not sure if this belongs to the SSIS, but should give overview of the whole problem.


0
Reza RadConsultant, TrainerCommented:
so,
do you mean result table should be like this: ????

gsdgsvfr          gsdgsvti               gsfjodag
----------          ----------               --------
2001.09.17     2001.09.19           1      
2001.09.20     2001.09.24           4      
....
....


if yes, you can use UNION ALL in your query, like this:

SELECT SUM(19-DATEPART("dd",gsdgsvfr)) as paymentFROM [Bal].[dbo].[payments]Group by [gsnumrec]UNION ALLSELECT SUM(gsfjodag- (19-DATEPART("dd",gsdgsvfr))) as  paymentFROM [Bal].[dbo].[payments]Group by [gsnumrec]

now just fill destination table with this result.

you can also have two different data sources , one for each of above queries , and then use a UNION transformation between them . and fill push result of union transformation to destination table.

let me know if something is missing.


0
Reza RadConsultant, TrainerCommented:
I have also a way to fetch working days,
this query return working days between gsdgsvfr and 19th of month as Payment1,
and days between 19th of month and gsdgsvti as Payment2   :


SELECT[gsnumrec],sum(   (DATEDIFF(dd, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18') )  -(DATEDIFF(wk, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18') * 2)  -(CASE WHEN DATENAME(dw, gsdgsvfr) = 'Sunday' THEN 1 ELSE 0 END)  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'18') = 'Saturday' THEN 1 ELSE 0 END)) as Payment1,sum(   (DATEDIFF(dd, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) )  -(DATEDIFF(wk, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) * 2)  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'18') = 'Sunday' THEN 1 ELSE 0 END)  -(CASE WHEN DATENAME(dw, gsdgsvti) = 'Saturday' THEN 1 ELSE 0 END)) as Payment2

from payments
Group by [gsnumrec]




0
Reza RadConsultant, TrainerCommented:
so, final query will be this: ( UNION ALL added)

SELECT
[gsnumrec],
sum(   (DATEDIFF(dd, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18') )
  -(DATEDIFF(wk, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18') * 2)
  -(CASE WHEN DATENAME(dw, gsdgsvfr) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'18') = 'Saturday' THEN 1 ELSE 0 END)
) as Payment

from payments
Group by [gsnumrec]

UNION ALL

SELECT
[gsnumrec],
sum(   (DATEDIFF(dd, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) )
  -(DATEDIFF(wk, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) * 2)
  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'18') = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, gsdgsvti) = 'Saturday' THEN 1 ELSE 0 END)
) as Payment2


from payments
Group by [gsnumrec]

Open in new window

0
Reza RadConsultant, TrainerCommented:
the minus values replaced by 0 in this query: ( so this can be final version)

SELECT
[gsnumrec],
case 
when sum(DATEDIFF(dd, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18'))<0 then 0
else
sum(   (DATEDIFF(dd, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18') )
  -(DATEDIFF(wk, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'18') * 2)
  -(CASE WHEN DATENAME(dw, gsdgsvfr) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'18') = 'Saturday' THEN 1 ELSE 0 END)
) 
end
as Payment

from payments
Group by [gsnumrec]

UNION ALL

SELECT
[gsnumrec],
case 
when sum(   (DATEDIFF(dd, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) ))<0 then 0
else
sum(   (DATEDIFF(dd, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) )
  -(DATEDIFF(wk, SUBSTRING(gsdgsvfr,1,8)+'18', gsdgsvti) * 2)
  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'18') = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, gsdgsvti) = 'Saturday' THEN 1 ELSE 0 END)
) 
end
as Payment


from payments
Group by [gsnumrec]

Open in new window

0
gosi75Business Intelligence ConsultantAuthor Commented:
Thanks alot for this. However I'm not getting this totally correct. I wrote earlier that:

gsdgsvfr          gsdgsvti               gsfjodag
----------          ----------               --------
2001.09.17     2001.09.19           1      
2001.09.20     2001.09.24           4      

but that is not right, I was looking at 2010.09.17 instead of 2001.09.17 and that day is a monday. So this should actually be:

gsdgsvfr          gsdgsvti               gsfjodag
----------          ----------               --------
2001.09.17     2001.09.19           2    
2001.09.20     2001.09.24           3

total of gsfjodag=5
The first date should also not count in gsfjodag, so the payment 1 period is 2001.09.18 & 2001.08.19.
sorry for that.

We could also look at some newer period, like

gsdgsvfr          gsdgsvti               gsfjodag
----------          ----------               --------
2009.05.15     2009.05.21            4    

but should be:
gsdgsvfr          gsdgsvti               gsfjodag
----------          ----------               --------
2009.05.15     2009.05.19            2   (15.05 is excluded as it's the first day, and 16.05 & 17.05 also as it's weekend)
2009.05.20     2009.05.21            2  

Could this affect your query so it's not returning the right results?
 
0
Reza RadConsultant, TrainerCommented:
OK,
try this query now:

SELECT
[gsnumrec],
MAX(gsdgsvfr),
MAX(SUBSTRING(gsdgsvfr,1,8)+'19'),
case 
when sum(DATEDIFF(dd, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'19'))<0 then 0
else
sum(   (DATEDIFF(dd, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'19') )
  -(DATEDIFF(wk, gsdgsvfr, SUBSTRING(gsdgsvfr,1,8)+'19') * 2)
  -(CASE WHEN DATENAME(dw, gsdgsvfr) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'19') = 'Saturday' THEN 1 ELSE 0 END)
) 
end
as Payment

from payments
Group by [gsnumrec]

UNION ALL

SELECT
[gsnumrec],
MAX(SUBSTRING(gsdgsvfr,1,8)+'20'),
MAX(gsdgsvti),
case 
when sum(   (DATEDIFF(dd, SUBSTRING(gsdgsvfr,1,8)+'20', gsdgsvti) ))<0 then 0
else
sum(   (DATEDIFF(dd, SUBSTRING(gsdgsvfr,1,8)+'20', gsdgsvti) +1)
  -(DATEDIFF(wk, SUBSTRING(gsdgsvfr,1,8)+'20', gsdgsvti) * 2)
  -(CASE WHEN DATENAME(dw, SUBSTRING(gsdgsvfr,1,8)+'20') = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, gsdgsvti) = 'Saturday' THEN 1 ELSE 0 END)
) 
end
as Payment


from payments
Group by [gsnumrec]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gosi75Business Intelligence ConsultantAuthor Commented:
This solves  my problem, thank you very much for this, your help has been priceless :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.