frogman22
asked on
Need SQL Case Statement Help
When using the following case statement I get results that = "Total Records" for the year in all monthly columns.
Please see attached results spreadsheet as example of query results.
I do not understand what the problem is.
If I change "count" to "sum" I get the following error-
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'sourcefrom' to data type int.
begindate = datetime
sourcerfrom = varchar(20)
select datepart(year,begindate) as Years,sourcerfrom,
count(case when datepart(month,begindate)= 1 then sourcerfrom else 0 end) as January,
count(case when datepart(month,begindate)= 2 then sourcerfrom else 0 end) as February,
count(case when datepart(month,begindate)= 3 then sourcerfrom else 0 end) as March,
count(case when datepart(month,begindate)= 4 then sourcerfrom else 0 end) as April,
count(case when datepart(month,begindate)= 5 then sourcerfrom else 0 end) as May,
count(case when datepart(month,begindate)= 6 then sourcerfrom else 0 end) as June,
count(case when datepart(month,begindate)= 7 then sourcerfrom else 0 end) as July,
count(case when datepart(month,begindate)= 8 then sourcerfrom else 0 end) as August,
count(case when datepart(month,begindate)= 9 then sourcerfrom else 0 end) as September,
count(case when datepart(month,begindate)= 10 then sourcerfrom else 0 end) as October,
count(case when datepart(month,begindate)= 11 then sourcerfrom else 0 end) as November,
count(case when datepart(month,begindate)= 12 then sourcerfrom else 0 end) as December,
count(*)as Total
from db1.dbo.tbl_1
group by datepart(year,begindate),s ourcerfrom
order by sourcerfrom,years
case-statement-results.xlsx
Please see attached results spreadsheet as example of query results.
I do not understand what the problem is.
If I change "count" to "sum" I get the following error-
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'sourcefrom' to data type int.
begindate = datetime
sourcerfrom = varchar(20)
select datepart(year,begindate) as Years,sourcerfrom,
count(case when datepart(month,begindate)=
count(case when datepart(month,begindate)=
count(case when datepart(month,begindate)=
count(case when datepart(month,begindate)=
count(case when datepart(month,begindate)=
count(case when datepart(month,begindate)=
count(case when datepart(month,begindate)=
count(case when datepart(month,begindate)=
count(case when datepart(month,begindate)=
count(case when datepart(month,begindate)=
count(case when datepart(month,begindate)=
count(case when datepart(month,begindate)=
count(*)as Total
from db1.dbo.tbl_1
group by datepart(year,begindate),s
order by sourcerfrom,years
case-statement-results.xlsx
What field contains the values you wish to sum up?
ASKER
sourceform
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was it!!!! Thanks for the help