Writing stored procedure/Join query

Question

Hi,

I want to write a query/stored procedure to meet the following requirement:

• For a given branch name and test date: the similar test classes should be clubbed for each of the test type (from Reports table)
• Count of each Summary type should be added for the same test class (from Projects table)

The out put should look like this:

Test class Test type Ok Compile TFI MIG CON Upl No upl H upg P upg
300 Migrate 400
300 Convert 200
300 Upgrade 40
300 Upload 50

Here for the test class 300 and test type Migrate, the OK error type count is 400. Similarly it should display for all error types and rest test types for all the test class.

Projects

CREATE TABLE [dbo].[Projects](
[TestType] nvarchar NULL,
[TestDate] nvarchar NULL,
[BranchName] nvarchar NULL,
[TestClass] nvarchar NULL,
[TestProject] nvarchar NULL,
[SummaryType] nvarchar NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,

Reports

CREATE TABLE [dbo].[Reports](
[TestType] nvarchar NULL,
[TestDate] nvarchar NULL,
[TestClass] nvarchar NULL,
[SummaryType] nvarchar NULL,
[TotalCount] [int] NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,

Please let me know how can I achieve above functionality
Please let me know if any other information I should provide. Kindly waiting for your response.

Thanks,
Santosh

Answer 1

Hi @Santosh Umarani ,

I created some test data myself, and the following is a query based on my understanding. If this does not solve your problem, you can create test data similar to yours as I did, and provide the corresponding desired results.

Please try:

CREATE TABLE [dbo].[Projects](  
[TestType] [nvarchar](50) NULL,  
[TestDate] [nvarchar](50) NULL,  
[BranchName] [nvarchar](50) NULL,  
[TestClass] [nvarchar](50) NULL,  
[TestProject] [nvarchar](50) NULL,  
[SummaryType] [nvarchar](50) NULL,  
[Id] [int] IDENTITY(1,1) NOT NULL)  
INSERT INTO [dbo].[Projects] VALUES('Migrate',NULL,NULL,300,NULL,NULL)  
                                  ,('Convert',NULL,NULL,300,NULL,NULL)  
								  ,('Upgrade',NULL,NULL,300,NULL,NULL)  
								  ,('Upload',NULL,NULL,300,NULL,NULL)  
  
  
CREATE TABLE [dbo].[Reports](  
[TestType] [nvarchar](50) NULL,  
[TestDate] [nvarchar](50) NULL,  
[TestClass] [nvarchar](50) NULL,  
[SummaryType] [nvarchar](50) NULL,  
[TotalCount] [int] NULL,  
[Id] [int] IDENTITY(1,1) NOT NULL)  
  
CREATE TABLE [dbo].[ErrorTypes](  
[ErrorTypeID] [int] IDENTITY(1,1) NOT NULL,  
[TestTypeID] [int] NOT NULL,  
[ErrorTypeName] [nvarchar](50) NOT NULL,  
[DisplayError] [nvarchar](50) NULL)  
INSERT INTO [dbo].[ErrorTypes] VALUES(0,0,'Ok') ,(0,0,'Compile')  
								  ,(0,0,'Ok'),(0,0,'Ok')  
								  ,(0,0,'Compile'),(0,0,'Compile')  
								  ,(0,0,'TFI'),(0,0,'MIG ')  
								  ,(0,0,'CON') ,(0,0,'Upl')  
								  ,(0,0,'MIG '),(0,0,'CON')  
								  ,(0,0,'upl'),(0,0,'MIG ')  
								  ,(0,0,'Hupg'),(0,0,'Pupg')  
								  ,(0,0,'No')  
  
  
;WITH cte  
as(SELECT p.Testclass,p.Testtype,e.[DisplayError]  
FROM [dbo].[Projects] p   
JOIN [dbo].[ErrorTypes] e ON p.Id=e.[ErrorTypeID])  
,cte2 as  
 (SELECT Testclass,Testtype,[DisplayError],  
    COUNT([DisplayError]) OVER(PARTITION BY [DisplayError] ORDER BY [DisplayError]) num  
  FROM cte)  
  
SELECT * FROM (SELECT Testclass,Testtype,[DisplayError],num FROM cte2) t  
PIVOT (MAX(num) FOR [DisplayError] IN(Ok,Compile,CON,Hupg,MIG ,No,Pupg,TFI,Upl))p  

Output:

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

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.