How would I write this... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How would I write this...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-29 : 15:02:07

count(claim), with max dib_mvt_seq_num
where DIB_MVT_TYP='r', org_ID <> ' R%' or org_id <> ' S%'
or org_ID <> ' V%' or org_ID <> ' P%'

I got this to work but I don't think it's right. What does it mean by count(claim) with max dib_mvt_seq_num? How do I write this?


SELECT doc, 
       COUNT(claim)  AS pendinfo, 
       MAX(dib_mvt_seq_num) AS maxseq 
FROM   pending 
WHERE  ( dib_mvt_typ = 'r' ) 
       AND ( org_id <> ' R%' 
              OR org_id <> ' S%' 
              OR org_id <> ' V%' 
              OR org_id <> ' P%' ) 
GROUP  BY doc

Here are the results I got. Is this correct?


doc     claim      PendInFO2   MaxSeq
A59	346688	        2	4
358	354120	        2	5
A44	259184	        1	2
410	777347	        1	2
358	371383	        6	6
A39	225453267	2	4

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-10-29 : 16:41:57

quote:
Originally posted by JJ297
[brHere are the results I got. Is this correct?

Nope.

You can't use the wildcard character like that for one. Notice how this retuns all the rows:

DECLARE @T TABLE (Val VARCHAR(100))

INSERT @T VALUES
('Right'), (' Right'), ( ' Really'), ('Wrong')

SELECT *
FROM @T
WHERE Val <> ' R%'

WHere as this will return, what I think, are the correct rows:

SELECT *
FROM @T
WHERE Val NOT LIKE  ' R%'

Also, please post your DDL, DML and expected output. You have over 500 posts, so I'd think you would undertand how to post a proper question. It doesn't help you to have us guessing at what you want. this link will help you with that.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-10-29 : 19:33:02

Thanks so much. I'm sorry I didn't think my DDL and DML was needed for this one but I will send it for now on which makes sense so you can see where I'm stuck. The client was out today I will ask him Monday what are his expected results.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-03 : 10:04:29

Okay I have the table and insert information

Is there a better way to write this, am I getting the count with
max(dib_mvt_seq_num)?


SELECT doc, 
       COUNT(clmos)  AS pendinfo, 
       MAX(dib_mvt_seq_num) AS maxseq 
FROM   testtbl 
WHERE  ( dib_mvt_typ = 'r' ) 
       AND ( org_id <> ' R%' 
              OR org_id <> ' S%' 
              OR org_id <> ' V%' 
              OR org_id <> ' P%' ) 
GROUP  BY doc 


I got these results:
doc   Pendinfo  maxseq
001	3	2
002	1	1
003	1	2
004	1	2
005	1	2
090	1	2
200	1	2
250	1	2
C65	1	1


Here's the table and insert info.  Thanks!


CREATE TABLE [dbo].[TestTbl](
	[DOC] [varchar](3) NOT NULL,
	[CLMOS] [char](6) NOT NULL,
	[CLMOS1] [char](6) NOT NULL,
	[APP_RCPDT] [smalldatetime] NULL,
	[DIB_MVT_SEQ_NUM] [smallint] NULL,
	[ORG_ID] [varchar](4) NULL,
	[DIB_MVT_TYP] [char](1) NULL,
	[DIB_MVT_DT] [smalldatetime] NULL,
	[DIB_MVT_DEST] [varchar](4) NULL
) ON [PRIMARY]


[code]

insert into testtbl
select '200', '458962', '458962', '3/15/2010', '2', 'S22', 'R',  '3/19/2010', ' ' union all
select 'C07', '569962', '569962', '7/10/2010', '1', '022', 'T',  '7/20/2010', 'S51' union all
select '250', '895962', '895962', '2/21/2010', '2','S24', 'R',  '2/26/2010', '   ' union all
select '199', '999962', '999962', '2/13/2010', '1', '022', 'T',  '3/19/2010', 'S22'  union all
select '001', '452262', '452262', '6/15/2010', '2', 'S51', 'R',  '6/29/2010', '   '  union all
select 'A24', '352962', '352962', '8/15/2010', '1', 'B05', 'T',  '8/19/2010', 'S36' union all
select '004', '458697', '458697', '3/15/2010', '2', 'S51', 'R',  '3/26/2010', ' ' union all
select 'A46', '558962', '558962', '5/15/2010', '1', '022', 'T',  '5/30/2010', 'S51' union all
select '001', '698962', '698962', '9/15/2010', '2', 'S26', 'R',  '9/26/2010', '' union all
select '003', '518962', '518962', '6/23/2010', '1', 'C64', 'T',  '7/20/2010', 'S08' union all
select 'A46', '235962', '235962', '4/15/2010', '2', 'S22', 'T',  '4/19/2010', 'S22' union all

select 'C65', '245782', '245782', '8/27/2010', '1', 'C65', 'T',  '8/27/2010', 'S22' union all
select 'C65', '245782', '245782', '9/28/2010', '2', 'S22', 'R',  '10/01/2010', '' union all
select '002', '006079', '006079', '7/27/2010', '2','002', 'T',  '9/29/2010', '   ' union all
select '002', '568923', '568923', '7/16/2010', '1', 'S22', 'R',  '9/17/2010', '   '  union all
select '005', '455562', '455562', '7/4/2010', '2', '002', 'R',  '9/15/2010', '   '  union all
select '005', '455562', '455562', '5/28/2010', '1', '005', 'T',  '5/22/2010', 'S36' union all
select '001', '458874', '458874', '8/24/2010', '2', 'S22', 'R',  '9/26/2010', '' union all
select '001', '458874', '458874', '7/4/2010', '1', '001', 'T',  '8/30/2010', 'S51' union all
select '003', '695698', '695698', '8/24/2010', '2', 'S22', 'R',  '9/26/2010', '' union all
select '003', '518587', '518587', '8/27/2010', '1', '003', 'T',  '9/20/2010', 'S08' union all
select '090', '237896', '237896', '7/4/2010', '2', 'S08', 'R',  '7/4/2010', ''

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-03 : 12:30:23

Ok that's a good start. Now, can you tell us what you want as output?

Also, what are you trying to do with the Org_ID? Your current query has a multiple predicate reference to that column ( <> ' R%') but that doesn't do anything. If you completely remove those checks you'll notice that they have no affect on the result set. This is what I was talking about in the first post. I *think* you need to use a LIKE instead of using the <> (not-equal-to) operator.

Does your table have a key or any indexes?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-03 : 13:59:03

Thanks for getting back to me.

The client wrote down what they want from the table it's in two parts:

(1)Count(Clmos), with max(dib_mvt_seq_num) 
where Dib_MVT_TYP=R, org_id not = 'r' or 's' or 'v' or 'p' and
 (2) Count(Clmos), with max(Dib_MVT_seq_num) where DIB_MVT_TYP=T

This table is from a text file and has duplicates. I don't have indexs or a primary key now.

I need the Org_id field to select certain records pertaining to Dib_MVT_TYP=R

What I want to do is count all of the clmos with max Dib_mvt_seq_num for each Doc listed.

I didn't know how to write the statements together so I wrote them separatly then I was going to add the results into a temp table then add the fields (PendInFO2 + PendInFO3) to get my grand total


(select doc, count(clmos) as PendInFO2,  max(dib_mvt_seq_num) as
    MaxSeq
    from testtbl
    where (DIB_MVT_TYP='r') and (org_ID Not Like ' R%' or org_id
    Not Like ' S%' or org_ID Not Like ' V%'or org_ID Not Like ' P%')
    group by doc, clmos)a

inner join
(select doc, count(clmos) as PendInFO3, max(dib_mvt_seq_num) as 
    MaxSeq
    from testtbl
    where DIB_MVT_TYP='t'
    group by doc)b
on a.doc = b.doc

I want to know if this is right or is there an easier way to do this?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 15:43:34

(org_ID Not Like ' R%' or org_id Not Like ' S%' or org_ID Not Like ' V%'or org_ID Not Like ' P%')
Each OR should be an AND

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-03 : 16:02:29

I don't want anything that starts with the letters R, S, V or P that's in the Org_ID field. So should I use or or and?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-03 : 16:05:59

I tried it with and and get the same results.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 16:14:32

OR is definitely wrong!

NOT OR NOT is always true.

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-03 : 18:15:31

what about not in ('R%','S%','V%','P%'))

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 04:01:42

No.
Joker like % are only possible when using LIKE.

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-04 : 09:16:42

I actually need the wildcard because I don't want the one's that start with R, S, V, or P in the Org_id field I have some S22 and S24 values.

I got this to work and will put the results into a temp table and grab what I want from there:


(select doc, count(clmos) as PendInFO2, max(dib_mvt_seq_num) as MaxSeq
from testtbl
where (DIB_MVT_TYP='r') and  ORG_ID not in (' R%' ,' S%',' V%',' P%')
group by doc)b
on a.doc = b.doc

inner join
(select doc, count(clmos) as PendInFO3, max(dib_mvt_seq_num) as MaxSeq
from t2dibpend
where DIB_MVT_TYP='t'
group by doc)c
on a.doc = c.doc

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-04 : 12:09:10

I'll try one last time.. If you want to use a wild card, aka %. Then you need to use the wildcard in conjuction with the LIKE operator. If you look at the second post in this thread, you will see that using a wildcard without a like operator does NOTHING!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-04 : 13:00:14

I got this to work:

select doc, count(clmos) as PendInFO2, max(dib_mvt_seq_num) as MaxSeq
from testtbl
where (DIB_MVT_TYP='r') and
( org_id not like ' R%' or org_id not like ' S%' or
org_id not like ' V%' or
org_id not like ' P%')

group by doc)

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-04 : 13:00:47

Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-04 : 13:04:33

It's a pity.

NOT OR NOT is always true.

May the Force be with you...

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-04 : 15:23:07

ugh...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-11-04 : 15:35:12

quote:
Originally posted by webfred

It's a pity.

NOT OR NOT is always true.

May the Force be with you...

No, you're never too old to Yak'n'Roll if you're too young to die.

It would be false if org_id is NULL

Here is one way to do it:


select
	doc,
	count(clmos) as PendInFO2,
	max(dib_mvt_seq_num) as MaxSeq
from
	testtbl
where
	DIB_MVT_TYP='r'	and 
	case
	when org_id like ' R%' then 1
	when org_id like ' S%' then 1
	when org_id like ' V%' then 1
	when org_id like ' P%  then 1
	else 0
	end = 0
group by
	doc

CODO ERGO SUM

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-04 : 15:56:54

Thanks that worked too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources