Geek Answers Handbook

SQL Server Table Execution Statement

Can I join the next 3rd expression to display sick_total_day , other_total_day , other_total_day on one line (same where clause)?

 SELECT staff_key, sum(days_applied) as sick_total_day from Tleave_sick_leave where staff_key = 131 and from_date>='2011/4/1 00:00:00' and to_date<='2011/4/30 00:00:00'; SELECT staff_key, sum(days_applied) as other_total_day from Tleave_other_leave where staff_key = 131 and from_date>='2011/4/1 00:00:00' and to_date<='2011/4/30 00:00:00'; SELECT staff_key, sum(days_applied) as other_total_day from Tleave_vleave where staff_key = 131 and from_date>='2011/4/1 00:00:00' and to_date<='2011/4/30 00:00:00';
+4
source share
4 answers

This will take care of repeating the where clause, but it will be followed by a performance penalty.

A better solution might be what EMP offers. Normalize tables to remove duplicate data.

 SELECT staff_key , SUM(CASE WHEN type = 'Sick' THEN days_applied ELSE 0 END) as sick_total_day , SUM(CASE WHEN type = 'Othert' THEN days_applied ELSE 0 END) as othert_total_day , SUM(CASE WHEN type = 'Otherv' THEN days_applied ELSE 0 END) as otherv_total_day FROM ( SELECT staff_key , days_applied , from_date , to_date , [Type] = 'Sick' FROM Tleave_sick_leave UNION ALL SELECT staff_key , days_applied , from_date , to_date , 'Othert' FROM Tleave_other_leave UNION ALL SELECT staff_key , days_applied , from_date , to_date , 'Otherv' FROM Tleave_vleave ) WHERE staff_key = 131 AND from_date>='2011/4/1 00:00:00' AND to_date<='2011/4/30 00:00:00'; GROUP BY staff_key
+2
source

You could probably do this in one statement, but you still need 3 separate WHERE clauses, because the from_date and to_date columns in each table are completely separated, even though they have the same name.

Looks like you found a problem in your table design. You have 3 tables with very similar data. If you can combine them into one table, you can also leave with one query.

+1
source

You can combine your three tables together to make one data set. You can then use PIVOT to transfer the SUM to columns.

That should work. Although I have not tested it with any data.

 SELECT [Tleave_sick_leave], [Tleave_other_leave], [Tleave_vleave] FROM ( SELECT 'Tleave_sick_leave' [Table], [staff_key], [days_applied], [from_date], [to_date] FROM [Tleave_sick_leave] UNION ALL SELECT 'Tleave_other_leave' [Table], [staff_key], [days_applied], [from_date], [to_date] FROM [Tleave_other_leave] UNION ALL SELECT 'Tleave_vleave' [Table], [staff_key], [days_applied], [from_date], [to_date] FROM [Tleave_vleave] ) [PivotData] PIVOT ( SUM([days_applied]) FOR [Table] IN ( [Tleave_sick_leave], [Tleave_other_leave], [Tleave_vleave] ) ) [Data] WHERE [staff_key] = 131 AND [from_date] >= '2011/4/1 00:00:00' AND [to_date] <= '2011/4/30 00:00:00'

NOTE. This is an approach based on SQL Server 2005. If you have 2000 or lower, you will need to find Leaven’s answer that will actually do the same.

+1
source

A small correction for the code provided by Lieven now works ...

 SELECT a.staff_key , SUM(CASE WHEN a.type = 'Sick' THEN a.days_applied ELSE 0 END) as sick_total_day , SUM(CASE WHEN a.type = 'Othert' THEN a.days_applied ELSE 0 END) as othert_total_day , SUM(CASE WHEN a.type = 'Otherv' THEN a.days_applied ELSE 0 END) as otherv_total_day FROM ( SELECT staff_key , days_applied , from_date , to_date , type = 'Sick' FROM Tleave_sick_leave UNION ALL SELECT staff_key , days_applied , from_date , to_date ,type = 'Othert' FROM Tleave_other_leave UNION ALL SELECT staff_key , days_applied , from_date , to_date ,type = 'Otherv' FROM Tleave_vleave ) a WHERE staff_key = '131' AND from_date>='2011/4/1 00:00:00' AND to_date<='2011/4/30 00:00:00' GROUP BY staff_key
0
source

More articles:


All Articles