Query help

28 Dec 2005 8:41 PM

Confused

I need a grouping and I'm not quite sure how to group it...an example
of my current query and the returned data is below. After that is more
detail about how I need to group it. Thank you!

SELECT ct1.Description AS Exemption, ISNULL( ej.TotalPeople,0) AS
NumPeople, ct1.Code AS Code,
    Web =       -- if it's a web exemption, show 'X' in report
    CASE SUBSTRING(ct1.Code, 1, 2)
        WHEN 'EX' THEN NULL
        WHEN 'WE' THEN 'X'
    END
FROM    Table1 ct1 WITH (NOLOCK)
LEFT OUTER JOIN
    (SELECT e.DiffCode,SUM(CASE WHEN j.HCNbr IS NULL THEN 0 ELSE 1 END)
AS TotalPeople
     FROM PeopleTable j WITH (NOLOCK)
       INNER JOIN Excuses e WITH (NOLOCK)
    ON j.HCNbr = e.HCNbr
       INNER JOIN JISSessiON srvs WITH (NOLOCK)
    ON srvs.SessionID = j.SvcSessionId
    AND srvs.SessionID = j.CalledSessionId
    AND srvs.SessionDt BETWEEN @StartDt AND @EndDt
    AND srvs.RoomID =@RoomId
      GROUP BY e.DiffCode) ej
ON ct1.Code = ej.DiffCode
WHERE ct1.codetype IN ('A', 'B', 'C', 'D')
AND ct1.Code <> 'Reason1' AND ct1.Code <> 'Reason2'
AND ej.TotalPeople > 0
ORDER BY NewColumn, ct1.Description

Result set looks like this:
Exemption              NumPeople     Code            Web
Age                           72               Reason3       NULL
Children <10              35                Reason5       NULL
Have child under 10    15                Reason14      X
Over 70                     42                Reason 9      NULL
Language                  95                Reason11      NULL
Able to read and write 17                Reason28      X

What I need to do with the above is combine (using SQL Reporting
Services for displaying output) the similar Exemptions without totaling
them, so I'd want something like the below:

Exemption       NumPeople        NumPeoplefromWeb           Total
Age                  72                         -
               -
Children < 10    35                         15
         50
Over 70            42                          -
              -
Language          95                         17
         112

I was able to group them so that similar exemptions are listed
together. Is this something that I can resolve through a query or does
it need to be done in my report itself using visibility, etc?

Thank you!

28 Dec 2005 9:05 PM

Ryan Powers

Is NewColumn in Table1? Do similar exemptions have the same value in this
column?

I'm thinking you will be able to do it in the query if you want.

Show quote

"Confused" wrote:

> I need a grouping and I'm not quite sure how to group it...an example
> of my current query and the returned data is below. After that is more
> detail about how I need to group it. Thank you!
>
> SELECT ct1.Description AS Exemption, ISNULL( ej.TotalPeople,0) AS
> NumPeople, ct1.Code AS Code,
>     Web =       -- if it's a web exemption, show 'X' in report
>     CASE SUBSTRING(ct1.Code, 1, 2)
>         WHEN 'EX' THEN NULL
>         WHEN 'WE' THEN 'X'
>     END
> FROM    Table1 ct1 WITH (NOLOCK)
>   LEFT OUTER JOIN
>     (SELECT e.DiffCode,SUM(CASE WHEN j.HCNbr IS NULL THEN 0 ELSE 1 END)
> AS TotalPeople
>      FROM PeopleTable j WITH (NOLOCK)
>        INNER JOIN Excuses e WITH (NOLOCK)
>     ON j.HCNbr = e.HCNbr
>        INNER JOIN JISSessiON srvs WITH (NOLOCK)
>     ON srvs.SessionID = j.SvcSessionId
>     AND srvs.SessionID = j.CalledSessionId
>     AND srvs.SessionDt BETWEEN @StartDt AND @EndDt
>     AND srvs.RoomID =@RoomId
>       GROUP BY e.DiffCode) ej
>   ON ct1.Code = ej.DiffCode
> WHERE ct1.codetype IN ('A', 'B', 'C', 'D')
>   AND ct1.Code <> 'Reason1' AND ct1.Code <> 'Reason2'
>   AND ej.TotalPeople > 0
> ORDER BY NewColumn, ct1.Description
>
> Result set looks like this:
> Exemption              NumPeople     Code            Web
> Age                           72               Reason3       NULL
> Children <10              35                Reason5       NULL
> Have child under 10    15                Reason14      X
> Over 70                     42                Reason 9      NULL
> Language                  95                Reason11      NULL
> Able to read and write 17                Reason28      X
>
>
> What I need to do with the above is combine (using SQL Reporting
> Services for displaying output) the similar Exemptions without totaling
> them, so I'd want something like the below:
>
> Exemption       NumPeople        NumPeoplefromWeb           Total
> Age                  72                         -
>                -
> Children < 10    35                         15
>          50
> Over 70            42                          -
>               -
> Language          95                         17
>          112
>
>
> I was able to group them so that similar exemptions are listed
> together. Is this something that I can resolve through a query or does
> it need to be done in my report itself using visibility, etc?
>
> Thank you!
>
>

28 Dec 2005 9:14 PM

Confused

Well, actually, NewColumn was something I was playing around with and
made up. I just forgot to take it out of the group by statement. What
I had done was the below:

NewColumn =   --set this so the ones to be combined will be together
    CASE ct1.Code
        WHEN 'EXEL' THEN '1'
        WHEN 'EXPF' THEN '2'
        WHEN 'EXENC' THEN '3'
        WHEN 'EXEMO' THEN '4'
        WHEN 'EXESM' THEN '5'
        WHEN 'WEBQE' THEN '1a'
        WHEN 'WEBQG' THEN '2a'
        WHEN 'WEBQC' THEN '3a'
        WHEN 'WEBQB' THEN '4a'
        WHEN 'WEBQD' THEN '5a'
        ELSE NULL
    END

I also took out the 'a' from the last five cases. Originally I thought
they were just supposed to be grouped next to each other.

28 Dec 2005 9:36 PM

Ryan Powers

I jumped the gun a bit on my next post.

Based on this answer. What I did won't work exactly right.

The problem is that there must be some way within the data to be able to
identify which exemptions are similar. Without it, I'm not sure what you can
do.

I think you could do something to combine this NewColumn idea and what I
posted already.

What do you think?

Show quote

"Confused" wrote:

> Well, actually, NewColumn was something I was playing around with and
> made up. I just forgot to take it out of the group by statement. What
> I had done was the below:
>
> NewColumn =   --set this so the ones to be combined will be together
>     CASE ct1.Code
>         WHEN 'EXEL' THEN '1'
>         WHEN 'EXPF' THEN '2'
>         WHEN 'EXENC' THEN '3'
>         WHEN 'EXEMO' THEN '4'
>         WHEN 'EXESM' THEN '5'
>         WHEN 'WEBQE' THEN '1a'
>         WHEN 'WEBQG' THEN '2a'
>         WHEN 'WEBQC' THEN '3a'
>         WHEN 'WEBQB' THEN '4a'
>         WHEN 'WEBQD' THEN '5a'
>         ELSE NULL
>     END
>
> I also took out the 'a' from the last five cases. Originally I thought
> they were just supposed to be grouped next to each other.
>
>

28 Dec 2005 9:45 PM

Confused

First, you are correct in saying that the "similar" exemptions are
artificial - there is nothing that connects them in any way except for
a person saying they are the same. Unfortunately, I have no control
over the database, so what is there is what I have to work with. I
looked at the data for identifying exemptions which are similar and it
can only sort-of be done, which is a problem.

I am looking at the query you sent and trying to figure out how the
NewColumn would/could work with it. What you sent is definitely
helpful - I appreciate it!

28 Dec 2005 9:56 PM

Ryan Powers

Cool.

Let me know what you come up with. Or if there is anything more I can try
to help with.

Good luck.

Show quote

"Confused" wrote:

> First, you are correct in saying that the "similar" exemptions are
> artificial - there is nothing that connects them in any way except for
> a person saying they are the same. Unfortunately, I have no control
> over the database, so what is there is what I have to work with. I
> looked at the data for identifying exemptions which are similar and it
> can only sort-of be done, which is a problem.
>
> I am looking at the query you sent and trying to figure out how the
> NewColumn would/could work with it. What you sent is definitely
> helpful - I appreciate it!
>
>

29 Dec 2005 9:40 PM

Confused

Found a clunky, klugy, yet workable, solution:

I ended up creating a table that "associated" the similar exemptions as
below.
DECLARE @table1 TABLE
(ExemptName varchar(50), nonwebcode varchar(20), webcode varchar(20))

INSERT INTO @table1 (ExemptName, nonwebcode, webcode)
VALUES ('Children < 10', 'Reason 5', 'Reason14')

Next, I queried for my nonwebcode exemption totals and returned those
in @table2
Third, I queried for my webcode exemption totals and returned those in
@table3

Finally, I joined the 3 temp tables:

SELECT t1.ExemptName, ISNULL(t2.totalExempt, 0) AS NonWebTotal,
ISNULL(t3.totalExempt, 0) AS WebTotal, ISNULL(t2.Web, t3.Web) AS
WebFlag
FROM @table1 t1
LEFT OUTER JOIN @table2 t2 ON t1.nonwebcode = t2.code
LEFT OUTER JOIN @table3 t3 ON t1.webcode = t3.code
WHERE ...

It's not great, but it works! Thank you, Ryan, for all of your input!

28 Dec 2005 9:24 PM

Ryan Powers

I'm going to assume that NewColumn is a column in Table1 that has the same
value for similar exemptions. I am also assuming that exemptions with
different names do not share the same code. The thing that makes this tricky
is that the exemptions have nothing about them that tells them they are
similar. The similar part seems to be somewhat "artificial". So, some
schema changes would make it simpler. But, I think the following would work
for your exact problem. Basically, I just did your same join a second time
to get the second column to work with and group by the only thing that knows
about similar execmption. It is not very pretty, but I think it or something
very close to it would work. Hope it helps.

SELECT MIN(ct1.Description) AS Exemption, --use first alpha exemption name
SUM( ISNULL(ej.TotalPeople,0)) AS NumPeople,
SUM( ISNULL(ej2.TotalPeople,0)) AS NumPeopleFromWeb,
SUM( ISNULL(ej.TotalPeople,ej2.TotalPeople)) AS TotalNumPeople,
FROM    Table1 ct1 WITH (NOLOCK)
LEFT OUTER JOIN
    (SELECT e.DiffCode,SUM(CASE WHEN j.HCNbr IS NULL THEN 0 ELSE 1 END)
AS TotalPeople
     FROM PeopleTable j WITH (NOLOCK)
       INNER JOIN Excuses e WITH (NOLOCK)
    ON j.HCNbr = e.HCNbr
       INNER JOIN JISSessiON srvs WITH (NOLOCK)
    ON srvs.SessionID = j.SvcSessionId
    AND srvs.SessionID = j.CalledSessionId
    AND srvs.SessionDt BETWEEN @StartDt AND @EndDt
    AND srvs.RoomID =@RoomId
      WHERE SUBSTRING(e.DiffCode, 1, 2) = 'EX'
      GROUP BY e.DiffCode) ej
ON ct1.Code = ej.DiffCode
LEFT OUTER JOIN
    (SELECT e.DiffCode,SUM(CASE WHEN j.HCNbr IS NULL THEN 0 ELSE 1 END)
AS TotalPeople
     FROM PeopleTable j WITH (NOLOCK)
       INNER JOIN Excuses e WITH (NOLOCK)
    ON j.HCNbr = e.HCNbr
       INNER JOIN JISSessiON srvs WITH (NOLOCK)
    ON srvs.SessionID = j.SvcSessionId
    AND srvs.SessionID = j.CalledSessionId
    AND srvs.SessionDt BETWEEN @StartDt AND @EndDt
    AND srvs.RoomID =@RoomId
      WHERE SUBSTRING(e.DiffCode, 1, 2) = 'WE'
      GROUP BY e.DiffCode) ej2
ON ct1.Code = ej2.DiffCode
WHERE ct1.codetype IN ('A', 'B', 'C', 'D')
AND ct1.Code <> 'Reason1' AND ct1.Code <> 'Reason2'
AND ISNULL(ej.TotalPeople,ej2.TotalPeople) > 0
GROUP BY NewColumn
ORDER BY NewColumn, ct1.Description

Show quote

"Confused" wrote:

> I need a grouping and I'm not quite sure how to group it...an example
> of my current query and the returned data is below. After that is more
> detail about how I need to group it. Thank you!
>
> SELECT ct1.Description AS Exemption, ISNULL( ej.TotalPeople,0) AS
> NumPeople, ct1.Code AS Code,
>     Web =       -- if it's a web exemption, show 'X' in report
>     CASE SUBSTRING(ct1.Code, 1, 2)
>         WHEN 'EX' THEN NULL
>         WHEN 'WE' THEN 'X'
>     END
> FROM    Table1 ct1 WITH (NOLOCK)
>   LEFT OUTER JOIN
>     (SELECT e.DiffCode,SUM(CASE WHEN j.HCNbr IS NULL THEN 0 ELSE 1 END)
> AS TotalPeople
>      FROM PeopleTable j WITH (NOLOCK)
>        INNER JOIN Excuses e WITH (NOLOCK)
>     ON j.HCNbr = e.HCNbr
>        INNER JOIN JISSessiON srvs WITH (NOLOCK)
>     ON srvs.SessionID = j.SvcSessionId
>     AND srvs.SessionID = j.CalledSessionId
>     AND srvs.SessionDt BETWEEN @StartDt AND @EndDt
>     AND srvs.RoomID =@RoomId
>       GROUP BY e.DiffCode) ej
>   ON ct1.Code = ej.DiffCode
> WHERE ct1.codetype IN ('A', 'B', 'C', 'D')
>   AND ct1.Code <> 'Reason1' AND ct1.Code <> 'Reason2'
>   AND ej.TotalPeople > 0
> ORDER BY NewColumn, ct1.Description
>
> Result set looks like this:
> Exemption              NumPeople     Code            Web
> Age                           72               Reason3       NULL
> Children <10              35                Reason5       NULL
> Have child under 10    15                Reason14      X
> Over 70                     42                Reason 9      NULL
> Language                  95                Reason11      NULL
> Able to read and write 17                Reason28      X
>
>
> What I need to do with the above is combine (using SQL Reporting
> Services for displaying output) the similar Exemptions without totaling
> them, so I'd want something like the below:
>
> Exemption       NumPeople        NumPeoplefromWeb           Total
> Age                  72                         -
>                -
> Children < 10    35                         15
>          50
> Over 70            42                          -
>               -
> Language          95                         17
>          112
>
>
> I was able to group them so that similar exemptions are listed
> together. Is this something that I can resolve through a query or does
> it need to be done in my report itself using visibility, etc?
>
> Thank you!
>
>