By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Cumulating data

P: 4
Hi there,

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

"SELECT CONVERT(char(10), SCANDATE, 102) AS Scandate, COUNT(CONVERT(char(10), SCANDATE, 102)) AS Number_of_scanned_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_scanned_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_scanned_invoices

2008.02 | 21
2008.03 | 46


Help is of course very appreciated.

Alain
Sep 29 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
Try changing your
Expand|Select|Wrap|Line Numbers
  1. CONVERT(char(10), SCANDATE, 102) 
  2.  
to

Expand|Select|Wrap|Line Numbers
  1. substring(CONVERT(char(10), SCANDATE, 112),1,6)
  2.  
-- CK
Sep 29 '08 #2

ck9663
Expert 2.5K+
P: 2,878
By the way, be careful with BETWEEN. It's inclusive.

-- CK
Sep 29 '08 #3

P: 4
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.
Sep 30 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.