• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

error

I have a data set which I need to count the monthly entries. For example

Jan-08   1
Feb-08   2
Mar-08   3

To do this I have put together some code which like this;

SELECT     CAST(MONTH([Rdate]) AS VarChar(20)) + '-' + CAST(YEAR([Rdate]) AS VarChar(20)) AS Month, COUNT([Rdate]) AS Rs
FROM         table1
GROUP BY [Rdate]
ORDER BY RIGHT([Rdate], 4), CAST(LEFT([Rdate], CHARINDEX('-', [Rdate]) - 1) AS INT)

However I am getting a error message which looks like this;

Invalid lenght passed to SUBSTRING function.

I am not sure what is wrong?
0
Mr_Shaw
Asked:
Mr_Shaw
  • 5
  • 5
  • 5
  • +1
1 Solution
 
chapmandewCommented:
try this:

SELECT     CAST(MONTH([Rdate]) AS VarChar(20)) + '-' + CAST(YEAR([Rdate]) AS VarChar(20)) AS Month, COUNT([Rdate]) AS Rs
FROM         table1
GROUP BY [Rdate]
ORDER BY RIGHT([Rdate], 4), CAST(LEFT([Rdate],
case when CHARINDEX('-', [Rdate]) - 1) >= 2 then CHARINDEX('-', [Rdate]) - 1)  else 0 end
AS INT)
0
 
momi_sabagCommented:
it means the length you pass to the left / right functions is illegal
0
 
mirzasCommented:
Try this:



SELECT     CAST(MONTH([Rdate]) AS VarChar(20)) + '-' + CAST(YEAR([Rdate]) AS VarChar(20)) AS Month, COUNT([Rdate]) AS Rs
FROM         table1
GROUP BY [Rdate]
ORDER BY [Rdate]

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Mr_ShawAuthor Commented:
I used the code

SELECT     CAST(MONTH([Rdate]) AS VarChar(20)) + '-' + CAST(YEAR([Rdate]) AS VarChar(20)) AS Month, COUNT([Rdate]) AS Rs
FROM         table1
GROUP BY [Rdate]
ORDER BY RIGHT([Rdate], 4), CAST(LEFT([Rdate],
case when CHARINDEX('-', [Rdate]) - 1) >= 2 then CHARINDEX('-', [Rdate]) - 1)  else 0 end
AS INT)

I am getting the following error

An expression of non-boolean type specified in a context where a condition is expected, near ')'

Not sure what that really means?
0
 
chapmandewCommented:
whoops....try this:

SELECT     CAST(MONTH([Rdate]) AS VarChar(20)) + '-' + CAST(YEAR([Rdate]) AS VarChar(20)) AS Month, COUNT([Rdate]) AS Rs
FROM         table1
GROUP BY [Rdate]
ORDER BY RIGHT([Rdate], 4),
CAST(LEFT([Rdate],
case when CHARINDEX('-', [Rdate]) >= 2 then CHARINDEX('-', [Rdate])-1  else 0 end)
AS INT)
0
 
mirzasCommented:
The query I sent you does not work?
0
 
Mr_ShawAuthor Commented:
Thanks the code work,

However things are not being grouped together properly. An example of my results are;

Month           Rs
1-2007      2
5-2007      1
5-2007      1
5-2007      1
6-2007      1

As you can see 5-2007 has three seperate entries.

0
 
mirzasCommented:
Try this



SELECT     CAST(MONTH([Rdate]) AS VarChar(20)) + '-' + CAST(YEAR([Rdate]) AS VarChar(20)) AS Month, COUNT([Rdate]) AS Rs
FROM         table1
GROUP BY [Rdate]
ORDER BY CAST(MONTH([Rdate]) AS VarChar(20)) + '-' + CAST(YEAR([Rdate]) AS VarChar(20)) AS Month
0
 
mirzasCommented:
whoops...
this one.. sorry.

SELECT     CAST(MONTH([Rdate]) AS VarChar(20)) + '-' + CAST(YEAR([Rdate]) AS VarChar(20)) AS Month, COUNT([Rdate]) AS Rs
FROM         table1
GROUP BY [Rdate]
ORDER BY CAST(MONTH([Rdate]) AS VarChar(20)) + '-' + CAST(YEAR([Rdate]) AS VarChar(20))
0
 
chapmandewCommented:
no problem

SELECT     CAST(MONTH([Rdate]) AS VarChar(20)) + '-' + CAST(YEAR([Rdate]) AS VarChar(20)) AS Month, COUNT([Rdate]) AS Rs
FROM         table1
GROUP BY CAST(MONTH([Rdate]) AS VarChar(20)) + '-' + CAST(YEAR([Rdate]) AS VarChar(20))
ORDER BY RIGHT([Rdate], 4),
CAST(LEFT([Rdate],
case when CHARINDEX('-', [Rdate]) >= 2 then CHARINDEX('-', [Rdate])-1  else 0 end)
AS INT)
0
 
Mr_ShawAuthor Commented:
This code still does not group the months together
0
 
chapmandewCommented:
mine does.
0
 
mirzasCommented:
SELECT CAST(YEAR([Rdate]) AS VarChar(20)) + '-' + CAST(MONTH([Rdate]) AS VarChar(20)) AS Month, Count(*)  from
table1
GROUP BY CAST(YEAR([Rdate]) AS VarChar(20)) + '-' + CAST(MONTH([Rdate]) AS VarChar(20))
ORDER BY CAST(YEAR([Rdate]) AS VarChar(20)) + '-' + CAST(MONTH([Rdate]) AS VarChar(20))


This one works fine.
0
 
Mr_ShawAuthor Commented:
I am now getting an aggregation error saying that Rdate is not part of order by or group by
0
 
chapmandewCommented:
try this:

select MonthVal, count(*)
from
(
SELECT  CAST(MONTH([Rdate]) AS VarChar(20)) + '-'
        + CAST(YEAR([Rdate]) AS VarChar(20)) AS MonthVal,
RIGHT([Rdate], 4),
        CAST(LEFT([Rdate],
                  case when CHARINDEX('-', [Rdate]) >= 2
                       then CHARINDEX('-', [Rdate]) - 1
                       else 0
                  end) AS INT) AS OrderByCol, RDate
FROM    table1
) a
GROUP BY MonthVal
ORDER BY MIN(OrderByCol)
0
 
Mr_ShawAuthor Commented:
Thanks, it worked fine :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now