September 18, 2013 at 5:01 am
Hi
I've got 2 tables in my db ( Property and Contact ) which I need to report on ( extracts below ) :
Property :
Prop Id.....Choices
48...........1_ChoiceA, 1_ChoiceB, 1_ChoiceC, 1_ChoiceD
49...........2_ChoiceA, 2_ChoiceB, 2_ChoiceC
50...........3_ChoiceA, 3_ChoiceB, 3_ChoiceC, 3_ChoiceD, 3_ChoiceE
Contact :
Interact Id....p48
1.................1_ChoiceA
2.................1_ChoiceC
3.................NULL
4.................1_ChoiceA
5.................NULL
6.................1_ChoiceB
7.................1_ChoiceA
8.................1_ChoiceC
I need to create a report which gives a list of totals for all possible instances of Property Id 48 from the Contact table including zeros. The users can add more 'choices' to this list so it needs to check each time I run it for the number of values in the 'Choices' field.
So basically, for each possible outcome in Choices field for Property Id 48 in Property table, total up the number of times it appears in the Contact table col p48.
e.g. it should return :
p48 Options Total
1_ChoiceA 3
1_ChoiceB 1
1_ChoiceC 2
1_ChoiceD 0
I have written this code ( using a Tally table ) to get list of possible Choices in the comma sep field :
SELECT SubString(',' + P.Choices + ',' , T.Inc_No ,
CharIndex(',' , ',' + P.Choices + ',' , T.Inc_No) - T.Inc_No)
FROM S_Tally T, Property P
WHERE T.Inc_No <= LEN(',' + P.Choices + ',')
AND SubString(',' + P.Choices + ',' , T.Inc_No - 1, 1) = ','
AND P.PropertyId = 48
This gives a list of all possible Choices but how do I join this to table Contact to get totals?
Hope this ramble makes sense!
September 18, 2013 at 1:56 pm
You really need to have an associative table. You should Never, Ever store data in a delimited string like that. Change that and your database coding life will become so much more simple.
Andrew SQLDBA
September 19, 2013 at 3:01 am
Hi
Thanks for your reply.
Unfortunately it's not my db design. I have just been asked to produce a report from a COTS product and that is how the data is stored.
September 19, 2013 at 8:34 am
We have a function that is used to create a temp table. It takes a parameter that is passed in with commas and puts each value into the table. It might be a start for you.
ALTER FUNCTION [dbo].[UDF_PARSE_MULTIPLE_PARAMETERS]
(@parametersVARCHAR(MAX))
RETURNS @params_table TABLE
(IDINT,
PARAMETERVARCHAR(500))
AS
BEGIN
DECLARE @id_ctrINT
SELECT @id_ctr = 1
IF CHARINDEX(',', @parameters) = 0
BEGIN
INSERT @params_table
VALUES (@id_ctr, @parameters)
SELECT @parameters = ''
SELECT @id_ctr = @id_ctr + 1
END
WHILE LEN(RTRIM(LTRIM(@parameters))) > 0
BEGIN
INSERT @params_table
VALUES (@id_ctr, SUBSTRING(@parameters, 1, CHARINDEX(',', @parameters) - 1))
SELECT @parameters = RTRIM(LTRIM(SUBSTRING(@parameters, CHARINDEX(',', @parameters) + 1, LEN(@parameters))))
SELECT @id_ctr = @id_ctr + 1
IF CHARINDEX(',', @parameters) = 0
BEGIN
INSERT @params_table
VALUES (@id_ctr, @parameters)
SELECT @parameters = ''
END
END
RETURN
END
Then it is called in the stored procedure like this:
DECLARE @Department_TBL TABLE
(ID INT,
DEPARTMENT_ID INT)
INSERT @Department_TBL
(ID, DEPARTMENT_ID)
SELECT ID, PARAMETER AS DEPARTMENT_ID
FROM UDF_PARSE_MULTIPLE_PARAMETERS(@DepartmentID)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply