tag:blogger.com,1999:blog-66404734032841253772024-03-14T04:21:37.630+11:00Thoughts in the CloudAshhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.comBlogger31125tag:blogger.com,1999:blog-6640473403284125377.post-66369927627576373892014-06-02T15:31:00.000+10:002014-06-02T15:31:00.994+10:00Exam 70-461: Querying Microsoft SQL Server 2012<span style="font-family: Arial, Helvetica, sans-serif;">Resources to help when sitting the 70-461: Querying Microsoft SQL Server 2012 Certification Exam.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">This is the overview page of the Exam. I strongly suggest going over the Skills Measured section. Ensure you have a good understanding of them. Including all the different options for Triggers, XML, Indexed Views etc.</span><br />
<a href="http://www.microsoft.com/learning/en-us/exam-70-461.aspx" target="_blank"><span style="font-family: Arial, Helvetica, sans-serif;">Exam 70-461 Querying Microsoft SQL Server 2012</span></a><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">The Training Kit book is a great reference resource. In the front it highlights the skills measured and which chapters focus on those. One word of warning, the practice test that comes with the book is far easier than the actual exam. Use it as a guide on how the questions will be asked.</span><br />
<a href="http://www.amazon.com/Training-Kit-Exam-70-461-Microsoft/dp/0735666059"><span style="font-family: Arial, Helvetica, sans-serif;">Amazon - Training-Kit-Exam-70-461-Microsoft</span></a><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Microsoft 70-461 Test Preparation - This is a playlist of 10 videos which will give you a good overview. The videos dont cover everything but its a great start. I suggest even if you are not planning on taking the Exam its a good set of videos to watch to improve your knowledge. </span><br />
<a href="http://www.youtube.com/playlist?list=PL20fh5442GK1yZ2eAOi5B6zSU3JX0fG6-"><span style="font-family: Arial, Helvetica, sans-serif;">Playlist of 10 Videos on You Tube</span></a><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">MCSA Certification Prep | Exam 461: Querying Microsoft SQL Server 2012 - This is from Microsoft to give you an overview and tips on what to study on</span><br />
<a href="http://www.youtube.com/watch?v=Kp3dS-T16x4&feature=youtube_gdata_player"><span style="font-family: Arial, Helvetica, sans-serif;">Microsoft Exam Preperation Guide</span></a><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">What's New in SQL Server 2012 (Part 2 of 13) - New Transact-SQL Enhancements - New Features of SQL 2012 which will of course be asked about during the exam</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="http://www.youtube.com/watch?v=5HBDGg-ABd8&feature=youtube_gdata_player">New Features of SQL 2012 - Querying</a></span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The Microsoft Virtual Academy has some good videos as well. </span><br />
<a href="http://www.microsoftvirtualacademy.com/training-courses/querying-microsoft-sql-server-2012-databases-jump-start">Querying Microsoft SQL Server 2012 Databases Jump Start</a><br />
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Microsoft Certification Offers - there can be some great discounts or double shot offers that you can take advantage of</span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><a href="http://www.microsoft.com/learning/en-us/offers.aspx">Microsoft Certification Offers</a></span></div>
<div>
<br /></div>
<div>
My tip would be if you are unsure of an answer rule out what is not right. I found I could easily rule out at least half of the answers by looking for obvious wrong answers. For example if it asks for columns A, B & C make sure all options give this columns, it could be something as simple as that to narrow down the options and if you still don't know at least you will have a higher chance when guessing.</div>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-64711991823724459472014-04-27T19:48:00.001+10:002014-04-27T19:48:34.607+10:00A Fan Asks Mike Rowe For Life Advice… His Response Is Truly Brilliant.
<p dir="ltr">Some great advice from Mike Rowe from Dirty Jobs.<br>
Although at the same time I do think people should aim high but they <u>shouldn't</u> cloud their ambitions with unrealistic criteria that must accompany them.</p>
<p dir="ltr">http://www.lifebuzz.com/mike-rowe/?utm_content=buffera2714&utm_medium=social&utm_source=facebook.com&utm_campaign=buffer</p>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-13753189824960065672014-03-29T18:13:00.001+11:002014-03-29T18:13:18.375+11:00A Gamification view of the future<p dir="ltr"><u>I</u> did a Gamification course on Coursera a while back and this video was referenced during it. While not the greatest topic it is a great view of what the future may hold and really what is shown doesn't seem so far fetched. Until the last seconds perhaps.</p>
<p dir="ltr">Believe in Gamification! [A Futuristic Short Film…: http://youtu.be/ziHCvpikLh8</p>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-67439320269124694862014-03-29T17:38:00.001+11:002014-03-29T17:38:20.892+11:00Microsofts in 2019<p dir="ltr">I do enjoy seeing these future vision clips. It does give inspiration and ideas for today.</p>
<p dir="ltr">Microsoft in 2019 [HQ]: http://youtu.be/RWxqSEMXWuw</p>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-73264898386919873422014-03-18T16:51:00.001+11:002014-03-18T16:52:54.313+11:00The world is one big dataset<p dir="ltr">This is a TED talk, seeing things like this really do inspire you.</p>
<p dir="ltr"><u>Dan</u> Berkenstock: The world is one big dataset. No…: http://youtu.be/7pVPmmwSeJQ</p>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-28528862942374665652014-03-09T19:05:00.001+11:002014-03-09T19:05:45.372+11:00Search SSRS Datasets in SQLThe XML for SQL Server Reporting Services Reports are stored in the ReportServer Database of the server.
Its possible to pull the XML out and then use the contents to find which reports are using which tables etc. This can be very handy if you there is a change to a schema where logic in reports may need to be updated but you dont know which reports need to be looked at.<br />
<br />
The below will place the XML into a table so you can use it to filter what you are after.<br />
<br />
<div style="border: 1px solid #ccc; font: 16px/26px Georgia, Garamond, Serif; height: 400px; overflow: auto; width: 600px;">
<pre class="brush: sql">CREATE TABLE [dbo].[ReportContents](
[CatalogItemID] [uniqueidentifier] NOT NULL,
[Type] [int] NOT NULL,
[Path] [nvarchar](425) NOT NULL,
[Name] [nvarchar](425) NOT NULL,
[ItemContent] [xml] NULL
) ON [PRIMARY]
INSERT INTO ReportContents
(CatalogItemID, [Type], [Path], [Name], ItemContent)
SELECT
ItemID
, [Type]
, [Path]
, [Name]
, cast(CONVERT(VARCHAR(MAX), CONVERT (VARBINARY(MAX), [Content])) as xml) As [ItemContent]
FROM ReportServer.dbo.Catalog
WHERE [Type] <> 1 --Folder
AND [Type] <> 3 --Image
</pre>
</div>
<br />
The below queries the table you created, adjusting the value of the @SQLToCheckFor variable will adjust which text to look for within the SQL Datasets of the SSRS XML.
Note this is set up for SQL2008, from memory its just a change of the http address from 2008 to 2005 etc.<br />
<br />
<div style="border: 1px solid #ccc; font: 16px/26px Georgia, Garamond, Serif; height: 400px; overflow: auto; width: 600px;">
<pre class="brush: sql">--Check all the reports to find a bit of SQL
Declare @SQLToCheckFor varchar(500) = 'FACTMemberBalance'--'dPlanClassification' --Enter your bit of SQL here
select MydataSets.CatalogItemID, MydataSets.ReportName , MyDataSets.DataSetName,
MydataSets.CommandText, MydataSets.ReportPath
from
(
select
CatalogItemID,
[Name] As ReportName,
[path] as ReportPath
,nref.value('@Name', 'nvarchar(255)' ) As DataSetName
,nref.query('.') As DataSetXML
,nref.value('declare namespace p1="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
(./p1:Query[1]/p1:CommandText)[1]', 'varchar(max)') As CommandText
,nref.value('declare namespace p3="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
declare namespace p1="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
declare default element namespace "http://schemas.microsoft.com/AnalysisServices/QueryDefinition";
(./p1:Query/p3:MdxQuery/QueryDefinition/QuerySpecification/From)[1]', 'varchar(max)') As Cube
from ReportContents
cross apply ItemContent.nodes('declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition";
declare namespace p1="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
//DataSet') as R(nref)
--A filter can be added here to limit what reports you are looking through
)MyDataSets
where CommandText like '%' + @SQLToCheckFor + '%'
<div>
</div>
</pre>
</div>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-20230261478518185482014-03-09T08:35:00.002+11:002014-03-09T08:35:53.061+11:00How Target Figured Out A Teen Girl Was Pregnant Before Her Father DidA favorite article of mine, was brought to my attention by a friend of mine. Its pretty impressive what Target was able to do but at the same time you have to wonder how far should organisations go to understand their customers.<br />
<br />
<a href="http://www.forbes.com/sites/kashmirhill/2012/02/16/how-target-figured-out-a-teen-girl-was-pregnant-before-her-father-did/" target="_blank">How Target Figured Out A Teen Girl Was Pregnant Before Her Father Did</a>Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-43032724509696747212014-03-08T07:20:00.001+11:002014-03-09T08:21:36.980+11:00Telstra fields 40,000 government data requests<div dir="ltr">
That seems pretty high to me. I understand there are times in emergencies etc but I would like to know a bit more about the rest.</div>
<div dir="ltr">
<br /></div>
<div dir="ltr">
<a href="http://www.computerworld.com.au/article/539974/telstra_fields_40_000_government_data_requests_six_months/" target="_blank">Telstra fields 40,000 Government data requests in six months</a></div>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-27568194301184696492014-03-04T14:09:00.000+11:002014-03-04T14:09:35.194+11:00Westpac using big data to woo customers with offers made to measure<span style="font-family: Arial, Helvetica, sans-serif;">An interesting article on how Westpac is using its data to target communications to its customers.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Nothing really new but it is a bit of a milestone a lot of Business would love to get to.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">The below comment caught my eye and made me wonder what their definition is of Big Data and why they would have more than one data warehouse. Maybe its just the initial extracts from the Web that is the "Big Data" component which is then transformed into structured data in a Warehouse?</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="background-color: white; color: #5d6063; font-family: Georgia, 'Times New Roman', Times, serif; font-size: 15px; line-height: 20.428800582885742px;">"Our data sources are growing very fast and customer interactions are growing very fast," Ms Ganschow said. "We know who you are paying. We know where you are shopping and what you are buying. There is a lot of data pouring into our data warehouses."</span><br />
<div>
<br /></div>
<div>
<br /></div>
<div>
<a href="http://www.theage.com.au/it-pro/business-it/westpac-using-big-data-to-woo-customers-with-offers-made-to-measure-20140303-hvfx5.html" target="_blank">Westpac using big data to woo customers with offers made to measure</a></div>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-23998436922861960432014-03-01T14:16:00.001+11:002014-03-01T14:16:34.860+11:00Microsoft Power BI<p dir="ltr">Interesting stuff, but like most of these tools you really need to use them first before you can make a call on them.</p>
<p dir="ltr">http://www.techrepublic.com/article/microsoft-power-bi-brings-big-data-to-the-little-guys/</p>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-21552418509436143652014-03-01T14:12:00.001+11:002014-03-01T14:12:36.423+11:00SQLCLR<p dir="ltr">I found this a useful article on SQL Server Central.<br>
SQLCLRs are a great way of integrating SQL Server with other applications and/or to create better ways of achieving your results.<br>
The Level 2 page is very in depth, I wish I had access to such information years ago when I first started playing with them.<br>
My favorite SQLCLR I created was to trigger the SSRS Web Service so we can schedule and run Reports with variables set in a table. Very handy when you need hundreds/thousands of reports scheduled to run only after certain events complete etc.</p>
<p dir="ltr">http://www.sqlservercentral.com/stairway/105855/</p>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-25525386309453621682013-10-04T14:14:00.000+10:002013-10-04T14:14:27.838+10:00All Sci-Fi Spaceships Known to ManIn theory "All Sci-Fi Spaceships Known to Man", it is missing the ship from Space Balls though. I guess it wouldnt of fit on the scale.
<br><br>
<div class='visually_embed' data-category='Technology' rel='infographic'>
<img class='visually_embed_infographic' src='http://thumbnails.visually.netdna-cdn.com/all-scifi-spaceships-known-to-man_52448ad9767ff_w587.jpg' rel='http://thumbnails.visually.netdna-cdn.com/all-scifi-spaceships-known-to-man_52448ad9767ff.jpg' alt='All Sci-Fi Spaceships Known to Man' />
<div class='visually_embed_bar'>
<span class='visually_embed_cycle'>Explore more <a href='http://visual.ly'>infographics</a> like this one on the web's largest information design community - <a href='http://visual.ly'>Visually</a>.</span>
</div>
<a id='visually_embed_view_more' target='_blank' href='http://visual.ly/all-sci-fi-spaceships-known-man?utm_source=visually_embed'></a>
<link rel='stylesheet' type='text/css' href='http://visual.ly/embeder/style.css' />
<script type='text/javascript' src='http://visual.ly/embeder/embed.js'></script>
</div>Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-62584152450803625262013-05-16T11:29:00.000+10:002013-05-16T11:29:18.707+10:00How to Survive Your Monster-In-Law<div class="visually_embed" data-category="Humor" rel="infographic">
<img alt="How to Survive Your Monster-In-Law" class="visually_embed_infographic" rel="http://thumbnails.visually.netdna-cdn.com/how-to-survive-your-monsterinlaw_502917e6acb94.png" src="http://thumbnails.visually.netdna-cdn.com/how-to-survive-your-monsterinlaw_502917e6acb94_w587.png" />
<br />
<div class="visually_embed_bar">
<span class="visually_embed_cycle"><a href="http://visual.ly/how-survive-your-monster-law/?utm_source=visually_embed">How to Survive Your Monster-In-Law infographic</a> </span>
</div>
<a href="http://visual.ly/how-survive-your-monster-law?utm_source=visually_embed" id="visually_embed_view_more" target="_blank"></a>
<link href="http://visual.ly/embeder/style.css" rel="stylesheet" type="text/css"></link>
<script src="http://visual.ly/embeder/embed.js" type="text/javascript"></script>
</div>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-75430747057801304052013-05-04T19:29:00.000+10:002013-05-04T19:34:34.694+10:00How to run your best 5kTwo reasons for this one. Firstly I'm loving Data Visualisations at the moment, they are a great way to put across a message or visualise the data. This one is not about the data but to convey the message of how to run 5 kilometers.<br />
The other reason is that I run and to actually see tips like this is very helpful. Maybe it can help me reduce the number of injuries I get.<br />
<br />
<div class='visually_embed' data-category='Sports' rel='infographic'>
<img class='visually_embed_infographic' src='http://thumbnails.visually.netdna-cdn.com/how-to-run-your-best-5k_517fd65d88ae8_w587.png' rel='http://thumbnails.visually.netdna-cdn.com/how-to-run-your-best-5k_517fd65d88ae8.png' alt='How To Run Your Best 5K' />
<div class='visually_embed_bar'>
<span class='visually_embed_cycle'><a href="http://visual.ly/how-run-your-best-5k/?utm_source=visually_embed">How To Run Your Best 5K infographic</a> <span>by </span> <a target='_blank' href='http://www.greatist.com?utm_source=visually_embed'>greatist</a>.</span>
</div>
<a id='visually_embed_view_more' target='_blank' href='http://visual.ly/how-run-your-best-5k?utm_source=visually_embed'></a>
<link rel='stylesheet' type='text/css' href='http://visual.ly/embeder/style.css' />
<script type='text/javascript' src='http://visual.ly/embeder/embed.js'></script>
</div>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-8478863872473026862013-05-04T17:43:00.001+10:002013-05-04T17:43:07.258+10:00Shooting Hoops: Making $$$I must I like this one a bit more because of my love of Basketball/sports in general, but still the amount of information put into is great.<br />
There is so much great data on Basketball I love it.<br />
If only I could work with sports data. An excuse to do what I have been doing since I was a boy as job, I think that would be my dream job.<br />
<br />
<br />
<iframe frameborder="0" height="450" marginheight="0" marginwidth="0" scrolling="no" src="http://www.visualizing.org/embedded/50224" width="620"></iframe>Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-42495547633360174272013-04-25T17:51:00.000+10:002013-04-25T17:51:18.783+10:00In 60 SecondsI'm really impressed at how well people are visualizing data theses days.<br />
Below is another great example, it makes you want to read each fact.<br />
I have to find a way to improve how I do the same, if I can get anywhere near this level I'll be very happy.<br />
<br />
<br />
<div class="visually_embed" data-category="Computers" rel="infographic">
<img alt="In 60 Seconds " class="visually_embed_infographic" rel="http://thumbnails.visually.netdna-cdn.com/in-60-seconds_50290c57990a5.jpg" src="http://thumbnails.visually.netdna-cdn.com/in-60-seconds_50290c57990a5_w587.jpg" />
<br />
<div class="visually_embed_bar">
<span class="visually_embed_cycle"><a href="http://visual.ly/60-seconds/?utm_source=visually_embed">In 60 Seconds infographic</a> </span>
</div>
<a href="http://visual.ly/60-seconds?utm_source=visually_embed" id="visually_embed_view_more" target="_blank"></a>
<link href="http://visual.ly/embeder/style.css" rel="stylesheet" type="text/css"></link>
<script src="http://visual.ly/embeder/embed.js" type="text/javascript"></script>
</div>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-82223211758364205512013-04-25T14:57:00.000+10:002013-04-25T14:57:03.285+10:00Row Counts for each table on Database from MetadataIm sure a lot of people do this already but for those of you who dont there is an easy way to find the row counts of all of your tables without having to wait for count(*) commands.<br />
<br />
SQL Server saves this information for you.<br />
<br />
Just run the below on each database you want to check OR if you want you can use the sp_foreachDB I mentioned in a previous post (<a href="http://ashcloudblog.blogspot.com.au/2013/03/run-sql-on-each-database.html" target="_blank">Run SQL On Each Database</a>) to run it on multiple databases. If I was to do that I would pump the data into a temp table each time and then return the results from it.<br />
<div style="border: 1px solid rgb(204, 204, 204); height: 400px; overflow: auto; width: 600px;">
<pre class="brush: sql"><span style="font-size: medium;"><span style="line-height: 26px;">SELECT
DB_NAME() as DatabaseName,
st.Name,
sc.Name as ''Schema'',
SUM(CASE WHEN (p.index_id &lt; 2) AND (a.type = 1) THEN p.rows
ELSE 0 END) AS Rows,
st.Modify_Date
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.tables st ON st.object_id = p.Object_ID
INNER JOIN sys.schemas sc on st.schema_id = sc.schema_id
GROUP BY st.Name, sc.Name, st.Modify_Date
ORDER BY rows desc, sc.Name, st.Name</span></span></pre>
</div>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-47055348802951810872013-04-13T16:21:00.002+10:002013-04-13T16:21:17.210+10:00Big Data analysis allows businesses and governments to mine your personal detailsA good article on what some organisations are doing in the way of analytics. Whenever you provide any information to some one or interact in a way where they know who you are even if it is just with a credit card they can track you.<br />
<br />
<a href="http://www.dailytelegraph.com.au/technology/big-data-analysis-allows-businesses-and-governments-to-mine-your-personal-details-to-a-scary-extent/story-fn7bsi21-1226619008569" target="_blank">Big Data analysis allows businesses and governments to mine your personal details</a><br />
<br />
<br />Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-17322963661466887202013-04-07T16:45:00.000+10:002013-04-07T17:47:01.542+10:00SSMS Tools PackIf you haven't already got <a href="http://www.ssmstoolspack.com/" target="_blank">SSMS Tools Pack</a> I highly recommend it.<br />
<br />
I use it a lot for the SQL Snippets which allows short cuts for example stf<ENTER> will return Select Top 100 FROM in the query window. All the Snippets are customisable and you create your own.<br />
<br />
Running Custom Scripts from the Object Explorer is great too, I have scripts I want to run at a server level so I just click on the Server in Object Explorer and then just choose the script I want to run.<br />
<br />
A lot of other really useful tools too, just makes life that little bit easier.Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-76975433685882259972013-04-07T16:15:00.002+10:002013-04-07T17:47:01.545+10:00Sourcing Data from Active Directory<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-Jm21yo91P3g/UWEQhvmhrZI/AAAAAAAAEJU/LFuZAAkg-J4/s1600/Active-Directory-logo.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="87" src="http://3.bp.blogspot.com/-Jm21yo91P3g/UWEQhvmhrZI/AAAAAAAAEJU/LFuZAAkg-J4/s320/Active-Directory-logo.png" width="320" /></a></div>
<br />
<br />
Something I've found useful in the past is being able to source user details from Active Directory.<br />
Its nice being able to link the User Names from your systems up to actual Names of the users when providing data.<br />
It is also handy when you want to find which users have certain security access. For example who has access to Database X.<br />
<br />
<div style="border: 1px solid rgb(204, 204, 204); height: 400px; overflow: auto; width: 600px;">
<pre class="brush: sql"><span style="font-size: medium;"><span style="line-height: 26px;">/*
--Check if Ad Hoc Distributed Queries is Visible/Turned on
sp_configure
--Make Visible if it isnt
sp_configure 'show advanced options', 1
reconfigure
--Turn it on
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
*/
--Return All Active Directory Users</span></span></pre>
<pre class="brush: sql"><span style="font-size: medium;"><span style="line-height: 26px;">--Replace </span></span><span style="font-size: medium; line-height: 26px;">ABC.DEF with your active directory server name</span><span style="font-size: medium;"><span style="line-height: 26px;">
</span></span><span style="font-size: medium;"><span style="line-height: 26px;">
select
*
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT Title, Department, Mail, DisplayName, Sn, GivenName, Cn
FROM ''LDAP://ABC.DEF'' where objectClass = ''User'' AND objectClass<>''computer'' '
)
--Find All Active Directory Groups</span></span></pre>
<pre class="brush: sql"><pre class="brush: sql"><span style="font-size: medium;"><span style="line-height: 26px;">--Replace </span></span><span style="font-size: medium; line-height: 26px;">ABC.DEF with your active directory server name</span></pre>
<span style="font-size: medium;"><span style="line-height: 26px;">
select
*, substring(AdsPath, charindex('CN=', adspath), 300) as GroupName
,
substring(AdsPath, 0, charindex('CN=', adspath) - 1) as Domain
FROM
OPENROWSET('ADSDSOObject', 'adsdatasource;',
'SELECT AdsPath, name FROM ''LDAP://ABC.DEF'' WHERE objectCategory=''Group'' '
)
--Find Users from X Active Directory Group</span></span></pre>
<pre class="brush: sql"><span style="font-size: medium;"><span style="line-height: 26px;">--Replace '</span></span><span style="font-size: medium; line-height: 26px;">YourADGroup' with the name of the Group you are after</span><span style="font-size: medium;"><span style="line-height: 26px;">
</span></span><span style="font-size: medium;"><span style="line-height: 26px;">
</span></span></pre>
<pre class="brush: sql"><pre class="brush: sql"><span style="font-size: medium;"><span style="line-height: 26px;">--Replace </span></span><span style="font-size: medium; line-height: 26px;">ABC.DEF with your active directory server name</span></pre>
<pre class="brush: sql"><span style="font-size: medium; line-height: 26px;">--Also again in the </span><span style="font-size: medium; line-height: 26px;">DC=ABC,DC=DEF section</span></pre>
<span style="font-size: medium;"><span style="line-height: 26px;">
select
*
FROM OPENROWSET('ADSDSOObject',
'adsdatasource;', 'SELECT Cn
FROM ''LDAP://ABC.DEF'' WHERE memberOf=''CN=YourADGroup,OU=Security,OU=Groups,DC=ABC,DC=DEF'' '
)</span></span></pre>
</div>
Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com2tag:blogger.com,1999:blog-6640473403284125377.post-61260694117639285642013-04-01T14:17:00.000+11:002013-04-01T14:17:11.091+11:00The Global State of Mobile MarketingI wonder how the surge of Android phones and the release of Windows 8 will impact these figures over the next 12 months.<br />
<br />
<img alt="The Global State of Mobile Marketing [INFOGRAPHIC] - ExactTarget Infographic" src="http://www.exacttarget.com/sites/exacttarget/files/styles/full/public/uploads/StateofGlobalMobileNEW_0.png" width="100%" />
<br />
Embedded from <a href="http://www.exacttarget.com/resource-center/digital-marketing/infographics/secret-menu-engagement-traffic" target="_blank">ExactTarget</a>Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-57508555113566584922013-03-29T12:03:00.001+11:002013-04-07T17:47:01.544+10:00Extended SoundexI think Soundex is great to be able to compare two names and find possible matches. One of the limitations though that I have found is that it will only return 4 characters which can mean it only checks the start of the name. This can be good when you are comparing two names and one has been shortened but what about longer names?<br />
<br />
Anyway so I created the below one day to help out someone when they wanted to compare names and the traditional soundex wasnt enough.<br />
<br />
It uses the same principles as Soundex but it searches through all of the name.<br />
<br />
<a href="http://en.wikipedia.org/wiki/Soundex">Wikipedia</a> has a good explanation of how the traditional Soundex works.<br />
<br />
Of course this is just a basic example, Id suggest to use it for larger pieces you place it into a User Defined Table Function perhaps Or a Stored Procedure which accepts tables - I believe you can do that now but haven't tried it myself yet.<br />
<br />
<br />
<div style="border: 1px solid #ccc; font: 16px/26px Georgia, Garamond, Serif; height: 400px; overflow: auto; width: 600px;">
<pre class="brush: sql">--Create my Name List
IF OBJECT_ID('tempdb..#NameList') IS NOT NULL
DROP TABLE #NameList
CREATE TABLE #NameList (ID INT IDENTITY, Surname varchar(120), GivenNames varchar(120), GivenNameLength int)
INSERT INTO #NameList<span style="white-space: pre-wrap;"> </span>(Surname, GivenNames, GivenNameLength)
Select 'Dummy 'Surname, 'Robert William Francis' as GivenNames, 22 as GivenNameLength
Declare @NameID as int = 1
Declare @Letter as int = 1
Declare @GivenNameLength as int
Declare @LetterToCheck as varchar(1)
Declare @SoundexValue as varchar(50)
Declare @Names as bigint = 0
DECLARE @ReturnList TABLE (Surname varchar(120), GivenNames varchar(120), SoundexValue varchar(50))
--Determine the number of Names to check
Select @Names = COUNT(*) from #NameList
while @NameID <= @Names
begin
--The first letter of the Name is put straight in, grab the length to know how many loops to perform
select @GivenNameLength = GivenNameLength, @SoundexValue = LEFT(GivenNames, 1)
from #NameList
where ID = @NameID
Select @Letter = 2
--For each letter in the Name assign a Soundex Value
while @Letter <= @GivenNameLength
begin
select @LetterToCheck = UPPER(SUBSTRING(GivenNames, @Letter, 1)) from #NameList
where ID = @NameID
--Assign Soundex Values
select @SoundexValue = @SoundexValue + case when @LetterToCheck IN ('b', 'f', 'p', 'v') then '1'
when @LetterToCheck IN ('c', 'g', 'j', 'k', 'q', 's', 'x', 'z') then '2'
when @LetterToCheck IN ('d', 't') then '3'
when @LetterToCheck IN ('l') then '4'
when @LetterToCheck IN ('m', 'n') then '5'
when @LetterToCheck IN ('r') then '6'
--when @LetterToCheck IN ('h', 'w') then 'hw'
when @LetterToCheck IN ('a', 'e', 'i', 'o', 'u') then 'v'
else '' end
Select @Letter = @Letter + 1
end
--Return a record
INSERT INTO @ReturnList
select Surname, GivenNames,
--If there are two letters with the same number next to each other return only 1
--Unless there is a vowel in which case they remain
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
Replace(@SoundexValue
, '11', '1')
, '22', '2')
, '33', '3')
, '44', '4')
, '55', '5')
, '66', '6')
, 'v', '') as NewSoundexValue
from #NameList
where ID = @NameID
select @NameID = @NameID + 1
end
SELECT * FROM @ReturnList
</pre>
</div>
<div style="background-color: white; color: #222222; font-family: arial, sans-serif; font-size: 13px;">
<br /></div>Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-18572102797189216502013-03-24T19:12:00.002+11:002013-03-24T19:12:36.090+11:00Hans Rosling Shows the best stats you've ever seenI love this TED Presentation by Hans Rosling, it makes you firstly think about the facts he is showing but then from a person who works with data about the difference between simply providing data and providing insight to get a point across.<br />
<br />
<a href="http://www.ted.com/talks/hans_rosling_shows_the_best_stats_you_ve_ever_seen.html">Hans Rosling Shows the best stats you've ever seen</a><br />
<iframe allowfullscreen="" frameborder="0" height="480" mozallowfullscreen="" scrolling="no" src="http://embed.ted.com/talks/hans_rosling_shows_the_best_stats_you_ve_ever_seen.html" webkitallowfullscreen="" width="853"></iframe>Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-12328372047374055812013-03-24T16:06:00.001+11:002013-03-24T16:07:13.282+11:00Creating a Calendar ViewI like to put commonly used scripts into Views/Stored Procedures etc. It means I dont have to re-write out the logic each time, I know that there has been a consistent use of the logic and if there is a change needed to the logic I make it once rather than many times across all of my scripts/reports etc. Yeah seems obvious to do but Ive seen a lot of developers who dont bother.<br />
<br />
Now this is view is related of course to my previous post <a href="http://ashcloudblog.blogspot.com.au/2013/03/creating-calendar-table.html">Creating A Calendar Table</a> but I find that having an independent view that you can use in Reports or reused SQL Scripts very helpful.<br />
<br />
What better way to set default date values than referring to a consistent view AND if for some reason there is a change in regards to the definition of say the last day of the Previous Month - maybe it is now the last Monday for example instead of the last actual day you can change it within the view once and all of your reports/scripts are instantly updated. No more having to go through them all updating the logic.<br />
<br />
Depending on where you are located you may want to change around a few of these values for example the Financial Years which wont apply to all.<br />
<br />
<br />
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b><br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: green; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">-- =============================================</span></b></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: green; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">-- Create View template</span></b></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: green; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">-- =============================================</span></b></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">USE </span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">ABC </span></b></b></div>
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: green; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">-- Adjust accordingly</span></div>
</b></b><br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">GO</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">IF </span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">object_id</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">N</span><span style="color: red; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">'dbo.vDateDetails'</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: red; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">'V'</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">IS</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">NOT</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">NULL</span></b></div>
<br />
<br /><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"></span><div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DROP VIEW dbo</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">.</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">vDateDetails</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">GO</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">CREATE</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">VIEW</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> dbo</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">.</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">vDateDetails </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">AS</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">SELECT </span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span></b></div>
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">AS</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [Now]</span></div>
</b><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></div>
</b><br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">d</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">d</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> Today</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">d</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">d</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">-</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> Yesterday</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">CONVERT</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 112</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">As</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> IntDate</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DAY</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">As</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [DayOfMonth]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEPART</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dayofyear</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [DayOfYear]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">Month</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> MonthOfYear</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATENAME</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">month</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">AS</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [MonthName]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [Year]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">case</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">when</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">Month</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">></span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 6 </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">then</span></div>
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"></span><div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">right(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">cast</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> as varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)),</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">+</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: red; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">'/'</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">+</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">right(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">cast</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dateadd</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">yy</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> as varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)),</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span></div>
<span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"></span><div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">else</span></div>
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"></span><div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">right(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">cast</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dateadd</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">yy</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">-</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> as varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)),</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">+</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: red; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">'/'</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">+</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">right(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">cast</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dateadd</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">yy</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> as varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)),</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">end as CurrentFinancialYear</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">case</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">when</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">Month</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">></span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 6 </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">then</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">right(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">cast</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dateadd</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">yy</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">-</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> as varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)),</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">+</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: red; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">'/'</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">+</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">right(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">cast</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dateadd</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">yy</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> as varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)),</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">else</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">right(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">cast</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dateadd</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">yy</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">-</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> as varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)),</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">+</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: red; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">'/'</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">+</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">right(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">cast</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dateadd</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">yy</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">-</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> as varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)),</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">end as PreviousFinancialYear</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">case</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">when</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">Month</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">></span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 6 </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">then</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">right(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">cast</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dateadd</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">yy</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> as varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)),</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">+</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: red; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">'/'</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">+</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">right(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">cast</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dateadd</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">yy</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> as varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)),</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">else</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">right(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">cast</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dateadd</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">yy</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> as varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)),</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">+</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: red; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">'/'</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">+</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">right(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">cast</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dateadd</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">yy</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> as varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)),</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">end as NextFinancialYear</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEPART</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">quarter</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> CalQuarter</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEPART</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">week</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> WeekOfYear</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEPART</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">weekday</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [Weekday]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATENAME</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">weekday</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">AS</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [WeekDayName]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">wk</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">wk</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> MondayOfWeek</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">s</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,-</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">mm</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">m</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">))</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> LastDayOfPreviousMonth</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">s</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,-</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">mm</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">m</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())+</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">))</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> LastDayOfCurrentMonth</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">s</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,-</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">mm</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">m</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())+</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">))</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> LastDayOfNextMonth</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">mm</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">m</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FirstDayOfCurrentMonth</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">mm</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">m</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())+</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FirstDayOfNextMonth</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">mm</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">m</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())-</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FirstDayOfPreviousMonth</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">qq</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">qq</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">()),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FirstDayOfCurrentQuarter</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">s</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,-</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">qq</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">q</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">getdate</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">())+</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">))</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> LastDayOfCurrentQuarter</span></b>Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0tag:blogger.com,1999:blog-6640473403284125377.post-47656013735473611762013-03-24T15:49:00.000+11:002013-03-24T18:43:09.733+11:00Creating a Calendar TableA Calendar table is always useful so this is a script I created a long time ago to create one.<br />
Basically I go through one day at a time and find all the variations I want for that particular day.<br />
You just need to adjust at the bottom of the query as to what you want to do with the data once you have it.<br />
<br />
Excuse the formatting I saved this one into a Google Document many moons ago and it played around with how it looks.<br />
<br />
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b><br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></b></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DECLARE </span></b></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDate </span></b></b></b></div>
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">10</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">=</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 20050101</span></div>
</b></b></b><br />
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></b><br />
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></b><br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></b></b></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DECLARE </span></b></b></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@ToDate </span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></b></b></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">10</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">=</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 20131231</span></b></b></b></div>
<br />
<br />
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<br /></div>
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"></span><div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">SET</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">NOCOUNT</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">ON</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">;</span></b></div>
<br />
<br /><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"></span><div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DECLARE</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> @CalTable </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">TABLE</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">[Date] </span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> IntDate </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">int</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [DayOfMonth] </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">int</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [DayOfYear] </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">int</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> MonthOfYear </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">int</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [MonthName] </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">20</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [Year] </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">int</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> CalQuarter </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">int</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> WeekOfYear </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">int</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">Weekday</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">int</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> WeekDayName </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">20</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> MondayOfWeek </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FortNight </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">int</span></b></div>
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></div>
</b><br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">LastDayOfPreviousMonth </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> LastDayOfCurrentMonth </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> LastDayOfNextMonth </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FirstDayOfCurrentMonth </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FirstDayOfNextMonth </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FirstDayOfPreviousMonth </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">, </span></b></div>
<b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">FirstDayOfCurrentQuarter </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> LastDayOfCurrentQuarter </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span></div>
</b><br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: green; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">--Convert from Integers to Datetime, I do wonder why I decided to start with Ints and convert to datetime but Im sure I had a good reason at the time</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DECLARE </span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DECLARE </span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@ToDateD </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"><br /></span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">SET </span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">=</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">CAST</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">CAST</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDate </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">8</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">SET </span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@ToDateD </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">=</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">CAST</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">CAST</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@ToDate </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">8</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">))</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">datetime</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: green; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">--Calculate for each day in range the set values</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"><br /></span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">WHILE </span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"><=</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> @ToDateD</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">BEGIN</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: green; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">--Insert each day into the temp table each time it goes through the loop</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">INSERT</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">INTO</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> @CalTable</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">SELECT</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">AS</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [Date]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">CONVERT</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">varchar</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> @FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 112</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">As</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> IntDate</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DAY</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">As</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [DayOfMonth]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEPART</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">dayofyear</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> @FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [DayOfYear]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">Month</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> MonthOfYear</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATENAME</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">month</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> @FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">AS</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [MonthName]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">YEAR</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [Year]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEPART</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">quarter</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> @FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> CalQuarter</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEPART</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">week</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> @FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> WeekOfYear</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEPART</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">weekday</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> @FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [Weekday]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATENAME</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">weekday</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> @FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">AS</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [WeekDayName]</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">wk</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">wk</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> MondayOfWeek</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">CASE</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">when</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEPART</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">week</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> @FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">in</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 4</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 6</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 8</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 10</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 12</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 14</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 16</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 18</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 20</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 22</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 24</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 26</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 28</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 30</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 32</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 34</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 36</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 38</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 40</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 42</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 44</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 46</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 48</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 50</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 52</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 54</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 56</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">Then</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 2 </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">else</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 1 </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">END</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">AS</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FortNight</span><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span></b></div>
<br />
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<b style="font-weight: normal;"></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"></b></b></div>
<div dir="ltr" style="display: inline !important; margin-bottom: 0pt; margin-top: 0pt;">
<b style="font-weight: normal;"><b id="internal-source-marker_0.01728002354502678" style="font-weight: normal;"><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">s</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,-</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">mm</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">m</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">))</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> LastDayOfPreviousMonth</span></b></b></div>
<span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; white-space: pre-wrap;">,</span><div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">s</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,-</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">mm</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">m</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)+</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">))</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> LastDayOfCurrentMonth<span style="color: grey;">,</span></span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">s</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,-</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">mm</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">m</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)+</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">2</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">))</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> LastDayOfNextMonth</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; white-space: pre-wrap;">,</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">mm</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">m</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FirstDayOfCurrentMonth</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; white-space: pre-wrap;">,</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">mm</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">m</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)+</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FirstDayOfNextMonth</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; white-space: pre-wrap;">,</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">mm</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">m</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)-</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FirstDayOfPreviousMonth</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; white-space: pre-wrap;">,</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">qq</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">qq</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">),</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> FirstDayOfCurrentQuarter</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; white-space: pre-wrap;">,</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">s</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,-</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">qq</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEDIFF</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">q</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)+</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">0</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">))</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">as</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> LastDayOfCurrentQuarter</span></div>
<br /><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"></span><div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: green; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">--Set the day + 1 so the loop moves on to the next day</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">SELECT </span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@FromDateD </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">=</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: magenta; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DATEADD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">(</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">DD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> 1</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">,</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> @FromDateD</span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">)</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">END</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"><br /></span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"><span style="color: green;">--INSERT INTO </span><span style="color: green;">CalendarTable </span><span style="color: green;">--If you have an existing table</span></span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">SELECT </span><span style="color: grey; font-family: Verdana; font-size: 13px; font-weight: bold; white-space: pre-wrap;">*</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: green; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">--INTO CalendarTable --If you one to create a new table</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">From</span></div>
<div dir="ltr" style="margin-bottom: 0pt; margin-top: 0pt;">
<span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">@CalTable </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">ORDER</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> </span><span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">BY</span><span style="font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;"> [Date]</span></div>
<span style="color: blue; font-family: Verdana; font-size: 13px; font-weight: bold; vertical-align: baseline; white-space: pre-wrap;">END</span>Ashhttp://www.blogger.com/profile/08567675567294308449noreply@blogger.com0