Counting by Calendar and Fiscal periods in same query

SQL Apprentice Question
I have a query that returns results based on a count of tests done by period
and year. The period can either be a calendar month or Fiscal Calendar month.

As always thanks in advance!


Select
count( modelDesc)as CompCnt,
TestYear as CalYear,
TestMonth as CalMonth,
FiscYear as FiscYear,
FiscMonth as FiscMonth
From CompFails
group by ModelDesc,FiscYear, FiscMonth, testYear, testMonth


I also attempted to set the count as


select COUNT(ModelDesc)as ModelCnt FROM (SELECT
DISTINCT f.modelDesc from compfails f
group by FiscYear, FiscMonth) as FiscCompCnt


And got the "subquery returns more than one row error"


Currently the query returns the results as so:


Current Results


COMPcnt CalYear CalMonth FiscYear FiscMonth
------- ----------- ----------- ----------- -----------
26 2005 10 2006 1
1 2005 10 2006 2
17 2005 11 2006 2
1 2005 11 2006 3
10 2005 12 2006 3
19 2006 1 2006 4
1 2006 1 2006 5
16 2006 2 2006 5


I would like the results as follows but I am having a heck of a time
figuring the syntax.


CalCompCnt CalYear CalMonth FiscCompCnt FiscYear FiscMonth
----------- ----------- ---------------------- ----------- -----------
20 2006 1 26 2006 1
16 2006 2 18 2006 2
27 2005 10 11 2006 3
18 2005 11 19 2006 4
10 2005 12 17 2006 5


DDL Follows:


CREATE TABLE CompFails (
ModelDesc Varchar(40) NULL,
CalorimeterTestDate DATETIME NULL,
TestYear INT NULL,
TestMonth INT NULL,
FiscYear INT NULL,
FiscMonth INT NULL
)


INSERT INTO CompFails
(ModelDesc,
CalorimeterTestDate,
TestYear,
TestMonth,
FiscYear,
FiscMonth)


VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-06 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-03 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-10 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-11 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-14 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-12 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-19 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-28 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-18 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-20 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-27 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-11-02 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-10-24 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-21 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-10-25 00:00:00.000,2005,10,2006,1)
VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-07 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-08 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-04 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-10-31 00:00:00.000,2005,10,2006,2)
VALUES('tstModelDesc',2005-12-05 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-10 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-12-02 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-11-15 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-29 00:00:00.000,2005,11,2006,3)
VALUES('tstModelDesc',2005-11-16 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-21 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-11-18 00:00:00.000,2005,11,2006,2)
VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-07 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-09 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-16 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2005-12-17 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2006-01-04 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2005-12-19 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2006-01-09 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-02-07 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2005-12-12 00:00:00.000,2005,12,2006,3)
VALUES('tstModelDesc',2006-01-13 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-16 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-18 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-19 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-22 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-01-24 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-25 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-20 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-21 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-02-01 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-01-30 00:00:00.000,2006,1, 2006,5)
VALUES('tstModelDesc',2006-01-27 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-01-28 00:00:00.000,2006,1, 2006,4)
VALUES('tstModelDesc',2006-02-02 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-08 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-22 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-16 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-17 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-14 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-15 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-18 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-19 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-02-21 00:00:00.000,2006,2, 2006,5)
VALUES('tstModelDesc',2006-01-02 00:00:00.000,2006,1, 2006,4)


Celko Answers
First construct a general enterprise calendar table; you should talk to
the trolls in accounting so you get the fiscal part right.

CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,
cal_year INTEGER NOT NULL,
cal_month INTEGER NOT NULL,
fiscal_year INTEGER NOT NULL,
fiscal_month INTEGER NOT NULL,
etc.);


Now get your tests table right. For example, why are things you must
know set to NULLs? Why did you have no key?


CREATE TABLE CompFails
(model_desc VARCHAR(40) NOT NULL,
test_date DATETIME NOT NULL,
PRIMARY KEY (model_desc, test_date)
);


Then your view or query will look like this skeleton:


SELECT ..
FROM CompFails AS T, Calendar AS C
WHERE T.fail_date = C.cal_date
AND ..;


He had computable columns in your original non-table, in addition to
the other design flaws.