Weeks Calculation for Adventureworks Time Dimension

-
The following Named Calculated filed returns Week Start Date & End Date.

'Week ' + Convert(Char(2), WeekNumberOfYear) + '(' +
Convert(Char(10),(FullDateAlternateKey - (DATEPART(dw,FullDateAlternateKey)-1)),101) + ' - ' +
Convert(Char(10),(FullDateAlternateKey - (DATEPART(dw,FullDateAlternateKey) - 1) + 6),101) + ')'

Examples:
-- for Date '2008-01-01', Returns Week 1(01/01/2008 - 01/07/2008)
-- for Date '2008-09-08', Returns Week 37(09/07/2008 - 09/13/2008)


ExcelSheet Formulas for Week Calculation:

Date Cell(A2): 09/08/2008

Week Start Date Cell(K2): =DATE(YEAR(A2), MONTH(A2), DAY(A2) - (WEEKDAY(A2) - 1))

-- Returns 09/07/2008

Week End Date Cell(L2): =DATE(YEAR(A2), MONTH(A2), DAY(A2) - (WEEKDAY(A2) - 1) + 6)

--Returns 09/13/2008

Start & End Date: =CONCATENATE(YEAR(A2), " - Week ", WEEKNUM(A2), " (", TEXT(K2, "mm/dd/yyyy"), " - ", TEXT(L2, "mm/dd/yyyy"), ")")

-- Returns Week 37(09/07/2008 - 09/13/2008)

Quarter Calcualtion:

=TEXT(A2,"yyyy") & " - " &"Q"&INT(((MONTH(A2))-1)/3)+1

-- Returns 2008 - Q3

Comments

Post a Comment

Popular posts from this blog

Windows Azure Package Build Error: The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.

-
Image
I often see when packaging Windows Azure project getting "The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters." error. To fix this issue I had to change Cloud Project User options file add add ServiceOutputDirectory entry to the file. 1) Open your Cloud Project file in Windows Explorer from Visual Studio. 2) Find Cloud Project file in opened directory (*.ccproj file) 3) Open file in Notepad 4) Add c:\azure entry Now your cloud visual studio project user options file looks similar to below: 5) Save file and open your solution again. 6) Package Solution and it should package without above error.
Read more

Resource ID : 1. The request limit for the database is 180 and has been reached.

-
Image
How to find root cause for SQL Azure DB error - " Resource ID : 1. The request limit for the database is 180 and has been reached." I have ran the following two queries to find long running queries that causing connection limit issue. When you run below queries the result set 1 shows all current running query on DB with active session ids. If result set1 has non-zero values in “blocking_session_id” column, you can find same session id in 2 nd result set and see the query text. Based on query text, you can guess which job is running this query & kill/suspend the job/query to release the server resources. select r . session_id , r . blocking_session_id , r . wait_type , r . wait_time , r . wait_resource , r . total_elapsed_time , r . cpu_time , r . reads , r . writes , s . nt_user_name , s . program_name , s . total_elapsed_time from sys . dm_exec_requests r join sys . dm_exec_sessions s on s . session_id = r . session_id SELECT  ...
Read more

How to get Client's Location using IPAddress

-
You can consume free Web Service, which can give IP Address Location Details. http://www.aspsnippets.com/post/2009/04/12/Find-Visitors-Geographic-Location-using-IP-Address-in-ASPNet.aspx The Following function returns Location Details as DataTable: private DataTable GetLocation( string ipaddress) { //Create a WebRequest WebRequest rssReq = WebRequest .Create( "http://freegeoip.appspot.com/xml/" + ipaddress); //Create a Proxy WebProxy px = new WebProxy ( "http://freegeoip.appspot.com/xml/" + ipaddress, true ); //Assign the proxy to the WebRequest rssReq.Proxy = px; //Set the timeout in Seconds for the WebRequest rssReq.Timeout = 2000; try { //Get the WebResponse WebResponse rep = rssReq.GetResponse(); //Read the Response in a XMLTextReader XmlTextReader xtr = new XmlTextReader (rep.Get...
Read more