How to do join for my sitaution

Question

i have two select statement from two different table and the common join field a datefield one date field name is JobFinishedDate and it is from MyJobs table and another date field name is createddate and it is from DailyFinishJobsHistory table

here is my two independent sql

sql 1

SELECT  CONVERT(varchar,createddate,101) as [Date],
                                SUM([No of Jobs]) as [No of Jobs],
                                SUM([Turnaround Time]) as [Turnaround Time],
                                SUM([One Day Per]) as [One Day Per],
                                ROUND((SUM([No of Jobs]) * SUM([Turnaround Time])),2) as [TOTAL hours]

from DailyFinishJobsHistory d
WHERE CONVERT(varchar,createddate,112)>='20131015' AND CONVERT(varchar,createddate,112)<='20131021' 
group by createddate

sql 2

Select 
  convert(varchar,JobFinishedDate,112) as JobFinishedDate
, Count(*) [Number of techs]
From
(
    select 
    convert(varchar,JobFinishedDate,112) JobFinishedDate,Specialist
    from MyJobs
    where convert(varchar,JobFinishedDate,112)>='20131015' 
    and convert(varchar,JobFinishedDate,112) <='20131021'  
    and JobState='FINISHED'
    group by convert(varchar,JobFinishedDate,112) , Specialist
) t1    
Group By 
  JobFinishedDate

now i have to join. i tried like this way but right syntax is not coming to my mind to join this way.

SELECT  CONVERT(varchar,createddate,101) as [Date],
                                SUM([No of Jobs]) as [No of Jobs],
                                SUM([Turnaround Time]) as [Turnaround Time],
                                SUM([One Day Per]) as [One Day Per],
                                ROUND((SUM([No of Jobs]) * SUM([Turnaround Time])),2) as [TOTAL hours],
(
Select 
  convert(varchar,JobFinishedDate,112) as JobFinishedDate
, Count(*) [Number of techs]
From
(
    select 
    convert(varchar,JobFinishedDate,112) JobFinishedDate,Specialist
    from bbajobs
    where convert(varchar,JobFinishedDate,112)>='20131015' 
    and convert(varchar,JobFinishedDate,112) <='20131021'  
    and JobState='FINISHED'
    group by convert(varchar,JobFinishedDate,112) , Specialist
) t1    
Group By 
  JobFinishedDate
) a
from DailyFinishJobsHistory d
WHERE CONVERT(varchar,createddate,112)>='20131015' AND CONVERT(varchar,createddate,112)<='20131021' 
group by createddate

so please help me bit to build the join. thanks

EDIT

i tried this script also but [Number of techs] is getting NULL

    SELECT  CONVERT(varchar,createddate,101) as [Date],
            SUM([No of Jobs]) as [No of Jobs],
            SUM([Turnaround Time]) as [Turnaround Time],
            SUM([One Day Per]) as [One Day Per],
            ROUND((SUM([No of Jobs]) * SUM([Turnaround Time])),2) as [TOTAL hours],
(
        SELECT 
        --CONVERT(varchar,JobFinishedDate,112) as JobFinishedDate,
        COUNT(*) [Number of techs]
    From
    (
        SELECT  CONVERT(varchar,JobFinishedDate,112) JobFinishedDate,
                Specialist
        FROM bbajobs
        WHERE d.createddate=JobFinishedDate AND
        JobState = 'FINISHED'  
        GROUP BY CONVERT(varchar,JobFinishedDate,112), Specialist
    ) t1    
    GROUP BY JobFinishedDate
) [Number of techs]


    FROM DailyFinishJobsHistory d
    WHERE CONVERT(varchar,createddate,112) >= '20131015' AND CONVERT(varchar,createddate,112) <= '20131021' 
    GROUP BY createddate

score 1 · Accepted Answer · edited May 23 '17 at 12:05

Something like this should do the trick:

    SELECT *
FROM
(
    SELECT  CONVERT(varchar,createddate,112) as [Date],
            SUM([No of Jobs]) as [No of Jobs],
            SUM([Turnaround Time]) as [Turnaround Time],
            SUM([One Day Per]) as [One Day Per],
            ROUND((SUM([No of Jobs]) * SUM([Turnaround Time])),2) as [TOTAL hours]
    FROM DailyFinishJobsHistory
    WHERE CONVERT(varchar,createddate,112) >= '20131015'
    AND CONVERT(varchar,createddate,112) <= '20131021' 
    GROUP BY createddate
) A
JOIN
(
    SELECT 
        JobFinishedDate,
        COUNT(*) [Number of techs]
    From
    (
        SELECT CONVERT(varchar,JobFinishedDate,112) JobFinishedDate
        FROM MyJobs
        WHERE CONVERT(varchar,JobFinishedDate,112) >= '20131015' 
        AND CONVERT(varchar,JobFinishedDate,112) <='20131021'  
        AND JobState = 'FINISHED'
    ) t1    
    GROUP BY JobFinishedDate
) B
ON B.JobFinishedDate = A.[Date]

See joining two select statements for a simpler example.

when two statement i am running independently then data is coming but when i am running sql given by you then no data is coming. any help. — Thomas, Oct 22 '13 at 14:35
Edited SQL, changed `CONVERT(..,103)` to `CONVERT(..,112)` in first query so date formats match for `ON` clause. Let me know if that fixes it. If not, what errors are you getting? — Dan Bechard, Oct 22 '13 at 14:47

How to do join for my sitaution

sql 1

sql 2

EDIT

1 Answers1