January 6, 2010 at 7:26 am
Hi to all,
Imagine that i have a table with the id of an ad, and i have datein (information about the day the ad was online) and i have dateout (information about the day the ad was offline), this 2 fields are datetime...
Now i need to know which ads were online in month x and year y.. this two parameters are type INT...
Which is the best way to do this?
Someone can help me?
January 6, 2010 at 7:30 am
Very carefully.
Actually, it would help us if you would provide us with the table definition(s) (CREATE TABLE statement(s)), sample date (a series of INSERT INTO statements) for the table(s), expected results based on the sample data provided, and most importantly what you have done so far to solve your problem.
January 6, 2010 at 7:33 am
Create either a permanent or inline calendar table and join to that.
Here's an example using a Numbers table to generate an inline calendar. I've got an add that ran from Jan through Mar, and I want to see what was active in February.
create table #Ads (
ID int identity primary key,
FromDate datetime,
ToDate datetime);
insert into #Ads (FromDate, ToDate)
select '1/1/2009', '3/1/2009';
;with InLineCalendar (Date) as
(select dateadd(day, Number, '2/1/2009') -- All the dates for February
from dbo.Numbers
where Number between 0 and 27)
select *
from #Ads
where exists
(select *
from InLineCalendar
where Date between FromDate and ToDate);
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2010 at 7:41 am
Hello,
I forgot...
CREATE TABLE Test (Ad_ID INT, DateIn DATETIME, DateOut DATETIME)
INSERT INTO Test
SELECT 1,'2009-05-26 00:00:00.000' ,'2009-05-28 00:00:00.000'
UNION
SELECT 2,'2008-06-26 00:00:00.000' ,'2009-12-28 00:00:00.000'
UNION
SELECT 3,'2009-03-12 00:00:00.000' ,'2009-12-28 00:00:00.000'
UNION
SELECT 4,'2009-10-26 00:00:00.000' ,'2009-10-28 00:00:00.000'
UNION
SELECT 5,'2009-09-26 00:00:00.000' ,'2009-12-28 00:00:00.000'
DECLARE @Month INT
DECLARE @Year INT
SET @Month = 10
SET @Year = 2009
select Distinct Ad_ID from test
WHERE (
( MONTH(Datein) = @Month AND YEAR(Datein) = @Year)
OR ( MONTH(DateOut) = @Month AND YEAR(DateOut) = @Year)
)
OR /*This part is not working correctly cause of the year*/
(MONTH(DateIn) < @Month AND YEAR(DateIn) = @Year
AND MONTH(DateOut) > @Month AND YEAR(DateOut) = @Year)
January 6, 2010 at 8:35 am
You should use variable with datetime type. It makes it a lot easier than dealing with the separate parts of a date value. It also helps performance since you don't aplly any function to the column you query against.
I used some basic math to get a number like 20091001 and converted that into a character type. The variable dayStart is datetime and SQL Server will perform an implicit conversion of '20091001' into a datetime value of #2009-10-01 00:00:00.000#
DECLARE @dayStart AS datetime
SET @dayStart=cast(@Year*10000+@Month*100+1 AS char(8))
select Distinct Ad_ID from @test-2
WHERE datein<dateadd(m,1,@dayStart) and dateout>=@dayStart
Edit: had to change the datein comparison to be < instead of <=
January 6, 2010 at 8:58 am
Hi guys,
Thanks to all 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply