locked
Need to write a SQL query RRS feed

  • Question

  • Below is the ERD.


    In need to create Report in following structure.


    The contact in above report will be the contact linked to the activity table where contact_Id=dbo.Contacts.Id

    CreatedBy is the Agent in above report which can be found on dbo.employees where UserAccount_Id=CreatedBy

    In row4 and row 5, there are 2 contacts from the company Company3. This will be the 2 activity records with same information except a diff contactId. Same time, same company Id,same day and date.

    In row7,8,9, there are 2 companies. In this there are 3 activity records with same date, time, Id

    What SQL query I should write.

    Need Help..

    Thanks


    Abhinav



    Thursday, February 5, 2015 5:07 AM

Answers

  • Hi AbhinavSultania,

    Please reference the below code. The @Tab stands for the JOIN results of your tables.

    DECLARE @Tab TABLE(Date DATE,Day VARCHAR(10),Time TIME,Company VARCHAR(99),Contact VARCHAR(99),AgentName VARCHAR(99))
    
    INSERT INTO @Tab VALUES('2014-08-26','Tuesday','18:00:00','Company1','ContactName1','Agent1')
    INSERT INTO @Tab VALUES('2014-08-27','Wednesday','10:00:00','Company2','ContactName2','Agent2')
    INSERT INTO @Tab VALUES('2015-02-02','Monday','12:00:00','Company3','ContactName3','Agent3')
    INSERT INTO @Tab VALUES('2015-02-02','Monday','12:00:00','company3','ContactName4','Agent3')
    INSERT INTO @Tab VALUES('2014-08-28','Thursday','10:00:00','company5','ContactName5','Agent5')
    INSERT INTO @Tab VALUES('2015-02-03','Tuesday','13:00:00','company6','ContactName6','Agent6')
    INSERT INTO @Tab VALUES('2015-02-03','Tuesday','13:00:00','company6','ContactName7','Agent6')
    INSERT INTO @Tab VALUES('2015-02-03','Tuesday','13:00:00','company7','ContactName8','Agent6')
    
    ;WITH Cte AS
    (
    SELECT Date,Day,Time,Company,Contact,AgentName,
    ROW_NUMBER() OVER(PARTITION BY Date ORDER BY Company,Date) RN,
    ROW_NUMBER() OVER(PARTITION BY Date,Company ORDER BY Company) RN2,
    ROW_NUMBER() OVER(ORDER BY Company,Date,Contact) AS OrderBy FROM @Tab
    )
    SELECT CASE WHEN RN>1 THEN '' ELSE CONVERT(VARCHAR(25),Date,1) END AS Date,
    	   CASE WHEN RN>1 THEN '' ELSE Day  END AS Day,
    	   CASE WHEN RN>1 THEN '' ELSE CONVERT(VARCHAR(25),Time,0) END AS Time,
    	   CASE WHEN RN2>1 THEN '' ELSE Company END AS Time,
    	   Contact,
    	   CASE WHEN RN>1 THEN '' ELSE AgentName END AS Time
    From CTE 
    ORDER BY OrderBy



    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    Friday, February 6, 2015 2:38 AM

All replies

  • You can use below as query

    SELECT a.[Date],
    DATENAME(dw,a.[Date]) AS [Day],
    CONVERT(time,a.[Date]) AS [Time],
    cm.Name AS Company,
    c.FirstName + COALESCE(' ' + c.LastName,'') AS Contact,
    e.FirstName + COALESCE(' ' + e.LastName,'') AS AgentName
    FROM dbo.Contacts c
    INNER JOIN dbo.Activities a
    ON a.Contact_Id = c.Id
    INNER JOIN dbo.EMployees e
    ON e.UserAccount_Id = a.CreatedBy
    INNER JOIN dbo.Companies cm
    c.CompanyID = cm.Id


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, February 5, 2015 5:26 AM
    Answerer
  • Thanks....But the query is not displaying the required format. 

    As I mentioned, in row 4 and 5 since date,time values are same, I dont need to display repeated values in row 5 


    Abhinav


    Thursday, February 5, 2015 5:46 AM
  • I need to display as Report using SSRS...

    In row 4 and 5 since date,time values are same, I don't need to display repeated values in row 5 .

    Need to display this in report.

    Please help!!!

    Thanks


    Abhinav

    Thursday, February 5, 2015 7:20 AM
  • tell me Unique fields you don't want repeat again.
    Thursday, February 5, 2015 7:48 AM
  • day, date, time and company

    Abhinav

    Thursday, February 5, 2015 7:54 AM
  • Hi AbhinavSultania,

    Please reference the below code. The @Tab stands for the JOIN results of your tables.

    DECLARE @Tab TABLE(Date DATE,Day VARCHAR(10),Time TIME,Company VARCHAR(99),Contact VARCHAR(99),AgentName VARCHAR(99))
    
    INSERT INTO @Tab VALUES('2014-08-26','Tuesday','18:00:00','Company1','ContactName1','Agent1')
    INSERT INTO @Tab VALUES('2014-08-27','Wednesday','10:00:00','Company2','ContactName2','Agent2')
    INSERT INTO @Tab VALUES('2015-02-02','Monday','12:00:00','Company3','ContactName3','Agent3')
    INSERT INTO @Tab VALUES('2015-02-02','Monday','12:00:00','company3','ContactName4','Agent3')
    INSERT INTO @Tab VALUES('2014-08-28','Thursday','10:00:00','company5','ContactName5','Agent5')
    INSERT INTO @Tab VALUES('2015-02-03','Tuesday','13:00:00','company6','ContactName6','Agent6')
    INSERT INTO @Tab VALUES('2015-02-03','Tuesday','13:00:00','company6','ContactName7','Agent6')
    INSERT INTO @Tab VALUES('2015-02-03','Tuesday','13:00:00','company7','ContactName8','Agent6')
    
    ;WITH Cte AS
    (
    SELECT Date,Day,Time,Company,Contact,AgentName,
    ROW_NUMBER() OVER(PARTITION BY Date ORDER BY Company,Date) RN,
    ROW_NUMBER() OVER(PARTITION BY Date,Company ORDER BY Company) RN2,
    ROW_NUMBER() OVER(ORDER BY Company,Date,Contact) AS OrderBy FROM @Tab
    )
    SELECT CASE WHEN RN>1 THEN '' ELSE CONVERT(VARCHAR(25),Date,1) END AS Date,
    	   CASE WHEN RN>1 THEN '' ELSE Day  END AS Day,
    	   CASE WHEN RN>1 THEN '' ELSE CONVERT(VARCHAR(25),Time,0) END AS Time,
    	   CASE WHEN RN2>1 THEN '' ELSE Company END AS Time,
    	   Contact,
    	   CASE WHEN RN>1 THEN '' ELSE AgentName END AS Time
    From CTE 
    ORDER BY OrderBy



    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    Friday, February 6, 2015 2:38 AM
  • Hi,

    Can you please post DDL+DML to help us help you

    DDL+DML: queries to create the relevant database elements (like tables), and DML in this case mean the queries to insert some sample data.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Saturday, February 7, 2015 10:46 AM