Create a Rolling summary field

I need to make a report that takes a rolling 12 month period.
Each row needs to show the sum of the current months value and all the 11 months prior.

For example, I have a count of items in their respective Months

2 - Jan 06
5 - Feb 06
2 - Mar 06
4 - April 06
2 - May 06
1 - June 06
2 - July 06
5 - Aug 06
2 - Sep 06
2 - Oct 06
4 - Nov 06
2 - Dec 06
2 - Jan 07
3 - Feb 07
1 - Mar 07

What I need as an output is

Jan 07 = 35
Feb 07 = 33
Mar 07 = 32

So each row sums the current and previous 11 rows.

I am using Crystal Report v10 and SQL Database.

Thanks,
DavidThe quick solution in my mind is.

Create a subreport and store the whole data in an array.
Now come to the actual report and while showing the current record you can sum the previous 11 months records from the array and show it there.|||Great thanks for the Idea, I got it working with an array and then another formula summing the 12 in the array. I haven't played with the chart function yet, i have to get all these fields totaling first. Any suggestions getting all the fields in the details section ploted into a chart (the ultimate goal. Each displayed point is a date and the summed 12 month total.|||Keep one thing in mind while dealing with the charts.

You can show/draw more then one values on change of some value. e.g on change of date you can show the sum of previous 11 months.

Also you can get the accumulative .

You have to explore the charts for your project.|||Thanks so much for your help. Sometimes I just need a little bump into the right direction and I can figure it out. I got it working now.

Counting values for transportation mode

I have a table for tracking transportation mode to work for each pay period. There are five modes

1=Transit
2=carpool
3=bicycle
4=walk
5=vanpool

The pay period database fields are below. A number is entered for each day representing the mode (the field type in the SQL db is char) . I would like my report to count the modes for each pay period.

I have create a group on the field "PayPeriod". It seems that I should be able to create a formula like this for each mode.

Wk_1_Sun
Wk_1_Mon
Wk_1_Tues
Wk_1_Wed
Wk_1_Thurs
Wk_1_Fri
Wk_1_Sat
Wk_2_Sun
Wk_2_Mon
Wk_2_Tues
Wk_2_Wed
Wk_2_Thurs
Wk_2_Fri
Wk_2_Sat

NumberVar tot1sun1 := 0;
NumberVar tot1mon1 := 0;

If ({ECAPParticipate.Wk_1_Sun}= "1") Then
tot1sun1 := count({ECAPParticipate.Wk_1_Sun})
else
If ({ECAPParticipate.Wk_1_Mon}= "1") Then
tot1mon1 := count({ECAPParticipate.Wk_1_Mon})

etc.

I am getting scewed results. I'm new at this so I'm sure that my programming is not correct. Can someone give me some guidance. Thank you.If your report is grouped by the payperiod, then add a summary that counts the modes for each one. Place that summary in your Group header or footer where ever you want it visible.

GJ

counting the inserts and updates on a table in a sql server database

Hello,

Can someone point me to getting the total number of inserts and updates on a table
over a period of time?

I just want to measure the insert and update activity on the tables.

Thanks.

- VishOn a single statement you can capture the @.@.rowcount into a variable and
write it to a log table. But if I'm reading this correctly, you don't want
to do this through the existing code base. Inserts are usually easy if
there is a primary or unique key. Assuming no deletes, simply how many new
keys are there since the last count. Or if the key is incrementing by one
what's the max value - the previous max value. Updates are more vague. How
many rows were updated or how many updates occurred These activities are
usually accommodated for in the initial table design with flag and
last_mod_date columns. Without auditing written into every piece of code or
proper schema design it's an ugly intensive task to rub to data sets
together (using checksums or straight comparisons) to find differences.

Danny

"Viswanatha Thalakola" <vthalakola@.yahoo.com> wrote in message
news:d762e418.0411301845.7504b0b4@.posting.google.c om...
> Hello,
> Can someone point me to getting the total number of inserts and updates on
> a table
> over a period of time?
> I just want to measure the insert and update activity on the tables.
> Thanks.
> - Vish|||On 30 Nov 2004 18:45:55 -0800, Viswanatha Thalakola wrote:

>Hello,
>Can someone point me to getting the total number of inserts and updates on a table
>over a period of time?
>I just want to measure the insert and update activity on the tables.
>Thanks.
>- Vish

Hi Vish,

The easiest way to do this is to add some counting logic to the stored
procedures that do the inserting, updating and deleting. But if you can't
or won't change those tables (or if you allow direct data modifications,
without using stored procedures), you have two other options:

1. Set up a profiler trace. Catch the trace results in a table or in a
file, then use either SQL queries (if in a table) or text manipulation
tools (if in a file) to count the number of inserts, updates, etc. I must
add that I don't know the exact format and I'm not sure either if the
number of rows affected is included in the trace data (it it isn't, you
can't use this approach).

2. Create triggers for each table you need to monitor. Have these triggers
copy @.@.rowcount in a local variable as the first statement (that yields
the number of rows affected by the statement that fired the trigger) and
save that value to a table.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> 1. Set up a profiler trace. Catch the trace results in a table or in a
> file, then use either SQL queries (if in a table) or text manipulation
> tools (if in a file) to count the number of inserts, updates, etc. I must
> add that I don't know the exact format and I'm not sure either if the
> number of rows affected is included in the trace data (it it isn't, you
> can't use this approach).

It isn't, but you can catch number of page writes. Still, though, not a
wholly reliable number.

Then again, I assume that the aim is not to save exact numbers, but get
some approxamite statistics, so some Profiler method is proably better
than adding triggers to the system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Counting stored procedure execution

I am looking for a way to count the number of times a stored procedure on the database has been executed over let's say over a period of time(month, years, etc).

Is there a system stored procedure or a system table that stores that information.

I am struggling to find some information about this topic
Thanks for the helpI don't know if that information is stored, but what you can do is to create your own table that will store the datetime the stored-procedure is run. You can then run your queries on that table.

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