SQL Server Performance

need help to filter my query

Discussion in 'SQL Server 2005 General Developer Questions' started by isa, Apr 4, 2008.

  1. isa New Member

    hello everyone, i m using SQL Sevrver 2005, here is my query , in this all the where clause filter applied on all columns in the select stmt , so my column in select smt "Incomplete returns nothing" , for this i req another filter which uses (dbo.TvsRecords.OpConfirmDate is null) and SystemRoleID= 4, here in my query i m unable to add this filter only for this column, so i used another methid (Used UnionALL )

    Without UnionALL and without apply filter for the Incomplete column, my Query Is:::::::::::

    SELECT DISTINCT dbo.TvsRecords.EntryOperatorUserID, dbo.SystemUsers.Address2 as UserShift,dbo.SystemUsers.FullName AS OperatorName, SUM(CASE WHEN TvsFormStatusCode = 1 THEN 1 ELSE 0 END)
    AS Incomplete, SUM(CASE WHEN TvsFormStatusCode = 2 THEN 1 ELSE 0 END) AS Unverified,
    SUM(CASE WHEN TvsFormStatusCode = 3 THEN 1 ELSE 0 END) AS InVerification, SUM(CASE WHEN TvsFormStatusCode = 4 THEN 1 ELSE 0 END)
    AS Verified, SUM(CASE WHEN TvsFormStatusCode = 5 THEN 1 ELSE 0 END) AS ReadOnly, Count(TvsRecordID) as Total ,
    Sum (case when TvsRecords.CorrectionsCount <> 0 Then 1 else 0 end) as CorrectionsCount
    FROM dbo.TvsRecords INNER JOIN
    dbo.SystemUsers ON dbo.TvsRecords.EntryOperatorUserID = dbo.SystemUsers.SystemUserID
    WHERE (dbo.TvsRecords.OpConfirmDate >= @FromDate + '00:00:00.000') AND (dbo.TvsRecords.OpConfirmDate <= @ToDate + '23:59:59.999')
    and dbo.SystemUsers.SystemRoleID = 4
    GROUP BY dbo.SystemUsers.Address2,dbo.SystemUsers.FullName, dbo.TvsRecords.EntryOperatorUserID
    order by dbo.SystemUsers.Address2 asc
    Now with the Union ALL ::::::::::
    SELECT DISTINCT dbo.TvsRecords.EntryOperatorUserID, dbo.SystemUsers.Address2 as UserShift,dbo.SystemUsers.FullName AS OperatorName,

    SUM(CASE WHEN TvsFormStatusCode = 1 THEN 1 ELSE 0 END)
    AS Incomplete,

    SUM(CASE WHEN TvsFormStatusCode = 2 THEN 1 ELSE 0 END) AS Unverified,
    SUM(CASE WHEN TvsFormStatusCode = 3 THEN 1 ELSE 0 END) AS InVerification, SUM(CASE WHEN TvsFormStatusCode = 4 THEN 1 ELSE 0 END)
    AS Verified, SUM(CASE WHEN TvsFormStatusCode = 5 THEN 1 ELSE 0 END) AS ReadOnly, Count(TvsRecordID) as Total ,
    Sum (case when TvsRecords.CorrectionsCount <> 0 Then 1 else 0 end) as CorrectionsCount
    FROM dbo.TvsRecords INNER JOIN
    dbo.SystemUsers ON dbo.TvsRecords.EntryOperatorUserID = dbo.SystemUsers.SystemUserID
    WHERE (dbo.TvsRecords.OpConfirmDate >= '1/1/1990 00:00:00.000') AND (dbo.TvsRecords.OpConfirmDate <= '12/31/2050 23:59:59.999')
    and dbo.SystemUsers.SystemRoleID =4
    GROUP BY dbo.SystemUsers.Address2,dbo.SystemUsers.FullName, dbo.TvsRecords.EntryOperatorUserID

    union all


    SELECT Distinct dbo.TvsRecords.EntryOperatorUserID, dbo.SystemUsers.Address2 as UserShift,dbo.SystemUsers.FullName AS OperatorName,

    SUM(CASE WHEN TvsFormStatusCode = 1 THEN 1 ELSE 0 END) AS Incomplete,
    0 AS Unverified,
    0 AS InVerification,
    0 AS Verified,
    0 AS ReadOnly,
    SUM(CASE WHEN TvsFormStatusCode = 1 THEN 1 ELSE 0 END) AS Total,
    0 as CorrectionsCount
    FROM dbo.TvsRecords INNER JOIN
    dbo.SystemUsers ON dbo.TvsRecords.EntryOperatorUserID = dbo.SystemUsers.SystemUserID
    WHERE dbo.TvsRecords.OpConfirmDate is null
    and dbo.SystemUsers.SystemRoleID =4
    GROUP BY dbo.SystemUsers.Address2,dbo.SystemUsers.FullName, dbo.TvsRecords.EntryOperatorUserID
    order by dbo.SystemUsers.Address2 asc
    But it returns duplicate rows and not give the accurate value for column "Total" , i also used select stmt within the select but nothing happenes
    Kindly tell me how i do this? Is this possible to get some column on some specific filters (where) and some on other types of filters and than joined both results and it gives the accurate value for column total (it displays the row values sum() , i hoped u all get my point, Plz reply me asap.
    Its really very urgent , Thanx in Advance.

  2. Adriaan New Member

    UNION with the ALL keyword means duplicates will be displayed, so just drop the ALL keyword to suppress the duplicates (you will notice that the query will take longer to execute).
    However, you should not really use a UNION just to include rows where the date is NULL. You can do it like this:
    WHERE (dbo.TvsRecords.OpConfirmDate IS NULL OR (
    (dbo.TvsRecords.OpConfirmDate >= @FromDate + '00:00:00.000') AND (dbo.TvsRecords.OpConfirmDate <= @ToDate + '23:59:59.999')
    )
    Also check out the Frank Kalis' famous article on date handling about the criteria ...
    I would rewrite as:
    WHERE (dbo.TvsRecords.OpConfirmDate IS NULL OR (dbo.TvsRecords.OpConfirmDate >= @FromDate AND dbo.TvsRecords.OpConfirmDate < @ToDate + 1))

Share This Page