Showing posts with label grouping. Show all posts
Showing posts with label grouping. Show all posts

Sunday, February 19, 2012

*Complex* Grouping in View - help needed

Hello,

I am having difficulty to find the right SQL query to create a View as i illustrate below.

Senario:

Criteria Table

Idn

Key1

Key2

Key3

TagId

1

A

C

B

100

2

A

NULL

B

200

3

B

D

NULL

300

Data Table

DataId

Key1

1

A

2

B

SubData Table

SubDataId

DataId

Key2

Key3

1

1

C

B

2

1

Z

B

3

1

X

B

4

2

D

Z

And below is my expected View:

TagId

Key1

Key2

Key3

100

A

C

B

200

A

Z

B

200

A

X

B

300

B

D

Z

I managed to get query that will be able to get above result, however it is teribbly slow. it took 1 minutes to query 1000 records from the view that i have created. My records are roughly around 80K++.

I would really appreciate if anyone could help me to make if faster or point me where i did wrong.

Below is sample T-SQL that which i can illustrate my situation:

set nocount on
create table #Criteria
(
idn int
,Key1 char(1)
,Key2 char(1)
,Key3 char(1)
,TagId int
)

create table #Data
(
DataId int
,Key1 char(1)
)

create table #SubData
(
SubDataId int
,DataId int
,Key2 char(1)
,Key3 char(1)
)

insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'A', 'C', 'B', 100)
insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'A', NULL, 'B', 200)
insert #Criteria (idn, Key1, Key2, Key3, TagId)
values(1, 'B', 'D', NULL, 300)

insert #Data(DataId, Key1)
values (1, 'A')
insert #Data(DataId, Key1)
values (2, 'B')

insert #SubData(SubDataId, DataId, Key2, Key3)
values (1, 1, 'C', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (2, 1, 'Z', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (3, 1, 'X', 'B')
insert #SubData(SubDataId, DataId, Key2, Key3)
values (4, 2, 'D', 'Z')

select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId


/** here is the query logic i used in the view **/
select min(#Criteria.TagId)
,ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3
from #Criteria
left join ( select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId ) as ConsolidatedData
on nullif(#Criteria.Key1, ConsolidatedData.Key1) IS NULL
and nullif(#Criteria.Key2, ConsolidatedData.Key2) IS NULL
and nullif(#Criteria.Key3, ConsolidatedData.Key3) IS NULL
group by ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3

drop table #Criteria, #Data, #SubData

P/s: i urgently need your feedback on this one.

Thank You!!!

sibikos@.hotmail.com

You posted a query but depending on you explanation maybe there's a completely different approach...

May you please explain which is the expected result ? something like "I would get a table that shows the key1 for each ...|||

Sorry for the confusion.. i always have trouble on explaining thing well :P

Basically, From the Criteria, Data and SubData tables that i've listed above, i would like to create a view as below

TagId

Key1

Key2

Key3

100

A

C

B

200

A

Z

B

200

A

X

B

300

B

D

Z

Currently i am using the query below query (well, not exactly the same but the logic is there) to get the view that i wanted but it is too slow for me. Just wondering if you guys have better solution.

select min(#Criteria.TagId)
,ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3
from #Criteria
left join ( select #Data.Key1
,#SubData.Key2
,#SubData.Key3
from #Data
join #SubData
on #Data.DataId = #SubData.DataId ) as ConsolidatedData
on nullif(#Criteria.Key1, ConsolidatedData.Key1) IS NULL
and nullif(#Criteria.Key2, ConsolidatedData.Key2) IS NULL
and nullif(#Criteria.Key3, ConsolidatedData.Key3) IS NULL
group by ConsolidatedData.Key1
,ConsolidatedData.Key2
,ConsolidatedData.Key3

Thanks

|||You had already posted those info... from the result you posted I see that you would like to obtain key1, 2 & 3 for each tagid in the criteria table, but is not clear to me why do you used the min(..) function... so I guess I haven't correctly understood what you'd like to accomplish... please explain with your own words which is the goal of the view, not data... words :)

Monday, February 13, 2012

**** grouping

hey peeps

well ive been stuck on this for a long time ... if anyone can help, i would appreciate it very much ....

in the database where im getting the data from, the data is displayed in the following way:

PART_ID PART_NAME PROD_TYPE DESC PASS_TYPE PASS_CNT
-----------------------------
4 BERT 5 CASH 0 15
6 BORO 5 CASH 0 1
6 BORO 5 CASH 3 4
etc
etc

here's my problem. when i try and display this in crystal, i group it by part_id and by prod_type but i still get two rows for CASH ...
example:

PRODUCT OPERATOR ADULT YOUTH
---------------------------
5 (CASH) 6 (PART_ID) 1 (PASS_COUNT)
5 (CASH) 6 (PART_ID) 4 PASS_COUNT

So what im trying to do is basically display it in one row....
NOW, ideally i want the above to be like this:

PRODUCT OPERATOR ADULT YOUTH
--------------------------
5 (CASH) 6 (PART_ID) 1 (PASS_COUNT) 4 PASS_COUNT

can anyone help ....
thanksK.
Lemme try this.

Firstly, if PASS_COUNT is a sumable value, it'll b a somewhat easier solution.
Make a formula field on your report that sums the pass_cnt field.
then place that field into the place of the other field.

Otherwise you can just make a sumed result set in your sql.
like

select ....,
sum(pass_cnt)
from <table>
where <conditions>
group by part_id,
part_name,
prod_type,
desc

but remember to leave out pass_type and pass_cnt. That is to elimenate the multiple rows.

Hope this helps somewhat.