Solved

need help constructing a stored procedure

Posted on 2011-03-13
32
350 Views
Last Modified: 2012-06-21
I have 2 tables
First Table has ff. structure and sample data

coID  smPID  PersonID         KeyCode Alpha            NDate             XDate                  YDate
0         1       32322                28                              4/1/2009
0          1      32322                49                                                       8/4/2005
0          1      32322                60              Yes                                          
0         1       92888                28                                7/1/2005    
0          1      92888                 60             Yes          
0          1      92888               37                                                                                  10/1/2009


Second Table

Person  ID           prmDate             Completed             Date_paid          report_date
82333                  6/1/2000                4/5/2001              8/1/2001
32322                  4/1/2009                Yes                    8/4/2005
92888                  7/1/2005                Yes                                            10/1/2009


Question; Id like to create a Insert  stored procedure  that would output to the first table based on the data
on second table.
For example  for personID 32322 in second table  if field prmDate is populated('4/1/2009') it would insert 28 in the keycode and '4/1/2009' in the Ncode. This action can be done only  if prmDate does not exist in first table. If keycode 28 already exists in first table, ignore it and do not insert Same logic also apply Date_paid(XDate in first table) and report Date. It would insert 49,60 depending what dates are populated in second table.
Generally First table can have mulitple records for same ID. I m assuming that the stored procedure will
have many INsert statements for each Date to test if exists...

I have about  many  records to loop and apply the stored procedure.

Please ask question if not clear.

0
Comment
Question by:zachvaldez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 20
  • 12
32 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35124685
you said it to be insert stored procedure.
does this stored procedure will perform insert into second table, and then insert into first table accordingly ?

or this stored procedure will insert into first table, base on data already exists in second table (and maybe with PersonID as input parameter) ?
0
 

Author Comment

by:zachvaldez
ID: 35124769
Inset only to First table, NO updates..

>>>or this stored procedure will insert into first table, base on data already exists in second table (and maybe with PersonID as input parameter)  YES

I think there should be a link between 2 tables which is personID then, check first if  for example
code 28 exists --this is the prmdate  field  in the second table   which is the Ndate value in first table,
if exist ignore it , if not insert code 28 and the Ndate in first table..
This would be the same method for inserting record  for   codes  60,37,49 in first table
For every code inserted, the dates or 'Yes; values are also inserted with it.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35124837
so, the mapping is as following ?

KeyCode   2nd table Field    1st Table Field
60             Completed           Alpha
28             prmDate              NDate
49             Date_Paid            XDate
37             report_date         YDate
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:zachvaldez
ID: 35124889
CORRECT!  those are only 3 examples  of the many codes that have to be inserted in the first table along with dates and alpha values, So one personID can have many records in the first table
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35124933
Since I don't know what is your data structure.
I just assume by your given example that, if there is no data, it will be blank string.
and since it can be blank string, data type should be char, not datetime or int etc.

here is the code

CREATE PROCEDURE CheckFirstTable
	@PersonID int
AS
Declare @Alpha varchar(20)
Declare @NDate varchar(20)
Declare @XDate varchar(20)
Declare @YDate varchar(20)

-- get value for each variable
SELECT @Alpha = Completed, @NDate = prmDate, @XDate = Date_Paid, @YDate = report_date
FROM SecondTable
WHERE PersonID = @PersonID

-- prepare expected data table for firsttable
Declare @Temp TABLE (KeyCode int, KeyValue varchar(20))
IF @Alpha <> '' INSERT INTO @Temp VALUES (60, @Alpha)
IF @NDate <> '' INSERT INTO @Temp VALUES (28, @Ndate)
IF @XDate <> '' INSERT INTO @Temp VALUES (49, @Xdate)
IF @YDate <> '' INSERT INTO @Temp VALUES (37, @Ydate)

-- insert data into firsttable, only for KeyCode which is not there
INSERT INTO FirstTable
SELECT
	0, 1, @PersonID, T.KeyCode, 
	CASE T.KeyCode WHEN 60 THEN T.KeyValue ELSE '' END,
	CASE T.KeyCode WHEN 28 THEN T.KeyValue ELSE '' END,
	CASE T.KeyCode WHEN 49 THEN T.KeyValue ELSE '' END,
	CASE T.KeyCode WHEN 37 THEN T.KeyValue ELSE '' END
FROM
	@Temp T
WHERE
	KeyCode NOT IN (SELECT KeyCode FROM FirstTable WHERE PersonID = @PersonID)

GO

Open in new window

0
 

Author Comment

by:zachvaldez
ID: 35125322
Is @Temp a temporary table or can I substitute it to the real table..?
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35125381
@temp is temporary table "variable"
you may change it to temporary table

Declare @Temp TABLE (KeyCode int, KeyValue varchar(20)) --> CREATE TABLE #Temp (KeyCode int, KeyValue varchar(20))

and replace all @Temp with #Temp

but if you want to change it to Actual Table, then you might need to delete old data in the table before insert value into it.
0
 

Author Comment

by:zachvaldez
ID: 35125439
What do you mean by delete data from  table?
What is a better method ? Use a temp variable or #Temp
Table ? Maybe I'm misunderstanding the concept ?
Of temp variable or temp table
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35125462
uses either kind of temp table is fine.
(just some older version of MSSQL may not support table variable)

the delete I mention, is in case you create permanent table (so, you don't have to create it inside stored procedure)
if you are using temp table as give example, we don't have to worry about delete, since table always new create.
0
 

Author Comment

by:zachvaldez
ID: 35129370
the date params are datetime types not varchar-fyi
0
 

Author Comment

by:zachvaldez
ID: 35129768
the code as well as the values corresponding to it like
keycode 28  is 'prmdate' '4/1/2009' should be inserted as well
0
 

Author Comment

by:zachvaldez
ID: 35130059
btw whatis T.keyvalue in the temp table
0
 

Author Comment

by:zachvaldez
ID: 35132230
modified table structure for First table
Instead of this...

First Table has ff. structure and sample data

coID  smPID  PersonID         KeyCode Alpha            NDate             XDate                  YDate
0         1       32322                28                              4/1/2009
0          1      32322                49                                                       8/4/2005
0          1      32322                60              Yes                                          
0         1       92888                28                                7/1/2005    
0          1      92888                 60             Yes          
0          1      92888               37                                                                                  10/1/2009

.. there willl be one field to handle all dates


First Table has ff. structure and sample data

coID  smPID  PersonID         KeyCode Alpha            Nfield             XDate
0                 32322                28                                                      4/1/2009
0          1      32322                49                                                       8/4/2005
0          1      32322                60              Yes                                          
0         1       92888                28                                                         7/1/2005    
0          1      92888                 60             Yes          
0          1      92888               37                                                         10/1/2009





0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35134475
Since "Date" field is datetime data type,
then back to sample table in your question.

the Second Table,  for PersonID = 32322, the report_date have no value display.
in database, what is this field stored?  is it NULL value?
0
 

Author Comment

by:zachvaldez
ID: 35134595
First of All,when Ipq copied and pasted,the fields were misaligned.All the date fields in the second table -prmdate,ndate,report date will be mapped to the xdate field of the second table,but with different keycodes.The alpha field will remain same.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35134679
Alright, here is what I'm assume for Second Table
1. Completed, will stored either 'Yes'   or empty string  ''
2. prmDate/Date_Paid/report_date  will either contain some date value or NULL

PersonID	prmDate		Completed	Date_paid	report_date
82333		6/1/2000				4/5/2001		8/1/2001
32322		4/1/2009	Yes		8/4/2005		NULL
92888		7/1/2005	Yes		NULL		10/1/2009

Open in new window

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35134699
Alright, here is what I'm assume for Second Table
1. Completed, will stored either 'Yes'   or empty string  ''
2. prmDate/Date_Paid/report_date  will either contain some date value or NULL

PersonID	prmDate		Completed	Date_paid	report_date
82333		6/1/2000			4/5/2001	8/1/2001
32322		4/1/2009	Yes		8/4/2005	NULL
92888		7/1/2005	Yes		NULL		10/1/2009

Open in new window


and here is new stored procedure.

CREATE PROCEDURE CheckFirstTable
	@PersonID int
AS
Declare @Alpha varchar(20)
Declare @NDate datetime
Declare @XDate datetime
Declare @YDate datetime

-- get value for each variable
SELECT @Alpha = Completed, @NDate = prmDate, @XDate = Date_Paid, @YDate = report_date
FROM SecondTable
WHERE PersonID = @PersonID

-- prepare expected data table for firsttable
Declare @Temp TABLE (KeyCode int, Alpha varchar(20), XDate datetime)
IF @Alpha <> '' INSERT INTO @Temp VALUES (60, @Alpha, NULL)
IF @NDate IS NOT NULL INSERT INTO @Temp VALUES (28, NULL, @Ndate)
IF @XDate IS NOT NULL INSERT INTO @Temp VALUES (49, NULL, @Xdate)
IF @YDate IS NOT NULL INSERT INTO @Temp VALUES (37, NULL, @Ydate)

-- insert data into firsttable, only for KeyCode which is not there
INSERT INTO FirstTable (coID, smPID, PersonID, KeyCode, Alpha, XDate)
SELECT
	0, 1, @PersonID, T.KeyCode, 
	CASE T.KeyCode WHEN 60 THEN T.Alpha ELSE '' END,
	CASE T.KeyCode
		WHEN 28 THEN T.XDate ELSE NULL END,
		WHEN 49 THEN T.XDate ELSE NULL END,
		WHEN 37 THEN T.XDate ELSE NULL END
FROM
	@Temp T
WHERE
	KeyCode NOT IN (SELECT KeyCode FROM FirstTable WHERE PersonID = @PersonID)

GO

Open in new window

0
 

Author Comment

by:zachvaldez
ID: 35135019
If null,does it write Null or "?It has to " not Null
0
 

Author Comment

by:zachvaldez
ID: 35138888
getting error 156--Incorrect syntax near the word 'When'
0
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 35139024
replace part of code with below

.
.
.
SELECT
	0, 1, @PersonID, T.KeyCode, 
	CASE T.KeyCode WHEN 60 THEN T.Alpha ELSE '' END,
	CASE WHEN T.KeyCode IN (28, 49, 37) THEN T.XDate ELSE NULL END
FROM
	@Temp T
.
.
.

Open in new window

0
 

Author Comment

by:zachvaldez
ID: 35139117
ok syntax is good but will test it. thanks
0
 

Author Comment

by:zachvaldez
ID: 35139383
Got an insert fail error
Cannot insert the value NULL int column 'XDate'
first table;column does not allow nulls. statment terminated
0
 

Author Comment

by:zachvaldez
ID: 35140047
Great! found the error but still testing
0
 

Author Comment

by:zachvaldez
ID: 35141059
I tried to change a date of one in the second table and run it and I get the error

"Syntax error converting the varchar value 'yes    ' to a column of data type int."
notice how 'yes got extra spaces
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35142865
As mention earlier that, I don't know your table structure
and how the data stored in case you consider there is no value

anyway, for error convert 'yes   ' to int
mean Completed is char, while Alpha is int
then I will assume (again) that Alpha = 1 if yes

CREATE PROCEDURE CheckFirstTable
	@PersonID int
AS
Declare @Completed varchar(20)
Declare @prmDate datetime
Declare @Date_Paid datetime
Declare @report_date datetime

-- get value for each variable
SELECT @Completed = Completed, @prmDate = prmDate, @Date_Paid = Date_Paid, @report_date = report_date
FROM SecondTable
WHERE PersonID = @PersonID

-- prepare expected data table for firsttable
Declare @Temp TABLE (KeyCode int, Alpha int, XDate datetime)
IF RTRIM(@Completed) = 'yes' INSERT INTO @Temp VALUES (60, 1, NULL)
IF @prmDate IS NOT NULL INSERT INTO @Temp VALUES (28, NULL, @prmdate)
IF @Date_Paid IS NOT NULL INSERT INTO @Temp VALUES (49, NULL, @Date_Paid)
IF @report_date IS NOT NULL INSERT INTO @Temp VALUES (37, NULL, @report_date)

-- insert data into firsttable, only for KeyCode which is not there
INSERT INTO FirstTable (coID, smPID, PersonID, KeyCode, Alpha, XDate)
SELECT
	0, 1, @PersonID, T.KeyCode, 
	CASE T.KeyCode WHEN 60 THEN T.Alpha ELSE 0 END,
	CASE WHEN T.KeyCode IN (28, 49, 37) THEN T.XDate ELSE NULL END
FROM
	@Temp T
WHERE
	KeyCode NOT IN (SELECT KeyCode FROM FirstTable WHERE PersonID = @PersonID)

GO

Open in new window

0
 

Author Comment

by:zachvaldez
ID: 35143716
alpha and completed have botg data types of CHAR.

I have 4 keys in the first table which are

coID  smPID  PersonID         KeyCode

Using the previous sp (not the one above) I was able to insert the records.  However for testing purposes, I changed the date paid under that personID,  and that's when I get the error.

What I'd like the stored proc to do is to not to have the ability to change the xdate or yes values if they already exist in the first table even though it was changed in the second table. So there should be somekind of checking. I don;t know if the 4 primary keys would already be enough to handle that.
OK, I will test your new stored procedure.
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35143819
my given code design to perform insert into first table for any missing related information base on second table.
if already exists, will just ignore,  no matter value is matched with second table or not.

the code perform following step
1. read data from second table for input @PersonID and put in into variable
    - you should adjust the data type to match with your table
2. check from variable to consider if it contain data, if it is, insert into @Temp table
    - you have to adjust the check condition
      for ex if RTRIM(@Completed) = 'yes'
               if @prmDate IS NOT NULL           -- if no data = NULL
               if @prmDate = '01 JAN 1900'       -- if no data contain date value as 01 JAN 1900
3. insert into first table for KeyCode which not there in first table for selected @PersonID
    - I don't know the condition of coID or smPID, I just put fixed value base on your given example
    - the CASE command, is to design which value to put into which field
    so, field Alpha will put value if KeyCode = 60
    and field XDate will put value if KeyCode in 28, 37, 48
    - you may need to adjust the value after ELSE to record proper value for your "no data"

I wish this give you clear understanding on what is my process try to do, So you can adjust it to meet your requirement.
Let me know if anything unclear and further help need,
0
 

Author Comment

by:zachvaldez
ID: 35148521
Here's my tbl structure on First Table where the insert happens.hope it helps


coID       smallint   for this I enter 0
smPID      tinyint     always 1
PersonID   int  
KeyCode    char
Alpha      char
Nfield     int     always 0
XDate      datetime
cc         char       always ''
cn         tinyint     always 0  
bc         char         always ''
bn          tinyint      always 0
ba         int           always  0
hw         char         always 'K'

BTW, the alpha field should be char because it takes differenr description for different keycodes
0
 

Author Comment

by:zachvaldez
ID: 35156107
I got it all working now,I hope
0
 

Author Comment

by:zachvaldez
ID: 35156179
Just to let you know,I stick with keeping Alpha and Completed fields to be chars data types. I modified and added the line If Rtrim('yes').....Thanks a lot. I never  done anything like this before. Fantastic!
0
 

Author Closing Comment

by:zachvaldez
ID: 35156367
So far brilliant!
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35156405
glad to hear you start to find some way out of the maze :)

let me know later if still something you can't solve.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question