February 5, 2016 at 1:37 pm
I have the following query below that is inserting correctly the way it is written. However, I need to modify to only insert the value of column 'ufid' set number of times. For example, the value should only be in the table 10 times.
For example if the ufid = 'K7'. I need to limit the number of times the 'K7' can be inserted. If 'K7' has already been inserted into the database 10 times, then the next ufid should be selected based on the where clause criteria. Although I do not want to usse a cursor, I tried using one but it did not work for me. (i can post that additional sql code with the cursor if needed). i didn't want to make a real long post. Ideas??
Sample data from Child_TBL
App_ID Major
10000001 DESIGN
10000002 CART
10000003 GRAPH
10000001 DESIGN
10000001 DESIGN
10000002 CART
10000003 GRAPH
10000003 GRAPH
10000002 CART
10000001 DESIGN
10000002 CART
10000003 GRAPH
Sample data from Health_TBL
ufIdsh_plan
AFS GRAPH
K7 DESIGN
DGF CART
JKY REDK
POL SDRET
sample output into the #TempNTable
App_ID UFID
10000001 AFS
10000001 DGF
10000001 JKY
10000001 POL
10000002 AFS
10000002 K7
10000002 JKY
10000002 POL
10000003 K7
10000003 DGF
10000003 JKY
10000003 POL
CREATE TABLE Child_TBL
(
App_ID INT,
Major varchar(10),
comp_id INT
);
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000001,'DESIGN');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000002,'CART');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000003,'GRAPH');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000001,'DESIGN');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000001,'DESIGN');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000002,'CART');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000003,'GRAPH');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000003,'GRAPH');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000002,'CART');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000001,'DESIGN');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000002,'CART');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000003,'GRAPH');
CREATE TABLE Health_TBL
(
ufId varchar(8),
sh_plan varchar(10)
);
INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('AFS','GRAPH');
INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('K7','DESIGN');
INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('DGF','CART');
INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('JKY','REDK');
INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('POL','SDRET');
SELECT App_ID, LEFT(Major, CHARINDEX('-', Major)-1) AS Major into #mult_nuf
FROM Child_TBL
where comp_id = 21;
SELECT ufId, sh_plan into #temp_rUF
FROM Health_TBL
WITH Match_NomineesWithReviewers AS
(
SELECT
[App_ID],
RTRIM(Major) AS Major,
COUNT(1) AS rowcnt
FROM
#mult_nuf
GROUP BY
[App_ID],
RTRIM(Major)
)
, rownum_matches AS (
SELECT
m.[App_ID],
t.ufid,
m.rowcnt,
ROW_NUMBER() OVER (PARTITION BY m.[App_ID] ORDER BY newid()) AS rownum
FROM
Match_NomineesWithReviewers m
JOIN
#temp_rUF t ON t.sh_Plan != m.Major
)
SELECT [App_ID], ufid into #TempNTable
FROM rownum_matches rm WHERE rownum <= rowcnt
February 5, 2016 at 2:11 pm
Please post DDL (CREATE TABLE) including indexes and constraints and DML (INSERT INTO) statements to allow us to create a test area on our side.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 6, 2016 at 4:51 pm
I added the DML statements that I am using to insert into the temp tables.
February 6, 2016 at 5:46 pm
where? Why not re-post the whole thing?
<DDL statements to create tables>
<DML to get at least part of the way there>?
February 6, 2016 at 6:21 pm
what we want are the supporting objects that will help us run your code on our machine. make it easier for us to help you.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 7, 2016 at 7:25 am
Raxter, what they're saying is that we need to be able to reproduce enough of your environment to be able to work on the problem. Keep in mind that we can't see what you see and we don't know what you know about your problem. All we really know is what you're telling us. We ask for DDL, sample data and expected output so we can give you a solid and tested answer. Without these things, we can look at your code and guess, but the odds of getting you where you want to be goes down considerably. For more information on what we ask for and why, check out the link in my signature.
February 7, 2016 at 7:32 am
I added the DDL statements to create tables. I apologize for not supplying all the needed information. I am new to this site but will make sure in the future any posts have all of the information. I appreciate all the input.
February 7, 2016 at 7:50 am
Raxter (2/7/2016)
I added the DDL statements to create tables. I apologize for not supplying all the needed information. I am new to this site but will make sure in the future any posts have all of the information. I appreciate all the input.
please provide sample data insert scripts and expected results based on that sample data
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 7, 2016 at 8:51 am
J Livingston SQL (2/7/2016)
Raxter (2/7/2016)
I added the DDL statements to create tables. I apologize for not supplying all the needed information. I am new to this site but will make sure in the future any posts have all of the information. I appreciate all the input.please provide sample data insert scripts and expected results based on that sample data
... and debug your code before posting. Currently it results in an error when I try to run it in an empty database. I can guess what I think the intended code is, but I rather be sure that I solve the right problem.
February 7, 2016 at 10:20 am
Thank you for all the assistance so far. I made some updates to my original post. I did have a syntax error from an extra comma that I removed.
February 7, 2016 at 1:33 pm
You still have not posted INSERT statements for the sample data. So the below is untested.
I must also add that I don't understand your explanation of the requirement, and that seeing the sample data didn't help me gain an understanding.
That being said, the below should produce the expected results - though no guarantee that it actually does what you need.
SELECT DISTINCT
ct.App_ID, ht.ufId
FROM dbo.Child_TBL AS ct
CROSS JOIN dbo.Health_TBL AS ht
WHERE ht.sh_plan <> ct.Major;
February 7, 2016 at 2:01 pm
are you referring to the the insert statement for the data from the child_TBL and Health_TBL? I am using the select into #table.
example: SELECT App_ID, LEFT(Major, CHARINDEX('-', Major)-1) AS Major into #mult_nuf.
is that what you are referring to?
The query I have currently does what it is intended to do. I am stuck on adding the functionality of limiting the same 'ufid' value from being inserted more than a certain number of times. That is what i need to do. Hope that makes sense. Thank you for your help.
February 7, 2016 at 2:07 pm
EDIT please read the following...it will help you help us help you.
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
as a start. , based on the info provided so far by the OP. here is my understanding of the sample data.
to the OP....please check this and amend/repost if required.
CREATE TABLE Child_TBL
(
App_ID INT,
Major varchar(10),
comp_id INT
);
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000001,'DESIGN');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000002,'CART');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000003,'GRAPH');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000001,'DESIGN');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000001,'DESIGN');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000002,'CART');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000003,'GRAPH');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000003,'GRAPH');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000002,'CART');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000001,'DESIGN');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000002,'CART');
INSERT INTO Child_TBL(App_ID,Major) VALUES (10000003,'GRAPH');
CREATE TABLE Health_TBL
(
ufId varchar(8),
sh_plan varchar(10)
);
INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('AFS','GRAPH');
INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('K7','DESIGN');
INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('DGF','CART');
INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('JKY','REDK');
INSERT INTO Health_TBL(ufId,sh_plan) VALUES ('POL','SDRET');
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 7, 2016 at 3:15 pm
Thank you! I edited my original post and added the INSERT statements.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply