November 6, 2017 at 5:55 am
I have a query that performs an average on a particular column but its giving me unexpected results and I just cant see why. I pass in a particular value to query against and Im not getting any results despite the fact that there are rows in the table that satisfy the query. Heres my query
) As AvEODPrice,
ProductMonth,
ProductYear
FROM ClosePricesPoint prices
inner join CloseCommodities commodities
on prices.CommodityId = commodities.CommodityId
inner join CloseProducts products
on prices.[ProductId] = products.[ProductId]
cross apply(SELECT
CASE WHEN LEFT(products.product, 3) = 'Jan' THEN
(SELECT 1)
WHEN LEFT(products.product, 3) = 'Feb' THEN
(SELECT 2)
WHEN LEFT(products.product, 3) = 'Mar' THEN
(SELECT 3)
WHEN LEFT(products.product, 3) = 'Apr' THEN
(SELECT 4)
WHEN LEFT(products.product, 3) = 'May' THEN
(SELECT 5)
WHEN LEFT(products.product, 3) = 'Jun' THEN
(SELECT 6)
WHEN LEFT(products.product, 3) = 'Jul' THEN
(SELECT 7)
WHEN LEFT(products.product, 3) = 'Aug' THEN
(SELECT 8)
WHEN LEFT(products.product, 3) = 'Sep' THEN
(SELECT 9)
WHEN LEFT(products.product, 3) = 'Oct' THEN
(SELECT 10)
WHEN LEFT(products.product, 3) = 'Nov' THEN
(SELECT 11)
WHEN LEFT(products.product, 3) = 'Dec' THEN
(SELECT 12)
WHEN LEFT(products.product, 2) = 'Q1' THEN --start at jan
(SELECT 1)
WHEN LEFT(products.product, 2) = 'Q2' THEN --start at apr
(SELECT 4)
WHEN LEFT(products.product, 2) = 'Q3' THEN --start at jul
(SELECT 7)
WHEN LEFT(products.product, 2) = 'Q4' THEN --start at jul
(SELECT 10)
WHEN LEFT(products.product, 1) = 'S' THEN --start at april for summer
(SELECT 4)
WHEN LEFT(products.product, 1) = 'W' THEN --start at october for winter
(SELECT 10)
END AS ProductMonth,
CONVERT(INT, RIGHT(products.product, 2)) AS ProductYear) Alias
where prices.[CommodityId] > 1
and (
CASE WHEN LEFT(products.product, 3) = 'Jan' THEN
(SELECT '01' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 3) = 'Feb' THEN
(SELECT '02' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 3) = 'Mar' THEN
(SELECT '03' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 3) = 'Apr' THEN
(SELECT '04' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 3) = 'May' THEN
(SELECT '05' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 3) = 'Jun' THEN
(SELECT '06' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 3) = 'Jul' THEN
(SELECT '07' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 3) = 'Aug' THEN
(SELECT '08' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 3) = 'Sep' THEN
(SELECT '09' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 3) = 'Oct' THEN
(SELECT '10' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 3) = 'Nov' THEN
(SELECT '11' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 3) = 'Dec' THEN
(SELECT '12' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 2) = 'Q1' THEN --start at jan
(SELECT '01' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 2) = 'Q2' THEN --start at apr
(SELECT '04' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 2) = 'Q3' THEN --start at jul
(SELECT '07' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 2) = 'Q4' THEN --start at jul
(SELECT '10' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 1) = 'S' THEN --start at april for summer
(SELECT '04' + RIGHT(products.product, 2))
WHEN LEFT(products.product, 1) = 'W' THEN --start at october for winter
(SELECT '10' + RIGHT(products.product, 2))
END) IN('0418','0518','0618','0718','0818','0918') -
and prices.[PriceType] = 'B'
and commodities.Commodity = 'base'
GROUP BY ProductMonth, ProductYear
order by ProductYear asc, ProductMonth
asc="sql"]
For a value of 'B' I get 6 rows, but if I pass in 'O' instead I get nothing but there are rows in the table that satisfy this condition, I've run a separate query to confirm this, so what am I doing wrong here ?
November 6, 2017 at 6:06 am
We can't answer this, because we do not have access to your data.
What I can suggest is that you trim down your query so that it contains only SELECT * + the JOINS and the WHERE conditions. From there, you can start removing things one at a time, to find out which JOIN or filter is the culprit.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 9, 2017 at 2:53 pm
Just to cross this item out, you are sure that your 'O' is not a'0' instead?
----------------------------------------------------
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply