Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ahouse
Starting Member
27 Posts |
 Posted - 2007-12-10 : 11:18:46
|
| Good Morning and happy Monday to all,I am currently trying to develop a commissions report and am completely stuck.First off, I'm using a software called DBXtra that can generate an ASP page based on my SQL query. It has some limitations but for the most part, it's flexible enough for me. So for this commissions report I need to use my date field to input which dates the commission is between. I'm not having a problem with this part. SELECT DISTINCT [vwCommissions].[UserID],Sum([vwCommissions].[CommissionRate]*[vwCommissions].[BaseCommission]) AS [Commission] FROM [dbo].[vwCommissions] [vwCommissions] GROUP BY [vwCommissions].[UserID] ORDER BY [vwCommissions].[UserID] ASCThis doesn't include the date between code because I think that is just coded directly to the ASP page and not shown in the SQL query. I have no problem making this work but the catch is, out of this two week period, 1 of the days is randomly chosen to be triple pay day. However much commission you make on triple pay day is tripled. So I somehow need to find a way to input the 2 week pay period as well as this exceptional day where pay would equal Base Rate * Commission Rate * 2. I can't think of any possible way to do this in a SQL query. Anyone have any comments or suggestions on where I should start?Thanks everyone. Andrew |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-12-11 : 05:31:39
|
In SQL:SELECT DISTINCT [vwCommissions].[UserID],SUM(case when <MyDateField> = @TripleDate then [vwCommissions].[CommissionRate]*[vwCommissions].[BaseCommission] * 2 else [vwCommissions].[CommissionRate]*[vwCommissions].[BaseCommission] end) AS [Commission]FROM [dbo].[vwCommissions] [vwCommissions]where <MyDateField> >= @StartDate and <MyDateField> <= @EndDateGROUP BY [vwCommissions].[UserID]ORDER BY [vwCommissions].[UserID] ASC Where <MyDateField> is your date field used.Not sure about DBXtra though. |
 |
|
|
|
|
|
Summary Report Predicament