April 13, 2012 at 8:58 am
ok..i have 2 tables...they are basically the same except for the column name in one of them because they deal with 2 different names, though..the data i want is in columns that have the same name.pretty much what i want to do...is .they also need to be distinct so i dont count duplicates...i can get them as separate tables...but i cant get them together..I need them in 1 column because of how it is sent to the C3 code page and how it reads it...the structure has already been previously set..and there are about 5 other statments that are being executed in this one stored procedure like this (also i wasnt the one who set this up). So if this is possible..let me know please.. i will also paste the exact code i have if you feel like seeing the real life code..i just tried to simplify it to make it easier to understand.
i'm going to post some images to hopefully explain it a little better
image 1 is what is currently set up
top part is what is stored in tables..bottom is more of the result
it basically runs this code to get the bottom
DECLARE @id INT;
DECLARE @invest nvarchar(50);
SET @id = '7633';
SET @invest = '';
SELECT 'a' + CONVERT(nvarchar, orderfindings.risk_rating) AS cat, COUNT(DISTINCT orderfindings.prnt_id) AS stat
FROM orderheader, orderaudits, orderfindings
WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderfindings.prnt_id
AND orderheader.id = @id AND orderfindings.risk_rating > 0 AND orderaudits.Investor_Name LIKE '%' + @invest + '%'
GROUP BY orderfindings.risk_rating
If i want agencies instead of findings..just replace it..agencies and findings are the 2 tables..they are the pretty much identical column wise...but i want the result together..i've tried several ways..but i cant seem to get it
image 2- the table at the bottom is more what i'm looking for..it combines them both into 1
if an order has a finding or agency or both in it..then it gets marked as a 1 for that risk rating...if it doesnt..then 0 for that risk rating..and then sum them all up to see what i got..any help is appreciated ..i'll clarify what i can if you dont understand
Edit:
i've been working with it...did this http://pastebin.com/117Z2TVh ..got it to display 2 columns..but still not the right result...i'm getting a1 = 1...a2 = 1...so its not running through all the orders...or it needs a way to count it...i put a sum at beginning of case statement..erro because of counts...so i took counts out...didnt really work..any advice?
April 13, 2012 at 10:13 am
been working more with it..my big problem now is tallying up the scores..i can only get one column 🙁 ..anyone know of a trick to get the right numbers out of this
--------------------new sql statement--------------
select 'a' + convert(nvarchar, risk_rating) as cat,
count(distinct orderfindings.prnt_id) as stat
from orderheader, orderaudits, orderfindings, orderagencies
where orderheader.id = orderaudits.orderheader_id and (orderaudits.ID = orderfindings.prnt_id or orderaudits.ID = orderfindings.prnt_id )
and orderheader.id = @id and risk_rating > 0 and orderaudits.Investor_Name like '%' + @invest + '%'
group by risk_rating
having count(orderfindings.prnt_id) > 0 or count(orderagencies.prnt_id) > 0
April 13, 2012 at 10:22 am
can you post your DDL (create table scripts) and Sample data as text in the thread. that will help every one help you.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 13, 2012 at 10:45 am
umm..i suppose i could create something..give me a couple hours..i'm literally about to have to goto lunch..and when i get back..that will be the first thing i do..unless someone solves it first ..lol..i'm so close..just missing something or doing something slightly wrong
also..this can go with update...new picture ..including results
April 13, 2012 at 1:13 pm
ok..i think this will work..hoping at least ..this is a very scaled down version of what i'm dealing with..but all the essentials are there
April 13, 2012 at 1:18 pm
here it is posted here
CREATE TABLE orderheader
(id int)
CREATE TABLE orderaudits
(id int,
OrderHeader_ID int,
Risk_Rating int,
Investor_Name nvarchar(50))
CREATE TABLE orderfindings
(id int,
Prnt_ID int,
Finding_nbr char(10)
)
CREATE TABLE orderagencies
(id int,
Prnt_ID int,
Agency_nbr char(10)
)
insert into orderheader
values (1)
--------------------------------------------------
insert into orderaudits
values (1,1,1, 'FM')
insert into orderaudits
values (2,1,1, 'FM')
insert into orderaudits
values (3,1,1, 'FM')
insert into orderaudits
values (4,1,1, 'FM')
insert into orderaudits
values (5,1,1, 'FM')
insert into orderaudits
values (6,1,2, 'FM')
insert into orderaudits
values (7,1,2, 'FM')
insert into orderaudits
values (8,1,2, 'FM')
insert into orderaudits
values (9,1,2, 'FM')
insert into orderaudits
values (10,1,2, 'FM')
-----------------------------------------
insert into orderfindings
values (1,1, '100')
insert into orderfindings
values (2,2, '100')
insert into orderfindings
values (3,3, '100')
insert into orderfindings
values (4,4, '100')
insert into orderfindings
values (5,4, '100')
insert into orderfindings
values (6,5, '100')
insert into orderfindings
values (7,8, '100')
insert into orderfindings
values (8,9, '100')
-------------------------------------
insert into orderagencies
values (1,1, '100')
insert into orderagencies
values (2,2, '100')
insert into orderagencies
values (3,4, '100')
insert into orderagencies
values (4,6, '100')
insert into orderagencies
values (5,9, '100')
insert into orderagencies
values (6,10, '100')
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 13, 2012 at 2:42 pm
small edit..mistyped something
--------------------new sql statement--------------
select 'a' + convert(nvarchar, risk_rating) as cat,
count(distinct orderfindings.prnt_id) as stat
from orderheader, orderaudits, orderfindings, orderagencies
where orderheader.id = orderaudits.orderheader_id and (orderaudits.ID = orderfindings.prnt_id or orderaudits.ID = orderagencies.prnt_id )
and orderheader.id = @id and risk_rating > 0 and orderaudits.Investor_Name like '%' + @invest + '%'
group by risk_rating
having count(orderfindings.prnt_id) > 0 or count(orderagencies.prnt_id) > 0
April 13, 2012 at 2:52 pm
ewhitaker (4/13/2012)
small edit..mistyped something--------------------new sql statement--------------
select 'a' + convert(nvarchar, risk_rating) as cat,
count(distinct orderfindings.prnt_id) as stat
from orderheader, orderaudits, orderfindings, orderagencies
where orderheader.id = orderaudits.orderheader_id and (orderaudits.ID = orderfindings.prnt_id or orderaudits.ID = orderagencies.prnt_id )
and orderheader.id = @id and risk_rating > 0 and orderaudits.Investor_Name like '%' + @invest + '%'
group by risk_rating
having count(orderfindings.prnt_id) > 0 or count(orderagencies.prnt_id) > 0
it is running what are you trying to get out of the query? what should the results look like.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 13, 2012 at 2:58 pm
pretty much what i am trying to get out of it..is a1 to say 5..and a2 to say 4.....the pic http://imgur.com/Ot3GO ...the big table sorta shows the query minus investor...."if order 1 has a finding or an agency or both, then i want it to count.., and it is in risk rating 1" and continue this so on.. so for risk rating one.. all 5 orders have a finding or agency...so count is 5...for risk rating 2..only four have a finding or agency or both..so its count is 4...so out of 10 orders..9 have a risk rating or agency.....but out of 10..only 7 orders have an agency and only 6 orders have a finding....does that help?
April 13, 2012 at 3:06 pm
not really can you put the output you would like to see? something like this:
cat stat
------------------------------- -----------
a1 7
a2 7
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 13, 2012 at 3:07 pm
i think my biggest problem atm is i'm not sure how to get it to go through each order individually ..like if i take group by out..for order 1 through 5 i get same results..and that shouldnt be haha..if i take group by out..it should be more like the table in the picture
April 13, 2012 at 3:15 pm
what query are you using to generate the table of data (the id risk rating...)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 13, 2012 at 3:16 pm
cat stat
------------------------------- -----------
a1 5
a2 4
April 13, 2012 at 3:18 pm
i have 3 i'm playing with atm..trying to get 1 to work
1
select 'a' + convert(nvarchar, B.risk_rating) as cat,
(select count(distinct orderfindings.prnt_id) as stat
from orderheader, orderaudits, orderfindings
where orderheader.id = orderaudits.orderheader_id and orderaudits.ID = orderfindings.prnt_id
and orderheader.id = @id and risk_rating = B.risk_rating and orderaudits.Investor_Name like '%' + @invest + '%') as stat1,
(select count(distinct orderagencies.prnt_id) as stat
from orderheader, orderaudits, orderagencies
where orderheader.id = orderaudits.orderheader_id and orderaudits.ID = orderagencies.prnt_id
and orderheader.id = @id and risk_rating = B.risk_rating and orderaudits.Investor_Name like '%' + @invest + '%') as stat2
from orderheader A, orderaudits B
where A.id = B.orderheader_id
and A.id = @id and risk_rating > 0 and B.Investor_Name like '%' + @invest + '%'
group by B.risk_rating
2
select 'a' + convert(nvarchar, risk_rating) as cat,
count(distinct orderfindings.prnt_id) as stat
from orderheader, orderaudits, orderfindings, orderagencies
where orderheader.id = orderaudits.orderheader_id and (orderaudits.ID = orderfindings.prnt_id or orderaudits.ID = orderagencies.prnt_id )
and orderheader.id = @id and risk_rating > 0 and orderaudits.Investor_Name like '%' + @invest + '%'
group by risk_rating
having count(orderfindings.prnt_id) > 0 or count(orderagencies.prnt_id) > 0
3
select 'a' + convert(nvarchar, risk_rating) as cat,
(case when (count(distinct orderfindings.prnt_id) > 0) or (count(distinct orderagencies.prnt_id) > 0) then 1 else 0 end) as stat
from orderheader, orderaudits, orderfindings, orderagencies
where orderheader.id = orderaudits.orderheader_id and (orderaudits.ID = orderfindings.prnt_id and orderaudits.ID = orderagencies.prnt_id )
and orderheader.id = @id and risk_rating > 0 and orderaudits.Investor_Name like '%' + @invest + '%'
group by risk_rating
having count(orderfindings.prnt_id) > 0 or count(orderagencies.prnt_id) > 0
April 13, 2012 at 3:50 pm
ok here we go here is the code:
;WITH finding AS (select oa.id, oa.Risk_Rating, COUNT (orf.Prnt_ID) findingcount
FROM orderaudits oa
LEFT JOIN orderfindings orf
on oa.ID = orf.Prnt_ID
GROUP BY oa.id, oa.Risk_Rating),
Agency AS (select oa.id, oa.Risk_Rating, COUNT (ora.Prnt_ID) agencycount
FROM orderaudits oa
LEFT JOIN orderagencies ora
on oa.ID = ora.Prnt_ID
GROUP BY oa.id, oa.Risk_Rating)
SELECT 'Finding Only', oa.Risk_Rating, COUNT(oa.id), SUM(CASE WHEN f.findingcount > 0 then 1 ELSE 0 END )
FROM orderaudits oa
LEFT JOIN finding f
ON oa.id = f.id
GROUP BY oa.Risk_Rating
UNION ALL
SELECT 'Agency Only', oa.Risk_Rating, COUNT(oa.id), SUM(CASE WHEN a.agencycount > 0 then 1 ELSE 0 END )
FROM orderaudits oa
LEFT JOIN Agency a
ON oa.id = a.id
GROUP BY oa.Risk_Rating
UNION ALL
SELECT 'Together', oa.Risk_Rating, COUNT(oa.id), SUM(CASE WHEN f.findingcount > 0 OR a.agencycount > 0 then 1 ELSE 0 END )
FROM orderaudits oa
LEFT JOIN finding f
ON oa.id = f.id
LEFT JOIN Agency a
ON oa.id = a.id
GROUP BY oa.Risk_Rating
i moved your sub queries to CTE's and then using the cte's when either count is greater than 0 the row returns 1 otherwise its a 0 then i sum those rows. should get you pointed in the right direction.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply