Single SQL Server query to get the total score by hourly, daily, weekly, monthly, and annual

I have the following requirement :

single SQL Server query to get the total score by hourly, daily, weekly, monthly, and annual data.

this is how the result should be:

thisperiodtotalscore  previousperiodtotalscore  sumtotalscore periodType

which meets the following criteria:

• where score comes from the table data, totalscore has to be summed up for the different members of different teams,
• where period can be hourly, daily, weekly, monthly, and annual and hourly is determined by the working hour definition, say it can be any number of selected hours (example: 1st working hour, 2nd working hour, and so on..)
• and weekly determined by working days definition, say it can be wednesday to wednesday,..)
• and likewise for monthy and annual

If the period has empty data, say if it is a holiday/leave that particular period should 开发者_如何学Cnot be skipped in the count.

Note:

thisperiodtotalscore (this period total score can be for any of the periods (hourly, daily, weekly, monthly, annual) for the user date input) - say the corresponding week score of the user input, the corresponding month score of the user date input, .. likewise

previousperiodtotalscore (previous period total score can be for any of the periods (hourly, daily, weekly, monthly, annual) for the user date input) - say previous week score of the user date input, previous month score of the user date input,.. likewise

sumtotalscore - total of the thisperiodtotalscore and previousperiodtotalscore

periodType - hourly, daily, weekly, monthly, annual, based on the period request type

and which meets the following criteria:

• where score comes from the table data, totalscore has to be summed up for the different members of different teams,
• where period can be hourly, daily, weekly, monthly, and annual and hourly is determined by the working hour definition, say it can be any number of selected hours (example: 1st working hour, 2nd working hour, and so on..)
• and weekly determined by working days definition, say it can be wednesday to wednesday,..)
• and likewise for monthly and annual

If the period has empty data, say if it is a holiday/leave that particular period should not be skipped in the count.

This is the requirement, also Welcome for other possible cases if missed in such kind of scenarios.

Thanks in advance, GravityPush

For start, take a look at DimDate in AdventureWorksDW sample. You can have a similar table in your database and write some join queries. I suggest use queries which are dynamically grouped by different columns of DimDate.