i have 2 tables
TABLE1
--
DESC
CODE
SEVERITY
CODE is the key here.
(SEVERITY ranges from 0 to 7)
TABLE2
--
CODE1
CODE2
CODE3
CODE4
CUST_ID
CALL_NBR
CUST_ID and CALL_NBR form the key.
CODE1 to CODE4 reference CODE in TABLE1
There can be at max 2 CALL_NBR per CUST_ID in TABLE2 i.e. at max 2
records per customer.
Now, what i need is
Extract 4 codes (CODE1, CODE2, CODE3, CODE4 frOM TABLE2...[remember,
there can be a max of 8 codes per customer]) into 4 variables per
customer based on priority defined in TABLE1This design definitely needs a rethink. The 4 Code columns are called a
repeating group and you should not have repeating groups in tables. There's
not really enough information to go on but I would expect a correctly
normalised design to look something like this:
CREATE TABLE CallCodes (descript VARCHAR(20) NOT NULL UNIQUE, code INTEGER
PRIMARY KEY, severity INTEGER NOT NULL CHECK (severity BETWEEN 0 AND 7))
CREATE TABLE CustomerCalls (cust_id INTEGER /* REFERENCES Customers
(cuist_id) */, call_nbr INTEGER, code INTEGER REFERENCES CallCodes (code),
PRIMARY KEY (cust_id, call_nbr, code) /* or (cust_id, call_nbr)? */)
Don't use Desc as a column name because it's a reserved word. When posting,
include DDL (CREATE TABLE statements, as above) with your posts so that it's
clear what the keys, constraints and data types are.
To find the 5 highest priority codes for each customer, assuming 7 is the
highest priority:
SELECT cust_id, code
FROM CustomerCalls AS A
WHERE code IN
(SELECT TOP 5 WITH TIES U.code
FROM CustomerCalls AS U
JOIN CallCodes AS O
ON U.code = O.code
WHERE U.cust_id = A.cust_id
ORDER BY O.severity DESC)
Note that if you had six codes for a customer with priorities 7,6,5,4,3,3
then this query will return all six codes, not five. You didn't specify what
you wanted to do if there were tied priorities so I've left them all in.
> Extract 4 codes (CODE1, CODE2, CODE3, CODE4 frOM TABLE2...[remember,
> there can be a max of 8 codes per customer]) into 4 variables per
> customer based on priority defined in TABLE1
Four variables per *customer*? You shouldn't need to do that in a relational
database. Please explain what you want to do and someone should be able to
suggest an alternative method.
--
David Portas
SQL Server MVP
--|||[Repost]
...
To find the 4 highest priority codes for each customer, assuming 7 is the
highest priority:
SELECT cust_id, code
FROM CustomerCalls AS A
WHERE code IN
(SELECT TOP 4 WITH TIES U.code
FROM CustomerCalls AS U
JOIN CallCodes AS O
ON U.code = O.code
WHERE U.cust_id = A.cust_id
ORDER BY O.severity DESC)
Note that if you had five codes for a customer with priorities 7,6,5,4,4
then this query will return all five codes, not four. You didn't specify
what you wanted to do if there were tied priorities so I've left them all
in.
> Extract 4 codes (CODE1, CODE2, CODE3, CODE4 frOM TABLE2...[remember,
> there can be a max of 8 codes per customer]) into 4 variables per
> customer based on priority defined in TABLE1
Four variables per *customer*? You shouldn't need to do that in a relational
database. Please explain what you want to do and someone should be able to
suggest an alternative method.
--
David Portas
SQL Server MVP
--|||Thanks for the response.
Well! I do understand that it is not a proper relational system but,
redesigning the table schemas is ruled out since it is a existing system
with lots of applications using it. The reason we have the 4 codes
stored per customer as different columns is coz we get these from an
external system based on many crietria,.
Here the DDLs of my 2 existing tables
CREATE TABLE [dbo].[MIX310] (
[REASON_CODE] [varchar] (2) NULL ,
[REASON_CODE_DESCRIPTION] [varchar] (80) NULL ,
[SEVERITY_CODE] [numeric](1, 0) NULL ,
[REASON_CODE_DESCRIPTION_SL] [varchar] (80) NULL
) ON [PRIMARY]
GO
note: SEVERITY ranges from 0 to 7
CREATE TABLE [dbo].[CUSTSWRESP] (
[CUST_ALT_ID] [varchar] (15) NOT NULL ,
[CALL_NBR] [numeric](1, 0) NOT NULL ,
[INPUT_MSG] [ntext] NULL ,
[MESSAGE_1] [varchar] (2) NULL ,
[MESSAGE_2] [varchar] (2) NULL ,
[MESSAGE_3] [varchar] (2) NULL ,
[MESSAGE_4] [varchar] (2) NULL ,
[SCORE] [varchar] (5) NULL ,
[RECOMMENDATION] [varchar] (2) NULL
(there are many other cols here...)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CUSTSWRESP] ADD
CONSTRAINT [PK_CUSTSWRESP] PRIMARY KEY CLUSTERED
(
[CUST_ALT_ID],
[CALL_NBR]
) ON [PRIMARY]
GO
Note: The only things of revelance in this case in CUSTSWRESP table are
the [CUST_ALT_ID], [MESSAGE_1],[MESSAGE_2],[MESSAGE_3] and [MESSAGE_4]
columns in our case.
Also, the MESSAGE_1...4 (message codes) refer to the REASON_CODE in
MIX310.
As I had earlier said, there can be at max 2 CALL_NBR for CUST_ALT_ID
i.e. at max 2 records per customer in CUSTSWRESP which implies have at
max 8 message codes. Also, all or just some of these message codes may
have values.
Now, i need to extract upto 4 REASON_CODE_DESCRIPTION from MIX310 based
on SEVERITY_CODE per CUST_ALT_ID where the message codes[MESSAGE_1...4]
in CUSTSWRESP match the REASON_CODE in MIX310.
I need urgent help with this. Please , let me know if there is any other
details needed.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||I'll assume that reason_code is unique in Mix310 even though that table
doesn't have a primary key (!).
CREATE VIEW CustomerMessages
(cust_alt_id, reason_code)
AS
SELECT cust_alt_id, message_1
FROM
(SELECT cust_alt_id, message_1
FROM Custswresp
UNION ALL
SELECT cust_alt_id, message_2
FROM Custswresp
UNION ALL
SELECT cust_alt_id, message_3
FROM Custswresp
UNION ALL
SELECT cust_alt_id, message_4
FROM Custswresp) AS M
WHERE message_1 IS NOT NULL
SELECT cust_alt_id, reason_code
FROM CustomerMessages AS T
WHERE reason_code IN
(SELECT TOP 4 WITH TIES C.reason_code
FROM CustomerMessages AS C
JOIN Mix310 AS M
ON C.reason_code = M.reason_code
WHERE C.cust_alt_id = T.cust_alt_id
ORDER BY M.severity_code DESC)
--
David Portas
SQL Server MVP
--|||Hi David!
Thanks for the quick response.
I could get it working and it gives the desired outcome.
I'll most probably twist it a little to use a #temp table instead of a
view since i m using it inside a stored procdure which recieves
cust_alt_id as input parameter.
I'll repost it when i m done for your comments since i m still a sql
novice.
Thanks!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||If you just wanted to return a result for a single customer at a time you
can do this:
SELECT TOP 4 WITH TIES M.reason_code, M.reason_code_description
FROM
(SELECT cust_alt_id, message_1
FROM Custswresp
UNION ALL
SELECT cust_alt_id, message_2
FROM Custswresp
UNION ALL
SELECT cust_alt_id, message_3
FROM Custswresp
UNION ALL
SELECT cust_alt_id, message_4
FROM Custswresp) AS
C (cust_alt_id,reason_code)
JOIN Mix310 AS M
ON C.reason_code = M.reason_code
WHERE C.cust_alt_id = @.cust_alt_id
ORDER BY M.severity_code DESC
--
David Portas
SQL Server MVP
--|||O! thatz a great help!!
I had atually started going the temp table way...
A lil more help...this returns me the top 4 reason codes descriptions.
Now, what i need (and what i apoligize for not having elaborated
earlier) is to assign these to 4 to local variables within the procedure
since i need them for some other processing and also return them to the
component which is invoking the stored procedure.
I need something like...
SELECT @.EmpiricaFirstFactor = ISNULL(REASON_CODE_DESCRIPTION,'')
SELECT @.EmpiricaSecondFactor = ISNULL(REASON_CODE_DESCRIPTION,'')...and
so on.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Rather than assign four variables and then manipulate them further, try to
extend my query to produce the end result you require and then return that
result set from the stored procedure. In general you should try to keep
step-by-step procedural processing to a minimum in SQL.
One problem with your specification is the handling of tied values. If you
have only four variables but you have five different reason_codes with
severities 7,6,5,4,4 , which values of reason_code_description do you want
to return? The two values with severity 4 might have different descriptions
but you only want to return one of them. At least add reason_code into the
ORDER BY clause so that you get a consistent (but not necessarily useful)
result in that case:
...
ORDER BY M.severity_code DESC, C.reason_code
If you must assign four variables in your SP then you can try the following.
However this is a rather questionable, undocumented and possibly not 100%
reliable method of doing this. I strongly recommend that you return the
values as a result set instead.
DECLARE @.empirica1 VARCHAR(80), @.empirica2 VARCHAR(80), @.empirica3
VARCHAR(80), @.empirica4 VARCHAR(80)
SELECT TOP 4
@.empirica1 = @.empirica2,
@.empirica2 = @.empirica3,
@.empirica3 = @.empirica4,
@.empirica4 = M.reason_code_description
FROM
...
--
David Portas
SQL Server MVP
--|||>> I do understand that it is not a proper relational system but,
redesigning the table schemas is ruled out since it is a existing
system with lots of applications using it. <<
LOL! Everytime I read "redesigning the schema is not allowed", I keep
thinking that: (1) This guy has decided that bankruptcy and total
failure ARE allowed! (2) I'm going to get a consulting job with a huge
daily rate in about a year
If this is true, then the application and the backend have been
coupled together much, much too tightly.
>> the reason we have the four codes stored per customer as different
columns is because we get these FROM an external system based on many
criteria. <<
Unh?? That has absolutely nothing to do with how the database stores
the facts. Quit mimicking a physical file layout.
>> here the DDLs of my two existing tables <<
MIX310 is not a table and it can never be a table. There are no keys
and with the NULL-able columns,there can never be a key. You failed
to put in a known constraint on the table. Your datatypes are wrong
-- VARCHAR(2) is a bitch for the front end guys who have to pad it out
to print it; use CHAR(n) for short codes.
You talk about joining on reason codes, but have no such column in
CUSTSWRESP. Did you actually give the same data element mulitple names
in the schema? Do you have not DRI between the tables?
This is such a mess you need to start over.
CREATE TABLE MIX310
(reason_code CHAR(2) NOT NULL PRIMARY KEY,
reason_description VARCHAR (80) NOT NULL, -- punch card width!
severity_code INTEGER NOT NULL -- code description in another table?
CHECK (severity BETWEEN 0 AND 7),
);
>> .. Which implies have at most 8 message codes. <<
So design a table something like this:
CREATE TABLE CustMessages
(cust_id VARCHAR (15) NOT NULL
REFERENCES Customers (cust_id)
ON UPDATE CASCADE,
message_nbr INTEGER DEFAULT (1) NOT NULL
CHECK (message_nbr BETWEEN 1 AND 8), -- enforce business
rule
message_txt TEXT NOT NULL,
reason_code CHAR(2) NOT NULL
REFERENCES MIX310 (reason_code)
ON UPDATE CASCADE,
...
PRIMARY KEY (cust_id, call_nbr));
Now use "message_nbr BETWEEN 1 and 4" to get that sample. Ordering the
messages by severity is a simple update.
Right now, you have no data integrity in the current schema. All your
queries will convoluted nightmares that produce erroneous results. If
you cannot fix it, you might want to update your resume and try to
find a company that will be in business.
