Cumulating data

[preek]

Hi there,

The following statement cumulates all scanned invoices from a given day:

"SELECT CONVERT(char(10 ), SCANDATE, 102) AS Scandate, COUNT(CONVERT(c har(10), SCANDATE, 102)) AS Number_of_scann ed_invoices
FROM invoices
WHERE (Scandate IS NOT NULL) AND
(Scandate BETWEEN '" + from + "' AND '" + until_ +
"') GROUP BY CONVERT(char(10 ), SCANDATE, 102)
ORDER BY CONVERT(char(10 ), SCANDATE, 102) ASC"

SCANDATE is a normal MSSQL timestamp.

The result looks like this:

Scandate | Number_of_scann ed_invoices

2008.02.11 | 4
2008.02.12 | 11
2008.02.21 | 1
2008.02.22 | 8
2008.02.26 | 26

What I need to know and what I'm trying to find out the whole day is how it can be implemented that the number of scanned invoices is cumulated not on a dayly, but a monthly basis.

It should look like something like this:

Scandate | Number_of_scann ed_invoices

2008.02 | 21
2008.03 | 46


Help is of course very appreciated.

Alain

[ck9663]

Try changing your

CONVERT(char(10), SCANDATE, 102)

to

substring(CONVERT(char(10), SCANDATE, 112),1,6)

-- CK

[ck9663]

By the way, be careful with BETWEEN. It's inclusive.

-- CK

[preek]

Wow, this is real magic happening(;

No, seriously - thank you very much; I wouldn't have come up with the idea, but it works like a charm and now after I've seen how it can be done.. of course I feel stupid, like all the time when something like that happens*g*

Anyway; you made my day - now I don't have to cumulate the data in a cronjob, just to work around my unworthy MSSQL skills..

Thanks again and have a great time
Alain

P.S.: I know of BETWEEN being inclusive; that's just what I want here. But thanks for the hint anyway.