roxcy
10-20-2006, 07:15 AM
Hi,
I am using Three tables One Parent table-CallDetails and two Child Tables Category,Branch.My Table CallDetails consists of the following columns.SrNo,Branch,Category,status,FromDate,ToDate
Branch has Columns BID,Branch Its values are 1-Mumbai,2-Calcutta
Category has Columns ID,Category Its values are 1-COM,2-VivaCosam.
On Client side I have a Form Where I have two Dropdowns Category & Branch.Now I want my output as For eg if I select Branch as Mumbai and Category as Com.Only details for the Branch Mumbai should be displayed along with the selected category.Following is my stored procedure.
SELECT BRANCH,COUNT(DISTINCT BRANCH),
SUM(CASE WHEN NOOFDAYSLOS BETWEEN 7 AND 30 THEN 1 ELSE 0 END)'Over a week',
SUM(CASE WHEN NOOFDAYSLOS BETWEEN 30 AND 90 THEN 1 ELSE 0 END)'Over 30 Days',
FROM AMCMCALLDETAILS
WHERE BRANCH = 'MUMBAI' AND CATEGORY = 'COM' AND
DATERECEIVED BETWEEN @FromDate AND @ToDate and STATUS = 'C'
GROUP BY BRANCH
ORDER BY BRANCH
So this code Gives me the Output AS
Branch Over a Week Over 30 Days
Mumbai 1 1
But this is Only incase of One Branch Mumbai,I have over 20 Branches and 4 Categories in All.So what logic should i implement in my above code.
Plz help me and give an appropriate Solution..
Thanks.......
I am using Three tables One Parent table-CallDetails and two Child Tables Category,Branch.My Table CallDetails consists of the following columns.SrNo,Branch,Category,status,FromDate,ToDate
Branch has Columns BID,Branch Its values are 1-Mumbai,2-Calcutta
Category has Columns ID,Category Its values are 1-COM,2-VivaCosam.
On Client side I have a Form Where I have two Dropdowns Category & Branch.Now I want my output as For eg if I select Branch as Mumbai and Category as Com.Only details for the Branch Mumbai should be displayed along with the selected category.Following is my stored procedure.
SELECT BRANCH,COUNT(DISTINCT BRANCH),
SUM(CASE WHEN NOOFDAYSLOS BETWEEN 7 AND 30 THEN 1 ELSE 0 END)'Over a week',
SUM(CASE WHEN NOOFDAYSLOS BETWEEN 30 AND 90 THEN 1 ELSE 0 END)'Over 30 Days',
FROM AMCMCALLDETAILS
WHERE BRANCH = 'MUMBAI' AND CATEGORY = 'COM' AND
DATERECEIVED BETWEEN @FromDate AND @ToDate and STATUS = 'C'
GROUP BY BRANCH
ORDER BY BRANCH
So this code Gives me the Output AS
Branch Over a Week Over 30 Days
Mumbai 1 1
But this is Only incase of One Branch Mumbai,I have over 20 Branches and 4 Categories in All.So what logic should i implement in my above code.
Plz help me and give an appropriate Solution..
Thanks.......