467,917 Members | 1,342 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,917 developers. It's quick & easy.

Cumulating data

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
  • viewed: 1054
Share:
3 Replies
ck9663
Expert 2GB
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 2GB
By the way, be careful with BETWEEN. It's inclusive.

-- CK
Sep 29 '08 #3
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.

Similar topics

11 posts views Thread by Qiangning Hong | last post: by
reply views Thread by Eric | last post: by
reply views Thread by NicK chlam via DotNetMonster.com | last post: by
3 posts views Thread by bbernieb | last post: by
5 posts views Thread by DC Gringo | last post: by
reply views Thread by Winder | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.