N00bie : Return Recent Metre Reading

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

General SQL Server Forums

New to SQL Server Programming

N00bie : Return Recent Metre Reading

Author

Topic

Kittyness
Starting Member

8 Posts

Posted - 2013-08-06 : 07:02:30

I'm running a query on a set of electricity metres for various sites and wish to determine the most recent metre read for each site, by selected site group.

There are 4 tables :
CONTACTS - Containing all the Site details
LOOKUP - Containing the Site Group details
POINTS - Containing all the metre id details
DATAELECTRICITY - Contains all the metre readings

The system I am using is a bespoke system which I believe is running transact sql. I'm very very new to sql and use Access to help visual and build my queries so I appriciate my phrasing may be a bit off :)

The query below returns all of the metre readings for each site,but I would like :
a) Only return the latest M1-M8 readings for the maximum DataElectricity.Date
b) Only return values for the selected Site Group

PARAMETERS [Site Group] Text ( 255 );
SELECT 
Lookup.Lookup_Name AS [Group], 
Contacts.Name AS Site, 
Points.Number AS MPAN, 
Max(DataElectricity.Date) AS MaxDate, 
DataElectricity.M1_Present, 
DataElectricity.M2_Present, 
DataElectricity.M3_Present, 
DataElectricity.M4_Present, 
DataElectricity.M5_Present, 
DataElectricity.M6_Present, 
DataElectricity.M7_Present, 
DataElectricity.M8_Present,
DataElectricity.Direct
FROM 
DataElectricity INNER JOIN (Lookup INNER JOIN (Points INNER JOIN Contacts ON Points.Contacts_Id = Contacts.Id) ON Lookup.Lookup_Id = Contacts.Group_1) ON DataElectricity.Point_Id = Points.Id
WHERE
((DataElectricity.Direct)='D')
GROUP BY 
Lookup.Lookup_Name, Contacts.Name, Points.Number, DataElectricity.M1_Present, DataElectricity.M2_Present, DataElectricity.M3_Present, DataElectricity.M4_Present, DataElectricity.M5_Present, DataElectricity.M6_Present, DataElectricity.M7_Present, DataElectricity.M8_Present, DataElectricity.Direct
ORDER BY 
Lookup.Lookup_Name, Contacts.Name, Max(DataElectricity.Date) DESC;

Any assistance or guidence is gratefully recieved

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-06 : 07:42:06

[code]--May be this?
SELECT
Lookup.Lookup_Name AS [Group],
Contacts.Name AS Site,
Points.Number AS MPAN,
de.MaxDate,
de.M1_Present,
de.M2_Present,
de.M3_Present,
de.M4_Present,
de.M5_Present,
de.M6_Present,
de.M7_Present,
de.M8_Present,
de.Direct
FROM
(SELECT TOP 1 DataElectricity.Date AS MaxDate,
DataElectricity.M1_Present,
DataElectricity.M2_Present,
DataElectricity.M3_Present,
DataElectricity.M4_Present,
DataElectricity.M5_Present,
DataElectricity.M6_Present,
DataElectricity.M7_Present,
DataElectricity.M8_Present,
DataElectricity.Direct
DataElectricity.Point_id
FROM DataElectricity
WHERE DataElectricity.Direct = 'D'
ORDER BY MaxDate DESC) de
INNER JOIN (Lookup INNER JOIN (Points INNER JOIN Contacts ON Points.Contacts_Id = Contacts.Id) ON Lookup.Lookup_Id = Contacts.Group_1) ON de.Point_Id = Points.Id
WHERE Lookup.Lookup_Name = 'SiteGroupName'
ORDER BY Lookup.Lookup_Name, Contacts.Name, de.MaxDate DESC;[/code]

--
Chandu

Kittyness
Starting Member

8 Posts

Posted - 2013-08-06 : 08:26:50

Hi, Thanks for your fast reply - I'm relieved that I was able to understand about 90% !!

I've updated the de to DataElectricity and there was a missing , after DataElectricity.Direct but when I run the query I get the message that there are no results to display.
I'm not prompted for the name of the site group and even when I hard code the name of the group instead of 'SiteGroupName' I still get that there are no results to display.

So I've tried to add in a parameter phrase that I know works for other queries and this is for a very big client that I know has hundreds of active sites, so I don't understand why there are no results :

PARAMETERS [Site Group] Text ( 255 );
SELECT
Lookup.Lookup_Name,
Contacts.Name AS Site,
Points.Number AS MPAN,
DataElectricity.M1_Present,
DataElectricity.M2_Present,
DataElectricity.M3_Present,
DataElectricity.M4_Present,
DataElectricity.M5_Present,
DataElectricity.M6_Present,
DataElectricity.M7_Present,
DataElectricity.M8_Present,
DataElectricity.Direct

FROM
(
SELECT TOP 1 DataElectricity.Date AS MaxDate,
DataElectricity.M1_Present,
DataElectricity.M2_Present,
DataElectricity.M3_Present,
DataElectricity.M4_Present,
DataElectricity.M5_Present,
DataElectricity.M6_Present,
DataElectricity.M7_Present,
DataElectricity.M8_Present,
DataElectricity.Direct,
DataElectricity.Point_id
FROM
DataElectricity
WHERE
DataElectricity.Direct = 'D'
ORDER BY MaxDate DESC
)

DataElectricity INNER JOIN (Lookup INNER JOIN (Points INNER JOIN Contacts ON Points.Contacts_Id = Contacts.Id) ON Lookup.Lookup_Id = Contacts.Group_1) ON DataElectricity.Point_Id = Points.Id
WHERE ((Lookup.Lookup_Name)=[Site Group])
ORDER BY Lookup.Lookup_Name, Contacts.Name, MaxDate DESC;

I'm guessing that somewhere in there I am making the selection too specific to match the actual data but I don't know enough to get to the bottom of it !

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-06 : 08:31:41

In the inner query there is one condition on DataElectricity.Direct = 'D'
and outer query has the condition Lookup.Lookup_Name= [Site Group]... Is [Site Group] the column name?
As per your second condition that condition should be Lookup.Lookup_Name= 'SiteGroupValue' -- put specific site group value

--
Chandu

Kittyness
Starting Member

8 Posts

Posted - 2013-08-06 : 09:23:05

I think this is where my 10% missing knowledge shows (#^_^#)

The Lookup table contains lots of fields including:
Lookup.ID - Which is what matches the Group_1 field in the Contacts table
Lookup_Name - Name of the group ie. Wallmart

The Contacts table contains :
ID - Which is the site's unique code
Name - Name of the individual site ie. Wallmart London, or Wallmart Manchester (these are made up, can you tell !)
Group_1 - Which is the value that matches the Lookup table

So I thought that by setting the parameter value as variable [Site Group] meant that I could use this as a valid 'token' when using the WHERE statement.

Phew .... this is giving me a headache !

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-07 : 01:17:29

Did you sort it out the above problem?
If not yet, let us know the sample data and expected output for the same data.

--
Chandu

Kittyness
Starting Member

8 Posts

Posted - 2013-08-07 : 05:32:21

Hi, I've not been able to get to the bottom of this yet. As requested here is some sample data minus any fields that are not relevant.

Link to file : [url]https://docs.google.com/file/d/0BybrcUCD29TxWVRsV1VtTm1Bems/edit?usp=sharing[/url]

I've saved it in a google drive file with a, hopefully, understandable view of the tables, how the fields connect and what the desired result is.

All data is random and made up for confidentiality.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-07 : 06:21:28

DECLARE @LookUp TABLE( Lookup_id int, Look_Group int, Lookup_Name VARCHAR(15))
insert into @LookUp
SELECT 111, 6, 'WALMART' union all
SELECT 222, 6, 'TARGET' union all
SELECT 333, 6, 'TESCO'

DECLARE @Contacts TABLE (id int, Code VARCHAR(20), Group_1 int, Name varchar(40))
insert into @Contacts
SELECT 3000, 'C924', 111 , 'Wallmart Birmingham' union all
SELECT 3001, 'CEA00082', 111 , 'Wallmart London' union all
SELECT 3002, 'C097', 111, 'Wallmart Cambridge' union all
SELECT 3003, 'CEA00083', 111, 'Wallmart Oxford' union all
SELECT 3004, 'TBC', 222, 'Target London' union all
SELECT 3005, 'C511', 222, 'Target Cambridge'

DECLARE @Points TABLE (Id int, Contacts_Id int, MPAN varchar(20))
insert into @Points
SELECT 1234, 3000, '123456789' union all
SELECT 4567, 3001, '456789123' union all
SELECT 8901, 3002, '987654321' union all
SELECT 9876, 3003, '101010101' union all
SELECT 4282, 3004, '121212121'

DECLARE @DataElectricity TABLE( Id int, Point_Id int, Date DATE, Direct CHAR(1), Estimate CHAR(1),M1_Present int,M2_Present int,M3_Present int,M4_Present int,M5_Present int,M6_Present int,M7_Present int,M8_Present int)
insert into @DataElectricity
SELECT 200555, 1234, '31-Jul-13', 'D', NULL, 895127, 895129, 0, 0, 0, 0, 0, 0 union all
SELECT 200554, 1234, '30-Jun-13', 'D', NULL, 88000, 87000, 0, 0, 0, 0, 0, 0 union all
SELECT 200487, 4567, '31-Jul-13', 'D', NULL, 1001010, 33000, 0, 0, 0, 0, 0, 0 union all
SELECT 200506, 4567, '30-Jun-13', 'D', NULL, 988351, 20341, 0, 0, 0, 0, 0, 0 union all
SELECT 200756, 8901, '30-Jun-13', 'D', 'A', 85665, 0, 0, 0, 0, 0, 0, 0 union all
SELECT 200606, 8901, '15-Jun-13', 'D', NULL, 79000, 0, 0, 0, 0, 0, 0, 0 union all
SELECT 200755, 8901, '20-May-13', 'D', 'A', 75654, 0, 0, 0, 0, 0, 0, 0 union all
SELECT 200634, 9876, '31-Jul-13', NULL, NULL, 723785, 13750, 1100, 0, 0, 0, 0, 0 union all
SELECT 200652, 9876, '30-Jun-13', 'D', NULL, 657986, 12500, 1000, 0, 0, 0, 0, 0 union all
SELECT 200653, 9876, '30-May-13', NULL, NULL, 598169, 11364, 909, 0, 0, 0, 0, 0

-- Query
SELECT l.Lookup_Name, 
	c.Name,
	p.MPAN,
	d.Point_id MPAN,
	d.MaxDate,
	d2.M1_Present, d2.M2_Present, d2.M3_Present , d2.M4_Present , d2.M5_Present , d2.M6_Present , d2.M7_Present , d2.M8_Present , d2.Direct
FROM @LookUp l 
JOIN @Contacts c ON l.Lookup_id = c.Group_1 AND l.LookUp_Name = 'WALMART'
JOIN @Points p ON p.Contacts_Id = c.id
JOIN (SELECT Point_id, MAX(Date) MaxDate FROM @DataElectricity WHERE Direct = 'D' GROUP BY Point_id) d ON d.Point_Id = p.Id
JOIN @DataElectricity d2 ON d.Point_Id = d2.Point_Id AND d.MaxDate = d2.Date

--
Chandu

Kittyness
Starting Member

8 Posts

Posted - 2013-08-07 : 06:46:24

Thank you for all your time and efforts in helping me, but I'm not sure I fully understand the above.

is
JOIN @Contacts c ON l.Lookup_id = c.Group_1 AND l.LookUp_Name = 'WALMART'
meant to read
JOIN Contacts.ID ON Lookup.Lookup_id = Contacts.Group_1 AND Lookup.LookUp_Name = 'WALMART'

I don't know what this means, Points.p ?
JOIN @Points p ON p.Contacts_Id = c.id

and you have references to tables d and d2 - I guessed that d was DataElectric, but I don't know what d2 is.

This is also still relying on my end users hard-code typing the name of the group each time to do a search, I have hundreds of groups each with hundreds of sites on my database. I'd rather use the list of groups already contained in the Lookups_Name field as a parametre to assist my users and reduce potential error.

I don't know what JOIN @Contacts c , JOIN @Points p ON p.Contacts_Id = c.id or why there is a d and d2 for table names ?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-07 : 07:15:56

>> JOIN @Contacts c ON l.Lookup_id = c.Group_1 AND l.LookUp_Name = 'WALMART'
meant to read
JOIN Contacts.ID ON Lookup.Lookup_id = Contacts.Group_1 AND Lookup.LookUp_Name = 'WALMART'

Yes exactly...

l. c. d1. d2 are only the alias names for the corresponding table... Do not worry about those names..
Just replace @Contacts to Contacts , @LookUp to LookUp , @Points to Points and @DataElectricity to DataElectricity

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources