asked
sql start end date
If I want for example the sum for only februari, what code do I need? SELECT ((SELECT COALESCE(SUM([Time]*[Price]*(100-[DiscountPct])/100),0) FROM [tblTime], [tblTask] WHERE [tblTime].[OwnerType]=2 AND [tblTime].[OwnerId]= [tblTask].[Id] AND [tblTask].[Status]=4 AND [tblTask].[CustomA3]='Norr') + (SELECT COALESCE(SUM([Amount]*[Price]*(100-[DiscountPct])/100),0) FROM [tblMaterial], [tblTask] WHERE [tblMaterial].[OwnerType]=2 AND [tblMaterial].[OwnerId]=[tblTask].[Id] AND [tblTask].[Status]=4 AND [tblTask].[CustomA3]='Norr'))
In order to help people with the same problem, I'd like to ask you to please indicate all helpful answers by clicking on the thumbs up next to those answers. So that others can understand what solved your problem, can you please mark the best answer by clicking on the check box next to it.
If you want to report by month, for all months and years, then you might do something like this (Guessing that the date of all the times is in tblTime.StartDate) SELECT COALESCE(SUM([Time]*[Price]*(100-[DiscountPct])/100),0) AS Cost_With_Discount SUBSTRING(CONVERT(CHAR(13),tblTime.StartDate,113),4,8) AS The_Month FROM tblTime INNER JOIN tblTask ON [tblTime].[OwnerId]= [tblTask].[Id] WHERE [tblTime].[OwnerType]=2 AND [tblTask].[Status]=4 AND [tblTask].[CustomA3]='Norr') GROUP BY SUBSTRING(CONVERT(CHAR(13),tblTime.StartDate,113),4,8) Of course, if you wanted to report for all customers, you'd want to group by customA3 rather than filter each one out with the WHERE clause. One gotcha is that you won't get 0 for a month if there are no times recorded (That coalesce isn't going to work because you wouldn't get a NULL total) How you tackle that would depend on how you do the reporting!
17 People are following this question.
