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 mont
h.
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)At the moment I'm not too sure about doing it one query - but you can
easily do it in two queries.
Barry|||I've continued working on this problem and have a partial solution if anyone
could help me refine it It still returns two rows with the same values The
results follow this select statement.
Select DISTINCT
y.CalCompCnt,
compfails.TestYear as CalYear,
compfails.TestMonth as CalMonth,
x.FiscCompCnt,
compfails.FiscYear as FiscYear,
compfails.FiscMonth as FiscMonth
From CompFails
INNER JOIN (Select DISTINCT Count(f.modelDesc) as FiscCompCnt,
f.fiscYear, f.fiscMonth from CompFails f
group by FiscYear, FiscMonth)x ON x.FiscYear = compfails.Fiscyear and
x.FiscMonth = compfails.FiscMonth
INNER JOIN (Select DISTINCT Count(b.modelDesc) as CalCompCnt,
b.TestYear, b.TestMonth from CompFails b
group by b.TestYear, b.TestMonth)y ON y.testYear = compfails.testyear and
y.testMonth = compfails.testMonth
group by compfails.FiscYear, compfails.FiscMonth,
compfails.testYear, compfails.testMonth, x.fisccompcnt, y.calcompcnt
CalCompCnt CalYear CalMonth FiscCompCnt FiscYear FiscMonth
-- -- -- -- -- --
27 2005 10 26 2006 1
27 2005 10 18 2006 2
18 2005 11 18 2006 2
18 2005 11 11 2006 3
10 2005 12 11 2006 3
20 2006 1 19 2006 4
20 2006 1 17 2006 5
16 2006 2 17 2006 5
"StvJston" wrote:
> I have a query that returns results based on a count of tests done by peri
od
> and year. The period can either be a calendar month or Fiscal Calendar mo
nth.
> 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)
>
>|||If I understand the problem, the fiscal calendar is different from the
regular calendar in two ways. First, it is offset by three months.
Second, months do not cut off at the calendar month end.
You need to construct a reference table to relate the two calendars.
The table should have one row per month. The table looks a lot like
certain columns your desired results, though it should contain the
full calendar of months:
CalYear CalMonth FiscYear FiscMonth
-- -- -- --
2005 10 2006 1
2005 11 2006 2
2005 12 2006 3
2006 1 2006 4
2006 2 2006 5
This table needs two unique constraints, (CalYear,CalMonth) and
(FiscYear,FiscMonth). One should be the primary key, the other a
unique index.
CREATE TABLE Months (
TestYear INT NULL,
TestMonth INT NULL,
FiscYear INT NULL,
FiscMonth INT NULL
)
GO
INSERT Months VALUES (2005,10,2006,1)
INSERT Months VALUES (2005,11,2006,2)
INSERT Months VALUES (2005,12,2006,3)
INSERT Months VALUES (2006, 1,2006,4)
INSERT Months VALUES (2006, 2,2006,5)
GO
We then will calculate each set of numbers, calendar and fiscal, in
two derived tables, and use the Months table to bring it all together.
SELECT M.TestYear, M.TestMonth, C.TestCount,
M.FiscYear, M.FiscMonth, F.FiscCount
FROM Months as M
LEFT OUTER
JOIN (select TestYear, TestMonth, count(*) as TestCount
from CompFails
group by TestYear, TestMonth) as C
ON M.TestYear = C.TestYear
AND M.TestMonth = C.TestMonth
LEFT OUTER
JOIN (select FiscYear, FiscMonth, count(*) as FiscCount
from CompFails
group by FiscYear, FiscMonth) as F
ON M.FiscYear = F.FiscYear
AND M.FiscMonth = F.FiscMonth
TestYear TestMonth TestCount FiscYear FiscMonth FiscCount
-- -- -- -- -- --
2005 10 27 2006 1 26
2005 11 18 2006 2 18
2005 12 10 2006 3 11
2006 1 20 2006 4 19
2006 2 16 2006 5 17
Roy Harvey
Beacon Falls, CT
On Mon, 24 Apr 2006 11:31:02 -0700, StvJston
<StvJston@.discussions.microsoft.com> wrote:
>I have a query that returns results based on a count of tests done by perio
d
>and year. The period can either be a calendar month or Fiscal Calendar mon
th.
>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)
>|||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.|||Hi,
Here is the query that gives you the result that you wanted.
select a.CalCompCnt,a.calyear ,a.calmonth,b.FiscCompCnt,b.FiscYear
,b.FiscMonth from
(select count(*) as id ,x.CalCompCnt,x.calyear ,x.calmonth
from
(select count(modeldesc) as CalCompCnt, TestYear as calyear, TestMonth as
calmonth from compfails group by TestYear,TestMonth) as x,
(select count(modeldesc) as CalCompCnt, TestYear as calyear, TestMonth as
calmonth from compfails group by TestYear,TestMonth) as y
where x.calmonth >= y.calmonth
group by x.CalCompCnt,x.calyear ,x.calmonth ) as a,
(select count(*) as id ,x.FiscCompCnt,x.FiscYear ,x.FiscMonth
from
(select count(modeldesc) as FiscCompCnt, FiscYear, FiscMonth from compfails
group by FiscYear, FiscMonth) as x,
(select count(modeldesc) as FiscCompCnt, FiscYear, FiscMonth from compfails
group by FiscYear, FiscMonth) as y
where x.FiscMonth >= y.FiscMonth
group by x.FiscCompCnt,x.FiscYear ,x.FiscMonth ) as b
where a.id = b.id
-- But a note of caution. I will never use this query in production. and the
result that you want doesn't seem functionally valid. I doubt you really hav
e
a requirement like this. Anyways, I had fun in writing this query :)
Hope I helped.|||Here is a better solution. I feel its got more meaning. relating the fiscal
and the calendar month. and its simple.
Let me know your thoughts. Hope this helps
select * from
(select count(modeldesc) as CalCompCnt, TestYear as calyear, TestMonth as
calmonth from compfails group by TestYear,TestMonth) as a,
(select count(modeldesc) as FiscCompCnt, FiscYear, FiscMonth from compfails
group by FiscYear, FiscMonth) as b
where fiscmonth + (12 *(FiscYear -calyear)) - calmonth = 3
and fiscyear = calyear + floor(calmonth/10)|||I thought you would have a comment, always appreciate your input.
I have a Fiscal Calendar table that is correct for the corp. The data SET I
am selecting from "CompFails" is a temp table that contains the records that
meet the selection criteria.
One of the report requirements (I know let the report do the work) is the
ability to select between fiscal and calendar data points. By returning bot
h
sets at the same time I can keep the fiscal/calendar date selection out of m
y
sql.
CompFails will be a temporary table in a stored proc once development is
done. Nulls and indexes will get handled as I continue testing. (There will
never be null fields all the fields are required from the selection or
computed results for the specific model/serial no in each row.
Primary key is done through the serialNo field which I did not include in my
example since it wasn't required for the select I am doing.
I solved this problem myself lastnight using another tack.
I created a Union query with the desired fields and added a dummy flag for
fiscal or calendar.
What seems to have been causing the problem was when a record was outside of
the Fiscal Month range but still in the calendar month range I would get a
duplicate return.
It was a small roadblock but as always learn or die...(grin)
Thanks all for the help and comments...
"--CELKO--" wrote:
> 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.
>|||I should have made myself more clear but when formulating a question its har
d
to figure out what information people need.
The CompFails table is a result set that is returned from sql that performs
calculations and summations on each record and loads CompFails.
the testMonth, Testyear, and FiscYear and FiscMonth are returned based on
the actual test date in the original data. They are correct values based on
the actual test date data using the year(CalorimetertestDate) and
month(CalorimetertestDate). The fiscal Year and month are returned to
CompFails using the following sql
INNER JOIN (select f.Monthno, f.Fiscalyear, MIN(f.BeginDate) as FBDate,
Max(f.Enddate)as FEndDate from fiscalcalendar f
where f.BeginDate>=CONVERT(VarCHAR, @.StartDate ,112)
and f.EndDate <= CONVERT(Varchar, @.EndDate,112)
group by f.monthno, f.Fiscalyear) fTest ON calorimetertestdate
between
ftest.fbdate and ftest.fenddate
I didn't expect folks to get caught with figuring dates. I just wanted to
return the correct monthly totals based on fiscal months or Calendar months.
By using a Union query with a dummy flag (calendar or Fiscal) I get both set
s
correctly and only have to make one call to the table.
Thanks for the help.
"Roy Harvey" wrote:
> If I understand the problem, the fiscal calendar is different from the
> regular calendar in two ways. First, it is offset by three months.
> Second, months do not cut off at the calendar month end.
> You need to construct a reference table to relate the two calendars.
> The table should have one row per month. The table looks a lot like
> certain columns your desired results, though it should contain the
> full calendar of months:
> CalYear CalMonth FiscYear FiscMonth
> -- -- -- --
> 2005 10 2006 1
> 2005 11 2006 2
> 2005 12 2006 3
> 2006 1 2006 4
> 2006 2 2006 5
> This table needs two unique constraints, (CalYear,CalMonth) and
> (FiscYear,FiscMonth). One should be the primary key, the other a
> unique index.
> CREATE TABLE Months (
> TestYear INT NULL,
> TestMonth INT NULL,
> FiscYear INT NULL,
> FiscMonth INT NULL
> )
> GO
> INSERT Months VALUES (2005,10,2006,1)
> INSERT Months VALUES (2005,11,2006,2)
> INSERT Months VALUES (2005,12,2006,3)
> INSERT Months VALUES (2006, 1,2006,4)
> INSERT Months VALUES (2006, 2,2006,5)
> GO
> We then will calculate each set of numbers, calendar and fiscal, in
> two derived tables, and use the Months table to bring it all together.
> SELECT M.TestYear, M.TestMonth, C.TestCount,
> M.FiscYear, M.FiscMonth, F.FiscCount
> FROM Months as M
> LEFT OUTER
> JOIN (select TestYear, TestMonth, count(*) as TestCount
> from CompFails
> group by TestYear, TestMonth) as C
> ON M.TestYear = C.TestYear
> AND M.TestMonth = C.TestMonth
> LEFT OUTER
> JOIN (select FiscYear, FiscMonth, count(*) as FiscCount
> from CompFails
> group by FiscYear, FiscMonth) as F
> ON M.FiscYear = F.FiscYear
> AND M.FiscMonth = F.FiscMonth
> TestYear TestMonth TestCount FiscYear FiscMonth FiscCount
> -- -- -- -- -- --
> 2005 10 27 2006 1 26
> 2005 11 18 2006 2 18
> 2005 12 10 2006 3 11
> 2006 1 20 2006 4 19
> 2006 2 16 2006 5 17
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 24 Apr 2006 11:31:02 -0700, StvJston
> <StvJston@.discussions.microsoft.com> wrote:
>
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment