August 10, 2012 at 8:36 am
I am executing the SQL Statements on SQL Server 2008 (SSMS).
What am I missing? (I'm sure it's me, not the computer - ha, ha)
Here's the SQL Statement to Update:UPDATE DIAPLUSDB.dbo.SalesForecast
set JUL_SAL = isnull(JUL_SAL,0) + isnull(ss.SLS_DOL,0)
FROM
DIAPLUSDB.dbo.SalesForecast sf
left join SALES_SUMMARY ss on ss.CSCODE = sf.CustID and ss.PLT_NO = sf.PlantID
WHERE ss.PPPERIOD = '201207' and ss.SLS_DOL is not null
There may be multiple SALES_SUMMARY records for each CustID/PlantID.
When I check the results it appears that only the first SALES_SUMMARY record for each CustID/PlantID is added to JUL_SAL.
When I list the records, they all show up:select sf.CustID, sf.PlantID, sf.JUL_SAL, ss.CSCODE, ss.PLT_NO, ss.SLS_DOL, ss.PPPERIOD
from DIAPLUSDB.dbo.SalesForecast sf
left join SALES_SUMMARY ss on ss.CSCODE = sf.CustID and ss.PLT_NO = sf.PlantID
WHERE ss.PPPERIOD = '201207' and ss.SLS_DOL is not null
order by sf.CustID, sf.PlantID
As you can see in the image, from the SELECT statement, CustID 1053 has 2 SALES_SUMMARY records, total SLS_DOL = 23,941.50. But, SalesForecast JUL_SAL = 11,751.00.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
August 10, 2012 at 8:45 am
You should learn about aggregate functions.
In your case you will need to use SUM()
August 10, 2012 at 8:59 am
Thanks. I started out trying to do just that - I use aggregate functions in SELECT statements.
I couldn't figure out how to construct the statement by including the SUM and could not find anything on the internet as an example to guide me.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
August 10, 2012 at 9:18 am
August 10, 2012 at 9:33 am
In your case you can use:
;WITH SUM_SALES_SUMMARY
AS
(
SELECT CSCODE AS CSCODE
,PLT_NO AS PLT_NO
,SUM(SLS_DOL) AS SUM_SLS_DOL
FROM SALES_SUMMARY
WHERE PPPERIOD = '201207'
)
UPDATE DIAPLUSDB.dbo.SalesForecast
SET JUL_SAL = ss.SUM_SLS_DOL
FROM DIAPLUSDB.dbo.SalesForecast AS sf
JOIN SUM_SALES_SUMMARY AS ss
ON ss.CSCODE = sf.CustID
AND ss.PLT_NO = sf.PlantID
August 12, 2012 at 10:34 pm
UPDATE sf
set JUL_SAL = isnull(sf.JUL_SAL,0) + isnull(ss.SLS_DOL,0)
FROM
DIAPLUSDB.dbo.SalesForecast sf
left join (
SELECT CSCODE, PLT_NO, SUM(SLS_DOL) AS Total_SLS_DOL
FROM SALES_SUMMARY
WHERE PPPERIOD = '201207' and SLS_DOL is not null
) ss on ss.CSCODE = sf.CustID and ss.PLT_NO = sf.PlantID
I'm not sure why do you need LEFT JOIN here.
If there are not corresponding records in SALES_SUMMARY the query will add 0 to JUL_SAL, leaving the value in there unchanged, but adding an overhead of a useless update.
Considering you WHERE clause effectively forced INNER JOIN anyway, I'd made it explicit.
_____________
Code for TallyGenerator
August 13, 2012 at 1:11 pm
Thank you all for our assistance. I got a solution from Utter Access message board.
UPDATE DIAPLUSDB.dbo.SalesForecast
SET JUL_FOR = isnull(sf.JUL_FOR,0) +
( SELECT SUM(SLS_DOL)
FROM SALES_SUMMARY
WHERE (SALES_SUMMARY.CSCODE = sf.CustID)
AND (SALES_SUMMARY.PLT_NO = sf.PlantID)
AND (SALES_SUMMARY.PPPERIOD = '201207')
AND (SALES_SUMMARY.SLS_DOL is Not NULL) )
FROM DIAPLUSDB.dbo.SalesForecast sf, SALES_SUMMARY
The structure isn't intuitive to me, but, now I have an understanding of the structure and can construct the same in the future (I think).
Sergiy, you asked, "I'm not sure why do you need LEFT JOIN here."
I guess I don't have a comprehensive understanding of the "mechanics" of the JOIN - what I call 'under the hood'. In my mind the record processing, is to view each SalesForecast record, find matches in SALES_SUMMARY (CSCODE & PLT_NO), but do not include those outside the Period (PPPERIOD) and those with NULL SLS_DOL. One thing I know is that there are fewer SalesForecast records than SALES_SUMMARY.
Anyway, Thanks all again for your input.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
August 13, 2012 at 10:24 pm
EdA ROC (8/13/2012)
Thank you all for our assistance. I got a solution from Utter Access message board.
That's probably the worst possible one.
It delivers pretty much the same result as mine or Eugene's queries, but it creates a hidden loop: it runs a separate query against the source table for each row in the target table.
On tables with some reasonable number of records it will be terribly slow. It's especially dangerous if to consider remote call involvement.
Try the ones from above and compare the execution plans and overall performance.
_____________
Code for TallyGenerator
August 14, 2012 at 1:26 pm
I hate it when UPDATE doesn't update correctly.
The probability of survival is inversely proportional to the angle of arrival.
August 15, 2012 at 8:10 am
Very funny Sturner - thanks for the smile.
I executed the 3 different statements as requested, each 3 times.
SALES_SUMMARY has 34,000 records, about 380 Bytes/record, data storage space is 14 MB
SalesForecast has 160 records, about 490 Bytes/record, .078 MB
The results are in microseconds:
Mine: 13, 13, 10
Sergiv: 6, 10, 6
Eugene: 6, 6, 10
The tables are small, but your point is taken - the difference is there.
Thanks for your help, I appreciate it.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply