September 19, 2013 at 10:57 pm
Hi everyone
Can Any Body Help me how to write Query for the below requirement.
I have Table in my Attachment
I want a query to get
1.party_code
2.Total Inst_amt
3.Total Paid_amt where paid_date<'27/11/2012'
how to write Query?
Can Any body please Help me.
September 19, 2013 at 11:55 pm
Please use the below query.
select party_code,sum(inst_amt) as Total_instamt,sum(paid_amt) as paid from @table1 where paid_date is null or paid_date <'2012-07-23'
group by party_code[/b]
September 21, 2013 at 5:59 am
Thanks for your reply sir.
But it was not fulfill my request.
please check my attachment again sir.
i am waiting for your reply
Thanks
September 21, 2013 at 9:34 am
It isn't really clear what you are trying to do. Is this what you want:
SELECT party_code, sum(inst_amt) as total_insts, sum(paid_amt) as total_paid
FROM @table WHERE paid_date < '2012-11-23T00:00:00'
GROUP BY party_code
That would make commercial sense of a sort, but what you asked for didn't restrict the inst amounts to those before 2012-11-23, only the paid amounts, so the person who replied previously suggested code that didn't make that restriction. Presumably that was because he wanted to suggest code that did what you actually asked for rather than code that did something sensible - - thinking that people actually mean what they say is the cause of a lot of software problems.
But even the above may not be what you wanted, because you may want to restrict the inst amounts based on inst_date rather than paid_date. That would be different code yet again.
Of course as you haven't named the table you will have to change @table to be whatever the table is called for the code to make sense.
Tom
September 21, 2013 at 10:16 am
perireddy.arikatla (9/21/2013)
But it was not fulfill my request.
select party_code, sum(inst_amt) as Total_instamt, sum(paid_amt) as paid
from @table1
where paid_date < '2012-11-27'
group by party_code
The previously posted query is very close what you asked for except for the NULL check and the wrong date. (Where does the 23rd keep coming from? Is it not < '2012-11-27')
If your requirements are not being met, you need to restate then and your desired end results so that they make sense. Stating that "It does not work" is not a good description of the problem. A picture is a start, but posting DLL and easily consumable insert statements will go a long ways in us helping you.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 21, 2013 at 10:36 pm
Thanks for your Reply sir.
But With the below Query what you suggested
SELECT party_code, sum(inst_amt) as total_insts, sum(paid_amt) as total_paid
FROM @table WHERE paid_date < '2012-11-27'
GROUP BY party_code
with this query we will get inst_amt total also<'2012-11-27'. but irrespective of paid_date i want total inst_amt as instTotal But PaidTotal must be <Given Date i.e('2012-11-27')
I Think I Explained This Time Clearly
Any Body Please Provide me Solution.
Thanks
September 21, 2013 at 11:09 pm
perireddy.arikatla (9/21/2013)
I Think I Explained This Time Clearly
A bit more clearer:rolleyes:. But again, this would be easier to come up with a solution if DLL and insert statements are provided!
This might work, though it won't be a speed demon.
SELECT party_code, sum(inst_amt) as total_insts,
(Select Sum(paid_amt) as total_paid
From @table t2
Where t2.paid_date < '2012-11-27' and t2.party_code = t1.party_code
Group by t2.party_code) as TotalPaid
FROM @table t1
GROUP BY party_code
EDIT: Only air code. Test it!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 22, 2013 at 1:28 am
Thanks For your Valuable Reply Sir.
It works when i give tha Party_code in the 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' and party_code='HA20076'
Group by party_code) as TotalPaid
FROM party_payments where party_code='HA20076'
GROUP BY party_code
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
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.
But My Requirement is Party Wise Total Inst_amt Value irrespective of Given Date and Total Paid< '11/27/2012' (i.e Given Date)
Can Any body Help me Please
September 22, 2013 at 8:54 am
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
Since you are unwilling or unable to provide the requested information, DLL and insert statements, below is an example of what should be provided for any future questions.
declare @party_payments table
(
party_code char(7),
inst_amt int,
inst_date datetime,
paid_amt int,
paid_date datetime
)
Insert @party_payments (party_code, inst_amt, inst_date, paid_amt, paid_date) values
('HA20076',3000,'2012-08-26 00:00:00.000', 3000, '2012-08-23 00:00:00.000')
,('HA20076',2917,'2012-09-15 00:00:00.000', 2900, '2012-09-17 00:00:00.000')
,('HA20076',2917,'2012-10-15 00:00:00.000', 2900, '2012-10-18 00:00:00.000')
,('HA20076',2917,'2012-11-15 00:00:00.000', 2900, '2012-11-27 00:00:00.000')
,('HA20076',2917,'2012-12-15 00:00:00.000', 2900, '2012-12-31 00:00:00.000')
,('HA20076',2917,'2013-01-15 00:00:00.000', 0, NULL)
,('HA20076',2917,'2013-02-15 00:00:00.000', 0, NULL)
,('HA20076',2917,'2013-03-15 00:00:00.000', 0, NULL)
,('HA20076',2917,'2013-04-15 00:00:00.000', 0, NULL)
,('HA20076',2917,'2013-05-15 00:00:00.000', 0, NULL)
,('HA20076',2917,'2013-06-15 00:00:00.000', 0, NULL)
,('HA20077',3000,'2012-08-26 00:00:00.000', 3000, '2012-08-23 00:00:00.000')
,('HA20077',3000,'2012-09-15 00:00:00.000', 3000, '2012-09-17 00:00:00.000')
,('HA20077',3000,'2012-11-15 00:00:00.000', 3000, '2012-11-27 00:00:00.000')
Expected Output:
party_codetotal_instsTotalPaid
HA20076 32170 8800
HA20077 9000 6000
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 22, 2013 at 11:29 am
perireddy.arikatla (9/22/2013)
Thanks For your Valuable Reply Sir.It works when i give tha Party_code in the 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' and party_code='HA20076'
Group by party_code) as TotalPaid
FROM party_payments where party_code='HA20076'
GROUP BY party_code
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
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.
But My Requirement is Party Wise Total Inst_amt Value irrespective of Given Date and Total Paid< '11/27/2012' (i.e Given Date)
Can Any body Help me Please
Yes, we can but you have to provide some better data instead of going back and forth with questions that make everyone guess. Help us help you. For future posts, please use the methods for posting readily consumable found in the article at the first link under "Helpful Links in my signature line below .
--Jeff Moden
Change is inevitable... Change for the better is not.
September 23, 2013 at 3:43 am
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
September 23, 2013 at 8:09 am
Using CTE's, the logic of certain queries can be broken down in to simpler steps:
[Code="sql"]
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
[/code]
September 23, 2013 at 8:57 am
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/
September 23, 2013 at 8:59 am
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
September 23, 2013 at 9:11 am
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/
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply