CODEWITHSUNDEEP
sql-server
Programmerzzz
Programmerzzz

Reputation: 1287

Generate an excel like report from SQL

I am novice to SQL and I have two tables Ticket and TicketAttributes with following Schema

CREATE TABLE [dbo].[Ticket](
   [TicketID] [int] IDENTITY(1,1) NOT NULL, --Primary key
   [Category] [varchar](256) NOT NULL,
   [Description] [varchar](256) NULL,
   [LibID] [int] NOT NULL,
   [Status] [smallint] NULL,
   [LogID] [int] NULL)

Ticket Attributes

CREATE TABLE [dbo].[TicketAttributes](
    [TicketID] [int] NOT NULL,
    [TicketAttrID] [int] IDENTITY(1,1) NOT NULL,
    [AttributeID] [int] NOT NULL,
    [AttributeGroup] [varchar](255) NULL,
    [AttributeValue] [nvarchar](max) NULL,
    [Status] [smallint] NULL,
    [LogID] [int] NULL)

Where Ticket Attribute is another table that stores different attributes of a ticket like TicketStatus, TicketCategory etc.. Now I need to generate a report that looks like

                   TicketStatus1     TicketStatus 2     TicketStatus3
    -----------------------------------------------------------------
TicketCategory1         7                3
Ticketcategory2                          4
TicketCategory3                                            8

I want to see the count of each of the status of each ticket category. For Eg:- I have the following Data in TicketTable

----------------------------------------------
TicketID  Name         Price        Date
------------------------------------------------
155       Ticket4      $20     16 Jan 2016
157       Ticket3      $300    17 Jan 2016
158       Ticket1      $100    18 Jan 2016
159       Ticket2      $500    19 Jan 2016

Now in the TicketAttribute Table

----------------------------------------------
    TicketID  AttributeID  AttributeValue
    ------------------------------------------------
    155       500             Joe     
    155       600             Reserved    
    155       700             Economy
    155       800             San Jose

where AttributeIDs

 500=Nameofthe Passenger
 600= Status of Ticket
 700= Class
 800= Destination

Now lets say I want to see what is the count of number of active tickets in each of the class per status 

               Booked     Cancelled     PaymentPending  ............
    -----------------------------------------------------------------
Economy         7                3
Economy Plus                     4
Business                                         8

Hope I am clear now. how to go about this using SQL Query

Upvotes: 0

Views: 51

Answers (1)

Matt
Matt

Reputation: 14341

USING PIVOT

;WITH cte AS (
    SELECT
       c.AttributeValue as Class
       ,s.AttributeValue as StatusOfTicket
    FROM
       Ticket t
       LEFT JOIN TicketAttributes c
       ON t.TicketId = c.TicketId
       AND c.AttributeID = 700
       LEFT JOIN TicketAttributes s
       ON t.TicketId = s.TicketId
       AND s.AttributeID = 600
)

SELECT *
FROM
    cte
    PIVOT (
       COUNT(StatusOfTicket) FOR StatusOfTicket IN (Reserved,Cancelled,PaymentPending)
    ) p

USING Conditional Aggregation:

SELECT
    c.AttributeValue as Class
    ,COUNT(DISTINCT CASE WHEN s.AttributeValue = 'Reserved' THEN c.TicketId END) as Reserved
    ,COUNT(DISTINCT CASE WHEN s.AttributeValue = 'Cancelled' THEN c.TicketId END) as Cancelled
    ,COUNT(DISTINCT CASE WHEN s.AttributeValue = 'PaymentPending' THEN c.TicketId END) as PaymentPending
FROM
    Ticket t
    LEFT JOIN TicketAttributes c
    ON t.TicketId = c.TicketId
    AND c.AttributeID = 700
    LEFT JOIN TicketAttributes s
    ON t.TicketId = s.TicketId
    AND s.AttributeID = 600
GROUP BY
    c.AttributeValue

Upvotes: 2

Related Questions