April 4, 2008 at 2:07 am
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.
April 4, 2008 at 3:22 am
How about ...
SELECT -- DISTINCT not needed because you use GROUP BY on these 3 columns
R.EntryOperatorUserID,
U.Address2 as UserShift,
U.FullName AS OperatorName,
SUM(CASE WHEN TvsFormStatusCode = 1 THEN 1
ELSE 0
END) AS Incomplete,
SUM(CASE WHEN TvsFormStatusCode = 2 and R.OpConfirmDate is not null THEN 1
ELSE 0
END) AS Unverified,
SUM(CASE WHEN TvsFormStatusCode = 3 and R.OpConfirmDate is not null THEN 1
ELSE 0
END) AS InVerification,
SUM(CASE WHEN TvsFormStatusCode = 4 and R.OpConfirmDate is not null THEN 1
ELSE 0
END) AS Verified,
SUM(CASE WHEN TvsFormStatusCode = 5 and R.OpConfirmDate is not null 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 R
INNER JOIN dbo.SystemUsers U
ON R.EntryOperatorUserID = U.SystemUserID
WHERE ( ( R.OpConfirmDate >= @FromDate + '00:00:00.000'
AND R.OpConfirmDate <= @ToDate + '23:59:59.999' )
or R.OpConfirmDate is null )
and U.SystemRoleID = 4
GROUP BY U.Address2
, U.FullName
, R.EntryOperatorUserID
order by U.Address2 asc
Both parties of your union operation may deliver data from the same
R.EntryOperatorUserID,
U.Address2 as UserShift,
U.FullName AS OperatorName
and your UNION (ALL) will not exclude them mutualy.
Or you need to encapsulate the full union query in a new one
select EntryOperatorUserID,
UserShift,
OperatorName,
sum (Incomplete),
...
from ( your
union
queries
) u
group by EntryOperatorUserID,
UserShift,
OperatorName
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply