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.
| Author |
Topic |
|
roxcy
Yak Posting Veteran
58 Posts |
 Posted - 2006-10-19 : 09:35:10
|
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,ToDateBranch has Columns BID,Branch Its values are 1-Mumbai,2-CalcuttaCategory 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.ALTER Procedure amcsp_GetDetails( @FromDate as datetime, @ToDate as datetime, @intSrNo as int = 0, @intBranchId as int = 0, @intCategoryId as int = 0)asif(@intSrNo = NULL) OR (@intSrNo = 0) BEGIN 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 CALLDETAILS WHERE DATERECEIVED BETWEEN @FromDate AND @ToDate and STATUS = 'C' GROUP BY BRANCH END else if (@intBranchId<>0 and @intCategoryId<>0) BEGIN 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 CALLDETAILS WHERE DATERECEIVED BETWEEN @FromDate AND @ToDate and STATUS = 'C' GROUP BY BRANCH END Although I am passing parameterised values But not getting the required Output.Plz help me with a solution... Thanks... |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-10-19 : 10:05:13
|
Although you are passing parameters, you are not utilising them inside the SP.if (@intBranchId<>0 and @intCategoryId<>0) BEGIN 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 CALLDETAILS WHERE DATERECEIVED BETWEEN @FromDate AND @ToDate and STATUS = 'C' andBranch = @intBranchId and Category = @intCategoryId GROUP BY BRANCH END Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
roxcy
Yak Posting Veteran
58 Posts |
Posted - 2006-10-20 : 03:42:16
|
Hi, I am posting my code which is fetching the required output But Still it is not Entirely the Feasible Solution.Becoz there are about 20 Branches and 4 Categories .Here is My code 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', SUM(CASE WHEN NOOFDAYSLOS BETWEEN 91 AND 180 THEN 1 ELSE 0 END)'Over 90 Days', FROM AMCMCALLDETAILS WHERE Branch = 'Mumbai' and Category = 'COM' and DATERECEIVED BETWEEN @FromDate AND @ToDate and STATUS = 'C' GROUP BY BRANCH My Question How to write procedure for each branch and Category.? |
|
|
|
roxcy
Yak Posting Veteran
58 Posts |
Posted - 2006-10-25 : 02:36:59
|
| Plzzz help!!! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-25 : 03:10:54
|
| It would really help with some sample data and some explanation what is going wring with your current query.What you have written so far is like going to the doctor and telling him "I think I have brain cancer or maybe constipation. Please give me a pill to cure me."Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|
|
Help required in storedProcedure?