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 |
|
shakeel2008
Starting Member
5 Posts |
 Posted - 2008-08-19 : 13:55:20
|
| I am writing a query to retrieve data for each month of a given year. I am also looking at different set of data, i.e. we have two teams namely Protection and Ongoing, we are looking for (1)how many cases being transferred from Protection to Ongoing worker in a given month, (2)how many cases being brought forward from previous months from the same Ongoing worker caseload,(3) how many cases transferred IN between Ongoing team as well as (4) transferred OUT from each Ongoing worker, (5) how many cases being closed from each Ongoing worker etc....As you can see, I need to have multiple query to achieve these results, I was able to get 80% of the info meaning compile to stored procedure with (1) to (4) queries. However, now running into this error : "Too many table names in the query. The maximum allowable is 256"Here is a snapshot of one of the query: I am using UNION to join them all. The query is working fine individually, I just need to join them all together, any info would be much appreciated.SELECT 0 AS SectionOrder,'Brought Forward Ongoing' AS SectionDesc, SupervisorID, WorkerID,1 AS MonthCount1,0 AS MonthCount2 ,0 AS MonthCount3,0 AS MonthCount4,0 AS MonthCount5,0 AS MonthCount6,0 AS MonthCount7,0 AS MonthCount8,0 AS MonthCount9,0 AS MonthCount10,0 AS MonthCount11,0 AS MonthCount12FROMFamily_Case AS FC INNER JOIN (SELECT DISTINCT FC.FamilyFileNumber AS FamilyFile ,FC.OpenDate AS FileOpenDate ,FC.CaseNumber AS Occurrence FROM Family_Case_Worker_History AS FCW INNER JOIN Family_Case AS FC ON FCW.CaseNumber = FC.CaseNumber INNER JOIN Family AS F ON FC.FamilyFileNumber = F.FamilyileNumber WHERE (FC.OpenDate <= @MonthStartDate13 AND (FC.DischargeDate IS NULL OR FC.DischargeDate >= @MonthStartDate13)) AND (FCW.[Date] < @MonthStartDate13 AND ISNULL(dbo.FamilyCaseNextWorkerDate(FCW.[ID]), @MonthStartDate13) >= @MonthStartDate13) AND dbo.FamilyCaseCaseTypeCode(FC.CaseNumber, FCW.[Date]) = 'E01' AND dbo.FamilyCaseTeamDesc(FC.CaseNumber, FCW.[Date]) LIKE ('%Family%') AND dbo.FamilyCaseCaseTypeCode(FC.CaseNumber, isnull(FC.DischargeDate, @MonthEndDate13)) LIKE 'E%' ) BF -- only brought forward Ongoing casesON FC.CaseNumber = BF.Occurrence |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-19 : 13:59:27
|
| Are your base tables (ie: Family_Case_Worker_History ) actually a VIEW? I'm going to guess it is a view and it is written with lots of columns defined by subqueries...am I right?Be One with the OptimizerTG |
 |
|
|
shakeel2008
Starting Member
5 Posts |
Posted - 2008-08-19 : 17:40:58
|
| No, Family_Case_Worker_History is a table, I'm only working with Tables in this query, however, as you can see I need to run 12 queries for the year (1 per month) for Brought Forward cases, the same for Closed, Transferred, etc. - the other five criteria... Is it possible any other way to achieve the results. Thanks once again. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-19 : 18:12:27
|
| Well one thing you could do is expand your time criteria to include all twelve months, then use a CASE statement to populate each of your [MonthCountN] columns. I suppose you could do something similar for the other 5 criteria.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Too many table names in query, Max allowable 256