March 28, 2011 at 12:54 pm
I have the following, and I'm getting results not in the date I specify. Is there anything wrong with my query?
SELECT Fios.bucket AS StartDate,Fios.io_line_item_id,SUM(Fios.Views) AS Impression,SUM(Fios.Sales) AS Sales,SUM(Fios.Sales/Fios.Views) AS CTR,
SUM(Fios.post_Views+Fios.post_Sales) AS Conversions,io.name,line.budget,
SUM(Fios.Views*line.unit_price/1000)AS Spend,Fios.io_id AS IO,Fios.advertiser_id
FROM Fios_delivery_daily Fios
inner JOIN uas_ios io
ON io.id=Fios.io_id
INNER JOIN uas_io_line_items line
ON line.id=Fios.io_line_item_id
WHERE Fios.advertiser_id IN ('162','163')
AND Fios.date BETWEEN '2011-3-1' AND '2011-3-31'
GROUP BY Fios.io_line_item_id,io.name,line.budget
March 28, 2011 at 1:47 pm
I might be way off track, but doesn't the between function require a full date and time specification? Try making that part of your sql statement look more like this:
WHERE......datetimevalue BETWEEN('2010-01-01 00:00:00.000') AND ('2010-01-01 23:59:59.999')
The one I put there shows everything that happenned between midnight and 11:59 PM on January 1st, 2010.
March 28, 2011 at 1:59 pm
Leaving the time off defaults it to midnight (i.e. '2011-3-1' = '2011-03-01 00:00:00.000').
Syntax looks ok. Noticed that you are returning a different date field in the select then you're filtering on in the where clause:
Fios.bucket in the select
Fios.date in the where clause
_____________________________________________________________________
- Nate
March 28, 2011 at 2:35 pm
What is the date '2011-3-1' supposed to represent? Is it March 1st or January 3rd?
SQL is going to interpret that date according to the date format for the server. In this case, I am betting that the server is interpreting it as YYYY-DD-MM.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply