22,209
社区成员
发帖
与我相关
我的任务
分享
print '01A' + '02A' + '03A' + '04A' + '05A' + '06A' + '07A' + '08A' + '09A' + '10A' + '11A' + '12A'
----------------------------------
01A02A03A04A05A06A07A08A09A10A11A12A
select * from
(select
SUM(ROUND(CASE WHEN Month(T1.ShipDate) = 1 THEN ShipQty ELSE 0 END,2)) AS '01A',
SUM(ROUND(CASE WHEN Month(T1.ShipDate) = 2 THEN ShipQty ELSE 0 END,2)) AS '02A',
SUM(ROUND(CASE WHEN Month(T1.ShipDate) = 3 THEN ShipQty ELSE 0 END,2)) AS '03A',
SUM(ROUND(CASE WHEN Month(T1.ShipDate) = 4 THEN ShipQty ELSE 0 END,2)) AS '04A',
SUM(ROUND(CASE WHEN Month(T1.ShipDate) = 5 THEN ShipQty ELSE 0 END,2)) AS '05A',
SUM(ROUND(CASE WHEN Month(T1.ShipDate) = 6 THEN ShipQty ELSE 0 END,2)) AS '06A',
SUM(ROUND(CASE WHEN Month(T1.ShipDate) = 7 THEN ShipQty ELSE 0 END,2)) AS '07A',
SUM(ROUND(CASE WHEN Month(T1.ShipDate) = 8 THEN ShipQty ELSE 0 END,2)) AS '08A',
SUM(ROUND(CASE WHEN Month(T1.ShipDate) = 9 THEN ShipQty ELSE 0 END,2)) AS '09A',
SUM(ROUND(CASE WHEN Month(T1.ShipDate) = 10 THEN ShipQty ELSE 0 END,2)) AS '10A',
SUM(ROUND(CASE WHEN Month(T1.ShipDate) = 11 THEN ShipQty ELSE 0 END,2)) AS '11A',
SUM(ROUND(CASE WHEN Month(T1.ShipDate) = 12 THEN ShipQty ELSE 0 END,2)) AS '12A',
Customer.SalesRepCode,Customer.name--,Customer.ShortChar01
from PUB.Customer
left join
(
select ShipHead.ShipDate,case when ShipDtl.PartNum = 'SXF01' then 0 else ShipDtl.OurInventoryShipQty end as ShipQty,ShipDtl.PartNum,OrderHed.OrderNum,OrderHed.SalesRepList,OrderHed.CustNum
from PUB.ShipHead
inner join PUB.ShipDtl on ShipHead.PackNum = ShipDtl.PackNum and ShipHead.Company = ShipDtl.Company
inner join PUB.OrderHed on ShipDtl.OrderNum = OrderHed.OrderNum and ShipDtl.Company = OrderHed.Company
inner join PUB.SalesRep on SalesRep.SalesRepCode = OrderHed.SalesRepList and SalesRep.Company = OrderHed.Company
where ShipHead.Company = 'Tube' and shipHead.ReadyToInvoice = 1 and ShipHead.ShipDate between '2011-1-1' and '2011-7-31' and OrderHed.SalesRepList = '1001' and SalesRep.Character01 = '104'
union all
select RMAHead.RMADate,case when RMADtl.PartNum = 'SXF01' then 0 else RMADtl.OurReturnQty * -1 end as ShipQty,RMADtl.PartNum,RMAHead.RMANum as OrderNum,OrderHed.SalesRepList,OrderHed.CustNum
from PUB.RMAHead
inner join PUB.RMADtl on RMAHead.RMANum = RMADtl.RMANum and RMAHead.Company = RMADtl.Company
inner join PUB.OrderHed on RMADtl.OrderNum = OrderHed.OrderNum and RMADtl.company = OrderHed.company
inner join PUB.SalesRep on SalesRep.SalesRepCode = OrderHed.SalesRepList and SalesRep.Company = OrderHed.Company
where RMAHead.Company = 'tube' and RMAHead.RMADate between '2011-1-1' and '2011-7-31'
and OrderHed.SalesRepList = '1001' and SalesRep.Character01 = '104'
) T1 on T1.CustNum = Customer.CustNum
where customer.Company = 'Tube'
group by Customer.name,Customer.SalesRepCode
) T2
where T2.SalesRepCode = '1001' or ('01A' + '02A' + '03A' + '04A' + '05A' + '06A' + '07A' + '08A' + '09A' + '10A' + '11A' + '12A') <> 0
create table tb
(
[01A] int,[02A] int,[03A] int,[04A] int,[05A] int,[06A] int,[07A] int,[08A] int,
[09A] int,[10A] int,[11A] int,[12A] int,SalesRepCode varchar(10),Name varchar(10)
)
insert into tb
select 0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,'1027' ,'SSS' union all
select 123 ,2 ,35 ,5 ,6 ,7 ,8 ,3 ,0 ,0 ,0 ,0 ,'1001' ,'DDD' union all
select 0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,'1001' ,'QQQ' union all
select 122 ,0 ,35 ,5 ,6 ,7 ,8 ,3 ,0 ,0 ,0 ,0 ,'1008' ,'BBB'
go
select *
from tb
where [01a]<> 0 or [02a]<> 0 or [03a]<> 0 or [04a]<> 0 or [05a]<> 0 or [06a]<> 0 or [07a]<> 0 or [08a]<> 0 or
[09a]<> 0 or [10a]<> 0 or [11a]<> 0 or [12a]<>0 or SalesRepCode = '1001'
drop table tb
/****************
01A 02A 03A 04A 05A 06A 07A 08A 09A 10A 11A 12A SalesRepCode Name
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------ ----------
123 2 35 5 6 7 8 3 0 0 0 0 1001 DDD
0 0 0 0 0 0 0 0 0 0 0 0 1001 QQQ
122 0 35 5 6 7 8 3 0 0 0 0 1008 BBB
(3 行受影响)
create table tb(a int,b int,c varchar(10))
insert into tb
select 0,0,'1027' union all
select -1,1,'1011' union all
select 1,0,'1011' union all
select 0,1,'1011'
go
select *
from tb
where a <> 0 or b <> 0 or c = '1011'
drop table tb
/************
a b c
----------- ----------- ----------
-1 1 1011
1 0 1011
0 1 1011
(3 行受影响)
create table tb
(
[01A] int,[02A] int,[03A] int,[04A] int,[05A] int,[06A] int,[07A] int,[08A] int,
[09A] int,[10A] int,[11A] int,[12A] int,SalesRepCode varchar(10),Name varchar(10)
)
insert into tb
select 0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,'1027' ,'SSS' union all
select 123 ,2 ,35 ,5 ,6 ,7 ,8 ,3 ,0 ,0 ,0 ,0 ,'1001' ,'DDD' union all
select 0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,'1001' ,'QQQ' union all
select 122 ,0 ,35 ,5 ,6 ,7 ,8 ,3 ,0 ,0 ,0 ,0 ,'1008' ,'BBB'
go
select *
from tb
where [01a]<> 0 or [02a]<> 0 or [03a]<> 0 or [04a]<> 0 or [05a]<> 0 or [06a]<> 0 or [07a]<> 0 or [08a]<> 0 or
[09a]<> 0 or [10a]<> 0 or [11a]<> 0 or [12a]<>0 or SalesRepCode = '1001'
drop table tb
select * from tb where 01A+012A+..+12A <> 0 or SalseRepCode=1001
select *
from tb
where 01A <> 0 or 02A <> 0 or ... or SalseRepCode=1001
select * from tb where 01A+012A+..+12A>0 or SalseRepCode=1001