Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2014
    Posts
    16

    Unanswered: SQL - Statement - Pivot table

    I have a table, and need to know what SQL statement will give this report

    TABLE:
    Click image for larger version. 

Name:	Image2.png 
Views:	8 
Size:	24.3 KB 
ID:	15899

    This is the layout of the Report. I know that it should be a Pivot table. My problem is the first column, how do I GROUP by Month + Year like in my image below
    Click image for larger version. 

Name:	Image3.png 
Views:	8 
Size:	8.2 KB 
ID:	15900

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I've modified your problem to make it easier to read/learn. The basic ideas are all present, just presented in a slightly different form to remove some of needless busywork for me to create the sample script.
    Code:
    DECLARE @t TABLE (
       Date_Completed   DATE        NOT NULL
    ,  Contacted        CHAR(1)     NOT NULL
          CHECK (Contacted IN ('T', 'F'))
    ,  foo              CHAR(1)     NOT NULL
          CHECK (foo IN ('E', 'S', 'U'))
    );
    
    INSERT INTO @t (Date_Completed, Contacted, foo) VALUES
       ('2013-12-01', 'T', 'E'), ('2013-12-01', 'T', 'E'), ('2013-12-01', 'T', 'E')
    ,  ('2013-12-01', 'T', 'E')
    ,  ('2014-02-02', 'F', 'U'), ('2014-02-02', 'T', 'S'), ('2014-02-02', 'F', 'U')
    ,  ('2014-02-02', 'F', 'U'), ('2014-02-02', 'F', 'U'), ('2014-02-02', 'T', 'S')
    ,  ('2014-03-03', 'T', 'S'), ('2014-03-03', 'F', 'S'), ('2014-03-03', 'T', 'E')
    ,  ('2014-03-03', 'T', 'S'), ('2014-03-03', 'T', 'S'), ('2014-03-03', 'F', 'S')
    ,  ('2014-04-04', 'T', 'U')
    ,  ('2014-06-02', 'T', 'S'), ('2014-06-02', 'T', 'S'), ('2014-06-03', 'F', 'S')
    ,  ('2014-07-03', 'F', 'S')
    ,  ('2014-08-03', 'T', 'S')
    ,  ('2014-09-03', 'T', 'E')
    ,  ('2015-01-03', 'T', 'E');
    
    SELECT * FROM @t
    
    SELECT bar AS 'Moth Date'
    ,  Sum(Contacted) AS Contacted, Sum(bat) AS 'Not Contacted', Sum(Employed) AS Employed
    ,  Sum(UnEmployed) AS UnEmployed, Sum(SelfEmployed) AS SelfEmployed
       FROM (
    SELECT DateName(month, Date_Completed) + ' ' +Convert(Char(4), Date_Completed, 120) AS bar
    ,  CASE WHEN 'T' = Contacted THEN 1 END AS Contacted
    ,  CASE WHEN 'F' = Contacted THEN 1 END AS bat
    ,  CASE WHEN 'E' = foo THEN 1 END AS Employed
    ,  CASE WHEN 'U' = foo THEN 1 END AS UnEmployed
    ,  CASE WHEN 'S' = foo THEN 1 END AS SelfEmployed
    ,  Date_Completed
       FROM @t
       ) AS z
       GROUP BY bar
       ORDER BY Min(Date_Completed)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If all results in a CASE expression were not true(e.g. December 2013 bat(Sumed AS 'Not Contacted')),
    wasn't the result of Sum null(not 0)?
    (This was true on DB2. But, I don't know on Microsoft SQL Server.)

    If so,
    it might be better to add ELSE 0 for all CASE expressions
    or to use COUNT rather than Sum.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Good thought Tonkuma, but in the image that the OP (marius40) posted the "empty" cells are blank and not zero. Allowing the NULL values to propagate via Sum() is the key to making those empty cells empty instead of showing a zero.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2014
    Posts
    16
    Quote Originally Posted by Pat Phelan View Post
    Good thought Tonkuma, but in the image that the OP (marius40) posted the "empty" cells are blank and not zero. Allowing the NULL values to propagate via Sum() is the key to making those empty cells empty instead of showing a zero.

    -PatP
    I am creating my solution in VB.NET. This is what I came up with.

    Code:
    cmd.CommandText = "     SELECT    CAST(MONTH(QUALIFICATION.[Datecompres] ) AS VARCHAR(2)) + '-' + CAST(YEAR(QUALIFICATION.[Datecompres]) AS VARCHAR(4))  as [Month Year]                                                " & _
                                            ",                                                                          " & _
                                            "Count(CASE                                                                 " & _
                                            "WHEN QUALIFICATION.[Datecompres]                                          " & _
                                            "BETWEEN '" & StartingDate & "'  AND '" & EndingDate & "' THEN 1           " & _
                                            "ELSE 0                                                                     " & _
                                            "END) AS [# Completed]                                                      " & _
                                            ",                                                                          " & _
                                            "SUM(CASE                                                                   " & _
                                            "WHEN STUDENT.[Socioeconomic_Status_Code]  ='01' and                        " & _
                                            "QUALIFICATION.[Datecompres]                                               " & _
                                            "BETWEEN '" & StartingDate & "' and '" & EndingDate & "' THEN 1             " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [EMPLOYED]                                                         " & _
                                            ",                                                                          " & _
                                            "SUM(CASE                                                                   " & _
                                            "WHEN STUDENT.[Socioeconomic_Status_Code]  ='05' and                        " & _
                                            "QUALIFICATION.[Datecompres]                                               " & _
                                            "BETWEEN '" & StartingDate & "' and '" & EndingDate & "' THEN 1             " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [SELF EMPLOYED]                                                    " & _
                                            ",                                                                          " & _
                                            "SUM(CASE                                                                   " & _
                                            "WHEN  (STUDENT.[Socioeconomic_Status_Code]  ='02' or                      " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='03'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='04'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='06'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='07'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='08'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='09'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='10'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='97'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='98'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='U' )  and                            " & _
                                            "QUALIFICATION.[Datecompres]                                                " & _
                                            "BETWEEN '" & StartingDate & "' and '" & EndingDate & "'  THEN 1            " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [UNEMPLOYED]                                                       " & _
                                            "FROM  (STUDENT  JOIN  QUALIFICATION   ON                                   " & _
                                            "QUALIFICATION.[ID] = STUDENT.[ID] )                                        " & _
                                            "where  QUALIFICATION.[Datecompres] BETWEEN '" & StartingDate & "' and '" & EndingDate & "'                                      " & _
                                            "GROUP BY CAST(MONTH(QUALIFICATION.[Datecompres] ) AS VARCHAR(2)) + '-' + CAST(YEAR(QUALIFICATION.[Datecompres]) AS VARCHAR(4))                 " & _
                                           " order by CAST(MONTH(QUALIFICATION.[Datecompres] ) AS VARCHAR(2)) + '-' + CAST(YEAR(QUALIFICATION.[Datecompres]) AS VARCHAR(4))"

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This code looks like it works with a schema that is rather different from your original posted data.

    I'm almost certain that you'll need to either order by the date itself or re-think your date format. I would suggest a YYYY-MM format using:
    Code:
    Convert(CHAR(7), QUALIFICATION.[Datecompres], 121)
    Your proposed code will present zeros instead of blanks for cells with missing data.

    I'd suggest using replacing the OR brigade with an IN ('02', '03', '04', '06', '07', '08', '09', '10', '97', '98', 'U') because it is easier to read and faster to process.

    What happened to the Contacted attribute?

    I can't see any point in including the date range comparison in both the WHERE clause and in the individual CASE conditions.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2014
    Posts
    16
    I am having a problem with joining a third table to this pivot table. I need to count the amount of [Completed] calls, where the [National_id] matches the second table. The Third table looks like this.

    Click image for larger version. 

Name:	Image4.png 
Views:	3 
Size:	18.2 KB 
ID:	15901

    My code is :
    Code:
    cmd.CommandText = "SELECT DateName(month, QUALIFICATION.[Datecompres]) + ' ' +Convert(Char(7), QUALIFICATION.[Datecompres], 121) AS [Month Year]" & _
                                            ",                                                                          " & _
                                            "Count(CASE                                                                 " & _
                                            "WHEN QUALIFICATION.[Datecompres]                                           " & _
                                            "BETWEEN '" & StartingDate & "'  AND '" & EndingDate & "' THEN 1            " & _
                                            "ELSE 0                                                                     " & _
                                            "END) AS [# Completed]                                                      " & _
                                            ",                                                                          " & _
                                            "Count(CASE                                                                 " & _
                                            "WHEN (Callertask1.[Complete] = 'T') THEN 1              " & _ ' This Callertask1.[Complete] refers to Calls that was Completed by the operator                                        "ELSE 0                                                                     " & _
                                            "END) as [Contacted]                                                        " & _
                                            ",                                                                          " & _
                                            "SUM(CASE                                                                   " & _
                                            "WHEN STUDENT.[Socioeconomic_Status_Code]  ='01' and                        " & _
                                            "QUALIFICATION.[Datecompres]                                                " & _
                                            "BETWEEN '" & StartingDate & "' and '" & EndingDate & "' THEN 1             " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [EMPLOYED]                                                         " & _
                                            ",                                                                          " & _
                                            "SUM(CASE                                                                   " & _
                                            "WHEN STUDENT.[Socioeconomic_Status_Code]  ='05' and                        " & _
                                            "QUALIFICATION.[Datecompres]                                                " & _
                                            "BETWEEN '" & StartingDate & "' and '" & EndingDate & "' THEN 1             " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [SELF EMPLOYED]                                                    " & _
                                            ",                                                                          " & _
                                            "SUM(CASE                                                                   " & _
                                            "WHEN  (STUDENT.[Socioeconomic_Status_Code]  ='02' or                      " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='03'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='04'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='06'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='07'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='08'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='09'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='10'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='97'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='98'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='U' )  and                            " & _
                                            "QUALIFICATION.[Datecompres]                                                " & _
                                            "BETWEEN '" & StartingDate & "' and '" & EndingDate & "'  THEN 1            " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [UNEMPLOYED]                                                       " & _
                                            "FROM  (STUDENT  JOIN  QUALIFICATION   ON                                   " & _
                                            "QUALIFICATION.[ID] = STUDENT.[ID] )                                        " & _
                                            "JOIN  CALLERTASK1 ON Callertask1.[national_id] = QUALIFICATION.[national_id]     " & _
                                            "WHERE  QUALIFICATION.[Datecompres] BETWEEN '" & StartingDate & "' and '" & EndingDate & "'                                      " & _
                                            "GROUP BY DateName(month, QUALIFICATION.[Datecompres]) + ' ' +Convert(Char(7), QUALIFICATION.[Datecompres], 121)                " & _
                                           " ORDER BY Min(QUALIFICATION.[Datecompres])  "
    The result on my Datagrid:
    Click image for larger version. 

Name:	Image5.png 
Views:	2 
Size:	21.7 KB 
ID:	15902

    As you can see the Contacted and Completed are the same count.
    What am I missing?
    Do I need another condition?
    or is the Join incorrect?

  8. #8
    Join Date
    Jan 2014
    Posts
    16
    I FOUND THE ERROR

    It was my blindness... COUNT will give the same , I should use SUM ... lol

    Code:
     ",                                                                          " & _
                                            "SUM(CASE                                                                 " & _
                                            "WHEN (Callertask1.[Complete] = 'T') and                                     " & _
                                            "QUALIFICATION.[Datecompres]                                                " & _
                                            "BETWEEN '" & StartingDate & "' and '" & EndingDate & "' THEN 1             " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [Contacted]                                                        " & _
                                            ",                                                                          " & _

  9. #9
    Join Date
    Jan 2014
    Posts
    16
    Can anyone help me to make this shorter... It is giving the correct results, but is just toooo long

    Code:
     cmd.CommandText = "  SELECT DateName(month, QUALIFICATION.[Datecompres]) + ' ' +Convert(Char(7), QUALIFICATION.[Datecompres], 121) AS [Month Year]" & _
                                            ",                                                                          " & _
                                            " count(*) as [# Completed]                                                 " & _
                                            ",                                                                          " & _
                                            "sum(CASE                                                                 " & _
                                            "WHEN (Callertask1.[Complete] = 'T') and                                     " & _
                                            "QUALIFICATION.[Datecompres]                                                " & _
                                            "BETWEEN '" & StartingDate & "' and '" & EndingDate & "' THEN 1             " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [Contacted]                                                        " & _
                                            ",                                                                          " & _
                                            "SUM(CASE                                                                   " & _
                                            "WHEN STUDENT.[Socioeconomic_Status_Code]  ='01' and                        " & _
                                            "QUALIFICATION.[Datecompres]                                                " & _
                                            "BETWEEN '" & StartingDate & "' and '" & EndingDate & "' THEN 1             " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [EMPLOYED]                                                         " & _
                                            ",                                                                          " & _
                                            "SUM(CASE                                                                   " & _
                                            "WHEN STUDENT.[Socioeconomic_Status_Code]  ='05' and                        " & _
                                            "QUALIFICATION.[Datecompres]                                                " & _
                                            "BETWEEN '" & StartingDate & "' and '" & EndingDate & "' THEN 1             " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [SELF EMPLOYED]                                                    " & _
                                            ",                                                                          " & _
                                            "SUM(CASE                                                                   " & _
                                            "WHEN  (STUDENT.[Socioeconomic_Status_Code]  ='02' or                      " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='03'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='04'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='06'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='07'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='08'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='09'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='10'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='97'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='98'  or                            " & _
                                            "STUDENT.[Socioeconomic_Status_Code]  ='U' )  and                            " & _
                                            "QUALIFICATION.[Datecompres]                                                " & _
                                            "BETWEEN '" & StartingDate & "' and '" & EndingDate & "'  THEN 1            " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [UNEMPLOYED]                                                       " & _
                                            "FROM  (STUDENT  JOIN  QUALIFICATION   ON                                   " & _
                                            "QUALIFICATION.[ID] = STUDENT.[ID] )                                        " & _
                                            "JOIN  CALLERTASK1 ON Callertask1.[id] = student.[id]     " & _
                                            "WHERE  QUALIFICATION.[Datecompres] BETWEEN '" & StartingDate & "' and '" & EndingDate & "'                                      " & _
                                            "GROUP BY DateName(month, QUALIFICATION.[Datecompres]) + ' ' +Convert(Char(7), QUALIFICATION.[Datecompres], 121)                " & _
                                           " ORDER BY Min(QUALIFICATION.[Datecompres])  "

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try the suggestions from my previous post. If that doesn't help enough, show what your code looks like after you applied them.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Jan 2014
    Posts
    16
    I used the IN clause and made use of parameters.. and also removed the BETWEEN that was not needed.

    Any thing else I can do?
    Code:
    cmd.CommandText = "  SELECT DateName(month, QUALIFICATION.[Datecompres]) + ' ' +Convert(Char(4), QUALIFICATION.[Datecompres], 121) AS [Month Year]" & _
                                            ",                                                                          " & _
                                            " count(*) as [# Completed]                                                 " & _
                                            ",                                                                          " & _
                                            "sum(CASE                                                                   " & _
                                            "WHEN (Callertask1.[Complete] = 'T') THEN 1                                 " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [Contacted]                                                        " & _
                                            ",                                                                          " & _
                                            "SUM(CASE                                                                   " & _
                                            "WHEN STUDENT.[Socioeconomic_Status_Code]  ='01' THEN 1                     " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [EMPLOYED]                                                         " & _
                                            ",                                                                          " & _
                                            "SUM(CASE                                                                   " & _
                                            "WHEN STUDENT.[Socioeconomic_Status_Code]  ='05'  THEN 1                    " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [SELF EMPLOYED]                                                    " & _
                                            ",                                                                          " & _
                                            "SUM(CASE                                                                   " & _
                                            "WHEN  STUDENT.[Socioeconomic_Status_Code]                                 " & _
                                            "IN ('02','03','04','06','07','08','09','10','97','98' ,'U')    THEN 1      " & _
                                            "ELSE 0                                                                     " & _
                                            "END) as [UNEMPLOYED]                                                       " & _
                                            "                                                                           " & _
                                            "FROM  (STUDENT  JOIN  QUALIFICATION   ON                                   " & _
                                            "QUALIFICATION.[ID] = STUDENT.[ID] )                                        " & _
                                            "JOIN  CALLERTASK1 ON Callertask1.[id] = student.[id]                       " & _
                                            "WHERE  QUALIFICATION.[Datecompres] BETWEEN  @DateStart and @EndDate        " & _
                                            "GROUP BY DateName(month, QUALIFICATION.[Datecompres]) + ' ' +Convert(Char(4), QUALIFICATION.[Datecompres], 121)                " & _
                                           " ORDER BY Min(QUALIFICATION.[Datecompres])  "
    
            cmd.Parameters.AddWithValue("@DateStart", StartingDate)
            cmd.Parameters.AddWithValue("@EndDate", EndingDate)

  12. #12
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    If you still trying to use the initial table structure with each of the attributes defines as a different field, the following should give you your results without the hassle of a pivot. Replace the #tmpData with your table name.

    Code:
    SELECT DATENAME(MONTH,Date_Completed) + ' ' + CONVERT(varchar,YEAR(Date_Completed)) AS Date_Completed,
    SUM(CASE WHEN ISNULL(Contacted,'') = 'T' THEN 1 END) AS Contacted,
    SUM(CASE WHEN ISNULL(Employed,'') = 'T' THEN 1 END) AS Employed,
    SUM(CASE WHEN ISNULL(Unemployed,'') = 'T' THEN 1 END) AS Unemployed,
    SUM(CASE WHEN ISNULL(SelfEmployed,'') = 'T' THEN 1 END) AS SelfEmployed
    FROM #tmpData
    GROUP BY DATENAME(MONTH,Date_Completed) + ' ' + CONVERT(varchar,YEAR(Date_Completed)),YEAR(Date_Completed), MONTH(Date_Completed)
    ORDER BY YEAR(Date_Completed), MONTH(Date_Completed)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •