Writing stored procedure/Join query

Santosh Umarani 81 Reputation points
2021-07-14T09:08:39.887+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,592 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
{count} votes

1 answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-07-15T10:23:12.513+00:00

    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:
    114956-image.png

    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.

    0 comments No comments