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..

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

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

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

tell me Unique fields you don't want repeat again.

day, date, time and company

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.

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.