August 16, 2013 at 2:31 pm
My count is incorrect when I do this query:
SELECT Records.JulianDate, Records.Records, COUNT(sales_view.JulianDate) AS Sales, COUNT(cancels_view.JulianDate) AS Cancels,
COUNT(sales_view.JulianDate) / Records.Records AS RP,
CASE WHEN COUNT(sales_view.JulianDate) = 0 THEN 0 ELSE (COUNT(cancels_view.JulianDate) / COUNT(sales_view.JulianDate)) END AS CP
FROM Records LEFT OUTER JOIN sales_view ON Records.Listcode = sales_view.listcode AND Records.JulianDate = sales_view.JulianDate
LEFT OUTER JOIN cancels_view ON Records.Listcode = cancels_view.listcode AND Records.JulianDate = cancels_view.JulianDate
GROUP BY Records.JulianDate, Records.Records, Records.Listcode
HAVING (Records.Listcode = 'AM') AND (Records.JulianDate = '362')
ORDER BY LEN(Records.JulianDate) DESC, Records.JulianDate DESC
When I run this query I get the correct amount of Records of 20000. But I get the incorrect number of Sales (119) and the Cancels is the same as the Sales (119). Now the correct amount of Sales with that listcode and juliandate is 17 and the correct amount of cancels is 7. Now if you multiply 17 * 7 you get 119. So how do I correct this?
August 16, 2013 at 4:10 pm
I would imagine that your query references VIEWS given the naming scheme, meaning we have no idea what is going on in your code. To have much hope of helping here we need full table and view definitions and sample data with expected outputs from that sample data.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 16, 2013 at 4:27 pm
I would guess the problem lies here:
LEFT OUTER JOIN sales_view ON Records.Listcode = sales_view.listcode AND Records.JulianDate = sales_view.JulianDate
LEFT OUTER JOIN cancels_view ON Records.Listcode = cancels_view.listcode AND Records.JulianDate = cancels_view.JulianDate
If a row in Records can hit multiple rows in sales_view and cancels_view for the same (ListCode, JulianDate), these rows will multiply with each other and inflate the count.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 16, 2013 at 4:36 pm
to theSQLGuru:
The reason I have a view was because my original data the juliandate and listcode are one field so the view splits the two out. There is nothing special in the view.
And to Erland:
that is what i figured but wasnt sure how to fix it from happening. I have just figured it out, with a little more searching and now learning new ways to do this.
SELECT Records.Listcode, Records.JulianDate, Records.Records, COALESCE (s.cnt, 0) AS Sales, COALESCE (c.cnt, 0) AS Cancels
FROM Records
LEFT OUTER JOIN
(SELECT listcode, JulianDate, COUNT(*) AS cnt FROM cancels_view GROUP BY listcode, JulianDate) AS c ON Records.Listcode = c.listcode AND Records.JulianDate = c.JulianDate
LEFT OUTER JOIN
(SELECT listcode, JulianDate, COUNT(*) AS cnt FROM sales_view GROUP BY listcode, JulianDate) AS s ON Records.JulianDate = s.JulianDate AND Records.Listcode = s.listcode
WHERE Records.Listcode = 'AM' AND Records.JulianDate = '362'
I am still needing to research exactly what COALESCE does/means. Thanks for the responses and trying to figure this out though!
August 17, 2013 at 3:20 am
ncurran217 (8/16/2013)
I am still needing to research exactly what COALESCE does/means. Thanks for the responses and trying to figure this out though!
coalesce(a, b, c, ...) is a shortcut for
CASE WHEN a IS NOT NULL THEN a
WHEN b IS NOT NULL THEN b
WHEN c IS NOT NULL THEN c
...
ELSE NULL
END
isnull() is a similar function, but isnull() does only accept two parameters and the return type of isnull() is always the type of the first expression, while the return type of coalesce() is the expression with the highest precedence.
coalesce is preferred by many since it is ANSI-compaitble, while isnull is proprietary to SQL Server. However, the implementation of coalesce in SQL Server leaves some to be desired; if a is a subquery, it is evaluated twice which can lead to surprises. For this reason, I've started to prefer isnull lately.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 19, 2013 at 3:50 am
I think it is the 2nd LEFT OUT JOIN condition problem.
1st: Records LEFT OUTER JOIN sales_view ON Records.Listcode = sales_view.listcode,
2nd: LEFT OUTER JOIN cancels_view ON Records.Listcode = cancels_view.listcode
I don't know why you still use Records table compare to cancels_views, that equals sales_view cross cancels_view,so you get 17*7=119
if you don't want 2 views cross, you should use inner join within 2 views, and then right outer join Records.
Am I right?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply