Add Subquery As A Column

The SQL below adds aggregated columns to a query by using sub queries. The key to using this type of query is to join the inner and outer tables on the correct field.

Use the Instant SQL Formatter to Format the SQL below.  Just copy and paste into the Formatter at: http://www.dpriver.com/pp/sqlformat.htm

SELECT DISTINCT a.eeo_cls,

                a.classification,

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[hispanic or latino] = 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[hispanic or latino] = 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[white] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[black or african american] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-W',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[black or african american] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-B',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[asian] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-A',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[native hawaiian or other pacific islander] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[american indian or alaska native] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-I',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[white] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[black or african american] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-W',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[black or african american] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-B',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[asian] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-A',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[native hawaiian or other pacific islander] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[american indian or alaska native] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-I',

                (SELECT Count(C.prem_emp)

                 FROM   #ee05_multiple C

                 WHERE  C.prem_gender = 'M'

                        AND C.eeo_cls = a.eeo_cls) AS 'M-O',

                (SELECT Count(C.prem_emp)

                 FROM   #ee05_multiple C

                 WHERE  C.prem_gender = 'F'

                        AND C.eeo_cls = a.eeo_cls) AS 'F-O'

FROM   #ee05temp a

       INNER JOIN #ee05temp b

               ON a.prem_emp = b.prem_emp

       INNER JOIN #ee05_multiple

               ON a.prem_emp = b.prem_emp

GROUP  BY a.classification,

          a.eeo_cls

ORDER  BY a.eeo_cls 

SQL Help

This is a pretty good website for coders: Code Project.  I use mainly for SQL information and they have a cool visual representation for SQL Joins.

PowerShell Copy and Rename Files

# Define variables.

$Source = "C:\Test\*.*"

$Destination = "C:\Testing\"

$DestinationFiles = "C:\Testing\*.*"



# Create the $sourceFileList variable to loop through

$sourceFileList = Get-ChildItem -path $Source



# Loop through the $soureFileList and copy the items to the $Destination.

foreach ($item in $sourceFileList)

{

    Copy-Item -Path $Source -Destination $Destination

}



# Create the $destinationFileList variable to loop through

$destinationFileList = Get-ChildItem -path $DestinationFiles



# Loop through the $destinationFileList and rename the files with appended DateTime stamp.

foreach ($itemDest in $destinationFileList)

{

    $Date = (Get-Date).ToString("yyyyMMdd_HHmmss")

    $newFileName = $Date + "_" + $itemDest.Name

    Rename-Item $itemDest -NewName $newFileName

}

T-SQL ESCAPE for Querying Underscores

In T-SQL the underscore is a special character when querying/filtering records. 





The following code will return all records and not only those with an underscore because the underscore acts like a wildcard if not escaped.




Select * from TableName

Where ColumnName Like '%_%'




Using ESCAPE in the query will make the special character such as the underscore a literate character.  The following code will return only those records that contain an underscore.




Select * from TableName

Where ColumnName Like '%\_% ESCAPE '\'




More information about ESCAPE

Change File Name with Powershell

Recently I began backing up photos from 15+ year old CD and DVDs to an external hard drive.  One problem I came across was the created date changed to the date I copied the file, but the modified date stayed in tact.  This is important for me because of the way I name and archive my photos.  I use a program titled NAMEXIF to change most of my photo names, but it would not work because a lot of my old photos do not have EXIF data so I had to find a different solution.  In doing some research I found out that you can accomplish the name change of photos/files from powershell. 



First I had to create a directory to place the files I wanted to change.  I copied over the pictures from the original directory into a Testing directory named C:\Test.  This is not necessary, if you are confident in the script, but I wanted to ensure I didn't mess up anything the first time since I haven't used Powershell much. 



1. Copy files into the Test directory.



In Powershell


2. Use the following code to get to the correct directory so you can run the script below: cd C:\Test



3. Run the following Powershell script:

Get-ChildItem *.jpg | Rename-Item -newname {$_.LastWriteTime.toString("yyyy-MM-dd-HH-mm-ss") + ".jpg"} 

Pass SSRS Field Values to URL

Recently a coworker wanted to pass a field value (ID) to a URL in order to go directly to a specific Web Page from an SSRS report.  This can be done in SSRS by creating a dynamic expression in the Action section of the Text Box or Item that will be clicked.  If you choose the Go To URL option you can create an expression that will do the job. Below is an example.

Dynamic expression that simply concatenates a URL with the value of a field in the report.  In this example the name of the field is ID.  

="http://www.somedomain.com/id="&Fields!ID.Value

This example uses javascript to open a new window. 

Notice: The format is different. When using javascript you have to use + instead of & to concatenate. 

="javascript:void(window.open('http://ritter.tea.state.tx.us/peims/standards/weds/index.html?"+ Fields!ElementID.Value + "','_blank'))"

This example opens a new window and uses the Right Function on the Field Value

="javascript:void(window.open('http://ritter.tea.state.tx.us/peims/standards/weds/index.html?r"+ Right(Fields!PEIMSRecord.Value,3)+ "','_blank'))"

This example opens a new window and grabs the url from a field within a database table.

="javascript:void(window.open('"+Fields!RefURL.Value+"','_blank'))"

Contributors: Mark Lansdon, Trey Lawyer

SSRS - Top Directory

Below is a script that can be used to strip out the '/' from the SSRS Path so you can display the Top Folder/Directory.

Replace(Left(Catalog.Path,CHARINDEX('/',Catalog.Path,2)-1),'/','') As Top_Folder

The following query will return information regarding subscriptions located on your SQL Server.

SELECT catalog.NAME,
       catalog.type,
       catalog.description,
       catalog.creationdate,
       catalog.modifieddate,
       subscriptions.laststatus,
       subscriptions.lastruntime,
       users.username,
       catalog.path,
       Replace(LEFT(catalog.path, Charindex('/', catalog.path, 2) - 1), '/', '')
       AS
       Top_Directory
FROM   catalog
       INNER JOIN subscriptions
               ON catalog.itemid = subscriptions.report_oid
       LEFT OUTER JOIN users
                    ON catalog.createdbyid = users.userid
                       AND catalog.modifiedbyid = users.userid
                       AND subscriptions.modifiedbyid = users.userid
                       AND subscriptions.ownerid = users.userid
WHERE  subscriptions.lastruntime >= '8/1/2015';