Totals of each transaction type
-
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>=[?] -
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
-
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_idThen 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_idBut get the same error as before
-
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_idThen 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_idBut 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?
-
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 :)