How do I add this to the procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 do I add this to the procedure?

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-23 : 11:32:43

Could someone help me add this select statement...

select clm from people where completedt is null

to the below query:



SELECT  n.DOC, ISNULL(COUNT(t.clmssn), 0) AS PendingInFO
FROM  t2DibPend AS t RIGHT OUTER JOIN
Natdocfile AS n ON n.DOC = t.doc 
AND DIB_MVT_SEQ_NUM IS NULL
group by n.doc
order by n.doc

I'm using three tables...Here are the table definitions:


CREATE TABLE [dbo].[T2DibPend](
	[DOC] [varchar](3) NOT NULL,
	[CLM] [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]


insert into T2DibPend
select '674', '005746', '9/23/2010' ' ', ' ', ' ', ' ',' ' union all
select 'C65', '005747', '4/19/2010' '2', 'C54', 'T', '4/28/2010','S22' union all
select '002', '005760', '8/31/2010' '2', 'S22', 'R', '9/2/2010',' ' union all
select '240', '016461', '7/20/2010' '2', 'S14', 'R', '7/23/2010',' ' union all
select '200', '016461', '7/20/2010' '2', 'S14', 'R', '7/23/2010',' ' union all
select 'A38', '016561', '9/28/2010' ' ', ' ', ' ', ' ',' ' union all
select '022', '009544', '7/16/2010' '2', 'S51', 'R', '8/6/2010','' union all
select '045', '016569', '7/8/2010' '1', '045', 'T', '7/8/2010','S24' union all
select '034', '019580', '7/30/2010' '1', '034', 'T', '7/30/2010','S24' union all
select '055', '020703', '6/4/2010' '1', '055', 'T', '6/4/2010','V37'


CREATE TABLE [dbo].[natdocfile](
	[doc] [varchar](3) NOT NULL)


insert into natdocfile

select '001' union all
select 'A00' union all
select '389' union all
select 'A38' union all
select '100' union all
select 'B14' union all
select '913' union all
select '009' union all
select '00K' union all
select '00N' union all
select '00U'


CREATE TABLE [dbo].[people](
	[clm] [char](6) NOT NULL,
	[lname] [char](6) NULL,
	[completedt] [datetime] NULL)
	


Insert into people

select '001091', 'Wester', NULL union all
select '001142', 'Demare', '8/1/2008' union all
select '001091', 'Ricci', '6/15/2008' union all
select '001149', 'Chapma', '6/3/2010' union all
select '001096', 'Grogan', Null union all
select '009622', 'Powers', NULL union all
select '009621', 'Bonnel', '6/3/2010' union all
select '009622', 'Tallma', NULL

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-23 : 12:16:28

you have 8 columns in T2DibPend, bur only 7 in the sample data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-23 : 13:19:54

Oops so sorry left out a comma try this:


insert into T2DibPend
select '674', '005746', '9/23/2010', ' ', ' ', ' ', ' ',' ' union all
select 'C65', '005747', '4/19/2010', '2', 'C54', 'T', '4/28/2010','S22' union all
select '002', '005760', '8/31/2010', '2', 'S22', 'R', '9/2/2010',' ' union all
select '240', '016461', '7/20/2010', '2', 'S14', 'R', '7/23/2010',' ' union all
select '200', '016461', '7/20/2010', '2', 'S14', 'R', '7/23/2010',' ' union all
select 'A38', '016561', '9/28/2010', ' ', ' ', ' ', ' ',' ' union all
select '022', '009544', '7/16/2010', '2', 'S51', 'R', '8/6/2010','' union all
select '045', '016569', '7/8/2010', '1', '045', 'T', '7/8/2010','S24' union all
select '034', '019580', '7/30/2010', '1', '034', 'T', '7/30/2010','S24' union all
select '055', '020703', '6/4/2010', '1', '055', 'T', '6/4/2010','V37'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-23 : 14:05:36

Your query is returning this:
Msg 207, Level 16, State 1, Line 55
Invalid column name 'clmssn'.

Your T2DibPend does not have that column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-23 : 14:08:02

Okay sorry try this:


SELECT  n.DOC, ISNULL(COUNT(t.clm), 0) AS PendingInFO
FROM  t2DibPend AS t RIGHT OUTER JOIN
Natdocfile AS n ON n.DOC = t.doc 
AND DIB_MVT_SEQ_NUM IS NULL
group by n.doc
order by n.doc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-23 : 14:10:48

Show us the expected output using your sample data for the end result that you want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-23 : 14:33:33

I only took a bit of data to create the tables so I could get some help but it wasn't enough to give me the results that I want, it returns all 0's. So when I added the bold part and ran it against the t2DibPend table I have on my server I got results! I will check with the client to see if results are correct.

SELECT n.DOC, ISNULL(COUNT(t.clm), 0) AS PendingInFO
FROM t2DibPend AS t RIGHT OUTER JOIN
Natdocfile AS n ON n.DOC = t.doc AND DIB_MVT_SEQ_NUM IS NULL and
t.clm not in (select clm from people
where completedt is null)
group by n.doc
order by n.doc


If you run the results from the sample data I sent I get:

DOC     PendingInFO
001          0
009          0
00K          0
00N          0
00U          0
100          0
389          0
913          0
A00          0
A38          0
B14          0

Thanks for your assistance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-23 : 14:35:36

Yes I did run your query to see the results, but that doesn't answer the question of what you want to display.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-11-23 : 14:58:37

I have to include the cases in the People table that are null in my counts. The cases that have dates in the People table are called SDW cases. I don't want to inlcude those cases in my count.

So I needed to add

select clm from people where completedt is null

I hope this makes sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources