Query to make groupby on paymode

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

SQL Server 2005 Forums

Transact-SQL (2005)

Query to make groupby on paymode

Author

Topic

krishnet
Starting Member

29 Posts

Posted - 2007-06-08 : 17:26:56

Hi,

I have the following tables

Table :Account

accountid int identity,(p.k.)
accountname varchar(20)not null

Table : MiscPay

miscid int identity,(p.k.)
accountid int not null, (f.k.)
storeid int not null,(f.k.)
paymentdt datetime not null,
payeename varchar(30) not null,
paymode varchar(20) not null,
bankname varchar(50),
chqdt datetime,
chqueno varchar(20),
amt numeric(10, 2) not null,
remarks varchar(50)not null

QUERY :

SELECT '1', '' AS PAYMODE, accountname AS NAME, '' AS payeename, '' AS paymode, NULL AS storeid, NULL AS amt, NULL AS paymentdt
FROM ACCOUNT
WHERE accountid = 2
UNION
SELECT '2', '' AS PAYMODE, '' AS NAME, payeename, paymode, storeid, amt, paymentdt
FROM MiscPay
WHERE accountid = 2 and paymode ='cash' and storeid=1
ORDER BY 1, 2, 3

What i want is the output as :

On basis of paymentmode as cash and cheque
On basis of accountname
Orderby paymentdt and amt.
I also want the summary total of the amt column

ACCOUNTNAME| PAYMODE| PAYDATE| PAYEENAME |AMT

Expenses |Cash
11/11/2006| A| 300.00
12/12/2006| B| 400.00
------
700.00

ACCOUNTNAME |PAYMODE| PAYDATE| CHQNO |BANKNAME |PAYEENAME |AMT

Expense |Credit
1/1/2007 |56678| SBI |A |850.00
1/2/2007 |46575| SBI |B | 50.00
--------
900.00
thanxs...

krishnet
Starting Member

29 Posts

Posted - 2007-06-12 : 09:46:41

Any solution ??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources