I have a requirement as below:
Group_ID - Member_ID
A - B
A - C
X - Y
X - Z
ID - Value1 - Value2
A - 111 - 222
B - 444 - 666
C - 566 - 556
X - 999 - 777
Y - 444 - 555
Z - 433 - 876
Group_ID - Group_val1 - Group_val2 - Member_ID - Member_val1 - Member_val2
A - 111 - 222 - B - 444 - 666
A - 111 - 222 - C - 566 - 556
X - 999 - 777 - Y - 444 - 555
X - 999 - 777 - Z - 433 - 876
Logic:- I need to join Table1 to Table2 once with Group_ID to get values and again with Member_ID to get the values to get the required output.
Thought of checking whether the same can be achieved with a single join to get the required output.
Thanks a lot in advance.
Thank you and Yes exactly.
Just thought of checking whether this can be achieved with only one LEFT JOIN instead of 2 LEFT JOINs on the same table.
you might do a LEFT JOIN using an ORed condition plus some CASE, but this will result in a real bad product join.
So better keep the two joins :-)
I need help in writing an SQL.
I have a Table "A", which has Emplyee ID, Joining Date, Resignation Date. [Resignation Date >= Joining Date].
I have another Table "B", which has the Days which are either Week-ends or, Business Holidays.
I wish to find how many days between Resignation Date & Joining Date is Week-ends/Business Holidays and Working Days for each Employee.
The way, I wrote the SQL:
Select A.Empid, A.Joining_Date, A.Resignation_Date, A.Resignation_Date - A.Joining_Date as "Total Days",
Sum(B.Calendar_Days) as "Non Working Days", "Total Days" - "Non Working Days" as "Working Days"
Join B -- "B" is a Subquery where the Conditions Week-Ends & Business Holidays are Checked.
on B.Calendar_Days between A.Joining_Date + 1 and A.Resignation_Date;
The Problem with above SQL is the Product Join. The Volumne of Data in "A" is huge. The Product Join is rendering the Server very busy and forcing the DBAs to Abort.
Any help or suggestion in witing the above SQL in optimized way is appreciated.
That's a variation of the common problem "how many working days were needed to process this task":
Add a new SMALLINT column to your calendar table like holiday_seqnum and populate it once with a running number using
SUM(case when weekend then 1 else 0 end) over (order by calendardate rows unbounded preceding).
Now it's two joins to your calendar and a simple difference of the holiday_seqnums of Resignation_Date and Joining_Date.
Of course you might do the same for working days or simply do number of days between star end end minus number of holidays:-)