stored procedure and queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure and queries

Author

Topic

kiranmayi
Starting Member

4 Posts

Posted - 2009-06-05 : 05:31:44

hi

iam using .net with sqlserver 2005

i have 3 tables tblcustomers and tblcustomerpayments and tblzones.

in tblcustomers i have id,customerid,name,status ....
id and customerid is the primary keys
in tblcustomerpayments i have id,customerid,foweek,amount,status..
here id is the primary key
in tblzones i have id,status and so on

now i want customerid,name ,max(forweek),amount,sum(amount) from these tables

select c.customerid,c.name
from tblcustomers c inner join tblzones z
on c.status=z.status
and z.status=1
group by c.name,c.customerid

and in the tblcustomerpayments table i want to select last paid amount paid by the customer with the week how group y customerid?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-05 : 13:07:46

[code]
select c.customerid,c.name,max(foweek),sum(amount)
from tblcustomers c inner join tblzones z
on c.status=z.status
and z.status=1
inner join tblcustomerpayments cp
on cp.customerid=c.customerid
group by c.name,c.customerid
[/code]

for getting last paid amount do you have date column in tblcustomerpayments? also is id in tblcustomerpayments identity field?

kiranmayi
Starting Member

4 Posts

Posted - 2009-06-06 : 00:46:22

hi

tblcustomers(customerid is primary key)
id customerid name status
1 2001 meena 1

tblcustomerpayments(primary key:id)
id customerid amount forweek status
1 1 125 2008-10-20 00:00:00.000 1
6 1 200 2008-11-03 00:00:00.000 1
9 1 157 2008-10-27 00:00:00.000 1

i want output like this
i want the list last paid amount paid by customer.

customerid name amount forweek
2001 meena 200 2008-11-03 00:00:00.000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 03:08:29

[code]
select c.customerid, c.name, cp.amount, cp.forweek
from tblcustomers c
inner join tblcustomerpayments cp
on cp.customerid=c.customerid
inner join (select customerid,max(forweek) as latest
from tblcustomerpayments
group by customerid) cp1
on cp1.customerid=cp.customerid
and cp1.latest=cp.forweek
[/code]

kiranmayi
Starting Member

4 Posts

Posted - 2009-06-08 : 01:20:14

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources