Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
msugradus
Starting Member
40 Posts |
 Posted - 2007-06-12 : 10:26:40
|
| currently I am creating a total if the date is between two entered dates:select sum(case when exigo_data_sync.orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end) as PrevMonthCount,I need to check an additional column for ($1.00) or $1.00. If it contains $1.00 then proceed as normal and add to the total. If it contains ($1.00) then subtract one from the total. Any advice? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-12 : 10:36:17
|
| sum(case when col = '$1.00' then 1 else -1 end)select sum(case when exigo_data_sync.orders.orderdate between @prevMonthStart and @prevMonthEnd then case when col1 = '$1.00' then 1 else 0 end else 0 end ) as PrevMonthCount,==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
msugradus
Starting Member
40 Posts |
Posted - 2007-06-12 : 11:19:59
|
| Its telling me to use the convert function to run this query? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-12 : 11:32:05
|
| how about select sum(case when exigo_data_sync.orders.orderdate between @prevMonthStart and @prevMonthEnd then case when col1 = 1 then 1 else 0 end else 0 end ) as PrevMonthCount,==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-12 : 11:32:40
|
quote:
Originally posted by msugradus
Its telling me to use the convert function to run this query?
Post your query and the exact error message you gotMadhivananFailing to plan is Planning to fail |
|
|
|
msugradus
Starting Member
40 Posts |
Posted - 2007-06-12 : 11:34:53
|
| never mind....doh |
|
|
|
msugradus
Starting Member
40 Posts |
Posted - 2007-06-12 : 11:48:36
|
| Well I thought that I had it but not quite..the last part of the puzzle: I need to convert ($1.00) to a -1. I tried convert(int,($1.00)) but it sees it as just a 1 not a -1 |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-06-13 : 08:50:54
|
| Trysum(case when col = '$1.00' then -1 else 1 end)MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
adding conditional statement to a sum