|
Hi
How can I get the total number of two types of transaction between two dates per user
Example data:
User TransactionType MovementDate
Avril RECP 2012/01/04
Pat RECP 2012/01/09
Pat SRET 2012/01/09
What I would like is the following:
User Recipts Returns
Avril 1 0
Pat 1 1
This is where I was heading, but as soon as I put in a where clause to limit the date range I get an Error " Incorrect column expression: 'SUM(CASE WHEN stkhistm.transaction_type = 'RECP Then 1 else 0 end)'
SELECT SUM(CASE WHEN stkhstm.transaction_type = 'RECP' then 1 else 0 end) , SUM(CASE WHEN stkhstm.transaction_type = 'SRET' then 1 else 0 end)
FROM vektron.scheme.stkhstm stkhstm
WHERE stkhstm.movement_date>=[?]
|
|
|
|
|
What database are you using, [?] as a parameter looks like Access, the code looks like TSQL and you are mixing the parameter types. A TSQL parameter would be @Date
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Oops sorry - SQL Server 2008 R2 - MS Query - Excel
Seem to have got a liitle further:
This works:
SELECT user_id, SUM(CASE WHEN stkhstm.transaction_type = 'RECP' then 1 else 0 end) ,
SUM(CASE WHEN stkhstm.transaction_type = 'SRET' then 1 else 0 end)
FROM vektron.scheme.stkhstm stkhstm
GROUP BY user_id
Then I tried to add a restriction on the dates:
SELECT user_id, SUM(CASE WHEN stkhstm.transaction_type = 'RECP' then 1 else 0 end) *
SUM(CASE WHEN stkhstm.movement_date > #01/01/2012# then 1 else 0 end) * sum(CASE WHEN stkhstm.movement_date < #01/01/2013# then 1 else 0 end),
SUM(CASE WHEN stkhstm.transaction_type = 'SRET' then 1 else 0 end)
FROM vektron.scheme.stkhstm stkhstm
GROUP BY user_id
But get the same error as before
|
|
|
|
|
As to the dates, you probably want to use BETWEEN -- I do hope you are storing dates as DATE or DATETIME, not strings.
If you execute your command in SSMS, what line does it tell you the error is on?
|
|
|
|
|
Thanks PIEBALDconsult
The problem was amongst other things the dates !!
This works now:
SELECT user_id,
SUM(CASE WHEN (stkhstm.transaction_type = 'RECP' AND stkhstm.movement_date >= {ts '2012-01-01 00:00:00'} AND stkhstm.movement_date < {ts '2013-01-01 00:00:00'}) then 1 else 0 end) ,
SUM(CASE WHEN (stkhstm.transaction_type = 'SRET' AND stkhstm.movement_date >= {ts '2012-01-01 00:00:00'} AND stkhstm.movement_date < {ts '2013-01-01 00:00:00'}) then 1 else 0 end)
FROM vektron.scheme.stkhstm stkhstm
GROUP BY user_id
'BETWEEN' would be a bit more elegant I guess 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
