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
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.
Attachments
My Table.png (31 views, 160.00 KB)
SQLINT
SQLINT
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 24
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]
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
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
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26221 Visits: 12506
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

LinksUp
LinksUp
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1760 Visits: 4640
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/
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
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
LinksUp
LinksUp
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1760 Visits: 4640
perireddy.arikatla (9/21/2013)


I Think I Explained This Time Clearly


A bit more clearerRolleyes. 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/
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
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
LinksUp
LinksUp
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1760 Visits: 4640
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_code total_insts TotalPaid
HA20076 32170 8800
HA20077 9000 6000



__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218805 Visits: 41998
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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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