TSQL - Union, Union All, Order By

I needed to do a pick list in an application that was a combined list from two different SQL tables. One table contains the fields ID, ProjectNo, and ProjectTitle. The other table contains the fields ID, TCode, and Description.

To begin with, for the pick-list to work I needed to have distinct values for the ID fields from the two different tables. I accomplished this by having the starting value for the ID field for one of the tables start at a value that I knew would always be higher than the other table.

The next thing that was needed was an SQL view to return the list. The list had to have the values for each table sorted but not mixed with each other. In other words my list had to be table A sorted plus table B sorted, not table A + B sorted.

What finaly gave me the results I needed was:

SELECT top 10000 ID, ProjectNo AS Value, ProjectTitle AS DescValue
FROM dbo.Project
order by Value
)Tab1
union all
select * from(
select top 10000 ID, TCode as Value, Description as DescValue
from Department
order by Value
)Tab2

There is quite a difference between doing a Union All vs. Union. Give it a try and see what you get.

I also tried these:

SELECT ID, ProjectNo AS Value, ProjectTitle AS DescValue
FROM dbo.Project
union 
select ID, TCode as Value, Description as DescValue
from Department
order by Value

SELECT ID, ProjectNo, ProjectTitle
FROM dbo.Project
group by ID, ProjectNo, ProjectTitle
union 
select ID, TCode, Description
from Department
group by ID, TCode, Description
order by 2 asc

SQL Dynamic Pivot Tables, BIRT Cross Tab Report

The day was spent working on getting a cross tab report in BIRT. It was a series of two steps forward and one step back, but at the end of the day it was done! Here's how I did it.

Here is a sample of the data in the table:

The report needs to do a monthly sum of the amounts for each Region/Office where the documentation is not complete. I started out by doing a regular pivot table in SQL. This worked fine but then I realized that my months and years would be changing so what I needed was a dynamic pivot table. Using this example from Kodyaz, I came up with this dynamic pivot table:

DECLARE @PivotColumnHeaders VARCHAR(Max)
--Create a list for the column names
--This list will be used in our PIVOT down below
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + [Month] + ']',
    '[' + [Month] + ']'
  )
FROM (select distinct top 50  datename(month, [dtAccountingPeriod]) + ' ' + cast(YEAR([dtAccountingPeriod]) AS varchar) as [Month]
, YEAR([dtAccountingPeriod]) AS IDXY, Month([dtAccountingPeriod]) AS IDXM
From[INformV2].[dbo].[FoAccountingDocumentAudit]
where [DocumentationComplete] = 0
order by IDXY, IDXM) TTD

DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
select * from(
SELECT 
luFoOfficeRegion.Value as Region
,FoOffice.OfficeIdentifier as Office
,datename(month, [dtAccountingPeriod]) + '' '' + cast(YEAR([dtAccountingPeriod]) AS varchar)  as [Month]
,[amtUSD] 
FROM [INformV2].[dbo].[FoAccountingDocumentAudit]
join FoOffice on fkOfficeID = FoOffice.ID
join luFoOfficeRegion on [INformV2].[dbo].[FoAccountingDocumentAudit].fkRegionID = luFoOfficeRegion.ID
where [DocumentationComplete] = 0
) Datatable
PIVOT(sum([amtUSD]) for [Month] in (' + @PivotColumnHeaders + ' )
)as pvtMonth
order by Region
'
EXECUTE(@PivotTableSQL)

The result was this:

Very nice!
Now on to BIRT. I started to create the report in BIRT and realized there was a problem, BIRT likes to work with static columns. Back to SQL.
I used this in a view to return my data:

SELECT top 100    dbo.luFoOfficeRegion.Value AS Region, dbo.FoOffice.OfficeIdentifier AS Office
,datename(month, dbo.FoAccountingDocumentAudit.dtAccountingPeriod) + ' ' + cast(YEAR(dbo.FoAccountingDocumentAudit.dtAccountingPeriod) AS varchar)  as [MonthYear]
,SUM(dbo.FoAccountingDocumentAudit.amtUSD) AS Total
,YEAR(dbo.FoAccountingDocumentAudit.dtAccountingPeriod) as Year
,Month(dbo.FoAccountingDocumentAudit.dtAccountingPeriod) as Month
FROM dbo.FoAccountingDocumentAudit INNER JOIN
dbo.FoOffice ON dbo.FoAccountingDocumentAudit.fkOfficeID = dbo.FoOffice.ID INNER JOIN
dbo.luFoOfficeRegion ON dbo.FoAccountingDocumentAudit.fkRegionID = dbo.luFoOfficeRegion.ID
WHERE dbo.FoAccountingDocumentAudit.DocumentationComplete = 0
GROUP BY dbo.luFoOfficeRegion.Value, dbo.FoOffice.OfficeIdentifier, dbo.FoAccountingDocumentAudit.dtAccountingPeriod

The year and month are used in BIRT to sort the columns.

The returned data:

Back to BIRT. I used the view in my report and created a data cube:

Then using the data in a BIRT "Cross Tab", applying a little formating, and here's my final result:

SharePoint 2010 - Fast Search Disk Space Problem

The organization I work for uses Fast Search and the drive where the search index is being stored was running low on disk space. I thought it was the index itself that was growing but as it turned out my problem was being caused by click-through. The folder, "...data\sprel\worker\build_proc", was being filled with files. I used the "spreladmin" utilty to stop the process and deleted the files. So far I have not been able to figure out what is causing the problem and since, in our case, there isn't much of a benefit in using click-through I just decided to leave the service off. I did discover some nice command-line tools for working with Fast Search Server. You can find them here.

TSQL - Using Like in a TSQL Join

More work importing data! This time for all of the tables I imported I needed to set a foreign key field for each record to indicate who created the record.

Each table that I imported the data from has a field called "Record Owner" that is the first and last name of the record creator plus an additional code. An example would be "Bob Smith 12345".

I have a table called "AppUser" that has primary key field "ID", and a "FirstName" and a "LastName" field. In my imported tables I have a field, "CreatedBy", that is the foreign key to the "ID" field in the "AppUser" table. Because of the additional code characters in the "Record Owner" field, I had to use a TSQL "like" to accomplish the join.

Here is my final TSQL query for populating the "CreatedBy" field:

UPDATE [INformV2].[dbo].[FoOfficeReportingPerformance]
SET [CreatedBy] = qbO.ID   
FROM [INformV2].[dbo].[FoOfficeReportingPerformance]
INNER JOIN [Importsv1].[dbo].[FoOfficeReportingPerformance] qbA
ON [INformV2].[dbo].[FoOfficeReportingPerformance].[QBRecordID] = qbA.[Office performance record ID#]
INNER JOIN [INformV2].[dbo].[AppUser] qbO
ON  qbA.[Record Owner] like (qbO.[FirstName] + ' ' +  qbO.[LastName] + '%')

Table "[Importsv1].[dbo].[FoOfficeReportingPerformance]" is the originally imported data.
Table "[INformV2].[dbo].[FoOfficeReportingPerformance]" is my final table. I imported data from "[Importsv1].[dbo].[FoOfficeReportingPerformance]" into this table. It has a field "QBRecordID" that matches the primary key, "Office performance record ID#", for the records in "[Importsv1].[dbo].[FoOfficeReportingPerformance]".

Anyway, what is interesting to me here is that you can use "like" as a comparisoin operator in a TSQL JOIN!

TSQL - Bitwise Operators

I recently had to set a field by ORing three different fields in a table. The easy solution was to use the Bitwise OR operator. So in the table design in SQL Manager, the "Computed Column Specification" for the field I set the formula to (([Field1]|[Field2])|[Field3]). You can learn more about the Bitwise Operators here.