November 6, 2009 at 12:45 am
My table and data as follow,
-- TrnxID in @tPosiHdr is unique
declare @tPosiHdr TABLE (
TrnxID int not null,
strPosi varchar (20) NOT NULL)
insert into @tPosiHdr values(1,'00')
insert into @tPosiHdr values(2,'00')
insert into @tPosiHdr values(3,'00')
insert into @tPosiHdr values(4,'00')
insert into @tPosiHdr values(5,'00')
insert into @tPosiHdr values(6,'00')
insert into @tPosiHdr values(7,'00')
-- relationship between @tPosiHdr and @tPosiDtl is 1:many
declare @tPosiDtl TABLE (
TrnxID int not null,
Posi varchar (20) NOT NULL)
insert into @tPosiDtl values(2,'10')
insert into @tPosiDtl values(3,'10')
insert into @tPosiDtl values(4,'00')
insert into @tPosiDtl values(2,'10')
insert into @tPosiDtl values(3,'10')
insert into @tPosiDtl values(4,'10')
insert into @tPosiDtl values(5,'00')
insert into @tPosiDtl values(2,'00')
insert into @tPosiDtl values(3,'10')
insert into @tPosiDtl values(7,'10')
after query as follow
select t1.TrnxID,strPosi,
posi
from
@tPosiHdr t1 left outer join @tPosiDtl t2
on t1.TrnxID=t2.TrnxID
i got,
TrnxID |strPosi | Posi
-------------------------------
1 00 NULL
2 00 10
2 00 10
2 00 00
3 00 10
3 00 10
3 00 10
4 00 00
4 00 10
5 00 00
6 00 NULL
7 00 10
My objective is,
1. if Posi <> null and also if strPosi <> Posi, count(Posi)
As a result, my resultset will return as follow
TrnxID |strPosi | count(Posi)
---------------------------------------
1 00 0
2 00 2
3 00 3
4 00 1
5 00 0
6 00 0
7 00 1
How my SQL look's like?
Really need help
November 6, 2009 at 1:36 am
Hi,
Try this
select t1.TrnxID,strPosi,posi,count(posi)coun
from
@tPosiHdr t1 left outer join @tPosiDtl t2
on t1.TrnxID=t2.TrnxID
and t1.strPosi <> t2.Posi
group by t1.TrnxID,strPosi,posi
November 6, 2009 at 3:24 am
arun.sas (11/6/2009)
Hi,Try this
select t1.TrnxID,strPosi,posi,count(posi)counfrom
@tPosiHdr t1 left outer join @tPosiDtl t2
on t1.TrnxID=t2.TrnxID
and t1.strPosi <> t2.Posi
group by t1.TrnxID,strPosi,posi
My origin table as follow,
-- TrnxID in @tPosiHdr is unique
declare @tPosiHdr TABLE (
TrnxID int not null,
strPosi varchar (20) NOT NULL)
if my new table as follow,
-- TrnxID in @tPosiHdr is unique
declare @tPosiHdr TABLE (
TrnxID int not null,
TTID varchar(20) null,
RNme varchar(20) null,
strPosi varchar (20) NOT NULL)
so at group by, i've to change into
group by t1.TrnxID,t1.TTID,t1.RNme,strPosi,posi
is that true?
November 6, 2009 at 3:37 am
Sharul Nizam (11/6/2009)
is that true?
Hi,
NO, group by all columns gives difference count in the above case; post your full table with sample data may give some clarity.
November 6, 2009 at 3:38 am
Try this
select t1.TrnxID,sum(case when Posi is not null and strPosi <> Posi then 1 else 0 end)
from
@tPosiHdr t1 left outer join @tPosiDtl t2
on t1.TrnxID=t2.TrnxID
group by t1.TrnxID
November 6, 2009 at 7:49 am
arun.sas (11/6/2009)
Sharul Nizam (11/6/2009)
is that true?Hi,
NO, group by all columns gives difference count in the above case; post your full table with sample data may give some clarity.
My table and data full version,
declare @tPosiHdr TABLE (
RID varchar(30) not null,
TrnxID int not null,
RNam varchar(30) null,
BusN varchar(30) null,
TTime varchar(6) not null,
TripN numeric not null,
TripDte datetime not null,
TTID varchar(30) null,
SeatN numeric not null,
strPosi varchar (20) NOT NULL)
insert into @tPosiHdr
values('002','176','KT-JB','TX4456','140000',4,'10/16/2009','',40,'00')
insert into @tPosiHdr
values('002','175','KT-JB','TX2389','170000',3,'10/16/2009','',44,'00')
insert into @tPosiHdr
values('002','174','KT-JB','TX3489','120000',2,'10/16/2009','',20,'00')
insert into @tPosiHdr
values('002','173','KT-JB','TX2389','200000',1,'10/16/2009','',36,'00')
--RID always same, TrnxID always unique, RNam alway same
--BusN may be same may be not, TTime always unique
--TripDte always same, TTID sometime no value sometime have value
--SeatN may be same may be not, strPosi always same
declare @tPosiDtl TABLE (
TrnxID int not null,
Posi varchar (20) NOT NULL)
insert into @tPosiDtl values(173,'10')
insert into @tPosiDtl values(174,'10')
insert into @tPosiDtl values(174,'00')
insert into @tPosiDtl values(173,'00')
insert into @tPosiDtl values(173,'10')
insert into @tPosiDtl values(175,'00')
--Relation between @tPosiHdr and @tPosiDtl is a 1:many
select t1.RID,t1.TrnxID,t1.RNam,t1.BusN,t1.TripN,
t1.TripDte,t1.TTID,t1.SeatN,t1.strPosi,
t2.posi
from
@tPosiHdr t1 left outer join @tPosiDtl t2
on t1.TrnxID=t2.TrnxID
November 6, 2009 at 8:31 pm
Sharul Nizam (11/6/2009)
--RID always same, TrnxID always unique, RNam alway same--BusN may be same may be not, TTime always unique
--TripDte always same, TTID sometime no value sometime have value
--SeatN may be same may be not, strPosi always same
Hi,
The values of count is same when you group by all, ensure the TTID column value before group this ,if the value exists than use the max(TTID) in the select statement, and remove the TTID from group by class. And the value not exists in the TTID; use the SET ANSI_WARNINGS OFF for removes the warning message.
select t1.RID,t1.TrnxID,t1.RNam,t1.BusN,t1.TripN,t1.TripDte,t1.TTID,t1.SeatN,t1.strPosi,t2.posi,count(posi)coun
from @tPosiHdr t1 left outer join @tPosiDtl t2
on t1.TrnxID=t2.TrnxID
and t1.strPosi <> t2.Posi
group by t1.RID,t1.TrnxID,t1.RNam,t1.BusN,t1.TripN,t1.TripDte,t1.TTID,t1.SeatN,t1.strPosi,t2.posi
RESULT
RID|TrnxID|RNam|BusN|TripN|TripDte|TTID|SeatN|strPosi|posi|coun
002|173|KT-JB|TX2389|1|2009-10-16 00:00:00.000||36|00|10|2
002|174|KT-JB|TX3489|2|2009-10-16 00:00:00.000||20|00|10|1
002|175|KT-JB|TX2389|3|2009-10-16 00:00:00.000||44|00||0
002|176|KT-JB|TX4456|4|2009-10-16 00:00:00.000||40|00||0
select t1.TrnxID,strPosi,posi,count(posi)coun
from
@tPosiHdr t1 left outer join @tPosiDtl t2
on t1.TrnxID=t2.TrnxID
and t1.strPosi <> t2.Posi
group by t1.TrnxID,strPosi,posi
RESULT
TrnxID|strPosi|posi|coun
173|00|10|2
174|00|10|1
175|00||0
176|00||0
November 8, 2009 at 5:57 am
arun.sas (11/6/2009)
Sharul Nizam (11/6/2009)
--RID always same, TrnxID always unique, RNam alway same--BusN may be same may be not, TTime always unique
--TripDte always same, TTID sometime no value sometime have value
--SeatN may be same may be not, strPosi always same
Hi,
The values of count is same when you group by all, ensure the TTID column value before group this ,if the value exists than use the max(TTID) in the select statement, and remove the TTID from group by class. And the value not exists in the TTID; use the SET ANSI_WARNINGS OFF for removes the warning message.
select t1.RID,t1.TrnxID,t1.RNam,t1.BusN,t1.TripN,t1.TripDte,t1.TTID,t1.SeatN,t1.strPosi,t2.posi,count(posi)counfrom @tPosiHdr t1 left outer join @tPosiDtl t2
on t1.TrnxID=t2.TrnxID
and t1.strPosi <> t2.Posi
group by t1.RID,t1.TrnxID,t1.RNam,t1.BusN,t1.TripN,t1.TripDte,t1.TTID,t1.SeatN,t1.strPosi,t2.posi
RESULT
RID|TrnxID|RNam|BusN|TripN|TripDte|TTID|SeatN|strPosi|posi|coun
002|173|KT-JB|TX2389|1|2009-10-16 00:00:00.000||36|00|10|2
002|174|KT-JB|TX3489|2|2009-10-16 00:00:00.000||20|00|10|1
002|175|KT-JB|TX2389|3|2009-10-16 00:00:00.000||44|00||0
002|176|KT-JB|TX4456|4|2009-10-16 00:00:00.000||40|00||0
select t1.TrnxID,strPosi,posi,count(posi)counfrom
@tPosiHdr t1 left outer join @tPosiDtl t2
on t1.TrnxID=t2.TrnxID
and t1.strPosi <> t2.Posi
group by t1.TrnxID,strPosi,posi
RESULT
TrnxID|strPosi|posi|coun
173|00|10|2
174|00|10|1
175|00||0
176|00||0
Did you have any sample
1 - if some TTID value exists than use the max(TTID) in the select statement, and remove the TTID from group by class
2- if all TTID value not exist -- me already have the SQL
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply