January 24, 2011 at 12:06 pm
Please can someone help to point out what i'm doing wrong here:
Select o.location, o.boardnumber, o.secs, o.amount from tbl_outdoor o inner join tbl_boardsize as b on o.secs = b.boardsizeid and o.fk_stateid = b.fk_stateid where case when o.fk_stateid = 'LA' then b.fk_stateid = 'LA' When o.fk_stateid = 'FC' then b.fk_stateid = 'FC' when o.fk_stateid in ('OY', 'OS', 'OD', 'EK', 'KW') then b.fk_stateid = 'GA' End
In short how can i use an IN statement in the case statement to identify which states id belong to LA group or FC group or GA group.
Thanks
January 24, 2011 at 1:02 pm
try it this way:
Select o.location, o.boardnumber, o.secs, o.amount
from tbl_outdoor o
inner join tbl_boardsize as b on o.secs = b.boardsizeid and o.fk_stateid = b.fk_stateid
where b.fk_stateid = case when o.fk_stateid = 'LA' then 'LA'
When o.fk_stateid = 'FC' then 'FC'
when o.fk_stateid in ('OY', 'OS', 'OD', 'EK', 'KW') then 'GA' End
_____________________________________________________________________
- Nate
January 25, 2011 at 1:24 am
Thanks so much nate, you solved the problem, Thanks, I was wondering if i can use the case inside the join such as
Select o.location, o.boardnumber, o.secs, o.amount
from tbl_outdoor o
inner join tbl_boardsize as b on o.secs = b.boardsizeid and o.fk_stateid = b.fk_stateid and b.fk_stateid = b.fk_stateid = case when o.fk_stateid = 'LA' then 'LA'
When o.fk_stateid = 'FC' then 'FC'
when o.fk_stateid in ('OY', 'OS', 'OD', 'EK', 'KW') then 'GA' End
where o.date between '1 dec 2010' and '31 dec 2010'
Thanks so much for your input
January 25, 2011 at 6:15 am
Sure, you just need to cleanup the join.
use
...and b.fk_stateid = case when...
instead of
...and b.fk_stateid = b.fk_stateid = case when...
_____________________________________________________________________
- Nate
January 26, 2011 at 1:05 am
Thanks so much for your help Nate, it works so nicely. Please i have another chalenge, i have this data:
insert into tbl_radio (AdDate, AdTime, Station, Duration) Values (
select '12-12-2010', '09:08:05', 'b104', '45' union all
select '12-12-2010', '09:08:06', 'b104', '45'
)
if u notice, you will find that i have an AD for the same date, different time, same station, and same duration of the AD, but the duration is 45 secs, and i'm having the next record as '09:08:06' meaning that an error occured during the loggin of the AD.
Please how can i filter the records such that i'll be able to calculate the duration and remove records that does not make up the time of the duration of the running AD.
I tried this query to bring out the ADs that fall under the same minute but its like its not giving me the right results:
SELECT AdDate, AdTime, station, Duration
FROM tbl_Radio
WHERE (FK_ProductId = 'cge006') AND (FK_BrandId = '0005') AND (AdDate BETWEEN '1 dec 2010' AND '31 dec 2010')
group by AdDate, AdTime, station, Duration
having count(substring(adtime,1,5)) > 1
order by station, addate, adtime
Thanks for your help
January 28, 2011 at 9:45 am
Just to be clear, when the first record's time plus the duration (45 sec) is greater than the second record's time then omit the second record?
_____________________________________________________________________
- Nate
January 28, 2011 at 10:11 am
Thanks Nate, thats exactly what i want.
January 28, 2011 at 11:49 am
Seems like there should be a better solution but this seems to work.
CREATE TABLE #tbl_radio (
AdDate CHAR(12)
, AdTime CHAR(8)
, Station CHAR(4)
, Duration TINYINT
)
INSERT INTO #tbl_radio (AdDate, AdTime, Station, Duration) VALUES ('12-12-2010', '09:08:05', 'b104', '45')
INSERT INTO #tbl_radio (AdDate, AdTime, Station, Duration) VALUES ('12-12-2010', '09:08:06', 'b104', '45') -- FAIL CASE
INSERT INTO #tbl_radio (AdDate, AdTime, Station, Duration) VALUES ('12-12-2010', '09:08:07', 'b104', '45') -- FAIL CASE
INSERT INTO #tbl_radio (AdDate, AdTime, Station, Duration) VALUES ('12-12-2010', '10:08:05', 'b104', '45')
INSERT INTO #tbl_radio (AdDate, AdTime, Station, Duration) VALUES ('12-12-2010', '10:08:06', 'b104', '45') -- FAIL CASE
INSERT INTO #tbl_radio (AdDate, AdTime, Station, Duration) VALUES ('12-13-2010', '09:08:05', 'b104', '45')
INSERT INTO #tbl_radio (AdDate, AdTime, Station, Duration) VALUES ('12-13-2010', '09:08:06', 'b104', '45') -- FAIL CASE
GO
WITH tbl_radio (AdDate, AdTime, Station, Duration, DateRank, DateDuration)
AS (
SELECTAdDate, AdTime, Station, Duration
, DENSE_RANK() OVER (PARTITION BY AdDate ORDER BY AdTime)
, DATEADD(SS,Duration,CONVERT(DATETIME,AdTime))
FROM#tbl_radio
)
SELECTDISTINCT tmp.AdDate, tmp.AdTime, tmp.Station, tmp.Duration
FROMtbl_radio tmp
LEFT OUTER JOIN (
SELECTt1.Station
, t1.AdDate
, t1.DateRank
, MIN(t2.DateRank) AS t2DateRank
FROMtbl_radio t1
LEFT OUTER JOIN tbl_radio t2
ON t1.Station = t2.Station
AND t1.AdDate = t2.AdDate
AND t1.DateRank < t2.DateRank
ANDt2.AdTime NOT BETWEEN CONVERT(DATETIME,t1.AdTime) AND t1.DateDuration
GROUP BY t1.Station
, t1.AdDate
, t1.DateRank
) x ON tmp.Station = x.Station
AND tmp.AdDate = x.AdDate
AND tmp.DateRank = x.t2DateRank
WHEREtmp.DateRank = 1
ORx.t2DateRank IS NOT NULL
DROP TABLE #tbl_radio
_____________________________________________________________________
- Nate
January 31, 2011 at 2:03 pm
Thanks so much Nate, sorry i'm replying so late, thanks for your solution, it really did the trick. You're blessed.
Thanks
I'll try some experiments on it, and i'll get back to you. Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply