April 19, 2005 at 4:17 pm
Can anybody show me where I am going wrong with this subquery:
Table with SerialNo, Date and CountValue. Each day each Serial number may or may not have a CountValue recorded. I need to find the last Date against each SerialNo that had a value recorded. Ex., if current date 4 Feb, SerialNo A may have last date of 1 Feb and SerialNo B may have 3 Feb.
Select Statement returns 2 records correctly:
SELECT SerialNo, Max(PeriodEndDate), AS EndDate FROM tblAssetUtil Group BY SerialNo
SubQuery Statement below returns "returned more than 1 value. This is not permitted". If I alter it slighly I get too many records being reurned ie., the max statement is not working
SELECT SerialNo, Max(PeriodEndDate), AS EndDate FROM tblAssetUtil as t1 WHERE SeriaNo = (SELECT SerialNo From tblAssetUtil WHERE t1.PeriodEndDate = tblAssetUtil.PeriodEndDate) Group BY SerialNo
Many thanks
Ian
April 19, 2005 at 4:48 pm
Do your max select in an embedded (temp table) query that you join to
select ...
from tablex a
JOIN ( select key, max( datefield )
from tablex where datefield is not null group by key
) b on a.key = b.key
April 20, 2005 at 3:32 am
There is no MAX statement within your subquery, so I assume that's why you get more than one serialno for each day...
/Kenneth
April 20, 2005 at 10:12 am
Thank you Kenneth and John for your thoughts
I have tried the Max criteria in both subquery and outer query to no avail.
John, I have tried your suggestion and the error message has now changed to "error converting datetime from character string". So this looks like a step forward and will have to review what and how the data is being stored in the db
Thanks again
Ian
April 20, 2005 at 1:19 pm
Hello, Ian:
If your data looks something like this:
create table test1 (serialno int, date1 datetime, countvalue int)
insert test1 values ('1', '02/01/2005',0)
insert test1 values ('1', '02/03/2005',200)
insert test1 values ('1', '02/04/2005',200)
insert test1 values ('1', '02/07/2005',0)
insert test1 values ('2', '02/05/2005',100)
insert test1 values ('2', '02/07/2005',200)
insert test1 values ('3', '02/01/2005',600)
insert test1 values ('3', '02/02/2005',0)
insert test1 values ('3', '02/04/2005',200)
insert test1 values ('4', '02/01/2005',100)
insert test1 values ('4', '02/02/2005',0)
insert test1 values ('4', '02/03/2005',0)
insert test1 values ('4', '02/04/2005',0)
Then
SELECT serialno, convert(varchar(10),date1,112)as date1, countvalue
FROM test1
would yield the results:
serialno date1 countvalue
----------- ---------- -----------
1 20050201 0
1 20050203 200
1 20050204 200****
1 20050207 0
2 20050205 100
2 20050207 200****
3 20050201 600
3 20050202 0
3 20050204 200****
4 20050201 100****
4 20050202 0
4 20050203 0
4 20050204 0
and if the rows marked with **** are the ones you want, then you could use:
SELECT serialno, convert(varchar(10),max(date1),112)as date1
FROM test1
WHERE countvalue<>0
GROUP BY serialno
which would yield:
serialno date1
----------- ----------
1 20050204
2 20050207
3 20050204
4 20050201
HTH - Linda
April 21, 2005 at 2:51 am
Hi Linda
Thank you for your suggestion above. You fully understand what I am trying to do and we are nearly there, but missing from the final Select statement are the CountValues. And this is where I have been coming unstuck.
Using your last select statement and labelling it t1 (this finds the filtered records that are needed)
SELECT serialno, convert(varchar(10),max(date1),112)as date1
FROM test1 as t1
WHERE countvalue<>0
GROUP BY serialno
Then, using this filtered information relook at the table as a whole and bring back the CountValue just for those filtered records with the link being on SerialNo and Date.
I believe it is the linking of the above select statement with the next select statement making a subquery. But perhaps a subquery is not right here?
SELECT serialno, convert(varchar(10),max(date1),112)as date1 , CountValue
FROM test1 as t2
Join on t1.SerialNo = t2.SerialNo and t1.Date = t2.Date
See what you think
Thanks
Ian
April 21, 2005 at 3:53 am
An update, .....
Having reviewed and now understanding John's and Linda's above notes more, I have constructed the following and getting Ambiguous or Invalid column name "PeriodEndDate" which I don't know how to solve.
SELECT a.SerialNo, Convert(varchar(10),PeriodEndDate,112) as Date CountValue FROM tblAssetUtil as a
JOIN
(SELECT SerialNo, Convert(varchar(10),Max(PeriodEndDate),112) as Date FROM tblAssetUtil as b
WHERE CountValue <>0 GROUP BY SerialNo)
b on
a.SerialNo = b.SerialNo and a.PeriodEndDate = b.PeriodEndDate
Ian
April 21, 2005 at 4:06 am
Try this...
SELECT x.serialno,
x.maxDate,
t.countvalue
FROM (
SELECT serialno,
max(convert(char(10), date1, 112)) as maxDate
FROM test1
WHERE countvalue <> 0
GROUP BY serialno
) x
JOIN test1 t
ON x.serialno = t.serialno
AND x.maxDate = convert(char(10), t.date1, 112)
/Kenneth
April 21, 2005 at 4:19 am
Kenneth - It Works.
The way you have constructed the statement below I have not seen used before, ... so one to fix in "the old memory" for the future
SELECT x.serialno,
x.maxDate,
t.countvalue
Many thanks for this Kenneth and everybody for their contributions
Regards
Ian
April 21, 2005 at 6:22 am
It is the same (only written out more complete) that John suggested in the 2nd post in this thread.
The select between the parens forms a resultset which you can view as a temptable-on-the-fly.
It's then named as 'x', so you can reference the columns within by the alias x and use it pretty much as a 'normal' table.
/Kenneth
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply