Hi all,

I am a newbie with SQL and I have a statement that someone else wrote
for me.
It works up to a fashion but there seems to be a bug with it (or maybe
I didn't make it very clear to start with ;-P).

The statement needs to pull in details from a table. If there are more
than one record for a given 'siteID' then it needs to total (sum) most
of the fields, however, there are three fields in which it needs to
pull the _latest_ value through. The original statement uses 'last' to
achieve this but this approach seems to rely on the data being input in
the correct order which is not always the case.

The fields in question are a date field, an integer field and a
currency field so consequently, using 'max' is not possible because the
latest figure may not always be the greatest figure.

Here is the relevant SQL statement:

SQ = "SELECT tblSites.sites_siteno, " & _
" tblSites.sites_sitename, " & _
" Sum(tblStockResults.stkr_result) AS SumOfstkr_result, " & _
" Sum(tblStockResults.stkr_takings) AS SumOfstkr_takings, " & _
" Sum(tblStockResults.stkr_gp) AS SumOfstkr_gp, " & _
" Last(tblStockResults.stkr_closestkval) AS LastOfstkr_closestkval,
" & _
" Last(tblStockResults.stkr_SoHDays) AS LastOfstkr_SoHDays, " & _
" Last(tblStockResults.stkr_endat) AS LastOfstkr_endat, " & _
" sum(tblStockResults.stkr_cashdiff) as SumOfstkr_cashdiff, " & _
" sum(tblstockresults.stkr_resultcost) as SumOfstkr_resultcost, " &
_
<snip>
" sum(tblstockresults.stkr_var_sundries) as SumOfstkr_var_sundries,
" & _
" sum(tblstockresults.stkr_adjustment) as SumOfstkr_adjustment " &
_
" FROM tblSites " & _
" INNER JOIN tblStockResults " & _
" ON tblSites.sites_siteid = tblStockResults.stkr_siteid " & _
" WHERE tblStockResults.stkr_endat Is Not Null and stkr_endat
between #" & Format("27/2/06", "dd-mmm-yyyy") & "# and #" &
Format(ToDate, "dd-mmm-yyyy") & "# " & _
" GROUP BY tblSites.sites_siteno, tblSites.sites_sitename; "

I have been searching google-groups for an answer and came across a
statement similar to the following:

select *
from tbl
group by SSN,Name
having TestDate=max(Testdate)

but I am unsure of how to implement it and also I don't think that it
will work for the Currency and Integer fields.

I have a feeling that the answer lies in nesting where clauses (!) but
that is only a phrase that I have seen bandied around these groups and
I have no idea how to begin implementing it.

If you have read this far then thank you very much for any assistance
that you feel able to give.

Regards,

Alan Englefield

Hi Alan

See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data useful to anyone asnwering your question. Using the following
example data and assuming only one entry per site in tblStockResults on any
given day:
SELECT sites_siteno, sites_sitename

INTO #tblSites

FROM ( SELECT 1 AS sites_siteno, 'Site 1' AS sites_sitename

UNION ALL SELECT 2 , 'Site 2'

UNION ALL SELECT 3 , 'Site 3'

UNION ALL SELECT 4 , 'Site 4' ) s

SELECT stkr_siteid, stkr_result, stkr_takings, stkr_closestkval, stkr_enddat

INTO #tblStockResults

FROM ( SELECT 1 AS stkr_siteid, 10 AS stkr_result, 100 AS stkr_takings, 100
stkr_closestkval, CAST('20060101' AS datetime) AS stkr_enddat

UNION ALL SELECT 1 , 11 , 101 , 101, CAST('20060102' AS datetime)

UNION ALL SELECT 1 , 12 , 102 , 102, CAST('20060101' AS datetime)

UNION ALL SELECT 2 , 20 , 200 , 200, CAST('20060101' AS datetime)

UNION ALL SELECT 2 , 21 , 201 , 201, CAST('20060101' AS datetime)

UNION ALL SELECT 2 , 22 , 203 , 202, CAST('20060102' AS datetime)

UNION ALL SELECT 2 , 23 , 203 , 203, CAST('20060103' AS datetime)

UNION ALL SELECT 2 , 24 , 204 , 204, CAST('20060101' AS datetime)

UNION ALL SELECT 3 , 30 , 300 , 300, CAST('20060101' AS datetime)

UNION ALL SELECT 3 , 31 , 301 , 301, CAST('20060102' AS datetime)

UNION ALL SELECT 4 , 40 , 401 , 401, CAST('20060101' AS datetime) ) s

You can use:

SELECT t.sites_siteno,

t.sites_sitename,

SUM(R.stkr_result) AS SumOfstkr_result,

SUM(R.stkr_takings) AS SumOfstkr_takings,

l.stkr_closestkval

FROM #tblSites t

JOIN #tblStockResults R ON t.sites_siteno = R.stkr_siteid

JOIN ( SELECT stkr_siteid, MAX(stkr_enddat) AS stkr_enddat FROM
#tblStockResults GROUP BY stkr_siteid ) c ON c.stkr_siteid = t.sites_siteno

JOIN #tblStockResults l ON l.stkr_siteid = t.sites_siteno AND l.stkr_enddat
= c.stkr_enddat

WHERE R.stkr_enddat IS NOT NULL

AND R.stkr_enddat BETWEEN '20060101' AND GetDate()

GROUP BY t.sites_siteno, t.sites_sitename, l.stkr_closestkval

A safe date format is CCYYMMDD and you can use the GetDate() function to get
the current time and date.

John


<alanenglefield (AT) gmail (DOT) com> wrote

(alanenglefield (AT) gmail (DOT) com) writes:
Judging from the syntax, you are not using SQL Server (which is what
we know in this newsgroup). Judging from how the date is delimited,
I would guess that you are using Access. In that case you are
probably better off in comp.databases.ms-access. They may even know
what the last() function does.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

John,

Thanks for the assistance, that code is a little over my head but I am
going to pull it apart and see if I can understand what it is doing. It
looks, on first glance, like it _should_ work.

Alan

Erland,

You're right of course, it is in Access but it is just the SQL
statement that I had a query with and I thought that all forms of SQL
were a standard syntax. (I did say I was a newbie)

I will try it in there and see how I get on.

Thanks,
Alan

(alanenglefield (AT) gmail (DOT) com) writes:
You are right as far that there is a standard for SQL. There are in fact
several revisions of it. However, few RDBMS inmplements all of the standard,
even not of the earlier revisions. At the same time time many RDBMS have
their own extensions.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx