Unique count from current and audit tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Unique count from current and audit tables?

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-12 : 17:07:53

< The original problem.....of getting unique rows/users from a "live" table and its associated audit table appears to be solved. However, the problem of trying to get a count of the unique users (by id) per location is still eluding me. Any help is appreciated...please see the last message. Thanks.>

Hello...

I am trying to get a Stored Proc to work with a single query, but the more I look at the data and the required results, I don't think I have any choice other than to use a cursor or temp table and loop through my initial data.

Here is the schema...for the sake of simplicity, I have removed all but the most important columns.
CREATE TableA ( UserID int, Location nvarchar (50), LocationStatus nvarchar (50), LastUpdate datetime)
CREATE Audit_TableA ( UserID int, Location nvarchar (50), LocationStatus nvarchar (50), LastUpdate datetime)

Here is the data:
INSERT INTO TableA (UserID, Location, LocationStatus, LastUpdate)
VALUES (1234, 'Building A', 'Departed', Getdate())
INSERT INTO TableA (UserID, Location, LocationStatus, LastUpdate)
VALUES (6789, 'Building A', 'Arrived', Getdate())
INSERT INTO TableA (UserID, Location, LocationStatus, LastUpdate)
VALUES (5454, 'Building B', 'Arrived', Getdate())
INSERT INTO TableA (UserID, Location, LocationStatus, LastUpdate)
VALUES (1003, 'Building H', 'Departed', Getdate())

INSERT INTO Audit_TableA (UserID, Location, LocationStatus, LastUpdate)
VALUES (1234, 'Building A', 'Arrived', Getdate())
INSERT INTO Audit_TableA (UserID, Location, LocationStatus, LastUpdate)
VALUES (1234, 'Building A', 'Arrived', Getdate())
INSERT INTO Audit_TableA (UserID, Location, LocationStatus, LastUpdate)
VALUES (1234, 'Building A', 'Arrived', Getdate())

Results user wants is to only see the distinct UserID, Location and Status in a combined view of TableA and Audit_TableA. So, even though a UserID might have the duplicates of Location and LocationStatus in the audit table, this needs to be returned as just one row.

Like:
Location Arrived Departed
Building A 3 1
Building H 0 1

The 3 Arrived for Building A includes the 2 users in from TableA and the 1 from Audit_TableA...even though the one user has several Arrived rows for that location.

Thanks for any help or suggestions.
- will

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-12 : 17:47:35

A union between the 2 tables?

this is from you r sample data:

select	Location
	,sum(case when LocationStatus = 'Arrived' then 1 else 0 end) as Arrived
	,sum(case when LocationStatus = 'Departed' then 1 else 0 end) as Departed
from
	(	select UserId, Location, LocationStatus, LastUpdate from TableA
		union
		select UserId, Location, LocationStatus, LastUpdate from Audit_TableA ) bothtables
group by
	Location


Result:
Location                             Arrived     Departed    
------------------------------------ ----------- ----------- 
Building A                           2           1
Building B                           1           0
Building H                           0           1

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2006-04-13 : 13:05:43

As it turned out...I need to perform 2 UNIONs. One as a UNION ALL with the Audit_TableA and another UNION (no ALL) on the Audit_TableA and itself. Kinda' weird...but it works for getting the data I want.

Only thing is the count for total users...that is not correct. It is adding all of the rows, not just the unique user id values.

Does anyone have any thoughts on how to get the total unique userid per location?

thanks...
- will

Here is my current statement:

SELECT Location,
sum(case when LocationStatus = 'Arrived'
then 1
else 0
end) as Arrived,
sum(case when LocationStatus = 'Departed'
then 1
else 0
end) as Departed,
sum(case WHEN (LocationStatus = 'Waiting'
then 1
else 0
end ) AS Waiting,
Count(*) AS TotalUsers
FROM (SELECT Location, LocationStatus, UserID
FROM TableA
UNION ALL
(SELECT Location, LocationStatus, UserID
FROM Audit_TableA
UNION SELECT Location, LocationStatus, UserID
FROM Audit_TableA) ) TableAlias
GROUP BY Location

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources