Re: Using Function Table ? using Sql Server 2008


Hi,

Thanks very much for the help, it really worked out.

As I have no sound knowledge about performance and tuning of queries, however I have heard that performance can be evaluated using Execution Plan, is it true? if so, how ?

I will appreciate if you please point me to the right direction.

Take care!

Luqman

"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:Od0oAQ09JHA.1492@xxxxxxxxxxxxxxxxxxxxxxx
I want to retrieve all Employees who are missing in particular Dates.

I suggest you change the function to use strongly-typed dates as this will make the query clearer. Better yet, create a permanent Calendar table to facilitate these types of queries. Below is a refactored example that I think will provide the desired results and also perform better:

ALTER FUNCTION dbo.GetDates
(
@StartDate datetime,
@EndDate datetime
)

RETURNS @ret TABLE(DateValue datetime)
AS
BEGIN

WITH
L0 AS (SELECT 0 AS N UNION ALL SELECT 1),
L1 AS (SELECT A.N+(B.N*2) AS N FROM L0 AS A, L0 AS B),
L2 AS (SELECT A.N+(B.N*4) AS N FROM L1 AS A, L1 AS B),
L3 AS (SELECT A.N+(B.N*16) AS N FROM L2 AS A, L2 AS B),
NUM AS (SELECT A.N+(B.N*256) AS N FROM L3 AS A, L3 AS B)
INSERT INTO @ret(DateValue)
SELECT DATEADD(day, N, @StartDate)
FROM NUM
WHERE DATEADD(day, N, @StartDate) BETWEEN @StartDate AND @EndDate
RETURN

END
GO

DECLARE
@FirstDate datetime,
@LastDate datetime
SELECT
@FirstDate = '20090601',
@LastDate = '20090630'
SELECT
A.EMPLOYEEID,
B.DateValue
FROM EMPLOYEE A
CROSS JOIN dbo.GetDates(@FirstDate,@LastDate) B
WHERE NOT EXISTS(
SELECT *
FROM transactionold
WHERE
employeeid = A.employeeid
AND transactionold.cDate = B.DateValue
)

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Luqman" <pearlsoft@xxxxxxxxxxxx> wrote in message news:%23wAZmZz9JHA.4692@xxxxxxxxxxxxxxxxxxxxxxx
I have created a function to find missing Dates.

The function name is GetDates.

CREATE FUNCTION dbo.GetDates
(
@Start BIGINT,
@End BIGINT
)

RETURNS @ret TABLE(Number BIGINT)
AS
BEGIN
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),
L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),
num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)

INSERT INTO @ret(Number) SELECT N FROM NUM WHERE N BETWEEN @Start AND @End
RETURN
END



I want to retrieve all Employees who are missing in particular Dates.

I tried following query but could not get the desired results.

SELECT EMPLOYEEID,CAST(@nfirstdate AS DATETIME) + Number-1
FROM EMPLOYEE A, dbo.GetDates(@nFirstDay,@nLastDay) B
WHERE NOT EXISTS
(SELECT * FROM transactionold
WHERE employeeid=A.employeeid and cDate=CAST(@nfirstdate AS DATETIME) + Number-1)

Any idea please ?

Thanks and Regards,

Luqman



.

Relevant Pages

  • Re: Passing DateDiff rather than Actual Dates as Parameters
    ... So for instance you could pass a date in international format. ... Declare @real_date datetime ... > SELECT @StartDate = DATEDIFF ... > SELECT @EndDate = DATEDIFF ...
    (comp.databases.ms-sqlserver)
  • Re: Using Function Table ? using Sql Server 2008
    ... which is basically a datetime version of your original. ... Special attention to execution plan detail is a good practice in such cases to ensure the query and indexes are as optimal as possible. ... @EndDate datetime ... SELECT DATEADD(day, N-1, @StartDate) ...
    (microsoft.public.sqlserver.programming)
  • Re: Using Function Table ? using Sql Server 2008
    ... make the query clearer. ... @StartDate datetime, ... SELECT DATEADD(day, N, @StartDate) ... @Start BIGINT, ...
    (microsoft.public.sqlserver.programming)
  • Re: Index Performance
    ... As you said most of your searches are based on both PayDate and StartDate, ... > StartDate datetime NULL, ... My application is giving timeouts for such queries. ...
    (microsoft.public.sqlserver.server)
  • Re: Overlapping and duplicate records
    ... work management type #1 ... - startdate = min startdate of the 2 original records ... , CAST(0x00009C2E00000000 AS DateTime) ...
    (microsoft.public.sqlserver.programming)
Loading