|
|
Posted by John Bell on 04/21/06 22:30
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@gmail.com> wrote in message
news:1145631018.320940.118840@i40g2000cwc.googlegroups.com...
> 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
>
Navigation:
[Reply to this message]
|