Post reply

Sum with String concatenation

  • August 24, 2017 at 1:48 am

    #323195

    I have already the solution for the problem, but not so sure if it was the proper way to do it.

    I have 4 tables

    1.) Candidate
    2.) Application
    3.) Vacancy
    4.) Client

    Each candidate can submit an application for a vacant job. The application process has two interviews (InterviewDate, InterviewDate2). I need to get all the interview dates that are not 12/12/1900 00:00 and count them. Then I need to display the CandidateID, CandidateName, TotalInterviews, ClientName. 

    Final Output would look like the output table in image

    My solution:


    DECLARE @TempTable TABLE
    (
        VacancyId int not null,
        CandidateID int not null,
        ClientID int not null,
        FirstName varchar(32) not null,
        LastName varchar(32) not null,
        Codes varchar(512) not null,
        Interviews int not null,
        Client varchar(max) not null
    )

    DECLARE @finaltable TABLE
    (
        CandidateID int not null,
        CandidateName varchar(64) not null,
        Codes varchar(512) not null,
        Interviews int not null,
        Client varchar(max) not null
    )

    INSERT INTO @TempTable
    SELECT v.VacancyID
        ,c.CandidateID
        ,cl.ClientID
        ,c.FirstName
        ,c.LastName
        ,c.Codes
        ,COUNT(DISTINCT a.InterviewDate) as Interviews
        ,cl.ClientName
        
        FROM [Candidate] c
            LEFT JOIN [Application] a on a.CandidateID = c.CandidateID
            LEFT JOIN Vacancy v on v.VacancyID = a.VacancyID
            LEFT JOIN Client cl on cl.ClientID = v.ClientID
            
            WHERE c.ReferrerID = 510 AND InterviewDate <> '1900-12-12 00:00:00'
            --AND c.CandidateID = 691241
            GROUP BY v.VacancyID
            ,c.CandidateID
            ,cl.ClientID
            ,FirstName
            ,LastName
            ,Codes
            ,a.InterviewDate
            ,cl.ClientName
        UNION ALL
    SELECT v.VacancyID
        ,c.CandidateID
        ,cl.ClientID
        ,c.FirstName
        ,c.LastName
        ,c.Codes
        ,COUNT(DISTINCT a.InterviewDate2) as Interviews
        ,cl.ClientName
        FROM [Candidate] c
            LEFT JOIN [Application] a on a.CandidateID = c.CandidateID
            LEFT JOIN Vacancy v on v.VacancyID = a.VacancyID
            LEFT JOIN Client cl on cl.ClientID = v.ClientID
            
            WHERE c.ReferrerID = 510 AND InterviewDate2 <> '1900-12-12 00:00:00'
            --AND c.CandidateID = 691241
            GROUP BY v.VacancyID
            ,c.CandidateID
            ,cl.ClientID
            ,FirstName
            ,LastName
            ,Codes
            ,a.InterviewDate2
            ,cl.ClientName    

        
    INSERT INTO @finaltable
        SELECT DISTINCT ST2.CandidateID
            ,FirstName + ' ' + LastName AS CandidateName
            ,Codes
            ,COUNT(Interviews) OVER(PARTITION BY CandidateID) AS Interviews
            ,(SELECT STUFF((
            SELECT DISTINCT ', ' + Client
            FROM @TempTable ST1
            Where ST1.CandidateID = ST2.CandidateID
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''))
            AS Client
        From @TempTable ST2

    SELECT * FROM @finaltable

  • August 24, 2017 at 2:40 am

    #1956554

    I suspect that you don't need that UNION ALL.  Indeed, you might not need to stage in @TempTable at all.  If you post table DDL (CREATE TABLE statements) and sample data (INSERT statements), I'll show you how.  Not everybody is comfortable opening Excel files from untrusted sources.

    John

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply