CPU usage 100% Resources failed

I have a server that Windows 2000 Advanced Server that is
clustered (Active/Active) with SQL Server 2000 with SP3A.
There are periods when the server CPU usage can be at 100%
for 30 minutes or longer.
What some of the resources that should fail when the CPU
usage is at this rate for an extended period of time?
Thanks,
Mike
Mike, you are losing me here. The system get pegged at 100% CPU and you want
to know what?
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:2910d01c464e9$6b435970$a401280a@.phx.gbl...
> I have a server that Windows 2000 Advanced Server that is
> clustered (Active/Active) with SQL Server 2000 with SP3A.
> There are periods when the server CPU usage can be at 100%
> for 30 minutes or longer.
> What some of the resources that should fail when the CPU
> usage is at this rate for an extended period of time?
> Thanks,
> Mike
|||failover will only happen when the heartbeat fails.
100% CPU doesn't mean that failover will happen.
"Rodney R. Fournier [MVP]" wrote:

> Mike, you are losing me here. The system get pegged at 100% CPU and you want
> to know what?
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> "Mike" <anonymous@.discussions.microsoft.com> wrote in message
> news:2910d01c464e9$6b435970$a401280a@.phx.gbl...
>
>
|||some of the resources that can fail when cpu usage is 100%?
Well if SQL Server cannot reply to the IsAlive queries than SQL Server resource can fail.
What resources fail in your case?
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

cpu at 100% by sqlserver.exe

Hi,
I've been seing some strange things on my sql server. The cpu reaches voor
100% usage, caused by the sqlserver.exe
I been looking at the periods and start/end time of the 100% usage. I cannot
explain it by looking at the start/end times of backup's, large queries etc
etc.
Has anyone seen this ?
Does anyone have an idea what may cause this?
Greetz,
Macdash
(macdash at home dot nl for replies if possible)
Hi
Run profiler and see what is running at that time.
Generaly 100% utilization is caused by a query not having suitable indexes,
but all data is in cache, so the table scan pegs the CPU at 100% as no disk
I/O is waited on.
Regards
Mike
"Erik" wrote:

> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I cannot
> explain it by looking at the start/end times of backup's, large queries etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>
>
|||High CPU can be caused by many things... including Order by, group by, and
having clauses... You need to run profiler and check the queries that are
running during the period.
If CPU peaks and 100 and stays forever, it could be the slammer virus if you
are not service packed up..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Erik" <macdash@.hotmail.com> wrote in message
news:%23EkpdAYxEHA.2564@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've been seing some strange things on my sql server. The cpu reaches voor
> 100% usage, caused by the sqlserver.exe
> I been looking at the periods and start/end time of the 100% usage. I
cannot
> explain it by looking at the start/end times of backup's, large queries
etc
> etc.
> Has anyone seen this ?
> Does anyone have an idea what may cause this?
> Greetz,
> Macdash
> (macdash at home dot nl for replies if possible)
>

Counting by Calendar and Fiscal periods in same query

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:
>
>