Record selection help - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Record selection help

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-08-24 : 13:09:55

Need some help on this please.

I would like the data result to only show those records where the 'level_trained' doesn't match the 'rev' column. These results must also not bring back any record that has 'No' in the 'level_trained' column.

My select statement is almost working, if I can only have it also return the null records that are found in the column 'level_trained'.

***********************

CREATE TABLE [dbo].[iso_employwi] (
[empWiID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[pin] [int] NOT NULL ,
[wi] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[rev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wi_level] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[level_trained] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_added] [smalldatetime] NULL ,
[date_trained] [smalldatetime] NULL ,
[wi_connection] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI46','C','B')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('001','WI47','A')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI48','G','1')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI49','A','No')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI50','F','F')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI51','E','E')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('001','WI52','H')

GO

SELECT pin, wi, rev, level_trained
FROM dbo.iso_employwi
WHERE (level_trained <> rev) AND (level_trained <> N'No')

GO

DROP TABLE dbo.iso_employwi

***********************

Thanks to anyone that can help,

JLM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-24 : 13:21:37

add this to the where:
WHERE (level_trained <> rev) AND (level_trained <> N'No') OR (level_trained is null)

is that what you need?

Go with the flow & have fun! Else fight the flow :)

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-08-24 : 14:10:19

Thanks for the help! I feel so dumb now, I actually had that statement in there, only I had it with an 'AND' instead of an 'OR'.

JLM

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-08-24 : 19:09:10

Now if I can also get help in creating a stored-procedure or writing a T-SQL statement from which I can launch from a scheduled job task. What I would like to do is for this task to go through the data results and email the user that the 'WI' has been updated.

Example of what the user pin #1 would recieve in an email:

-----------
From: wiupdate@server.com
Subject: WI update notification

<body of msg begin>
There has been some WI updates. Please read WI's:

WI46
WI47
WI48
WI52

Upon compleation, please update your status.
<body of msg end>
-----------

There is currently two different Select statements for sending email that work for me. I just don't have any idea on how to combine it with other code to have it do what I want. Here are the two Select statements:

#1
***********************************
declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'WIAdmin@Server1.com',
@TO = N'user1@test.com',
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = N'WI update notification',
@message = N'',
@type = N'text/plain',
@server = N'server2.com'
select RC = @rc
go
***********************************

#2
***********************************
CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@CC varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
GO
***********************************

Sample Tables/data
***********************************
CREATE TABLE [dbo].[iso_employwi] (
[empWiID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[pin] [int] NOT NULL ,
[wi] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[rev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wi_level] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[level_trained] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_added] [smalldatetime] NULL ,
[date_trained] [smalldatetime] NULL ,
[wi_connection] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI46','C','B')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('001','WI47','A')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI48','G','1')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI49','A','No')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI50','F','F')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI51','E','E')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('001','WI52','H')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('002','WI46','E','E')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('002','WI57','H')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('002','WI48','G','1')
GO

CREATE TABLE [dbo].[iso_employees] (
[pin] [int] NOT NULL ,
[email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO iso_employees (pin,email)
VALUES ('001','test1@abc.com')
INSERT INTO iso_employees (pin,email)
VALUES ('002','test2@abc.com')

GO

SELECT iso_employwi.pin, iso_employwi.wi, iso_employwi.rev, iso_employwi.level_trained,
iso_employees.email
FROM dbo.iso_employwi INNER JOIN dbo.iso_employees ON dbo.iso_employwi.pin = dbo.iso_employees.pin
WHERE (iso_employwi.level_trained <> iso_employwi.rev) AND
(iso_employwi.level_trained <> N'No') OR (iso_employwi.level_trained is null)

GO

DROP TABLE dbo.iso_employwi
DROP TABLE dbo.iso_employees
***********************************

And as always, thanks alot to anyone whom can help me!

JLM

maydo
Starting Member

20 Posts

Posted - 2004-08-26 : 00:42:08

I'm not sure if that's what you need but have a look:

--1. retrive the email-list of all recepients
declare @maillist varchar(8000)
select @maillist = COALESCE(@maillist + ';', '') + iso_employees.email
FROM dbo.iso_employwi INNER JOIN dbo.iso_employees ON dbo.iso_employwi.pin = dbo.iso_employees.pin
WHERE (iso_employwi.level_trained <> iso_employwi.rev) AND
(iso_employwi.level_trained <> N'No') OR (iso_employwi.level_trained is null)

--2. send emai
EXEC xp_sendmail @recipients = @maillist,
@query = 'put a SQL select statement returning the result set you want to send OR use the @message parameter below to send a static messge',
@subject = 'put your subject here',
@message = 'static messge',

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-08-27 : 13:56:58

Thanks Maydo but thats not the results I was looking for, maybe you or anyone else can help me fix the one below?

Below is a procedure Michael did for me that almost does what I'm looking for. His procedure emails one named person whom will get all the WI's from different user pins. What I needed was for this procedure to email each user with only their updates that they have to complete.

Example of what user 1 and user 2 would receive if the sample table/data is ran with a procedure that does what I need.

User 1 (test1@abc.com) would get an email:
-----------
From: wiupdate@server.com
Subject: WI update notification

<body of msg begin>
There has been some WI updates. Please read WI's:

WI46
WI47
WI48
WI52

Upon compleation, please update your status.
<body of msg end>
-----------

User 2 (test2@abc.com) would get an email:
-----------
From: wiupdate@server.com
Subject: WI update notification

<body of msg begin>
There has been some WI updates. Please read WI's:

WI48
WI57

Upon compleation, please update your status.
<body of msg end>
-----------

Example table/data:
----------------------------------
CREATE TABLE [dbo].[iso_employwi] (
[empWiID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[pin] [int] NOT NULL ,
[wi] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[rev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wi_level] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[level_trained] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_added] [smalldatetime] NULL ,
[date_trained] [smalldatetime] NULL ,
[wi_connection] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI46','C','B')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('001','WI47','A')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI48','G','1')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI49','A','No')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI50','F','F')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI51','E','E')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('001','WI52','H')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('002','WI46','E','E')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('002','WI57','H')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('002','WI48','G','1')
GO

CREATE TABLE [dbo].[iso_employees] (
[pin] [int] NOT NULL ,
[email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO iso_employees (pin,email)
VALUES ('001','test1@abc.com')
INSERT INTO iso_employees (pin,email)
VALUES ('002','test2@abc.com')

GO

SELECT iso_employwi.pin, iso_employwi.wi, iso_employwi.rev, iso_employwi.level_trained,
iso_employees.email
FROM dbo.iso_employwi INNER JOIN dbo.iso_employees ON dbo.iso_employwi.pin = dbo.iso_employees.pin
WHERE (iso_employwi.level_trained <> iso_employwi.rev) AND
(iso_employwi.level_trained <> N'No') OR (iso_employwi.level_trained is null)

GO

DROP TABLE dbo.iso_employwi
DROP TABLE dbo.iso_employees
----------------------------------

Here is Michaels procedure that almost does what I need:
----------------------------------
CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@CC varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
GO

CREATE PROCEDURE [dbo].[WI_updated]
as
declare @message varchar(4000)
declare @wi varchar(20)

set @message = ''

declare wi_cursor cursor for
SELECT iso_employwi.wi
FROM dbo.iso_employwi INNER JOIN dbo.iso_employees ON dbo.iso_employwi.pin = dbo.iso_employees.pin
WHERE (iso_employwi.level_trained <> iso_employwi.rev) AND
(iso_employwi.level_trained <> N'No') OR (iso_employwi.level_trained is null)

open wi_cursor

fetch next from wi_cursor into @wi

while @@fetch_status = 0
begin
set @message = @message + @wi + char(10)

fetch next from wi_cursor into @wi

end

set @message = 'There has been some WI updates. Please read WI''s' + char(10) + char(10) + @message + char(10) + char(10) + 'Upon compleation, please update your status.'

close wi_cursor
deallocate wi_cursor

exec [dbo].[sp_send_cdontsmail] 'wiupdate@Server.com', 'user@server.com', 'Update Notification', @message

GO
----------------------------------

I'm guessing another variable needs to be set for the user's email and have the procedure do some kind of loop until all the user's have been emailed of their notification?
" exec [dbo].[sp_send_cdontsmail] 'wiupdate@Server.com', @email, 'Update Notification', @message "?

Can someone help to fix this procedure to run the results I'm looking for, Thanks!

JLM

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-29 : 19:44:55

N'No'

Why N is used in front of 'No'
I never used it

mk_garg

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-29 : 20:46:49

level_trained is an Nvarchar (unicode) column. Putting N in front of a constant, as in N'No', has the same effect as Cast ('No' as Nvarchar).

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-29 : 20:58:53

Thanks Ken

mk_garg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources