SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Write Query for the Below Requirement?


How to Write Query for the Below Requirement?

Author
Message
perireddy.arikatla
perireddy.arikatla
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 40
Thank u So Much Sir.
It works for me.


I am New to This forms.So I don't know how to Post.


Thank u so much for all your patient replies.


Thanks
R. Brush
R. Brush
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 352
Using CTE's, the logic of certain queries can be broken down in to simpler steps:


WITH pd(zParty_Code, zSumPaid_Amt)
AS (
SELECT party_code, SUM(paid_amt)
FROM dbo.tPartyData
WHERE paid_date < '11/27/2012'
GROUP BY party_code
)

SELECT party_code
, SUM(inst_amt) AS Total_Inst_Amt
, zSumPaid_Amt AS Total_Paid_Amt
FROM dbo.tPartyData
JOIN pd
ON party_Code = zParty_Code
GROUP BY party_code, zSumPaid_Amt


LinksUp
LinksUp
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 4456
perireddy.arikatla (9/23/2013)

I am New to This forms.So I don't know how to Post.


Please don't use that as an excuse. You were told how to post 3 times, twice by myself (with an example) and once by Mr. Moden, who provided a link to an article on "How to Post".

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 Visits: 2582
LinksUp (9/22/2013)
perireddy.arikatla (9/22/2013)


When i Run the query without giving party_code in where clause
i.e

SELECT party_code, sum(inst_amt) as total_insts,
(Select Sum(paid_amt) as total_paid
From party_payments
Where paid_date < '11/27/2012'
Group by party_code) as TotalPaid
FROM party_payments
GROUP BY party_code


It was Giving Following Error. . .



Why are you not trying and testing out the code that was actually posted?? After I put together my OWN DLL and sample data, the query itself became trivial. NOTE: It is very close to the air code posted earlier.


SELECT t1.party_code, sum(t1.inst_amt) as total_insts,
(Select Sum(t2.paid_amt)
From @party_payments t2
WHERE t2.paid_date < '2012-11-27' and t2.party_code = t1.party_code
Group by t2.party_code) as TotalPaid
FROM @party_payments t1
GROUP BY t1.party_code





+1 to LinksUp for providing the DDL and sample data for this question.

However, the solution above requires 3 scans of the @party_payments table (and R. Brush's CTE version requires 2 scans), which is trivial up to a few thousand rows or so, but after that can get expensive. This code gives the same result with only a single scan:

SELECT party_code
,SUM(inst_amt) AS total_insts
,SUM(CASE WHEN paid_date < '2012-11-27' THEN paid_amt ELSE 0 END) AS TotalPaid

FROM @party_payments

GROUP BY party_code



Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
LinksUp
LinksUp
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 4456
wolfkillj (9/23/2013)


+1 to LinksUp for providing the DDL and sample data for this question.

However, the solution above requires 3 scans of the @party_payments table (and R. Brush's CTE version requires 2 scans), which is trivial up to a few thousand rows or so, but after that can get expensive. This code gives the same result with only a single scan:

SELECT party_code
,SUM(inst_amt) AS total_insts
,SUM(CASE WHEN paid_date < '2012-11-27' THEN paid_amt ELSE 0 END) AS TotalPaid

FROM @party_payments

GROUP BY party_code






Your code is indeed more efficient!

I just ran all 3 queries and checked the Execution plan on each one and I am only seeing 2 Table Scans on both my solution and the cte. Whereas your solution does only have 1. What are you looking at to determine 3 Table Scans?

(See attached jpg for execution plan)

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Attachments
Query.jpg (4 views, 114.00 KB)
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 Visits: 2582
LinksUp (9/23/2013)


Your code is indeed more efficient!

I just ran all 3 queries and checked the Execution plan on each one and I am only seeing 2 Table Scans on both my solution and the cte. Whereas your solution does only have 1. What are you looking at to determine 3 Table Scans?

(See attached jpg for execution plan)


When I run all three versions with SET STATISTICS IO ON, I get this:

=========================================
Prepare sample data:

(14 row(s) affected)

(1 row(s) affected)
=========================================
Subselect version:

(2 row(s) affected)
Table '#3B219CFC'. Scan count 3, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
=========================================
CTE version:

(2 row(s) affected)
Table '#3B219CFC'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
=========================================
CASE version:

(2 row(s) affected)
Table '#3B219CFC'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)


Your subselect has to be performed once for each distinct value of party_code, even though you see only one table scan operator in the execution plan. If you look at the execution plan, you'll see that once branch of the logical tree shows a table scan of [party_payments] [t2] feeding a stream aggregate that computes the SUM in the subselect. If you look at the XML of the plan (by opening the attached .sqlplan with a text editor), you'll see that the table scan is actually performed twice, once for each distinct value of party_code. Here's is the RunTimeInformation node of the RelOp node for the stream aggregrate (NodeID = "15"):

                          <RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="0" ActualExecutions="2" />
</RunTimeInformation>


and the RunTimeInformation node of the RelOp node for the supporting table scan (NodeID = "16") - you see it's a child node of the stream aggregate RelOp:
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5" ActualEndOfScans="2" ActualExecutions="2" />
</RunTimeInformation>



Note the value of the ActualExecutions attribute in each case: "2".

Add these two scans to the table scan of [party_payments] [t1] and you have 3 scans altogether.

You can test this by adding rows with additional distinct values of party_code - you'll see an additional scan for each additional distinct value.

If you're familiar with XPath notation, here are the XPaths to those nodes:

The stream aggregate:
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/ComputeScalar/RelOp/NestedLoops/RelOp[2]/ComputeScalar/RelOp

and the table scan:
/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/RelOp/ComputeScalar/RelOp/NestedLoops/RelOp[2]/ComputeScalar/RelOp/StreamAggregate/RelOp

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Attachments
Subselect.sqlplan (5 views, 42.00 KB)
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 Visits: 2582
And duh, of course you can see the number of executions of a node in an execution plan displayed as Number of Executions in the Properties that pop up when you hover over the node. No need to scrutinize the XML - I just like to dig through it sometimes because you can find things there that you can't see in SSMS's graphical display.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
LinksUp
LinksUp
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 4456
wolfkillj (9/23/2013)
And duh, of course you can see the number of executions of a node in an execution plan displayed as Number of Executions in the Properties that pop up when you hover over the node. No need to scrutinize the XML - I just like to dig through it sometimes because you can find things there that you can't see in SSMS's graphical display.


Thanks for the reminder. I never looked beneath the surface of the execution plan to see the actual number of executions.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 Visits: 2582
LinksUp (9/23/2013)
wolfkillj (9/23/2013)
And duh, of course you can see the number of executions of a node in an execution plan displayed as Number of Executions in the Properties that pop up when you hover over the node. No need to scrutinize the XML - I just like to dig through it sometimes because you can find things there that you can't see in SSMS's graphical display.


Thanks for the reminder. I never looked beneath the surface of the execution plan to see the actual number of executions.


It's easy to overlook such things with only a small amount of test data (that you had to create yourself, no less). Running the code on a large data set would have brought the issue to light, I'm sure. Thanks for being the one to educate the OP about the need to provide DDL and test data.

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
LinksUp
LinksUp
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 4456
wolfkillj (9/23/2013)
Thanks for being the one to educate the OP about the need to provide DDL and test data.


What kind of torques my wrench is that the OP posted another question today with the same deficiencies; "I need a query that does _________."

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search