question

Eastra avatar image
Eastra 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'))
datesumstart
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·

1 Answer

·
Phil Factor avatar image
Phil Factor answered
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!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks everyone. I will try your answers.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.