Не получается "отключить" условие

Игорь Горбонос
Дата: 25.02.2010 12:59:52
Здравствуйте
есть таблица и данные к ней:
+
CREATE TABLE [t1] (
[code] [int] IDENTITY (1, 1) NOT NULL ,
[data] [datetime] NULL ,
[tipe] [bit] NULL ,
[new] [bit] NULL ,
[refused] [bit] NULL ,
[manager_examine] [bit] NULL ,
[accepted] [bit] NULL ,
[boss_refused] [bit] NULL ,
[boss_stoped] [bit] NULL ,
[boss_accepted] [bit] NULL
CONSTRAINT [PK_applications] PRIMARY KEY CLUSTERED
(
[code]
) ON [PRIMARY]
)

INSERT INTO t1(data, tipe, new, refused, manager_examine, accepted, boss_refused, boss_stoped, boss_accepted) values('2010-03-01 00:00:00.000',1,1,0,0,0,0,0,0)
INSERT INTO t1(data, tipe, new, refused, manager_examine, accepted, boss_refused, boss_stoped, boss_accepted) values('2010-02-24 00:00:00.000',1,0,1,0,0,0,0,0)
INSERT INTO t1(data, tipe, new, refused, manager_examine, accepted, boss_refused, boss_stoped, boss_accepted) values('2010-02-24 00:00:00.000',1,0,0,1,0,0,0,0)
INSERT INTO t1(data, tipe, new, refused, manager_examine, accepted, boss_refused, boss_stoped, boss_accepted) values('2010-02-15 00:00:00.000',1,0,0,0,1,0,0,0)
INSERT INTO t1(data, tipe, new, refused, manager_examine, accepted, boss_refused, boss_stoped, boss_accepted) values('2010-02-24 00:00:00.000',1,0,0,0,0,1,0,0)
INSERT INTO t1(data, tipe, new, refused, manager_examine, accepted, boss_refused, boss_stoped, boss_accepted) values('2010-02-24 00:00:00.000',1,0,0,0,0,0,1,0)
INSERT INTO t1(data, tipe, new, refused, manager_examine, accepted, boss_refused, boss_stoped, boss_accepted) values('2010-03-01 00:00:00.000',1,0,0,0,0,0,0,1)

пытаюсь выбрать таким запросом:
+
DECLARE @m_year INT,
@m_month_start INT,
@m_month_stop INT,
@n_new bit
DECLARE @n_refused bit,
@n_manager_examine bit,
@n_accepted bit
DECLARE @n_boss_refused bit,
@n_boss_stoped bit,
@n_boss_accepted bit

SELECT @m_year = 2010,
@m_month_start = 2 ,
@m_month_stop = 2 ,
@n_new = 1 ,
@n_refused = 1 ,
@n_manager_examine = NULL,
@n_accepted = 1 ,
@n_boss_refused = NULL,
@n_boss_stoped = 1 ,
@n_boss_accepted = 1


SELECT *
FROM dbo.t1
WHERE
(
(
(
YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) >= @m_month_start
AND YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) <= @m_month_stop
AND dbo.t1.tipe = 1
AND dbo.t1.new = @n_new
AND dbo.t1.refused =0
AND dbo.t1.accepted =0
AND dbo.t1.manager_examine=0
AND dbo.t1.boss_refused =0
AND dbo.t1.boss_stoped =0
AND dbo.t1.boss_accepted =0
)
OR @n_new IS NULL
)
OR
(
(
YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) >= @m_month_start
AND YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) <= @m_month_stop
AND dbo.t1.tipe = 1
AND dbo.t1.new = 0
AND dbo.t1.refused =@n_refused
AND dbo.t1.accepted =0
AND dbo.t1.manager_examine=0
AND dbo.t1.boss_refused =0
AND dbo.t1.boss_stoped =0
AND dbo.t1.boss_accepted =0
)
OR @n_refused IS NULL
)
OR
(
(
YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) >= @m_month_start
AND YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) <= @m_month_stop
AND dbo.t1.tipe = 1
AND dbo.t1.new = 0
AND dbo.t1.refused =0
AND dbo.t1.accepted =0
AND dbo.t1.manager_examine=@n_manager_examine
AND dbo.t1.boss_refused =0
AND dbo.t1.boss_stoped =0
AND dbo.t1.boss_accepted =0
)
OR @n_manager_examine IS NULL
)
OR
(
(
YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) >= @m_month_start
AND YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) <= @m_month_stop
AND dbo.t1.tipe = 1
AND dbo.t1.new = 0
AND dbo.t1.refused =0
AND dbo.t1.accepted =@n_accepted
AND dbo.t1.manager_examine=0
AND dbo.t1.boss_refused =0
AND dbo.t1.boss_stoped =0
AND dbo.t1.boss_accepted =0
)
OR @n_accepted IS NULL
)
OR
(
(
YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) >= @m_month_start
AND YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) <= @m_month_stop
AND dbo.t1.tipe = 1
AND dbo.t1.new = 0
AND dbo.t1.refused =0
AND dbo.t1.accepted =0
AND dbo.t1.manager_examine=0
AND dbo.t1.boss_refused =@n_boss_refused
AND dbo.t1.boss_stoped =0
AND dbo.t1.boss_accepted =0
)
OR @n_boss_refused IS NULL
)
OR
(
(
YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) >= @m_month_start
AND YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) <= @m_month_stop
AND dbo.t1.tipe = 1
AND dbo.t1.new = 0
AND dbo.t1.refused =0
AND dbo.t1.accepted =0
AND dbo.t1.manager_examine=0
AND dbo.t1.boss_refused =0
AND dbo.t1.boss_stoped =@n_boss_stoped
AND dbo.t1.boss_accepted =0
)
OR @n_boss_stoped IS NULL
)
OR
(
(
YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) >= @m_month_start
AND YEAR(dbo.t1.data) = @m_year
AND MONTH(dbo.t1.data) <= @m_month_stop
AND dbo.t1.tipe = 1
AND dbo.t1.new = 0
AND dbo.t1.refused =0
AND dbo.t1.accepted =0
AND dbo.t1.manager_examine=0
AND dbo.t1.boss_refused =0
AND dbo.t1.boss_stoped =0
AND dbo.t1.boss_accepted =@n_boss_accepted
)
OR @n_boss_accepted IS NULL
)
)

результат:
codedatatipenewrefusedmanager_examineacceptedboss_refusedboss_stopedboss_accepted
12010-03-01 00:00:00.00011000000
72010-03-01 00:00:00.00010000001
52010-02-24 00:00:00.00010000100
62010-02-24 00:00:00.00010000010
22010-02-24 00:00:00.00010100000
32010-02-24 00:00:00.00010010000
42010-02-15 00:00:00.00010001000

а я хотел такой результат:
codedatatipenewrefusedmanager_examineacceptedboss_refusedboss_stopedboss_accepted
12010-03-01 00:00:00.00011000000
72010-03-01 00:00:00.00010000001
62010-02-24 00:00:00.00010000010
22010-02-24 00:00:00.00010100000
42010-02-15 00:00:00.00010001000


Не пойму, почему попадают в результат эти строки
codedatatipenewrefusedmanager_examineacceptedboss_refusedboss_stopedboss_accepted
52010-02-24 00:00:00.00010000100
32010-02-24 00:00:00.00010010000

Ведь они должны выпадать по условию OR @n_boss_refused IS NULL и OR @n_manager_examine IS NULL.

Почему этого не происходит и как изменить запрос?

Спасибо
alexeyvg
Дата: 25.02.2010 13:09:40
Игорь Горбонос
Ведь они должны выпадать по условию OR @n_boss_refused IS NULL и OR @n_manager_examine IS NULL.

Почему этого не происходит и как изменить запрос?

Спасибо
Так ведь OR @n_boss_refused IS NULL означает, что выводить если @n_boss_refused установлено в NULL.

А у вас так и есть
HandKot
Дата: 25.02.2010 14:17:50
если я правильно понял, не выводить записи с 1, если соответствующий параметр равен NULL, то
select 
	*
from
	#t1
where
	YEAR(#t1.data) = @m_year
	AND MONTH(#t1.data) between @m_month_start AND @m_month_stop
	AND #t1.tipe = 1
	AND
		case when #t1.new <> coalesce(@n_new, 0) then 1 else 0 end + 
		case when #t1.refused <> coalesce(@n_refused, 0) then 1 else 0 end +
		case when #t1.accepted <> coalesce(@n_accepted, 0) then 1 else 0 end +
		case when #t1.manager_examine <> coalesce(@n_manager_examine, 0) then 1 else 0 end + 
		case when #t1.boss_refused <> coalesce(@n_boss_refused, 0) then 1 else 0 end +
		case when #t1.boss_stoped <> coalesce(@n_boss_stoped, 0) then 1 else 0 end +
		case when #t1.boss_accepted <> coalesce(@n_boss_accepted, 0) then 1 else 0 end + 1=  
	 		 coalesce(@n_new, 0) + coalesce(@n_refused, 0) + coalesce(@n_accepted, 0) + coalesce(@n_manager_examine, 0) + coalesce(@n_boss_refused, 0) + coalesce(@n_boss_stoped, 0) + coalesce(@n_boss_accepted, 0)

или так
select 
	*
from
	#t1
where
	YEAR(#t1.data) = @m_year
	AND MONTH(#t1.data) between @m_month_start AND @m_month_stop
	AND #t1.tipe = 1
	AND NOT (
		(#t1.new = coalesce(@n_new, 1) and @n_new is null)
		OR (#t1.refused = coalesce(@n_refused, 1) and @n_refused is null)
		OR (#t1.accepted = coalesce(@n_accepted, 1) and @n_accepted is null)
		OR (#t1.manager_examine = coalesce(@n_manager_examine, 1) and @n_manager_examine is null)
		OR (#t1.boss_refused = coalesce(@n_boss_refused, 1) and @n_boss_refused is null)
		OR (#t1.boss_stoped = coalesce(@n_boss_stoped, 1) and @n_boss_stoped is null)
		OR (#t1.boss_accepted = coalesce(@n_boss_accepted, 1) and @n_boss_accepted is null)
	)

I Have Nine Lives You Have One Only
THINK!
Игорь Горбонос
Дата: 25.02.2010 15:38:14

alexeyvg, HandKot

Спасибо за мысли в правильном направлении
оказалось не в ту сторону думал
нужно было писать не OR @n_boss_refused IS NULL, а AND NOT @n_boss_refused IS NULL
и все заработало как нужно :)
З.Ы. Да и в тестовых данных две записи с третим месяцем, сори недоглядел

Posted via ActualForum NNTP Server 1.4