tag:blogger.com,1999:blog-36754417353950182862024-03-06T07:43:39.529+01:00Acinta ExpertExpert advice on installing and configuring business intelligence systemsUnknownnoreply@blogger.comBlogger13125tag:blogger.com,1999:blog-3675441735395018286.post-91446748190232301782017-08-21T11:19:00.001+02:002017-08-21T11:19:26.993+02:00Get first name and initial of last nameSometimes names are longer than convenient for displaying in tables and graphs, so you may consider making them shorter.<br />
<br />
Considering the name "Jonathan White" as example, here are a few tips for you:<br />
<br />
<h2>
Only first name</h2>
<div>
SELECT LEFT(Name, CHARINDEX(' ', Name) - 1)</div>
<div>
<blockquote class="tr_bq">
"Jonathan"</blockquote>
</div>
<div>
<br /></div>
<h2>
First name plus initial of last name</h2>
<div>
<div>
SELECT LEFT(Name, CHARINDEX(' ', Name) + 1)</div>
<blockquote class="tr_bq">
"Jonathan W"</blockquote>
</div>
<div>
<br /></div>
<h2>
Just initials</h2>
<div>
SELECT SUBSTRING(Name, 1, 1) + SUBSTRING(Name, CHARINDEX(' ', Name) + 1, 1)
</div>
<blockquote class="tr_bq">
"JW"</blockquote>
<br />
<h3>
Notes</h3>
<div>
There are some caveats with the above suggestions. They may not work as expected if:<br /><ul>
<li>If there is only one name, e.g. "Peter"</li>
<li>If there is more than 2 names, e.g. "Jennifer Alexandra Smith"</li>
</ul>
<div>
For more advanced processing a UDF (function) or SP (procedure) is probably better adviced.</div>
</div>
<div>
<br /></div>
<!--StartFragment -->Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3675441735395018286.post-57219066887615683432015-10-14T00:56:00.002+02:002015-10-14T09:41:31.389+02:00Day of week calculationWith SQL Server you can get the day in week using this simple code:<br />
<br />
<blockquote class="tr_bq">
<span style="font-family: Courier New, Courier, monospace;">SELECT DATEPART(weekday, getdate())</span></blockquote>
This gives you a number from 1 to 7, corresponding to the day in the week.<br />
<br />
But which day is the first day in the week?<br />
<br />
If you are European - it's Monday.<br />
If you are American - it's Sunday.<br />
If you are SQL Server - it depends...<br />
<br />
There's a setting in SQL Server that controls this behavior and perhaps it is not set to your liking. And perhaps you don't want to change it and run the risk of side-effects - or perhaps you simply don't have the permissions.<br />
<br />
Despair not!<br />
<br />
This simple code will allow you to get the day in week independent of SQL Server's settings:<br />
<br />
<b>European:</b><br />
<span style="font-family: Courier New, Courier, monospace;">SELECT (DATEDIFF(dd, DATEADD(YEAR, 0, 0), getdate()) + 1) % 7</span><br />
<br />
<b>American:</b><br />
<span style="font-family: Courier New, Courier, monospace;">SELECT (DATEDIFF(dd, DATEADD(YEAR, 0, 0), getdate()) + 2) % 7</span><br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3675441735395018286.post-18778367698122939192015-03-03T01:05:00.000+01:002015-03-03T01:05:09.644+01:00How to create lookup views for enum types in ERP system<h2>
Problem</h2>
Quite often we have to include fields in our data model that have no lookup table in the ERP system. They are usually some kind of "Type" and they are sometimes referred to as "Enum" types in the ERP system.<br />
<br />
They have their text values stuck somewhere inside the ERP application layer rather than in real database tables.<br />
<br />
Also often it is not desirable to create physical tables in ERP database. And even if we could they would be separated from both the ERP system and the data model we are creating, living their own lives so to speak.<br />
<br />
So how do we get a lookup table with the text values corresponding to the enum types?<br />
<br />
<h2>
Solution</h2>
<div>
One possible solution to this is to create a Dynamic View in Modeller and simple create the record synthetically - with no physical tables involved.</div>
<div>
<br /></div>
<div>
All you need is the ERP system to look up the text values you need and then enter them in the Dynamic View.</div>
<div>
<br /></div>
<h2>
Example</h2>
<div>
Let's say we have an enum called "LineType", which has these values:</div>
<div>
<br /></div>
<blockquote class="tr_bq">
0: Item<br />1: Text</blockquote>
<div>
<br /></div>
<div>
We can create a Dynamic View for these values with this SQL:</div>
<div>
<br /></div>
<blockquote class="tr_bq">
SELECT 0 AS LineType_ID, 'Item' AS LineType_Name<br />UNION ALL<br />SELECT 1, 'Text'</blockquote>
<div>
<br /></div>
<div>
For practical purposes there will usually be more than 2 values of your enum, so just keep adding "UNION ALL... SELECT..." statements for all the values you need.</div>
<div>
<div>
<br /></div>
<div>
<br /></div>
<div>
That's all. Enjoy!</div>
<div>
<br /></div>
</div>
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-3675441735395018286.post-19212067724242970952014-02-13T14:50:00.000+01:002014-02-13T14:50:15.784+01:00Antivirus system makes loading of project very slowIf you observe that Acinta Modeller, Datashop og Enterprise Manager take a very long time to connect to and load metadata then the cause may be an installed antivirus system.<br />
<br />
This problem was observed at a customer that had Trend Micro antivirus running.<br />
<br />
Just connecting to metadata (after entering login) could take several minutes.<br />
Loading the project - well, we had better things to do.<br />
<br />
When we turned off Trend Micro antivirus, all worked normal.<br />
<br />
<blockquote class="tr_bq">
<b style="background-color: white; color: #333333; font-family: Arial, Helvetica, sans-serif; font-size: 13px; line-height: 18.200000762939453px;"><span style="background-color: #93c47d; color: white;"> SOLUTION </span></b></blockquote>
<br />
<ol>
<li>Turn off the antivirus system, or the parts hereof that cause the problem, or</li>
<li>Add the Acinta applications to the antivirus system's "White-list"</li>
</ol>
<div>
For more details about Trend Micro and how to add applications to it's white-list, follow this link:</div>
<div>
<a href="http://esupport.trendmicro.com/solution/en-US/1059313.aspx">http://esupport.trendmicro.com/solution/en-US/1059313.aspx</a></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3675441735395018286.post-49941750359473634092012-12-13T10:54:00.002+01:002013-03-06T10:52:27.843+01:00Installing on SQL Server 2000If you need to install Acinta Shortcut on a SQL Server 2000 then there are a couple of changes you need to make. They are briefly described below.<br />
<br />
<h2>
Views</h2>
<ol>
<li><b>"Acinta_Split" not supported.</b> You'll see an error like this: "Syntax error near a". Acinta_Split is used to decipher "Tælleværker" in C5. Table-valued functions are not supported by SQL Server 2000. You have to remove/comment out all SELECT statements using this function. As a result, you'll not be able to see the results of "Tælleværker".</li>
<li><b>Top N not supported.</b> A couple of places contain sub-queries in the form:<br />SELECT TOP 1 <Expr><br />.....<br />ORDER BY ....<br /><br />This must be rewritten to:<br />SELECT MAX(<Expr>)<br />.....<br /><br />These functions were introduced in SQL Server 2005.<br />How to solve: Replace TOP 1 with MAX( ) and remove the ORDER BY clause.</li>
<li><b>Row_number() and RANK not supported.</b> In one place these functions are used. There is no simple way to express this function in SQL Server 2000 so the simplest solution is to replace the whole expression by "NULL". If you need this function you'll have to rewrite it yourself. However, it is rarely used so don't worry.</li>
</ol>
<h2>
Indexes</h2>
<div>
Indexes in SQL Server 2000 don't allow the INCLUDE columns. You have to rewrite the indexes and put all needed columns in the main index part. Remember there's a limit of 16 fields per index.</div>
<div>
<br /></div>
<div>
In addition, the "WITH" sub-clause specifying index options, is not supported and must be removed.</div>
<div>
<br /></div>
<div>
For example you have to rewrite this index:</div>
<div>
<blockquote class="tr_bq">
CREATE NONCLUSTERED INDEX [ACINTA_IX_ProjTrans] ON [dbo].[PROPOST]<br />
(<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[DATASET] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[DATO] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[LXBENUMMER] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[NUMMER] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[ART] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[VARENUMMER] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[LOKATION] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[MEDARBEJDER] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[ENHED] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[BUDGETKODE] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[TRANSAKTION] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[VALUTA] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[PROTYPE] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[KOPIERET] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[OVERFXRTLXN] ASC<br />
)<br />
INCLUDE ( [ANTAL],<br />
[KOSTBELXB],<br />
[TEKST],<br />
[BELXBVAL],<br />
[BELXBSTD],<br />
[DRIFTSFXRTANTAL],<br />
[DRIFTSFXRTBELXBVAL],<br />
[DRIFTSFXRTBELXBSTD],<br />
[KURS],<br />
[DRIFTFXRTLXNTILLXGVAL],<br />
[DRIFTFXRTLXNTILLXGSTD]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]<br />
GO</blockquote>
</div>
<div>
<br /></div>
<div>
to something like this:</div>
<div>
<blockquote class="tr_bq">
CREATE NONCLUSTERED INDEX [ACINTA_IX_ProjTrans] ON [dbo].[PROPOST]<br />
(<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[DATASET] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[DATO] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[NUMMER] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[ART] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[VARENUMMER] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[LOKATION] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[MEDARBEJDER] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[BUDGETKODE] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[TRANSAKTION] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[VALUTA] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[PROTYPE] ASC,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[OVERFXRTLXN] ASC,<br />
[ANTAL],<br />
[KOSTBELXB],<br />
) ON [PRIMARY]<br />
GO</blockquote>
</div>
<div>
<h2>
Other considerations</h2>
</div>
<h3>
How do I know which SQL Server version it is?</h3>
<div>
In order til check the version of SQL Server you need to open SQL Server Management Studio (or Enterprise Manager for older versions) and connect to the server.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWBVlRbERD6EQf_HQ0SfWtd7WFMjv3-77SKQtqwkA7TCNzeI6I3_uitQcVPVJEQXG6g_se52jZtCCNRET36NNPtm7FOws4jMTcq9o8DRBDEZrnEo2PcKquvsn4o5JEWvKIHwUdEEK1lwYQ/s1600/Screenshot.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWBVlRbERD6EQf_HQ0SfWtd7WFMjv3-77SKQtqwkA7TCNzeI6I3_uitQcVPVJEQXG6g_se52jZtCCNRET36NNPtm7FOws4jMTcq9o8DRBDEZrnEo2PcKquvsn4o5JEWvKIHwUdEEK1lwYQ/s1600/Screenshot.png" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
The internal version number can be read in the red box: 10.</div>
<div class="separator" style="clear: both; text-align: left;">
The internal version numbers have this meaning:</div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<blockquote class="tr_bq">
8: SQL Server 2000<br />
9: SQL Server 2005<br />
10: SQL Server 2008<br />
11: SQL Server 2012</blockquote>
<br />
<h3>
Beware of "Compatibility mode"!</h3>
<div>
SQL Server provides a feature called "Compatibility mode" that enables a newer version of SQL Server to pretend it is an older version. The background for this obscurity is of course to allow older applications to work seamless with newer versions of SQL Server that they were not designed for.</div>
<div>
<br /></div>
<div>
Thus, what may at first sight look like e.g. SQL Server 2008 could in fact be an SQL Server 2000. Or actually it is not that simple. When SQL Server runs in compatibility mode for SQL Server 2000 then the following applies:</div>
<div>
<ol>
<li>It does not recognize newer SQL features such as "TOP" and "Row_number( )". Thus, the fix for these features applies.</li>
<li>It will allow you to <i>create</i> table-value functions (Acinta_Split) but it won't let you use them, thus the remedy for this applies.</li>
<li>It still allows you to create indexes with Included columns. So there's no need to rewrite indexes!</li>
</ol>
<div>
One last thing to consider is whether the compatibility mode is actually correct or if it should be set to the actual version. But you have to verify this; if you change the compatibility mode it may cause some applications to malfunction!</div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3675441735395018286.post-23755140934285552202012-07-10T10:50:00.001+02:002012-07-10T10:50:40.629+02:00Installing on C5 with non-standard schema (not 'dbo')If you need to install Acinta on a C5 that has been installed with a non-standard schema then you need to make some adjustments to the installation procedure as described below.<br />
<br />
<h2>
How do I know that C5 is installed with a non-standard schema?</h2>
<div>
When you look at the C5 tables in SQL Server Management Studio you will see that all tables have a pre-fix that is different than 'dbo'.</div>
<div>
For example, the table "SALESLINE" could look like this:</div>
<blockquote class="tr_bq">
c5_supervisor.SALESLINE</blockquote>
<div>
<br /></div>
<div>
instead of this:</div>
<blockquote class="tr_bq">
dbo.SALESLINE</blockquote>
<h2>
How do I install on a non-standard schema?</h2>
<div>
To install Acinta on such a C5 you need to follow the normal installation procedure with these extra steps:</div>
<div>
<ol>
<li><b>Update Index script.</b> Before installing the indexes you must perform a search-and-replace in the index script where you replace all occurrences of 'dbo.' with the actual schema name.</li>
<li><b>Rename tables in Modeller.</b> When you open the project in Modeller initially, Modeller will start the Rename wizard because it can't find the tables it is looking for. All you have to do is to follow the instructions on the screen. For example Modeller will tell you: "The table dbo.SALESLINE no longer exists. Do you want to replace it?" and then in the list of tables below you simply select the proper replacement table - c5_supervisor.SALESLINE in this example - and click "Rename"</li>
<li><b>Update Dynamic Views.</b> You have to locate all dynamic views in Modeller's Warehouse section. Right-click on the dynamic view table's header and choose "Edit Dynamic View SQL" from the context menu. Review the SQL and make sure all table references use the correct schema prefix</li>
</ol>
</div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3675441735395018286.post-4019334270545477002012-05-09T16:42:00.000+02:002012-05-09T16:42:51.010+02:00"Syntax error near a" and the Acinta_Split() functionI have come across this error while compiling the view/dynamic view "ACINTA_DV LedgerOfAccountsRelation" in the C5 data model:<br />
"Syntax error near a".<br />
<br />
It happens in this line of the view:<br />
<br />
SELECT COUNT(*)<br />
<span style="background-color: #fff2cc;"> FROM Acinta_Split(';', a.TXLLEVXRK) x</span><br />
<div>
<br /></div>
<br />
<br />
The error occurs because SQL Server does not recognize the function "Acinta_Split", which is part of the Acinta Shortcut package.<br />
<br />
Now, the function was actually compiled and present in the system, so the error seems mysterious at first.<br />
It turns out that the database was configured to have compatibility level 80 = SQL Server 2000.<br />
<br />
And since table-based functions were introduced in SQL Server 2005 it is also the natural explanation why SQL Server throws a syntax error at us.<br />
<br />
<h3>
Solution</h3>
To get out of this error you have two choices:<br />
<br />
<ol>
<li>Set the compatibility level of the database to at least 90 (SQL Server 2005)</li>
<li>Comment out the parts of the view that make use of the Acinta_Split function. Block comments have the form: "/* <commented code> */</li>
</ol>
<div>
Obviously, you should choose solution 1. whenever possible. But under some circumstances this is not possible. One such circumstance is when you have a C5 version 3.0 solution, because this version of C5 is only compatible with SQL Server 2000; It does not work with later versions of SQL Server.</div>
<div>
<br /></div>
<h3>
Applies to</h3>
<div>
Please note that this error and its solution applies generally and not just to the C5 solution. I.e. it may apply to:</div>
<div>
<ul>
<li>Acinta Shortcut for C5</li>
<li>Acinta Shortcut for Ax</li>
<li>Acinta Shortcut for Nav</li>
<li>Acinta Shortcut for XAL</li>
<li>Acinta Shortcut for Visma</li>
</ul>
<div>
<br class="Apple-interchange-newline" /></div>
</div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3675441735395018286.post-13850718188455421362012-04-18T11:47:00.003+02:002012-07-13T04:10:07.544+02:00Datashop crashes at startup<b id="internal-source-marker_0.8192276642657816"></b><br />
<span style="font-family: Arial, Helvetica, sans-serif;">If you are experiencing that Datashop crashes before it even displays its splash screen and you get a message from Windows that the application crashed then the cause is probably restrictions on the user's permissions.<br /><br /><br />The possible scenario is that you are trying to make an installation without running the Acinta installer, e.g. a network installation. The installation works fine for you (because you use a classified account, e.g. Administrator) but it fails on end-users' PCs.<br /></span><br />
<blockquote class="tr_bq">
<span style="font-family: Arial, Helvetica, sans-serif;"><b><span style="background-color: #93c47d; color: white;"> SOLUTION </span></b> Right-click on the exe-file or shortcut and choose "Run as administrator...". You only need to do this once. Next time you open Datashop it will work fine.</span></blockquote>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3675441735395018286.post-63175498625324055852012-03-30T12:12:00.001+02:002014-02-13T15:58:51.810+01:00Project takes long time to load - slow loading<b id="internal-source-marker_0.8192276642657816"></b><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Sometimes when you have installed a new project or you are opening the project from a different computer you may experience that the loading of the project is notably slow.<br /></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">There are several possible causes for this problem.</span><br />
<h2 dir="ltr" style="font-weight: bold;">
<span style="font-family: Arial; font-size: 19px; vertical-align: baseline; white-space: pre-wrap;">Possible causes</span></h2>
<h3 dir="ltr" style="font-weight: bold;">
<span style="color: #666666; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;">Old version of Acinta Intelligence Suite</span></h3>
<span style="font-family: Arial, Helvetica, sans-serif;">Check if the version of Intelligence Suite you are trying to open is up to date. If metadata was edited using a newer version then it may have stored properties that are not understood by older versions. When this happens, an error trapping mechanism is invoked that writes all the unrecognized properties to the log file. You can verify the problem by investigating the log file.</span><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"><br /></span><br />
<blockquote class="tr_bq">
<span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"><b>SOLUTION</b></span><span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="font-family: Arial; font-size: 16px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> Update the .exe files to a new version.</span></blockquote>
<h3 dir="ltr">
<span style="color: #666666; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"><br /></span></h3>
<h3 dir="ltr">
<span style="color: #666666; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;">Upgrade to Acinta Intelligence Suite 2014</span></h3>
<span style="font-family: Arial, Helvetica, sans-serif;">If you recently upgraded to Acinta Intelligence Suite 2014 from an older version then the problem may be caused by obsolete properties in metadata. I.e. properties that are no longer supported by the new version. Such properties cause internal validation errors and a lot of writing to the log-files.</span><span style="font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"><br /></span><br />
<blockquote class="tr_bq">
<span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"><b>SOLUTION</b></span><span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"> You must "clean" the metadata by loading the project in Modeller and run the "Repair" utility and then save the project. It is best to use the option "Save all objects" but it may take somewhat longer to process.</span></blockquote>
<span style="font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br />
<span style="font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br />
<h3 dir="ltr" style="font-weight: bold;">
<span style="color: #666666; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;">Slow network</span></h3>
<span style="font-family: Arial, Helvetica, sans-serif;">A lot of metadata must be transferred over the network when Datashop, Modeller etc. opens a project. A slow network may cause this process to take a very long time. Note: Look out for wireless networks since these are notably slower than wired networks.</span><span style="font-family: Arial; font-size: 15px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<blockquote class="tr_bq">
<span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"><b>SOLUTION</b></span><span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="font-family: Arial; font-size: 16px; font-weight: normal; vertical-align: baseline; white-space: pre-wrap;"> Wire the PC to a cable or upgrade/fix problems in the (wireless) network.</span></blockquote>
<br />
<h3 dir="ltr">
<span style="color: #666666; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;">Too little RAM</span></h3>
<span style="font-family: Arial, Helvetica, sans-serif;">If the server has too little RAM to fit all the running processes inside then some memory will be mapped to disk. Since disks are multitudes slower than RAM this will inevitably lead to prolonged load times during start-up because thousands of dynamic objects are created representing metrics, dimensions, dashboards etc. This problem is typical of virtual machines where RAM is often a scarce resource to be shared across many virtual machines.</span><span style="font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<blockquote class="tr_bq">
<span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"><b>SOLUTION</b></span><span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"> Enhance the server with more RAM. If the server is a virtual machine it should be considered whether the RAM is dedicated to the server or dynamically shared. Dedicated RAM is better.</span></blockquote>
<h3 dir="ltr">
<span style="color: #666666; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"><br /></span></h3>
<h3 dir="ltr">
<span style="color: #666666; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;">Antivirus system interference</span></h3>
<span style="font-family: Arial, Helvetica, sans-serif;">Antivirus software such as Trend Micro may cause the project to load <i>extremely</i> slow due to dynamic scanning of running processes.</span><span style="font-family: Arial; font-size: 15px; vertical-align: baseline; white-space: pre-wrap;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"><b>SOLUTION</b></span><span style="background-color: #93c47d; color: #e5f7de; font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="font-family: Arial; font-size: 16px; vertical-align: baseline; white-space: pre-wrap;"> Turn off the antivirus system or the parts hereof that cause the problem, e.g. "process scanning". Or perhaps better: Add the Acinta applications (Datashop, Modeller etc.) to the antivirus' so-called <i>White-list</i> to prevent the AV software from scanning these applications at run-time.</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3675441735395018286.post-84334004570036820162012-01-13T02:00:00.000+01:002012-03-09T12:24:52.335+01:00Fiscal/financial year date calculationsAs a followup to my previous article on calculation of dynamic dates I will demonstrate how we can make similar calculations that take the company's financial year into consideration.<br />
<br />
This is important for companies that have a financial year that is not coinciding with the calendar year. In Denmark the financial year typically starts at the beginning of a quarter, i.e.:<br />
<br />
<ul>
<li>1st of January</li>
<li>1st of April</li>
<li>1st of July, or</li>
<li>1st of October</li>
</ul>
<br />
Thus, if in our dashboard we want to use a "year-to-date" filter then we actually want the filter to span from the beginning of the financial year till today's date.<br />
<br />
Such a filter condition can be implemented using this SQL:<br />
<blockquote class="tr_bq">
<br />
<div class="MsoNormal">
DATEADD(dd, 0, DATEADD(mm, 6, DATEADD(yy, CASE WHEN
MONTH(getdate()) >= 7 THEN YEAR(getdate()) ELSE YEAR(getdate()) - 1 END -
1900, 0)))</div>
</blockquote>
The result of this expression (assuming today's date is January 13th 2012) is:<br />
<blockquote class="tr_bq">
'2011-07-01'</blockquote>
i.e. an ISO formatted date, which should be valid in any SQL Server.<br />
<br />
The above example assumes that the financial year starts on July 1st, but obviously you can use the approach for any start day of the financial year.<br />
<br />
To change the starting month of the financial year you simple have to modify the script in two places:<br />
<br />
<ul>
<li><b>6:</b> Replace this value with the <i>last </i>month in the financial year</li>
<li><b>7:</b> Replace this value with the <i>first</i> month in the financial year</li>
</ul>
<br />
<br />
<span style="font-size: large;"><b>Filter modifiers</b></span><br />
As usual, date functions are very useful with filter modifiers. Using a filter modifier you can implement a report where the user selects a date and then your filter modifier transforms the selection into a "year-to-date" date range using the user's selection.<br />
<br />
To use the above script in a filter modifier you simply have to replace the occurences of "getdate()" with the desired filter modifier placeholder, {Value1} or {Value2}.<br />
<br />
I.e. you need to use this variation of the SQL expression:<br />
<blockquote class="tr_bq">
<br />
<div class="MsoNormal">
DATEADD(dd, 0, DATEADD(mm, 6, DATEADD(yy, CASE WHEN
MONTH({Value1}) >= 7 THEN YEAR({Value1}) ELSE YEAR({Value1}) - 1 END -
1900, 0)))</div>
</blockquote>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3675441735395018286.post-90285131928848208152011-12-06T21:23:00.001+01:002013-03-06T12:41:33.070+01:00How to calculate dynamic dates in SQL ServerQuite often we need to calculate some kind of dynamic date based on the current date or the user's selection in a date filter. Examples include "First day of the year", "First day of the month" and so on.<br />
<br />
To help you build such date formulas I have assembled a set of useful calculations for you to use.<br />
<br />
Note: To use the calculations in Filter Modifiers you simply replace "getdate()" with the desired filter placeholder, e.g. "{Value1}".<br />
<br />
<h3 style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
First Day of Month</h3>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="white-space: pre-wrap;">DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<br /></div>
<h3 style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
Monday of the Current Week</h3>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="white-space: pre-wrap;">DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<br /></div>
<h3 style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
First Day of the Year</h3>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="white-space: pre-wrap;">DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="white-space: pre-wrap;"><br /></span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;">First Day of the Quarter</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="white-space: pre-wrap;">DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;"><br /></span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;">Midnight for the Current Day</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="white-space: pre-wrap;">DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;"><br /></span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;">Last Day of Prior Month</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="white-space: pre-wrap;">DATEADD</span><span class="Apple-style-span" style="white-space: pre-wrap;">(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;"><br /></span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;">Last Day of Prior Year</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="white-space: pre-wrap;">DATEADD</span><span class="Apple-style-span" style="white-space: pre-wrap;">(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;"><br /></span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;">Last Day of Current Month</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="white-space: pre-wrap;">DATEADD</span><span class="Apple-style-span" style="white-space: pre-wrap;">(dd,-1,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;"><br /></span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;">Last Day of Current Year</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="white-space: pre-wrap;">DATEADD</span><span class="Apple-style-span" style="white-space: pre-wrap;">(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;"><br /></span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="font-weight: bold;">First Monday of the Month</span></div>
<div style="background-color: white; font-family: verdana, arial, helvetica; font-size: 13px;">
<span class="Apple-style-span" style="white-space: pre-wrap;">DATEADD(wk, DATEDIFF(wk,0,</span><span class="Apple-style-span" style="white-space: pre-wrap;"> dateadd(dd,6-datepart(day,getdate()),getdate())</span><span class="Apple-style-span" style="white-space: pre-wrap;">), 0) </span></div>
<div>
<br />
<b>First Day of the Month 12 Months Ago</b><br />
DATEADD(mm, DATEDIFF(mm,0,getdate()) - 12, 0)<br />
<i>Note: You can change "12" to any number of months ago you wish.</i><br />
<br /></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3675441735395018286.post-50563502738697483412010-12-07T11:22:00.001+01:002010-12-07T11:23:02.373+01:00Fejl: "Repository databases are not correctly configured" - problem med danske tegn<span class="Apple-style-span" style="font-family: inherit;">Hvis du får beskeden:</span><br />
<div style="margin-left: 20px;"><span class="Apple-style-span" style="font-family: 'Helvetica Neue', Arial, Helvetica, sans-serif;">---------------------------<br />
Warning<br />
---------------------------<br />
Repository databases are not correctly configured. <br />
You may re-configure repository from menu: File|Configure repository.</span></div><br />
<span class="Apple-style-span" style="font-family: inherit;">når du åbner Datashop/Modeller, så skyldes det muligvis problemer med tegnsæt og ukendte tegn i Projects.dspx filen.</span><br />
<span class="Apple-style-span" style="font-family: inherit;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: inherit;">Åbn din Projects.dspx fil og kontrollér den for danske bogstaver og andre specialtegn og fjern dem.</span><br />
<span class="Apple-style-span" style="font-family: inherit;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: inherit;">En anden mulighed - men dette har jeg dog ikke testet - er at gemme filen i utf8 Unicode format.</span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-3675441735395018286.post-74152470415645893702010-11-28T17:15:00.000+01:002010-11-28T17:15:30.708+01:00ISO ugenumre i AcTimeDim - rettelse til fejl i ugenumre<span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;">Nogle ældre versioner af metadata havde desværre fejl i ugenumrene.</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;">Det er let at rette ugenumrene til korrekte ISO ugenumre, dvs. de ugenumre, der anvendes bredt i Europa med nedenstående scripts:</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;">For at rette ugenumrene skal du gøre flg.:</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;">1.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;">Åbn SQL Server Management Studio</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;">2.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;">Åbn en query</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;">3.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;">Vælg databasen ”AcintaXXMeta” (NN er systemnavnet)</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;">4.<span style="font: normal normal normal 7pt/normal 'Times New Roman';"> </span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;">Indsæt nedenstående SQL og eksekvér det</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">create</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">function</span> dbo<span style="color: grey;">.</span>F_ISO_WEEK_OF_YEAR</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">(</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> @Date <span style="color: blue;">datetime</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">)</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">returns</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">int</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">as</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: green; font-family: 'Courier New'; font-size: 10pt;">/*</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: green; font-family: 'Courier New'; font-size: 10pt;">Function F_ISO_WEEK_OF_YEAR returns the</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: green; font-family: 'Courier New'; font-size: 10pt;">ISO 8601 week of the year for the date passed.</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: green; font-family: 'Courier New'; font-size: 10pt;">*/</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">begin</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">declare</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> @WeekOfYear <span style="color: blue;">int</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">select</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: green;">-- Compute week of year as (days since start of year/7)+1</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: green;">-- Division by 7 gives whole weeks since start of year.</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: green;">-- Adding 1 starts week number at 1, instead of zero.</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> @WeekOfYear <span style="color: grey;">=</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">(</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: magenta; font-family: 'Courier New'; font-size: 10pt;">datediff</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">(</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;">dd<span style="color: grey;">,</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">case </span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: green; font-family: 'Courier New';">-- Case finds start of year</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">when</span> NextYrStart <span style="color: grey;"><=</span> @date</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">then</span> NextYrStart</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">when</span> CurrYrStart <span style="color: grey;"><=</span> @date</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">then</span> CurrYrStart</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">else</span> PriorYrStart</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">end</span><span style="color: grey;">,</span>@date<span style="color: grey;">)/</span>7<span style="color: grey;">)+</span>1</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">from</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">(</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">select</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: green;">-- First day of first week of prior year</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> PriorYrStart <span style="color: grey;">=</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"><span style="color: magenta;"> dateadd</span><span style="color: grey;">(</span>dd, <span style="color: grey;">(</span><span style="color: magenta;">datediff</span><span style="color: grey;">(</span>dd<span style="color: grey;">,-</span>53690<span style="color: grey;">,</span><span style="color: magenta;">dateadd</span><span style="color: grey;">(</span>yy<span style="color: grey;">,-</span>1<span style="color: grey;">,</span>aa<span style="color: grey;">.</span>Jan4<span style="color: grey;">))/</span>7<span style="color: grey;">)*</span>7<span style="color: grey;">,-</span>53690<span style="color: grey;">),</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: green;">-- First day of first week of current year</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> CurrYrStart <span style="color: grey;">=</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: magenta;">dateadd</span><span style="color: grey;">(</span>dd<span style="color: grey;">,(</span><span style="color: magenta;">datediff</span><span style="color: grey;">(</span>dd<span style="color: grey;">,-</span>53690<span style="color: grey;">,</span>aa<span style="color: grey;">.</span>Jan4<span style="color: grey;">)/</span>7<span style="color: grey;">)*</span>7<span style="color: grey;">,-</span>53690<span style="color: grey;">),</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: green;">-- First day of first week of next year</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> NextYrStart <span style="color: grey;">=</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: magenta;">dateadd</span><span style="color: grey;">(</span>dd<span style="color: grey;">,(</span><span style="color: magenta;">datediff</span><span style="color: grey;">(</span>dd<span style="color: grey;">,-</span>53690<span style="color: grey;">,</span><span style="color: magenta;">dateadd</span><span style="color: grey;">(</span>yy<span style="color: grey;">,</span>1<span style="color: grey;">,</span>aa<span style="color: grey;">.</span>Jan4<span style="color: grey;">))/</span>7<span style="color: grey;">)*</span>7<span style="color: grey;">,-</span>53690<span style="color: grey;">)</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">from</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;"> </span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">(</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: blue;">select</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: green;">--Find Jan 4 for the year of the input date</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> Jan4 <span style="color: grey;">=</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"><span style="color: magenta;">dateadd</span><span style="color: grey;">(</span>dd<span style="color: grey;">,</span>3<span style="color: grey;">,</span><span style="color: magenta;">dateadd</span><span style="color: grey;">(</span>yy<span style="color: grey;">,</span><span style="color: magenta;">datediff</span><span style="color: grey;">(</span>yy<span style="color: grey;">,</span>0<span style="color: grey;">,</span>@date<span style="color: grey;">),</span>0<span style="color: grey;">))</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> </span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: grey; font-family: 'Courier New'; font-size: 10pt;">)</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> aa</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> <span style="color: grey;">)</span> a</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">return</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> @WeekOfYear</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">end</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">go</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">UPDATE</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> AcTimeDim</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="color: blue; font-family: 'Courier New'; font-size: 10pt;">SET</span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New'; font-size: 10pt;"> CalendarWeek <span style="color: grey;">=</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: blue;">CASE</span> <span style="color: blue;">WHEN </span><span style="color: grey;">(</span>dbo<span style="color: grey;">.</span>F_ISO_WEEK_OF_YEAR<span style="color: grey;">(</span>AcDate<span style="color: grey;">)</span> <span style="color: grey;">></span> 50<span style="color: grey;">)</span> <span style="color: grey;">AND</span><span style="color: grey;">(</span><span style="color: magenta;">Month</span><span style="color: grey;">(</span>AcDate<span style="color: grey;">)</span> <span style="color: grey;">=</span> 1<span style="color: grey;">)</span> <span style="color: blue;">THEN</span> <span style="color: magenta;">YEAR</span><span style="color: grey;">(</span>AcDate<span style="color: grey;">)</span> <span style="color: grey;">-</span> 1 <span style="color: blue;">ELSE</span> <span style="color: magenta;">YEAR</span><span style="color: grey;">(</span>AcDate<span style="color: grey;">)</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><br />
</span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New';"><span style="color: blue;">END</span> <span style="color: blue;">AS</span> <span style="color: blue;">CHAR</span><span style="color: grey;">(</span>4<span style="color: grey;">))</span></span></span><span class="Apple-style-span" style="-webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; color: #6f6f6f; font-family: arial, sans-serif; font-size: 13px; line-height: 22px;"><span style="font-family: 'Courier New';"> <span style="color: grey;">+</span> <span style="color: red;">'-'</span> <span style="color: grey;">+</span> <span style="color: grey;">RIGHT(</span><span style="color: red;">'0'</span> <span style="color: grey;">+</span> <span style="color: magenta;">CAST</span><span style="color: grey;">(</span>dbo<span style="color: grey;">.</span>F_ISO_WEEK_OF_YEAR<span style="color: grey;">(</span>AcDate<span style="color: grey;">)</span> <span style="color: blue;">AS</span> <span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span>2<span style="color: grey;">)),</span> 2<span style="color: grey;">)</span></span></span>Unknownnoreply@blogger.com0