March 5, 2009 at 1:00 am
I have a table;
MstMarket
MDate date
MarketID int
MatchedSize real
ProfitAndLoss real
TransID int
(& other fields not relevant to this query)
File contains multiple entries per Month / day / market, each row erpresents one transaction that has a matched size and a profitandloss value.
I have an existing query to generate my turnover per day;
select sum(Matchedsize) as turnover, datepart(month, matcheddate) as myMonth,
datepart(day, matcheddate) as myDay
from mstbets
group by datepart(month, matcheddate), datepart(day, matcheddate)
having datepart(month, matcheddate)=1
order by datepart(month, matcheddate), datepart(day, matcheddate)
This works fine but I am trying to create a query to;
Generate a count at month / day level for profitable and non profitable markets. For example;
Month Day ProfitableCount NonProfitableCount
3 4 6 3
Indicating that on the 4th of March the total profit and loss on 6 markets was >0 and on 3 markets was <0.
Any help would be greatly received. Is this really complex or I am being a wimp at not being able to master it??:w00t:
March 5, 2009 at 1:52 am
Hello,
The code below is generic, but you could adapt it for your needs:-
Declare
@MyTable Table (MyColumn Int)
Insert @MyTable Values (100)
Insert @MyTable Values (-100)
Insert @MyTable Values (500)
Select
Sum(Case When MyColumn > 0 Then 1 Else 0 End) As GreaterThanZero,
Sum(Case When MyColumn < 0 Then 1 Else 0 End) As LessThanZero
From
@MyTable
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
March 5, 2009 at 2:18 am
Many thanks for such a prompt reply.
This is where I start and feel really :blush: thick. I tried the above code and it works great, I am having trouble adapting to my scenario.
The following query returns the results below;
select CAST((sum(profitandloss)*1.00) AS decimal(8,2)) as pl, datepart(month, matcheddate) as myMonth,
datepart(day, matcheddate) as myDay, marketid
from mstbets
group by datepart(month, matcheddate), datepart(day, matcheddate), marketid
having datepart(month, matcheddate)=3
order by datepart(month, matcheddate), datepart(day, matcheddate), marketid
nb: the cast is to only show 2 decimal places
pl myMonth myDay marketid
-6.6031100401231
0.0031100401235
6.3531100401241
0.1531100401272
0.0031100401284
2.7632100404048
0.2732100404056
-0.1232100404059
0.0732100404063
1.9332100404069
-1.8332100404071
0.0032100404458
0.0032100404462
0.3532100404466
1.1932100404470
1.0832100404472
So I want to adapt the sql posted in reply to generate a result set that would show (for the above data)....
myMonth myDay GreaterThanZero LessThanZero
3 1 2 1
3 2 6 2
Again all replies appreciated as I must seem rather dim to most on here:hehe:
March 5, 2009 at 3:50 am
What about something like
SELECTPL.MyMonth,
PL.MyDay,
SUM(CASE WHEN PL.ProfitAndLoss > 0 THEN 1 ELSE 0 END) AS GreaterThanZero,
SUM(CASE WHEN PL.ProfitAndLoss < 0 THEN 1 ELSE 0 END) AS LessThanZero
FROM(select sum(profitandloss) as ProfitAndLoss,
datepart(month, matcheddate) as myMonth,
datepart(day, matcheddate) as myDay, marketid
from mstbets
group by datepart(month, matcheddate), datepart(day, matcheddate), marketid
having datepart(month, matcheddate)=3) AS PL
GROUP BY PL.MyMonth,PL.MyDay
ORDER BY PL.MyMonth,PL.MyDay
March 5, 2009 at 4:14 am
Brilliant:D
Many thanks John & md, that works great and will save me a lot of time. Having examples like this is great for learning SQl for me.
Have a great day guys, you have brightened mine up 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply