SQL Query Help 1

[robert030975]

I am sure this is easy but I'm really unsure in how to approach this

Col a Col B Col c
2011-07-03 00:00:00 B Y
2011-07-04 00:00:00 S Y
2011-07-05 00:00:00 DO N
2011-07-06 00:00:00 S Y
2011-07-07 00:00:00 DO N
2011-07-08 00:00:00 S Y
2011-07-09 00:00:00 B Y


What I need is where COL B = DO if its between S then make COL C NA ( Can't have anything other than S between )



Thanks

 

[robert030975]

Got the first one


S DO S By


select distinct COLA, COLB, COLC
into #s1
from table
and CODE = ('DO')

select distinct COLA, COLB, COLC
into #s2
from table
and CODE = ('S')

Select * from #s1 a inner join #s2 b on a.COLa = b.colB-1
inner join #s2 c on a.fk_pemast = b.fk_pemast
and a.COLa = b.colB+1

Bit messy but does the trick,


Although there needs to be a second rule


S DO DO S Or S DO DO DO DO S it has to be grouped by days


M T W T F
S DO DO S DO SO friday would be exempt.

in the above youd have
04/07/11 S
05/07/11 DO
06/07/11 DO
07/07/11 S
08/07111 DO

 

[robert030975]

Ive joined it on to a calendar table but what condition can you use to select the 05/06??

NULL NULL 2011-07-03 00:00:00
NULL NULL 2011-07-04 00:00:00
DO 2011-07-05 00:00:00 2011-07-05 00:00:00
DO 2011-07-06 00:00:00 2011-07-06 00:00:00
NULL NULL NULL 2011-07-07 00:00:00
DO 2011-07-08 00:00:00 2011-07-08 00:00:00
NULL NULL NULL 2011-07-09 00:00:00

 

[Qik3Coder]

robert,

after 3 posts and trying infer your problem from the code posted, I have no idea what you're asking.

You're posting in 1/2 thoughts, which at least I cannot follow.


If I have your question correct, You need all the data, sorted in date order. Where the day before and the day after = 'S' then you need Col C to display as N/A instead of whatever is actually there?

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[robert030975]

yes sorry

there are 7 days in week

s
m s
t s
w
t
f s
s

what i would like to see is only a group of s

So m and t would be 1 whereas f would be zero.

 

[Qik3Coder]

DECLARE @_tbl as table (
colA Datetime,
colB varchar(1),
colC varchar(5))

INSERT INTO @_tbl
SELECT '20110703', '', '1' UNION ALL
SELECT '20110704', 's', '1' UNION ALL
SELECT '20110705', 's', '1' UNION ALL
SELECT '20110706', '', '1' UNION ALL
SELECT '20110707', '', '1' UNION ALL
SELECT '20110708', 's', '1' UNION ALL
SELECT '20110709', '', '1'

select b.colA, b.colb, CASE WHEN b.colb = 's' and a.colb = '' and c.colb = '' THEN '0' ELSE '1' END
from @_tbl b
LEFT JOIN @_tbl a ON
	b.colA-1 = a.colA
LEFT JOIN @_tbl c ON
	b.colA+1 = c.colA
--WHERE b.colB = 's'
order by colA

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[robert030975]

This sort of works but how would you handle if the S was on the 9th or 3rd?

 

[Qik3Coder]

it a rolling compare, so it's always a yesterday-today-tomorrow check. Sunday compares against Sat and Mon.

What doesn't work, or is missing?

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[robert030975]

My Bad I'm just being stupid ! Really appreciate the help though.