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 123»»»

How to Write Query for the Below Requirement? Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 10:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 7:24 PM
Points: 25, 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.


  Post Attachments 
My Table.png (27 views, 160.66 KB)
Post #1496704
Posted Thursday, September 19, 2013 11:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 6, 2014 5:48 AM
Points: 83, Visits: 23
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]
Post #1496714
Posted Saturday, September 21, 2013 5:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 7:24 PM
Points: 25, 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
Post #1497134
Posted Saturday, September 21, 2013 9:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 7,745, Visits: 9,493
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
Post #1497142
Posted Saturday, September 21, 2013 10:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 357, Visits: 1,480
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/
Post #1497146
Posted Saturday, September 21, 2013 10:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 7:24 PM
Points: 25, 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
Post #1497181
Posted Saturday, September 21, 2013 11:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 357, Visits: 1,480
perireddy.arikatla (9/21/2013)


I Think I Explained This Time Clearly


A bit more clearer. 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/
Post #1497183
Posted Sunday, September 22, 2013 1:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 7:24 PM
Points: 25, 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
Post #1497192
Posted Sunday, September 22, 2013 8:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 357, Visits: 1,480
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/
Post #1497207
Posted Sunday, September 22, 2013 11:29 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:02 AM
Points: 35,268, Visits: 31,761
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."

(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 #1497214
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse