sql server programming: suppose i have a table with two columns
col1 has numerical values and col2 is the month in which this value occur.
ex:

col1 col2
50 Jan 2003
70 Feb 2003
90 Mar2003
62 Apr 2003
73 May 2003
40 Jun 2003
61 Jul 2003
100 Aug 2003
90 Sep 2003
55 Oct 2003

I have two questions:

1) I want to trace the values over quarters and capture the quarter that
have values more than 60

(all of the values in the quarter are aboe 60).
i.e. in the above example Quarter1 fails because Jan is 50, and also
Quarter2 fails, but Quarter3

succeeds because the three months are above 60, and so on.

2) I want to capture any three consequtive months that have values more than
60 regardless the

quarter.
i.e ignore Jan then consider Feb, Mar, and Apr and report that months Feb to
Apr are aboe 60.
The next three months (May,Jun,Jul) does not satify our criteria so we
neglect them and begin

from the first motnh that satisfy our criteria (Jul) and then report that
Jul to Aug succeeds.

How can we do this by TSQL?
Thanks in advance.

Here is starter for you on what I think it is you are asking

CREATE TABLE MyDateGroupings(colVal tinyint, ColMyDate smalldatetime)

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(50,'20030101')

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(70,'20030201')

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(90,'20030301')

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(62,'20030401')

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(73,'20030501')

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(40,'20030601')

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(61,'20030701')

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(100,'20030801')

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(90,'20030901')

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(55,'20031001')

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(70,'20031101')

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(80,'20031201')

INSERT MyDateGroupings(colVal, ColMyDate) VALUES(90,'20040101')

SELECT

CAST(MONTH(A.ColMyDate) as char(2)) as TimePeriod,

CAST(YEAR(A.ColMyDate) as char(4)) as YearPeriod,

(SELECT SUM(B.ColVal) FROM MyDateGroupings B WHERE B.ColMyDate BETWEEN
DATEADD(month,-2,A.ColMyDate) AND A.ColMyDate) as RollingVals

FROM

MyDateGroupings A

UNION

SELECT

'Q' + CAST(DATEPART(quarter,A.ColMyDate) as char(1)),

Datepart(year,A.ColMyDate),

SUM(A.ColVal)

FROM

MyDateGroupings A

GROUP BY

DATEPART(quarter,A.ColMyDate),

Datepart(year,A.ColMyDate)

HAVING

SUM(A.ColVal) > 60



--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


[quoted text, click to view]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

[quoted text, click to view]
occur. <<

This poorily named col1, is it FLOAT, DECIMAL or INTEGER?
There is no such thing as a year-month datatype in SQL; you should be
using a date, say the first of each month.

CREATE TABLE Foobar
(foo_date DATETIME NOT NULL PRIMARY KEY,
CHECK (...),
value INTEGER NOT NULL);

Standard SQL programming trick: create a table of all the calendar
information you use in your enterprise and join on it.

CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY
CHECK (...),
qtr CHAR (6) NOT NULL -- example: '2003Q1'
CHECK (qtr LIKE '[0-9][0-9][0-9][0-9]Q[1-4]'),
month_seq INTEGER NOT NULL,
..);

[quoted text, click to view]
that have values more than 60 <<

SELECT qtr
FROM Foobar AS F1, Calendar AS C1
WHERE F1.foo_date = C1.cal_date
AND F1.value >= 60
GROUP BY qtr
HAVING COUNT(*) = 3;

[quoted text, click to view]
more than 60 regardless the quarter. <<

What if I have 18 months in a row over 60? Do you want only 3 month
blocks?

--CELKO--


*** Sent via Developersdex http://www.developersdex.com ***

[quoted text, click to view]
begin counting again from the next month till gathering 3 consecutive
months. <<

SELECT F1.foo_date AS start_date,
DATEADD(MM, 2, F1.foo_date)AS end_date
FROM Foobar AS F1
WHERE 60 <=
ALL (SELECT F2.value
FROM Foobar AS F2
WHERE F2.foo_date
BETWEEN F1.foo_date
AND DATEADD(MM, 2, F1.foo_date);


--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

sorry for my poor english.
I want 3 months blocks, and if any month fails in the sequence, we begin
counting again from the next month till gathering 3 consequtive months.
[quoted text, click to view]

Thanks
[quoted text, click to view]