I need query for three table with join

Question

Dear all,

Kindly find below table records for sample data.

TABLE-01
Dept	Total No of codes
A	1191
B	1039
C	2012
D	3780
E	9
F	5972
G	2186


TABLE-02
user	dept
U1	A
U2	A
U3	B
U4	C
U5	E
U6	G


TABLE-03
USER	VAL1	date
U1	1001	06-10-2020
U2	1002	06-10-2020
U4	1004	06-10-2020
U3	1003	07-10-2020
U4	1005	07-10-2020
U4	1004	07-10-2020
U4	1006	08-10-2020
U5	1005	08-10-2020
U3	1003	09-10-2020
U6	1006	09-10-2020

An I need result as below:

Result
Dept	Total No of codes	Total CompletedCodes	06-10-2020	07-10-2020	08-10-2020	09-10-2020
A	10	2	2	0	0	0
B	20	2	0	1	0	1
C	15	4	1	2	1	0
D	30	0	0	0	0	0
E	9	1	0	0	1	0
F	44	0	0	0	0	0
G	58	1	0	0	0	1
total	186	10	3	3	2	2

Please find below image for your ref.

Kiran Mohanty · Answer

You can user PIVOT as below. select * from ( select * from ( select t1.dept , t1.CodesCount as 'Todatl tests' , isnull ( t3.value1, 0) as 'value1' , t3.TestDate from table1 t1 left outer join table2 t2 on t2.Dept = t1.Dept left outer join table3 t3 on t2.UserName = t3.UserName ) t pivot ( sum (value1) for TestDate in ( [2020-06-10],[2020-06-09], [2020-05-10],[2020-07-10] ) ) as pivot_table ) result order by dept;

Mageshwaran R · Answer

Hi Tamil Selvan, please refer this link: https://www.geeksforgeeks.org/joining-three-tables-sql/

Tamil Selvan · Answer

This is for Query: SELECT B.dept, A.req_date1, COUNT (A.req_no) as [Total] FROM TABLE -03 A left join TABLE -02 B on A.user_id=B.user_id where A.status NOT IN ( 'D' , 'R' ) GROUP BY b.dept,A.req_date1 ORDER BY B.dept desc Result as Below: dept req_date1 total A 06-10-2020 2 B 07-10-2020 1 B 09-10-2020 1 C 06-10-2020 1 C 07-10-2020 2 C 08-10-2020 1

Abhishek Chadha · Answer

you can use CTE to get the dynamic column or if you have a query you made for this you can post here i can made changes in that

Tamil Selvan · Answer

I need dynamic data for column field and row field also. Like date will change day by day. So row filed is date, column field is dept.

Abhishek Chadha · Answer

You can use the pivot to get the expected output. for reference - https://www.c-sharpcorner.com/UploadFile/8911c4/pivot-examples-in-sql-server/