Archives for the month of: June, 2014
Consider this SQL SELECT statement:

SELECT
    [TS].*
FROM
    [dbo].[TimeSheet] [TS]
WHERE
    ([TS].[EntryDate] = @ActivityStartDate)

Seems pretty straight forward, but you run into some issues when comparing dates with timestamps. If @ActivityStartDate is passed into the query as 6/1/2014 12:00 AM, because a specific time was never specified, but the timesheet entries contain timestamps, then you run into some inconsistencies. For example, if the Timesheet date is equal to 6/1/2014 10:00 AM, then the row will not be returned because the Timesheet date evaluates to LESS THAN the parameter. You can use the DATEDIFF method to resolve this:

DATEDIFF(DAY, [TS].[EntryDate], @ActivityStartDate) = 0

Or you can simply remove the timestamp:

DATEADD(DD, DATEDIFF(DD, 0, [TS].[EntryDate]), 0) = @ActivityStartDate
Advertisements

Did you know that you can use Cross Apply in a SQL Query to join together a SQL Table with a Table-valued Function into a single result set?  Here’s how: I have created a Function that contains several calculations and returns some standard Project Metrics:


CREATE FUNCTION [dbo].[fnGetProjectMetrics]
(
    @ProjectId int
)
RETURNS @results table
(
    [ActivityStartDate] datetime
    , [ActivityEndDate] datetime
    , [BillableHours] money
    , [NonbillHours] money
    , [TotalHours] money
    , [TotalRevenue] money
    , [AverageRate] money
    , [EffectiveRate] money
)

I can include the results of that function in my SQL Query:


SELECT
    [Project].[Id]
    , [Project].[Client]
    , [Project].[Name]
    , [ProjectMetrics].[ActivityStartDate]
    , [ProjectMetrics].[ActivityEndDate]
    , [ProjectMetrics].[BillableHours]
    , [ProjectMetrics].[NonbillHours]
    , [ProjectMetrics].[TotalHours]
    , [ProjectMetrics].[TotalRevenue]
    , [ProjectMetrics].[AverageRate]
    , [ProjectMetrics].[EffectiveRate]
FROM
    [dbo].[Project]
    CROSS APPLY [dbo].[fnGetProjectMetrics]([Project].[ProjectId) AS [ProjectMetrics]

I maintain a roll-up table of project metrics, which get updated on a frequent basis. This table contains a lot of calculations and data from multiple sources, which first populates a TABLE variable. Once all of the calculations and aggregate data sources have been completed, I want to update my project metrics table with the contents of the TABLE variable. Rather than looping through my table variable and updating my project metrics table one row at a time, I would like to perform a BULK UPDATE.

Turns out to be a very simple SQL statement in which I join the table I am updating [dbo].[DeliveryDashboardProjectMetrics] with my TABLE variable @work in order update the associated columns:

    UPDATE 
        [dbo].[DeliveryDashboardProjectMetrics] 
    SET 
        [d].[ProjectId] = w.[ProjectId], 
        [d].[ProjectType] = w.[ProjectType], 
        [d].[Solution] = w.[Solution], 
        [d].[EstimatedStart] = w.[EstimatedStart], 
        [d].[EstimatedEnd] = w.[EstimatedEnd], 
        [d].[EstimatedHours] = w.[EstimatedHours], 
        [d].[EstimatedRevenue] = w.[EstimatedRevenue], 
        [d].[ClientRating] = w.[ClientRating], 
        [d].[FirstActivity] = w.[FirstActivity], 
        [d].[LastActivity] = w.[LastActivity], 
        [d].[ActualHours] = w.[ActualHours], 
        [d].[ActualRevenue] = w.[ActualRevenue], 
        [d].[NonbillHours] = w.[NonbillHours], 
        [d].[BillHours] = w.[BillHours], 
        [d].[AverageRate] = w.[AverageRate], 
        [d].[EffectiveRate] = w.[EffectiveRate] 
    FROM 
        [dbo].[DeliveryDashboardProjectMetrics] [d] 
        INNER JOIN @work w ON [d].ProjectId = w.ProjectId