Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Complex Query. Please help Expand / Collapse
Author
Message
Posted Monday, January 06, 2014 11:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:04 AM
Points: 3,517, Visits: 2,606
Luis Cazares (1/6/2014)
sqlnaive (1/6/2014)
It's basically for OLAP environment so no worruies about using nolock. Your query seems good. But what do you think about the performance from the bulk (which i mentioned) perspective ?

If it's an OLAP environment, then there shouldn't be any need to use nolock hints that might give you inconsistent data.
About the bulk perspective, I'm not sure what you're talking about.
For performance, you should test yourself. If there's a problem, then alternatives can be considered.

There seems to be a catch in your query Luiz.
Say in any case the data does not have any Attribute at all (say Attr2 in our example). In this case, all Attr3 values will go to Attr2.

I'm sure that you can figure that out if you understand the query. If you don't, check the following article and come back for specific questions. http://www.sqlservercentral.com/articles/T-SQL/63681/


Luiz, May be i was not specific to where i am getting an issue. I already have a table (in this case table_final) which has fixed number of columns Col1, Col2 and Col3 (along with the associated values). Now value Attr1 should always come to Col1 or else NULL, Attr2 should always come to Col2 or else NULL and Attr3 should always come to Col3 or else NULL.

In this query, in case there are just Attr2 and Attr3 for any ID, then their associated rn will be 1 and 2. In final query, these will be going to Col1 & Col2 respectively and not in Col2 & Col3.

	MAX(CASE WHEN d.rn = 1 THEN d.Attribute END) Col1,
MAX(CASE WHEN d.rn = 1 THEN d.Value END) Col1_Val,
MAX(CASE WHEN d.rn = 2 THEN d.Attribute END) Col2,
MAX(CASE WHEN d.rn = 2 THEN d.Value END) Col2_Val,
MAX(CASE WHEN d.rn = 3 THEN d.Attribute END) Col3,
MAX(CASE WHEN d.rn = 3 THEN d.Value END) Col3_Val


Pleas ehelp me understand if i'm incorrect here.
Post #1528214
Posted Monday, January 06, 2014 12:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 2,763, Visits: 5,908
It seems that you're facing the same problem as your previous post.
http://www.sqlservercentral.com/Forums/Topic1524935-392-1.aspx
And it was solved over there.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1528217
Posted Tuesday, January 07, 2014 5:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:04 AM
Points: 3,517, Visits: 2,606
I'm getting the required data from the following query.

WITH cteMaster AS(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ID, Book, Party ORDER BY [Type]) rn
FROM #Master
WHERE [Type] IN ('ABC', 'DEF')
),
ctenew AS(
SELECT * FROM cteMaster
where rn = 1
)
SELECT m.ID,
m.Book,
m.Party,
m.[Desc],
m.[Type],

d.Attribute, d.Value,
d1.Attribute, d1.Value,
d2.Attribute, d2.Value
FROM ctenew m
LEFT JOIN cteDetails d ON m.ID = d.ID AND m.Book = d.Book AND m.Party = d.Party and d.Attribute = 'Attr1'
LEFT JOIN cteDetails d1 ON m.ID = d1.ID AND m.Book = d1.Book AND m.Party = d1.Party and d1.Attribute = 'Attr2'
LEFT JOIN cteDetails d2 ON m.ID = d2.ID AND m.Book = d2.Book AND m.Party = d2.Party and d2.Attribute = 'Attr3'



The only thing which worries me with your query is that the row numbering is happening dynamically and based on that, we are allocating Attribute values to different columns. While I've a table already with fix columns. Otherwise this query seems fantastic. Please still feel free to let me understand if this query can handle the situation (or there is somethign which i am not able to pick here).
I'll update my other chain and close that. That totally got out of my mind. It's a bad practice. I admit it and feel sorry for that.
Post #1528441
Posted Tuesday, January 07, 2014 6:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:33 AM
Points: 6,754, Visits: 12,854
Working on a completely different principle, this will perform differently to Luiz' fine solution. Test them and assuming they return correct values, choose the fastest - on the assumption that current indexing is optimal for both.

WITH Masters AS (
SELECT
rn = ROW_NUMBER() OVER(PARTITION BY ID, Book, Party ORDER BY [Type], [Desc]),
ID, Book, Party, [Desc], [Type]
FROM #Master
WHERE [Type] IN ('ABC', 'DEF')
)
SELECT ID, Book, Party, [Desc], [Type], x.*
FROM Masters m
CROSS APPLY (
SELECT
Col1 = MAX(CASE WHEN Attribute = 'Attr1' THEN Attribute ELSE NULL END),
Col1_Val = MAX(CASE WHEN Attribute = 'Attr1' THEN Value ELSE NULL END),
Col2 = MAX(CASE WHEN Attribute = 'Attr2' THEN Attribute ELSE NULL END),
Col2_Val = MAX(CASE WHEN Attribute = 'Attr2' THEN Value ELSE NULL END),
Col3 = MAX(CASE WHEN Attribute = 'Attr3' THEN Attribute ELSE NULL END),
Col3_Val = MAX(CASE WHEN Attribute = 'Attr3' THEN Value ELSE NULL END)
FROM #Details d
WHERE d.ID = m.ID
AND d.Book = m.Book
AND d.Party = m.Party
) x
WHERE rn = 1;



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1528459
Posted Wednesday, January 08, 2014 6:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:04 PM
Points: 11, Visits: 66
other solution is using the CTE and PIVOT like the follow script, in the sample I define the tables, but you can use your defined tables and only use the CTE statement, I wish resolve the problem :

DECLARE @Master TABLE (
ID INT ,
Book CHAR (5) ,
Party INT ,
[Desc] VARCHAR (50),
[Type] CHAR (3) );

INSERT @Master ([ID], [Book], [Party], [Desc], [Type])
VALUES (1234, 'X12DY', 1828, 'This is First Book', 'ABC'),
(8888, 'ROME5', 6513, 'Class Act', 'ABC'),
(9999, 'ASL72', 23412, 'Routine', 'DEF'),
(3456, '12LM3', 22891, 'Not Single', 'ABC'),
(3456, '12LM3', 22891, 'Not Single', 'DEF'),
(4444, '5NNK2', 27823, 'Different Kind', 'ABC'),
(4444, '5NNK2', 27823, 'Different Book', 'ABC'),
(3489, '345H8', 34345, 'New Thing', 'ABC'),
(3489, '345H8', 34345, 'New Thing', 'DEF'),
(3489, '345H8', 34345, 'New Book', 'ABC');

DECLARE @Details TABLE (
ID INT ,
Book CHAR (5) ,
Party INT ,
Attribute CHAR (5) ,
[Value] VARCHAR (15));

INSERT @Details ([ID], [Book], [Party], [Attribute], [value])
VALUES (1234, 'X12DY', 1828, 'Attr1', 'Red'),
(1234, 'X12DY', 1828, 'Attr2', 'Round'),
(1234, 'X12DY', 1828, 'Attr3', 'Big'),
(9999, 'ASL72', 23412, 'Attr1', 'Blue'),
(9999, 'ASL72', 23412, 'Attr2', 'Square'),
(3456, '12LM3', 22891, 'Attr1', 'White'),
(3456, '12LM3', 22891, 'Attr2', 'Square'),
(4444, '5NNK2', 27823, 'Attr1', 'Blue'),
(4444, '5NNK2', 27823, 'Attr2', 'Rectangle'),
(3489, '345H8', 34345, 'Attr1', 'Black'),
(3489, '345H8', 34345, 'Attr2', 'Round');

WITH cteMaster
AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (PARTITION BY ID, Book, Party ORDER BY [Type]) AS rn,
[ID],
[Book],
[Party],
[Desc],
[Type]
FROM @master
ORDER BY [Type], id),
pivoting
AS (SELECT [ID],
[Book],
[Party],
CASE [Attr1]
WHEN 1 THEN (SELECT [Attribute]
FROM @Details AS d
WHERE [Attribute] = 'Attr1'
AND [d].[ID] = [pvt].[ID]) ELSE NULL
END AS [Col1],
CASE [Attr1]
WHEN 1 THEN (SELECT [value]
FROM @Details AS d
WHERE [Attribute] = 'Attr1'
AND [d].[ID] = [pvt].[ID]) ELSE NULL
END AS [Col1_Val],
CASE [Attr2]
WHEN 1 THEN (SELECT [Attribute]
FROM @Details AS d
WHERE [Attribute] = 'Attr2'
AND [d].[ID] = [pvt].[ID]) ELSE NULL
END AS [Col2],
CASE [Attr2]
WHEN 1 THEN (SELECT [value]
FROM @Details AS d
WHERE [Attribute] = 'Attr2'
AND [d].[ID] = [pvt].[ID]) ELSE NULL
END AS [Col2_Val],
CASE [Attr3]
WHEN 1 THEN (SELECT [Attribute]
FROM @Details AS d
WHERE [Attribute] = 'Attr3'
AND [d].[ID] = [pvt].[ID]) ELSE NULL
END AS [Col3],
CASE [Attr3]
WHEN 1 THEN (SELECT [value]
FROM @Details AS d
WHERE [Attribute] = 'Attr3'
AND [d].[ID] = [pvt].[ID]) ELSE NULL
END AS [Col3_Val]
FROM (SELECT [ID],
[Book],
[Party],
[Attribute],
[Value]
FROM @Details) AS t PIVOT (COUNT ([value]) FOR attribute IN ([Attr1], [Attr2], [Attr3])) AS pvt)
SELECT [m].[ID], [m].[Book], [m].[Party], [m].[Desc], [m].[Type],
[p].[Col1], [p].[Col1_Val], [p].[Col2], [p].[Col2_Val], [p].[Col3], [p].[Col3_Val]
FROM [cteMaster] AS m LEFT OUTER JOIN
[pivoting] AS p
ON [p].[ID] = [m].[ID]
AND [p].[Book] = [m].[Book]
AND [p].[Party] = [m].[Party]
WHERE [rn] = 1;


Post #1529174
Posted Thursday, January 09, 2014 8:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 2,763, Visits: 5,908
eli.misael (1/8/2014)
other solution is using the CTE and PIVOT like the follow script, in the sample I define the tables, but you can use your defined tables and only use the CTE statement, I wish resolve the problem :

That seems really complex compared to the cross tabs approach. At least for me.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1529369
Posted Friday, January 10, 2014 3:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:04 AM
Points: 3,517, Visits: 2,606
Everything was working fine but got the requirement change.Idea is:
a. base table can have multiple values of ID based on different books but can have only two values for object (juice and/or nojuice). b. nobase table can have multiple values for an ID based on different books with different values of Values for their Type.
output should be calculated as:
c. In final output, we need one entry per ID with Object "juice" as priority. That is, if there is only juice value for an ID then it will be taken, if nojuice only then it will be taken, if for an ID, there are both, then Juice will be taken and nojuice entries should be removed from point d calculation..
d. once object type "juice" or "nojuice" for decided for an ID, also we need to consider, which book for that ID has max value for "value" column. Only that book has to be considered PLUS the final "value" should contain net of all "Value" (in case the ID has both juice and nojuice, then only consider books with juice for netting the value)

Base table data

ID	Book	Object
1234 abc1 juice
1234 abc2 juice
1234 abc3 juice
1234 abc4 juice
1111 abc1 juice
1111 abc2 nojuice
1111 abc3 juice
1111 abc4 nojuice
2222 kkk1 nojuice
2222 kkk2 nojuice
2222 kkk3 nojuice


NoBase table data

ID	Book	Type	Value
1234 abc1 Simple 500
1234 abc2 Simple 600
1234 abc3 Simple 1000
1234 abc4 Simple 200
1111 abc1 Simple 300
1111 abc2 Simple 400
1111 abc3 Simple 200
1111 abc4 Simple 500
2222 kkk1 Medium 200
2222 kkk2 Medium 300
2222 kkk3 Medium 400


Output Needed

ID	Book	Object	SimpleType	SimpleValue
1234 abc3 Juice Simple 2300
1111 abc1 Juice Simple 500
2222 kkk3 nojuice Medium 900
Post #1529697
Posted Friday, January 10, 2014 8:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 2,763, Visits: 5,908
To get this query, i would use nested CTEs (or subqueries) first using a RANK() function to determine which products I will include, then ROW_NUMBER() on the result to get the book with highest the highest value and finally get the aggregates over this.
If you want a coded answer from me, you need to provide at least consumable sample data.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1529852
Posted Friday, January 24, 2014 6:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
sqlnaive (1/10/2014)
Everything was working fine but got the requirement change.Idea is:
a. base table can have multiple values of ID based on different books but can have only two values for object (juice and/or nojuice). b. nobase table can have multiple values for an ID based on different books with different values of Values for their Type.
output should be calculated as:
c. In final output, we need one entry per ID with Object "juice" as priority. That is, if there is only juice value for an ID then it will be taken, if nojuice only then it will be taken, if for an ID, there are both, then Juice will be taken and nojuice entries should be removed from point d calculation..
d. once object type "juice" or "nojuice" for decided for an ID, also we need to consider, which book for that ID has max value for "value" column. Only that book has to be considered PLUS the final "value" should contain net of all "Value" (in case the ID has both juice and nojuice, then only consider books with juice for netting the value)

Base table data

ID	Book	Object
1234 abc1 juice
1234 abc2 juice
1234 abc3 juice
1234 abc4 juice
1111 abc1 juice
1111 abc2 nojuice
1111 abc3 juice
1111 abc4 nojuice
2222 kkk1 nojuice
2222 kkk2 nojuice
2222 kkk3 nojuice


NoBase table data

ID	Book	Type	Value
1234 abc1 Simple 500
1234 abc2 Simple 600
1234 abc3 Simple 1000
1234 abc4 Simple 200
1111 abc1 Simple 300
1111 abc2 Simple 400
1111 abc3 Simple 200
1111 abc4 Simple 500
2222 kkk1 Medium 200
2222 kkk2 Medium 300
2222 kkk3 Medium 400


Output Needed

ID	Book	Object	SimpleType	SimpleValue
1234 abc3 Juice Simple 2300
1111 abc1 Juice Simple 500
2222 kkk3 nojuice Medium 900


I strongly recommend you do yourself a favor and start posting data in a readily consumable format as previously requested on this and many other posts.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534688
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse