MS SQL Query
We need a professional SQL coder to construct a query to return a value that will be used as a form DefaultValue.
The query we require must return a value that includes ALL of the following -
1/ Current Month Name (with a +14 hour time difference for our time zone)
2/ a hyphen “-“ to separate 1/above & 3/below
3/ Final Level Number (calculated on our users Birthdays +/- Level_Adjustment)
So a completed result will look like “September-10” (current month is September and the Level is 10).
The query should be constructed based on the following criteria-
Current Month Name from our database/server and add 14 hours to make up for our time zone difference - DATENAME(MONTH,DATEADD(HOUR,14,GETDATE()))
We have our users Birthday stored in ONE field named Birthday in mm/dd/yyyy format (including the slash marks so - 09/28/1965) in our UserProfile table.
We have a Level_Adjustment column that stores values from -2 to +2 (including 0) in the UserProfile table. (This is for us to manually override a user’s age based level. Almost all values will be 0).
The WHERE will be based on our logged in user and should be written as -
WHERE UserID = $(UserID)
Our School years run from April 1st to March 31st and we want to calculate how old the user was on the previous April 1st from the current +14 hour time (so based on today’s date that means April 1st 2013. If today was March 31st 2014 the calculation still needs to be done on April 1st 2013).
We then need to assign a level from 0 to 18 based on how old the student was on April 1st. If they were 0 then the Level is 0, if they were 1 then the Level is 1, etc…. If they were 18 OR over then the Level is 18.
Once the user Level is calculated we need to add/subtract the Level_Adjustment value and that will give us the Final_Level_Number (if it is less than 0, then we will use 0 instead of a negative number).
Finally we need to return a result to be used as the DefaultValue.
The code will be something like -
SELECT DATENAME(MONTH,DATEADD(HOUR,14,GETDATE()))
+ '-' + (calculation here that give us the Final_Level_Number) AS DefaultValue
FROM UserProfile
WHERE UserID = $(UserID)
In order for us to test your code, we need another version of the same SQL Query written in a way that allows us to manually insert our own current date/time values into the query and see the Month- Final_Level_Number returned based on our the users birthdays in our table.
We would assume that this would just be a longer version of the main SQL query that doesn’t call the server/database time but uses the time we decide (it would still need to add 14 hours for the time zone difference).
Looking forward to your questions, availability and price.
Thank you.
mssql expert here. I can do this query for you with 100% quality and functionality. As assurance, DONT PAY ME anything if any of your requirements aren't met. Thanks