Group by

I have a table defined with the following field names.
TableID Varchar(20)
CrcNbr Int
RegionName Varchar(25)
Sample Data:
--
pr_mstr,11500,Test
pr_mstr,11500,Trng
prd_det,12000,Test
prd_det,12005,Trng
prd_det,12005,Prod
I want to produce a report from this data that gives me this info. Saying
In what Regions does the table exist and are there a difference in the
CrcNbr's.
Table-Name Test Trng Prod Test/Trng Crc
Trng/Prod Crc
pr_mstr Y Y N Match
If 'N' under Prod tab leave this blank
prd_det Y Y Y Test Doesn't match Trng
MatchHi
It is usually better to do this one the client, but if not you can self join
the table and use case to determine the Ys or Ns such as:
SELECT T.Name,
CASE WHEN D1.RegionName IS NULL THEN 'N' ELSE 'Y' END AS Test,
CASE WHEN D2.RegionName IS NULL THEN 'N' ELSE 'Y' END AS Trng,
CASE WHEN D3.RegionName IS NULL THEN 'N' ELSE 'Y' END AS Prod,
CASE WHEN D1.CrcNbr <> D2.CrcNbr AND D1.CrcNbr <> D3.CrcNbr AND D3.CrcNbr <>
D2.CrcNbr THEN 'No Matches'
WHEN D1.CrcNbr <> D2.CrcNbr AND D1.CrcNbr <> D3.CrcNbr AND D3.CrcNbr =
D2.CrcNbr THEN 'Trng Matches Prod'
...
END AS [Crc Checks]
FROM MyTables T
LEFT JOIN MyData d1 on T.TableId = D1.TableId AND D1.RegionName = 'Test'
LEFT JOIN MyData d2 on T.TableId = D2.TableId AND D2.RegionName = 'Trng'
LEFT JOIN MyData d3 on T.TableId = D2.TableId AND D3.RegionName = 'Prod'
John
"Hoosbruin" wrote:

> I have a table defined with the following field names.
> TableID Varchar(20)
> CrcNbr Int
> RegionName Varchar(25)
> Sample Data:
> --
> pr_mstr,11500,Test
> pr_mstr,11500,Trng
> prd_det,12000,Test
> prd_det,12005,Trng
> prd_det,12005,Prod
>
> I want to produce a report from this data that gives me this info. Saying
> In what Regions does the table exist and are there a difference in the
> CrcNbr's.
> Table-Name Test Trng Prod Test/Trng Crc
> Trng/Prod Crc
> pr_mstr Y Y N Match
> If 'N' under Prod tab leave this blank
> prd_det Y Y Y Test Doesn't match Trng
> Match
>
>
>
>|||hi,
Select TableName,
Max (Case When RegionName = 'Test' Then 'Y' Else 'N' End) As Test,
Max (Case When RegionName = 'Trng' Then 'Y' Else 'N' End) As Trng,
Max (Case When RegionName = 'Prod' Then 'Y' Else 'N' End) As Prod,
Case When Sum (Case When RegionName = 'Test' Then CrcNbr
When RegionName = 'Trng' Then -1 * CrcNbr
Else 0
End) = 0 Then 'Match'
When Sum (Case When RegionName = 'Test' Then 1
When RegionName = 'Trng' Then 1
Else 0
End) = 2 Then 'NoMatch'
Else ''
End As 'Test/Trng',
Case When Sum (Case When RegionName = 'Prod' Then CrcNbr
When RegionName = 'Trng' Then -1 * CrcNbr
Else 0
End) = 0 Then 'Match'
When Sum (Case When RegionName = 'Prod' Then 1
When RegionName = 'Trng' Then 1
Else 0
End) = 2 Then 'NoMatch'
Else ''
End As 'Prod/Trng'
From <YourTable>
Group by TableName
"Hoosbruin" <Hoosbruin@.Kconline.com> wrote in message
news:TKudnS3FiNW6jkLfRVn-3w@.kconline.com...
>I have a table defined with the following field names.
> TableID Varchar(20)
> CrcNbr Int
> RegionName Varchar(25)
> Sample Data:
> --
> pr_mstr,11500,Test
> pr_mstr,11500,Trng
> prd_det,12000,Test
> prd_det,12005,Trng
> prd_det,12005,Prod
>
> I want to produce a report from this data that gives me this info. Saying
> In what Regions does the table exist and are there a difference in the
> CrcNbr's.
> Table-Name Test Trng Prod Test/Trng Crc Trng/Prod Crc
> pr_mstr Y Y N Match If 'N' under Prod tab
> leave this blank
> prd_det Y Y Y Test Doesn't match Trng
> Match
>
>
>
>|||Thanks...
worked GREAT !!!!!!
"arik" <arikf@.top4.com> wrote in message
news:OmQytdcjFHA.3580@.TK2MSFTNGP09.phx.gbl...
> hi,
> Select TableName,
> Max (Case When RegionName = 'Test' Then 'Y' Else 'N' End) As Test,
> Max (Case When RegionName = 'Trng' Then 'Y' Else 'N' End) As Trng,
> Max (Case When RegionName = 'Prod' Then 'Y' Else 'N' End) As Prod,
> Case When Sum (Case When RegionName = 'Test' Then CrcNbr
> When RegionName = 'Trng' Then -1 * CrcNbr
> Else 0
> End) = 0 Then 'Match'
> When Sum (Case When RegionName = 'Test' Then 1
> When RegionName = 'Trng' Then 1
> Else 0
> End) = 2 Then 'NoMatch'
> Else ''
> End As 'Test/Trng',
> Case When Sum (Case When RegionName = 'Prod' Then CrcNbr
> When RegionName = 'Trng' Then -1 * CrcNbr
> Else 0
> End) = 0 Then 'Match'
> When Sum (Case When RegionName = 'Prod' Then 1
> When RegionName = 'Trng' Then 1
> Else 0
> End) = 2 Then 'NoMatch'
> Else ''
> End As 'Prod/Trng'
> From <YourTable>
> Group by TableName
>
> "Hoosbruin" <Hoosbruin@.Kconline.com> wrote in message
> news:TKudnS3FiNW6jkLfRVn-3w@.kconline.com...
>