consecutive month count 1

[chaoma]

Hi,

I am new to SQL programming. Please help.

I would like to select all the Month count in 3/2005, 4/2005, 5/2005 from this table:

ID Start End
1 3/1/2005 3/31/2005
1 4/1/2005 4/30/2005
1 5/1/2005 5/31/2005
2 2/1/2003 Null
3 3/1/2005 4/30/2005
4 3/1/2005 6/30/2005

I want the result to be as follow:

ID MonthCountFeb_May
1 3
2 3
4 3

Record with ID=3 is not include because it doesn't cover the period between 3/2005-5/2005.

Is this sound like a good solution?

1. Copy to new table
2. Update Begin column to 3/1/2005 if <=3/1/2005
3. Update End column to 5/31/2005 if null or >=5/31/2005
4. Create new column call MonthCount
5. update MonthCount=?????

Any help would be appreciate.

 

[PHV]

SELECT ID, 3 MonthCountFeb_May
FROM yourTable
GROUP BY ID
HAVING Min(Start)<='2005-03-01'
AND (Max(End)>='2005-05-31' OR Max(End) Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[chaoma]

PHP,

Thank you very much for your help,

 

[chaoma]

Phv,

It doesn't work, because it would not select record for ID=1 because they covered from

ID start end
1 2/1/2005 2/28/2005
1 3/1/2005 3/31/2005
1 4/1/2005 4/30/2005

Each record won't cover the whole three months, but sum of those three will.

Thanks.

 

[chaoma]

Correction.

It doesn't work, because it would not select record for ID=1 because they covered from

ID start end
1 3/1/2005 3/31/2005
1 4/1/2005 4/30/2005
1 5/1/2005 5/31/2005

Each record won't cover the whole three months, but sum of those three will.

There is another scenario that will cover 3 months with 2 data:

ID start end
5 3/1/2005 4/30/2005 (2 months)
5 5/1/2005 5/31/2005 (1 months)

total month 3 months

Thanks.

 

[kittyyo]

SELECT ID, SUM(
MONTH( IF((End IS NULL) OR (End > '2005-05-31'),
'2005-05-31', End) ) -
MONTH( IF(Start < '2005-03-01', '2005-03-01',
Start) ) + 1) AS Months
FROM Test
GROUP BY ID


This includes 3 Months for record 3, because I didn't understand your statement

"Record with ID=3 is not include because it doesn't cover the period between 3/2005-5/2005."

- doesn't the fact that End IS NULL mean that the period is infinite into the future?

Anne

 

[chaoma]

phv,

My stupidity, your code does work. I have another column in between making the GROUP BY returning less records.

However, I don't think aggregate function work with NULL value. I convert all NULL to 4/30/2004.

Ann,

Thank you. I think code work as well. I don't have to worry about NULL for aggregate function. I am using SQL 2000 Query Analyzer, which doesn't seem to take the IF statement they way you did it. I will try to make the IF statement work in SQL 2000. If anyone have any suggestion, I would appreciate it.

Yes, NULL=infinite.

Again, thank you.

 

[PHV]

Use CASE ... WHEN ... ELSE ... END

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[chaoma]

Thank you. Complicate, but it working.