Click here to Skip to main content
15,123,851 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more:
How do i join 2 select queries

SQL
SELECT DATENAME(DW,Dated) as Days, FinalTime
  FROM [Attendance].[dbo].[AttnDaily]
  where EmpID=111 and Dated>='2015-09-14' and Dated<='2015-09-18'


OUTPUT:
Days	        FinalTime
Monday	        09:23:27
Tuesday	        08:55:50
Wednesday	    08:52:38
Thursday	    00:00:00
Friday	        10:31:53


SQL
SELECT 'Total Hours', cast(sum(datediff(second,0,[FinalTime]))/3600 as varchar(12)) + ':' + 
        right('0' + cast(sum(datediff(second,0,[FinalTime]))/60%60 as varchar(2)),2) +
        ':' + right('0' + cast(sum(datediff(second,0,[FinalTime]))%60 as varchar(2)),2) 
  FROM [Attendance].[dbo].[AttnDaily]
  where EmpID=111 and Dated>='2015-09-14' and Dated<='2015-09-18'


OUTPUT:
(No column name)  (No column name)
Total Hours	         37:43:48


Required Output:
Days	        FinalTime
Monday	        09:23:27
Tuesday	        08:55:50
Wednesday	    08:52:38
Thursday	    00:00:00
Friday	        10:31:53
Total Hours	    37:43:48
Posted
Updated 21-Sep-15 9:24am
v3
Comments
Kornfeld Eliyahu Peter 21-Sep-15 8:02am
   
Look for UNION...
[no name] 21-Sep-15 8:07am
   
If i use UNION ill get this error:
Conversion failed when converting date and/or time from character string.
Herman<T>.Instance 21-Sep-15 8:13am
   
SELECT DATENAME(DW,Dated) as Days, CAST(FinalTime, varchar) as FinalTime

and then the UNION
[no name] 21-Sep-15 8:18am
   
Again error:
Incorrect syntax near 'CAST', expected 'AS'.
Herman<T>.Instance 21-Sep-15 8:21am
   
CAST (FinalTime as varchar) as FinalTime
[no name] 21-Sep-15 8:32am
   
Thanks :)

1 solution

Try:
SQL
SELECT DATENAME(DW,Dated) as Days, FinalTime
  FROM [Attendance].[dbo].[AttnDaily]
  WHERE EmpID=111 and Dated>='2015-09-14' and Dated<='2015-09-18'
UNION ALL
SELECT 'Total Hours' As Days, CONCAT(cast(sum(datediff(second,0,[FinalTime]))/3600 as varchar(12)), ':' , 
        right('0' + cast(sum(datediff(second,0,[FinalTime]))/60%60 as varchar(2)),2) ,
        ':' , right('0' + cast(sum(datediff(second,0,[FinalTime]))%60 as varchar(2)),2)) AS FinalTime
  FROM [Attendance].[dbo].[AttnDaily]
  WHERE EmpID=111 and Dated>='2015-09-14' and Dated<='2015-09-18'


For further information, please, see:
UNION[^]
CONCAT[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900