Combining 2 tables with a COUNT involved

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 2005 Forums

Transact-SQL (2005)

Combining 2 tables with a COUNT involved

Author

Topic

fakepoo
Starting Member

7 Posts

Posted - 2009-11-10 : 18:43:37

Hello,

I am fairly new to SQL but I am having tons of problems. I have 2 tables: Accounts and Meters. Meters links to Accounts by the AccountID value.

[Accounts]
AccountID
RouteID

[Meters]
MeterID
AccountID
DateLastUpdated

I would like to write a select statement that returns one row for each RouteID. In that row, I would like to have: RouteID, Maximum value for DateLastUpdated as EndDateTime, Minimum value of DateLastUpdated as StartDateTime, and the Count of the meters as MeterCount. I have tried just about everything but nothing seems to work. I keep getting multiple rows for a RouteID. Here is what I am trying:


SELECT DISTINCT Accounts.RouteID,MIN(Meters.DateLastUpdated) AS StartDateTime, MAX(Meters.DateLastUpdated) AS EndDateTime, COUNT(Meters.MeterID) AS MeterCount
FROM Accounts LEFT JOIN Meters ON Accounts.AccountID = Meters.AccountID 
GROUP BY Accounts.RouteID, Accounts.AccountID, Meters.MeterID, Meters.DateLastUpdated

Any help is greatly appreciated.
-fakepoo

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-10 : 18:49:55

quote:
Originally posted by fakepoo

Hello,

I am fairly new to SQL but I am having tons of problems. I have 2 tables: Accounts and Meters. Meters links to Accounts by the AccountID value.

[Accounts]
AccountID
RouteID

[Meters]
MeterID
AccountID
DateLastUpdated

I would like to write a select statement that returns one row for each RouteID. In that row, I would like to have: RouteID, Maximum value for DateLastUpdated as EndDateTime, Minimum value of DateLastUpdated as StartDateTime, and the Count of the meters as MeterCount. I have tried just about everything but nothing seems to work. I keep getting multiple rows for a RouteID. Here is what I am trying:

SELECT ~~DISTINCT~~ Accounts.RouteID,MIN(Meters.DateLastUpdated) AS StartDateTime, MAX(Meters.DateLastUpdated) AS EndDateTime, COUNT(Meters.MeterID) AS MeterCount
FROM Accounts LEFT JOIN Meters ON Accounts.AccountID = Meters.AccountID
GROUP BY Accounts.RouteID~~, Accounts.AccountID, Meters.MeterID, Meters.DateLastUpdated~~

Any help is greatly appreciated.
-fakepoo

fakepoo
Starting Member

7 Posts

Posted - 2009-11-11 : 09:08:02

Thank you Russell. For some reason, I thought you had to have every field in the group by statement.

I do have another extension to this question. Now, I also have a column in my Meters table called IsRead (int) that is 1 if read and 0 if not. I also have 4 columns (Comment1, Comment2, Comment3, Comment4) (nvarchar) that can be NULL, empty, or have a value. The meter will be considered "Visited" if IsRead = 1 or if any of the Comments have a value. I would like to return a column and call it VisitedCount that counts the number of visited meters. This is what I tried but got a syntax error because it did not like the use of the equals sign in the select statement:


SELECT 
	Accounts.RouteID, 
	MIN(Meters.DateLastUpdated) AS StartDateTime, 
	MAX(Meters.DateLastUpdated) AS EndDateTime, 
	COUNT(Meters.MeterID) AS MeterCount, 
	SUM(Meters.IsRead) AS ReadCount, 
	COUNT(Meters.MeterID) - SUM(Meters.IsRead) AS UnreadCount, 
	COUNT(Meters.IsRead = 1 OR Meters.Comment1 OR Meters.Comment2 OR Meters.Comment3 OR Meters.Comment4) AS VisitedCount
FROM Accounts LEFT JOIN Meters ON Accounts.AccountID = Meters.AccountID 
GROUP BY Accounts.RouteID

mickey_w
Starting Member

1 Post

Posted - 2009-11-11 : 09:28:37

Hi, how about

sum(case when Meters.IsRead = 1 then 1
when Meters.Comment1 is not null then 1
when Meters.Comment2 is not null then 1
when Meters.Comment3 is not null then 1
when Meters.Comment4 is not null then 1
else 0 end) as VisitedCount

mickey

fakepoo
Starting Member

7 Posts

Posted - 2009-11-11 : 09:58:15

Thank you Mickey. I think I'm starting to get the hang of this. Here is my current query:


SELECT 
	Accounts.RouteID, 
	MIN(Meters.DateLastUpdated) AS StartDateTime,
	MAX(Meters.DateLastUpdated) AS EndDateTime, 
	COUNT(Meters.MeterID) AS MeterCount, 
	SUM(Meters.IsRead) AS ReadCount, 
	COUNT(Meters.MeterID) - SUM(Meters.IsRead) AS UnreadCount, 
	SUM(CASE	WHEN Meters.IsRead = 1 THEN 1
				WHEN Meters.Comment1 is not null THEN 1
				WHEN Meters.Comment2 is not null THEN 1
				WHEN Meters.Comment3 is not null THEN 1
				WHEN Meters.Comment4 is not null THEN 1
				ELSE 0 END) AS VisitedCount,
	SUM(CASE	WHEN Meters.Comment1 is not null THEN 1
				WHEN Meters.Comment2 is not null THEN 1
				WHEN Meters.Comment3 is not null THEN 1
				WHEN Meters.Comment4 is not null THEN 1
				ELSE 0 END) AS CommentCount
FROM Accounts LEFT JOIN Meters ON Accounts.AccountID = Meters.AccountID 
GROUP BY Accounts.RouteID

I have just 2 more things that I would like to do.
1) Convert StartDateTime and EndDateTime to a datetime. The problem here is that they can have NULL and empty-string values. Is there a way to check for a valid date string first then convert otherwise return NULL?

2) I would like to return the difference in the dates in seconds as another column called ReadingSeconds. This is probably tied to the question above as I would have to convert it first but the NULL values are really making this difficult.

Thanks again,
-fakepoo

fakepoo
Starting Member

7 Posts

Posted - 2009-11-11 : 12:05:19

I think I finally have it figured out. This is what I have:


SELECT 
	Accounts.RouteID, 
	MIN(CASE WHEN Meters.DateLastUpdated IS NOT NULL THEN CONVERT(DATETIME, SUBSTRING(Meters.DateLastUpdated,1,8) + ' ' + SUBSTRING(Meters.DateLastUpdated,9,2) + ':' + SUBSTRING(Meters.DateLastUpdated,11,2) + ':' + SUBSTRING(Meters.DateLastUpdated,13,2)) ELSE NULL END) AS StartDateTime,
	MAX(CASE WHEN Meters.DateLastUpdated IS NOT NULL THEN CONVERT(DATETIME, SUBSTRING(Meters.DateLastUpdated,1,8) + ' ' + SUBSTRING(Meters.DateLastUpdated,9,2) + ':' + SUBSTRING(Meters.DateLastUpdated,11,2) + ':' + SUBSTRING(Meters.DateLastUpdated,13,2)) ELSE NULL END) AS EndDateTime,
	DATEDIFF(second, MIN(CASE WHEN Meters.DateLastUpdated IS NOT NULL THEN CONVERT(DATETIME, SUBSTRING(Meters.DateLastUpdated,1,8) + ' ' + SUBSTRING(Meters.DateLastUpdated,9,2) + ':' + SUBSTRING(Meters.DateLastUpdated,11,2) + ':' + SUBSTRING(Meters.DateLastUpdated,13,2)) ELSE NULL END), MAX(CASE WHEN Meters.DateLastUpdated IS NOT NULL THEN CONVERT(DATETIME, SUBSTRING(Meters.DateLastUpdated,1,8) + ' ' + SUBSTRING(Meters.DateLastUpdated,9,2) + ':' + SUBSTRING(Meters.DateLastUpdated,11,2) + ':' + SUBSTRING(Meters.DateLastUpdated,13,2)) ELSE NULL END)) AS ReadingSeconds,	
	COUNT(Meters.MeterID) AS MeterCount, 
	SUM(Meters.IsRead) AS ReadCount, 
	COUNT(Meters.MeterID) - SUM(Meters.IsRead) AS UnreadCount, 
	SUM(CASE	WHEN Meters.IsRead = 1 THEN 1
				WHEN Meters.Comment1 is not null THEN 1
				WHEN Meters.Comment2 is not null THEN 1
				WHEN Meters.Comment3 is not null THEN 1
				WHEN Meters.Comment4 is not null THEN 1
				ELSE 0 END) AS VisitedCount,
	SUM(CASE	WHEN Meters.Comment1 is not null THEN 1
				WHEN Meters.Comment2 is not null THEN 1
				WHEN Meters.Comment3 is not null THEN 1
				WHEN Meters.Comment4 is not null THEN 1
				ELSE 0 END) AS CommentCount
FROM Accounts LEFT JOIN Meters ON Accounts.AccountID = Meters.AccountID 
GROUP BY Accounts.RouteID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources