The title sounds like something out of CSI or Law & Order SVU…

Today our Development Manager was inquiring about what ESUs have been applied and to what environments.

Here is the SQL that I came up with:

Note: I restricted this query to only ESUs that were applied after our "Fix Current" project completed on 106041 (Feb. 10, 2006).  Otherwise, there would be more listings than I could handle.  Also, you’ll find the little trick to convert the JDE 6 digit date to something more readable.

SELECT SDPKGNAME as ESU,
   date(char(Min(Case when SDSUDFUT2='PS811' then SDSUDATE end)+1900000)) as PS811,
   date(char(Min(Case when SDSUDFUT2 = 'DV811' then SDSUDATE end)+1900000)) as DV811,
   date(char(Min(Case when SDSUDFUT2 = 'PY811' then SDSUDATE end)+1900000)) as PY811,
   date(char(Min(Case when SDSUDFUT2 = 'PD811' then SDSUDATE end)+1900000)) as PD811
FROM SY811/F9671, SY811/F9670
WHERE (SDSUDET = '90')
   and (SUSUDATE > 106041)
   and (SDPKGNAME=SUPKGNAME)
GROUP BY SDPKGNAME
ORDER BY SDPKGNAME DESC 

.csharpcode, .csharpcode pre
{
/*font-size: small;*/
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }