​In this example, I am combining the data from three tables.  Two of the tables have different columns and no relation or join. 

I am managing a soccer team.  I have a list of weeks in the soccer season.  And I also have a list of players. In addition, I also have a list of weeks in which each player have confirmed. 

DECLARE@WeeksTABLE

(

       WeekIdintIDENTITYPRIMARYKEY

       ,WeekDateDATETIME

)

DECLARE@PlayersTABLE

(

       PlayerIdintIDENTITYPRIMARYKEY

       ,PlayerNamevarchar(50)

)

DECLARE@ConfirmationsTABLE

(

       ConfirmationIdintIDENTITYPRIMARYKEY

       ,PlayerIdint

       ,WeekIdint

)

Using Transact SQL, I want to create a roster that combines all of the players and all of the weeks and specifies whether a player has confirmed participation for that week.

-- declare result table

DECLARE@RosterTABLE

(

       WeekIdint

       ,WeekDatedatetime

       ,PlayerIdint

       ,PlayerNamevarchar(50)

       ,IsConfirmedvarchar(3)

)

To combine all Players and all Weeks, you simply perform a select without a join.

-- combine all weeks and all players to create roster

INSERTINTO@Roster

(

       WeekId

       ,WeekDate

       ,PlayerId

       ,PlayerName

)

SELECT

       w.WeekId

       ,w.WeekDate

       ,p.PlayerId

       ,p.PlayerName

FROM

       @Weeksw

       ,@Playersp

Once you have your roster, you can perform a quick update to add whether the player has confirmed for each of the weeks.

-- perform multiple update for confirmations'

UPDATE

    @Roster

SET

    IsConfirmed=CASEWHENc.PlayerIdISNOTNULLTHEN'Yes'ELSE'No'END

FROM

    @Rosterr

    [email protected]=c.PlayerIdANDr.WeekId=c.WeekId

And here is your roster!  Let’s play some soccer.  : )

Issue: Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS”

I received the following error when I was attempting to compare columns in a SQL query between two tables located in two separate databases. In this case, the collation settings between the two databases were configured differently. Rather make any changes to the databases, I was able to apply a simple fix to my SQL query:

ORIGINAL QUERY –

UPDATE

    [database1].[dbo].[table1]

SET

    [id] = (SELECT [d2t1].[id] FROM [database2].[dbo].[table1] [d2t1] WHERE [d2t1].[name] = [database1].[dbo].[table1].[name])

WHERE

    [id] IS NULL

FIX –

Simply apply the default collation to the fields you are comparing.

UPDATE

    [database1].[dbo].[table1]

SET

    [id] = (SELECT [d2t1].[id] FROM [database2].[dbo].[table1] [d2t1] WHERE [d2t1].[name] COLLATE DATABASE_DEFAULT = [database1].[dbo].[table1].[name] COLLATE DATABASE_DEFAULT)

WHERE

    [id] IS NULL

Find out more about SQL here:

1. Float vs Decimal in SQL Server
2. Calculate weekdays in SQL
3. SQL server error: changes not permitted

On many occasions, I am asked to run a quick ad-hoc query and dump the results into an Excel spreadsheet. This is typically a piece of cake except in those cases when I am asked to include a long description field. In some cases, the long description field might contain new line characters. This wreaks havoc when I try to copy the results into Excel or save to a comma-delimited list, because the line breaks cause the row in Excel to break out into multiple rows. Messy to clean up.

If you want to ensure that your description field does not return any line breaks and new lines, it is simple to remove those straight in the SQL query:

Here is how to do it:

In my never ending quest to completely phase out SQL cursors, I am truly discovering some truly awesome features about SQL Server that I have never looked deeply enough before to consider.

My requirement: I need to determine the total value for a project based on a set of monthly deliverables. These deliverables each contain a start and end date, a monthly revenue value, and a monthly set of hours.

For example:

What makes this interesting is that for those months in which the contract either doesn’t start on the 1^st or end of the last day of the month, the Monthly Revenue must be prorated out: Total Days/Revenue Per Day

I created the following Table Valued function to handle the calculations:

FUNCTION [dbo].[fnCalculateManagedServicesDeliverableTotals]

(

    @deliverable_id int,

    @published int

)

RETURNS @RtnValue table

(    

    deliverable_id int,

    published int,

    total_hours money,

    total_revenue money

)

My goal, as I said, is to calculate the total project value. Before I gave up cursors, I would have simply created a cursor that iterated each project deliverable and updated some variables with the results of my Table Valued function. I wanted to accomplish the same thing WITHOUT USING A CURSOR.

The first thing I did was to create and populate a table variable to hold my project deliverables:

DECLARE @deliverables TABLE

(

    deliverable_id int

)

I then created another table variable to hold my results:

DECLARE @work TABLE

(

    deliverable_id int,

    total_amount money,

    total_hours money

)

This is where it got a little sticky for me. I wanted to insert into my @work table the deliverables from my @deliverables table, along with the results of my Table Valued function.

Initially I tried this and got a SQL syntax error: L

INSERT INTO @work

(

    deliverable_id,

    total_amount,

    total_hours

)    

SELECT

    pd.deliverable_id,

    d.total_revenue,

    d.total_hours

FROM

    @deliverables AS pd

    INNER JOIN dbo.fnCalculateManagedServicesDeliverableTotals(pd.deliverable_id, @published) AS d

WHERE

    pd.deliverable_id = d.deliverable_id

It turns out that this is entirely possible, as long as you are not passing in a value or variable to the table function as parameter, but NOT a column from the joining table.

SQL Server does have a solution for this called CROSS APPLY. If you use CROSS APPLY for INNER JOINS and OUTER APPLY for LEFT OUTER JOINS, then you have the ability to create a join between two table valued expressions, which in my case is a TABLE VARIABLE and the results of a TABLE VALUED FUNCTION.

INSERT INTO @work

(

    deliverable_id,

    total_amount,

    total_hours

)    

SELECT

    pd.deliverable_id,

    d.total_revenue,

    d.total_hours

FROM

    @deliverables AS pd

    CROSS APPLY dbo.fnCalculateManagedServicesDeliverableTotals(pd.deliverable_id, @published) AS d

WHERE

    pd.deliverable_id = d.deliverable_id

Here are my results: