Solved

need help constructing a stored procedure

Posted on 2011-03-13
32
344 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
  • 20
  • 12
32 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:zachvaldez
Comment Utility
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
Comment Utility
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
Comment Utility
Is @Temp a temporary table or can I substitute it to the real table..?
0
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
the date params are datetime types not varchar-fyi
0
 

Author Comment

by:zachvaldez
Comment Utility
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
Comment Utility
btw whatis T.keyvalue in the temp table
0
 

Author Comment

by:zachvaldez
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
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
Comment Utility
If null,does it write Null or "?It has to " not Null
0
 

Author Comment

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

Accepted Solution

by:
JoeNuvo earned 500 total points
Comment Utility
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
Comment Utility
ok syntax is good but will test it. thanks
0
 

Author Comment

by:zachvaldez
Comment Utility
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
Comment Utility
Great! found the error but still testing
0
 

Author Comment

by:zachvaldez
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I got it all working now,I hope
0
 

Author Comment

by:zachvaldez
Comment Utility
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
Comment Utility
So far brilliant!
0
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now