Click here to Skip to main content
14,924,733 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Good Morning..

This is my query in sql server

SQL
select mb.ID,c.Membershipnumber as MemNum,c.Name, mi.ID as ItemId
mi.Name as ItemName ,mb.Amount from MemberMonthlyBill mb, MemberBill m ,MemberItems mi,Customer c where mb.ItemID=mi.ID and m.ItemID=mi.ID and mb.BillingMonth=m.BillingMonth and m.CustomerID=c.ID and mb.CustomerID=c.ID  and
mb.BillingMonth>='2011-04-1' and mb.BillingMonth<='2011-04-30' order by c.ID


result is

MSIL
ID  MemNum  Name                  ItemId  ItemName            Amount
1   A001    MR.A.V.R.K.KUMAR        18    LockerRent          25.00
2   A001    MR.A.V.R.K.KUMAR        19    SubscriptionFees    441.20
3   A001    MR.A.V.R.K.KUMAR        20    ShowerCharges       420.26
6   A002    MR. M. ADISANKAR        19    SubscriptionFees    441.20
12  A003    MR. ASHOK KUMAR GUPTA   19    SubscriptionFees    441.20
11  A003    MR. ASHOK KUMAR GUPTA   18    LockerRent          25.00
16  A004    MR. S.S. ANAND          19    SubscriptionFees    0.00
20  A005    MR. A.S. ANAND          19    SubscriptionFees    441.20
24  A006    MR. T.P. ANANTHAM       19    SubscriptionFees    441.20
29  A007    MR. AZIZ MEHDI          19    SubscriptionFees    441.20
28  A007    MR. AZIZ MEHDI          18    LockerRent          25.00
33  A008    CAPT. AMIT CANUMALLA    19    SubscriptionFees    441.20


here one custer having more than one record depending on itemid.

but i want the result like bellow
MSIL
MemNum  Name                 LockerRent SubscriptionFees Other    Total
A001    MR.A.V.R.K.KUMAR        25.00    441.20          420.26   886.46
A002    MR. M. ADISANKAR                 441.20                   441.20
A003    MR. ASHOK KUMAR GUPTA   25.00    441.20                   466.20



A001 customer having 3 record with 3 items.
Member Items table
ID int Unchecked
Name nvarchar(50) Checked
Amount decimal(19, 2) Checked
MemberGroupID int Checked
TaxMethodID int Checked
Status nchar(1) Unchecked

MemberMonthlyBill
ID int Unchecked
BillingMonth datetime Checked
CustomerID int Checked
ItemID int Checked
Amount decimal(19, 2) Checked

MemberBill
ID int Unchecked
BillingMonth datetime Checked
CustomerID int Unchecked
Amount decimal(18, 0) Checked
GroupID int Checked
ItemID int Checked
Quantity int Checked
Upd_by nvarchar(50) Checked
Upd_on datetime Checked
Type nchar(1) Checked

customer
ID int Unchecked
Name nvarchar(255) Unchecked
Membershipnumber nvarchar(20) Checked
PassportNo nvarchar(255) Checked
Nation nvarchar(255) Checked
EmergencyContact nvarchar(255) Checked


any one suggest the quary plz
Posted
Updated 28-Jun-11 19:45pm
v3
Comments
[no name] 29-Jun-11 1:28am
   
Please provide your table structures.

1 solution

You need to look at creating a PIVOT TABLE: MSDN[^]
   
Comments
ajitha.pusapati 29-Jun-11 2:49am
   
Thanks for ur reply. its very nice.
I prepate quary like the following. but i con't do two tings in that.

SELECT memNo,memberName,[18] AS LockerRent, [19] AS SubscriptionFees
FROM
(select c.ID as cid ,c.Membershipnumber as memNo,c.Name as memberName, mi.ID as ItemId, mi.Name as ItemName,mb.Amount as Amount from MemberMonthlyBill mb, MemberBill m ,MemberItems mi,Customer c
where mb.ItemID=mi.ID and m.ItemID=mi.ID and mb.BillingMonth=m.BillingMonth and m.CustomerID=c.ID and mb.CustomerID=c.ID and
mb.BillingMonth>='2011-04-1' and mb.BillingMonth<='2011-04-30') p
PIVOT
(
sum(Amount)
FOR ItemId IN
( [18], [19])
) AS pvt
ORDER BY pvt.cid;

1. except 18 and 19, for remaining item ids item name is Other.
2. it show multiple record for one customer .

result is
MemNo MemName LockerRent SubscriptionFees
A001 MR.A.V.R.K.KUMAR 25.00 NULL
A001 MR.A.V.R.K.KUMAR NULL 441.20
A002 MR. M. ADISANKAR NULL 441.20
A003 MR. ASHOK KUMAR GUPTA 25.00 NULL
A003 MR. ASHOK KUMAR GUPTA NULL 441.20

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900