Jim 2.0

SQL tutorials and learning resources

James.Pipe — Wed, 14 Nov 2007 16:26:00 GMT

Following a few requests, here are some sites you may find useful for supporting your SQL learning:

General

http://www.stickyminds.com/
http://www.thefreecountry.com/documentation/onlinesql.shtml (free online tutorials)

T-SQL

http://sqlcourse.com/ (free online tutorial)
http://www.w3schools.com/sql/default.asp (free online tutorial)
http://sqlzoo.net/
http://www.sqlservercentral.com/

PL/SQL

http://www.orafusion.com/
http://ora-00937.ora-code.com/ (Oracle error codes)

Syntax differences between T-SQL and PL/SQL

http://www.dba-oracle.com/oracle_news/2005_12_16_sql_syntax_differences.htm

If I come across any others I'll add them

James

SQL: Using the Trim function to remove white space from a field

James.Pipe — Tue, 14 Aug 2007 15:19:00 GMT

Today I was looking at a table with an apparantly empty column and discovered that the field is actually populated with blank spaces - Fortunately this field is of no interest to me and I noticed it purely by chance when I pasted an extract of the dat into excel and happened to notice the cell was unusually wide when I formatted the sheet. Anyway, it reminded me of an issue that is is often worth looking out for of trailing (or I suppose preceeding) spaces.

Fortunately this is easily solved with the trim function, which will remove white spaces from a field

SELECT  TRIM(fieldname)
,            LTRIM(fieldname)
,            RTRIM(fieldname)
,            LTRIM(RTRIM(fieldname))
FROM     tablename

Depending on the particular 'flavour' of SQL being used:
TRIM() will remove both leading and trailing white spaces;
LTRIM() removes leading white spaces (i.e. from the beginning of a string);
RTRIM() removes trailing white space (i.e. from the end of a string);
If TRIM() is not supported, then LTRIM(RTRIM()) will achieve the same result.

As I said, this is a useful way to get rid of pesky additional spaces, however just because it can be done, it does not necessarily mean it should be done, since additional functions such as this have a cost in terms of performance to the query. One temptation is to apply the function to every field you select in a statement, but this is not a good idea, instead simply bare it in mind as a useful trick for when it is necessary to remove these white spaces.

James

SQL Quick Tip: Using Top N to browse a database

James.Pipe — Thu, 09 Aug 2007 22:23:00 GMT

If you ever need to browse through a database, and dip in and out of tables to see what the data looks like, but you have very little data to go on, then you will be aware that simply running a select * against each table can be a time consuming and inefficient process. I am in this situation currently with a database that has no enforced referential integrity between it's tables that I am having to explore (foreign keys exist but aren't enforced at the database level).

In such situations, I like to use the top N command to return just a portion of the data. This gives me enough to get a feel for what the data in a table looks like, but doesn't waste time trying to return all the rows. The following will return the top N rows for T-SQL and PL/SQL respectively:

T-SQL

SELECT TOP 100 field
FROM tablename

PL/SQL

SELECT  field
FROM    tablename
WHERE  rownum <= 100

Obviously the top N rows will depend on what column the ordering is based on, and as above will run on whatever the default is. You can specify an order by clause, although this will increase the time taken to run the query.

James

Automatically format your SQL statements

James.Pipe — Thu, 09 Aug 2007 18:26:00 GMT

A colleague forwarded me this link today, which I thought would be beneficial to anyone who has to write a lot of SQL statements.

The tool will automatically format the statements to 'beautify' them, so no more excuses for 'ugly' code! Check it out here

James

Using TFS for Agile Testing

James.Pipe — Mon, 06 Aug 2007 15:19:00 GMT

Recently I was sent an email asking me for some advice on using TFS with the scrum for team systems plug-in for managing testing tasks and defect tracking on an agile project. I thought the answers might be useful to others, so I have posted them here:

Basically I want to use VSTS / TFS (with the Conchango template) to manage functional testing as well as unit testing. Our project test team will own the functional testing of the application releases both in the scope of a Sprint and the scope of System Test and User Acceptance Testing.

Generally speaking around managing specific testing, as I'm sure you already have set up, testing tasks can be entered as sprint backlog items assigned to the appropriate tester. Test cases/scripts (depending on your terminology I refer to the sql queries to be executed) can be stored in source control so a record is kept of them and version history can be tracked. Regarding unit testing, it has always been my understandin in agile that this is the developers responsibility as part of ensuring their code is ready to go to test. They can however store unit tests in source control and if desired I would suggest adding a field to the release note on the wiki to confim that developers have successfully carried out unit testing.

How do you typically use the status types available: Decomposed, Deferred, Deleted, In Progress, Not Done? I’m trying to relate these values to what I’m used to: New, Re-Open, Assigned, Ready for Re-Test, Fixed, or Closed.

You're right in that the status types are simplistic, but you can relate them to the values you are used to:

When a defect/bug is created it has a status of 'Not done' and it remains like this until work is started on it, at which time the status should be changed to 'in progress' - I suppose this is the equivalent of 'new', although this is not used because otherwise we would have to define how long is a bug new for and when does it get updated to not being new, who's job would that be etc.
Re-open does not match up, however I've never encountered a scenario where I would need this. Generally if a defect is closed off and then reoccurs a new defect would be raised, and you could link to the previous one if you wanted to maintain a record that this has happened before. If it turns out the defect was never fixed properly, you can retrieve it from the done list and change the status back. The history tab would keep a record that it had changed status back and you could leave a comment if desired.
A defect is always assigned to someone, therefore this is not a status of the defect. It is either assigned to someone 'not done' or assigned to them and 'in progress'
Ready for re-test also is defined by the owner of the item. When a developer finishes fixing a defect, the status remains 'in progress' and they assign it to an appropriate tester. Any defect therefore assigned to a tester with a status of 'in progress' can be assumed to be ready for test. If a tester has a defect with a status of 'not done', it is likely a defect which has been raised but not assigned to a developer yet. This is best practice, but of course sometimes people forget to update this status field, and comments can be added to the history tab to help keep a record.
When a defect is fixed and retesting has confirmed this, the status can be changed to 'done', at which point it is also closed. Fixed therefore also equates to closed. An item may also be closed if it is deleted (perhaps the defect has already been raised, or turns out not to have been a defect, etc). I also like to add a comment to the histroy tab to state I have retested the defect, in case the status is changed accidentally.
An item may be deferred although this shouldn't happen regularly. Situations where it might be necessary include when an area of functionality is parked therefore defects raised against it also need to be parked, so the status is set to deferred so that when development resumes these issues are not forgotten about. Alternatively, a defect may not be resolvable at the current time due to circumstances outside of the team's control.
Decompossed I must confess is new to me as I don't have it available on the version of TFS I'm using.

I usually collect details such as how to reproduce the bug, comments / responses from developers, etc. In TFS, is there a difference between “description” (owned by testers) and “comments” (shared between testers / developers)?

Regarding capturing details, the description field is designed to hold much more detailed information than the comments field and is intended to give the developer enough information (perhaps combined with some screen shots attached) to fix the defect. This field is not necessarily owned by the tester, since anyone can raise a defect and therefore enter a description in this field. Furthermore, I may sometimes not know enough detail to fully populate this field, and the defect may be temporarily assigned to a BA to make a call on something. They will then update the description before passing this on to the appropriate developer. Alternatively, it may be assigned to a devloper for investigation, who may then update this field and pass it on to another developer for resolution. The history tab is used for much smaller comments, such as 'update the config file', 'ready for testing', and 'retested and passed in test', etc.

Capturing the resolution in a defined field is very helpful for researching similar defects. Does TFS have the ability to do that?

Capturing the resolution is a good point and TFS does not provide a clear way to do this, although it can still be done if required. As above, detail about the defect and what the solution was can be added to the description field, or as a comment. In the 'area' field you can select the specific area of the project that the defect relates to, so that you can review the defect by area at a later date should anpther defect arise in the same area. Furthermore, you can link defects together, or to any sprint backlog or product backlog item, so there is always that relationship to refer to.

Is there a way to automate escalation, notifications, etc.

TFS can notify you via email anytime one of your sprint backlog items (and defects are SBIs) is changed or assigned to you: In the 'Team Explorer' pane, right click on your project and select 'Project Alerts'. Check the first box; 'My work items are changed by others'. and enter an email address to send to.

It seems like the tool is focused on completing everything within a sprint (including sprint backlog items of type “bug”) by the end of the sprint. Is there a robust way to manage / report on items that might hang around for a while (across multiple sprints or releases)?

you are absolutely correct that the tool is focussed on the sprint, as it should be, since it is intended that all issues are resolved within the sprint they are raised. This is the ideal though and it is recognised that this often is not the case, and each defect item has a sprint number so you can review by sprint the outstanding items. There probably isn't a system to manage these which is as 'robust' as you are used to, however I would argue it isn't required because other aspects of the agile methodolgy make it unnecessary to rely on a tool to do this for you.

Beyond answering these questions above I also want to make the point that most important are the principles of constant communication and participation; the daily scrum is an opportunity for big ticket issues and blocker to be raised, which can create an awareness for a subsequent meeting later in the say of required. It is also recommended to have regular defect and feedback meetings (currently we run them twice a week, although they can be daily if required), which the majority of the team should attend, during which outstanding defects are discussed along with progress on these. New defect and feedback are also discussed and a decision is made on priority, who to assign to and any clarification is requested/given. This facet of agile testing impacts all of the questions above in a number of ways

Report tracking on outstanding defects from previous sprints does not need to be as robust because defects are constantly talked about. You will quickly realise if a defect is still being discussed in the regular defect meetings after two weeks, a sprint, two sprints, etc
Escalation and notification is more efectively communicated by the formal daily scrum and by the constant communication between team members
When a bug is raised it is not done in isolation and then passed to a developer with no direct communication. There is two way communication and often collaboration on identifying, troubleshooting or pin-pointing the defect and developers are encouraged to have a conversation with the tester to understand the defect fully. The regular defect meetings also provide a forum for th

Generally tracking the number of outstanding items, the status of these items and recognising similar defects/relating these to similar defects is also supported in this non-structured way. It may sound somewat flaky in comparison to a traditional approach to testing, however when you take into account that we work sprint by sprint, it is expected that you have only the one sprint's worth of items to deal with (already acknowledged that in reality this is not exactly the case), therefore it is quite possible to maintain a bulk of this knowledge in the team's collective head. I generally know the defects I currently have open, those that are ready for retesting and the big ticket items still waiting to be resolved. For when I do need a reminder I find the reporting on TFS adequate enough to provide me the detail I need. Similarly, if a particular defect arises again, especially if it is a big one, then in my experience someone on the team will realise this and together we are able to find the previous solution.

Put most bluntly, quite a lot of the time, we don't need a formal tool to give us the information about the current state of things; we just know. This is because everyone is involved everyday in constant communication about the project.

There are a number of queries you may want to consider setting up under 'team queries' in the 'team explorer' pane. If you don't have permission to create a team query, then you can create them under 'My queries'. Currently I have set up among others the following:

All active defects (Only defects in-progress or not done)
All active defects - High/Medium/Low priority (one for each)
All defects raised today (useful for the defect meetings; I actually have this set up to show the last 3 days since our meetings are twice weekly, not daily)

It should be possible to set up queries to show this sprint's defects, defects from previous sprints, and any number of other views required. I am happy to help if you have any questions about how to do this.

James

SQL Server 2005: Passing variables into an OPENQUERY argument

James.Pipe — Thu, 28 Jun 2007 16:32:00 GMT

Today I ran into a blocker when trying to do this. Having spent a reasonable amount of time taking all of my sql statements out of the OPENQUERY argument and putting them into a string variable I then found it didn't work. The reason (from BOL) it turns out is that, and I quote; "OPENQUERY does not accept variables for its arguments."

Why this is the case I haven't been able to find out so far, however there is a workaround I have discovered with thanks to this forum.

First, let's look at the statement I started with: Here I have a simple openquery statement to select everything from the table named 'Atable' in an Oracle database named 'OracleDB' (we're assuming a linked server has already been set up), for records since June 1st 2007.

select  *
into    MyTable
from    openquery    (    OracleDB
                     ,    'select  *
                          from     Atable
                          where    DateValue >= to_date(''20070601'', ''YYYYMMDD'')'
                     )
 
What I wanted to do was make the date sent to the Oracle database a variable so I could easily change it, so I declare this variable above the query and use it in the argument.
 
declare @TheDate     datetime
set     @TheDate =   '2007-06-01T00:00:00'
 
select  *
into    MyTable
from    openquery    (    OracleDB
                     ,    'select  *
                          from     Atable
                          where    DateValue >= to_date(''' + convert(varchar(30),@TheDate,120) + ''', ''YYYY-MM-DD HH24:MI:SS'')'
                     )
 
This doesn't work however, because . . . "OPENQUERY does not accept variables for its arguments." (See above).
The next step therefore is to put the select statement to be sent to the Oracle database in a string variable and use the date variable within this string. The variable '@MyString' is then used in the OPENQUERY statement.
 
declare @TheDate     datetime
,       @MyString    varchar(max)
set     @TheDate =   '2007-06-01T00:00:00'
set     @MyString =  'select  *
                     from     Atable
                     where    DateValue >= to_date(''' + convert(varchar(30),@TheDate,120) + ''', ''YYYY-MM-DD HH24:MI:SS'')'
 
select  *
into    MyTable
from    openquery    (    OracleDB
                     ,    @MyString
                     )
 
This again does not work however, because we are still trying to use a variable as an argument in the OPENQUERY statement, so now the trickery begins!
 
Immediately after setting the string variable as the select statement, it is then set again as the entire sql statement we are trying to execute, with the previously defined string used as an argument in the new string. Note that 'N' is used preceeding the string value to convert the tring to Nvarchar to eliminate any confusion around special characters.
 
This string is then executed without error because all variables have been converted by this point. I have included a print command in order to verify the statement that is executed, which is useful for debugging.
 
declare @TheDate     datetime
,       @MyString    varchar(max)
set     @TheDate =   '2007-06-01T00:00:00'
set     @MyString =  'select  *
                     from     Atable
                     where    DateValue >= to_date(''' + convert(varchar(30),@TheDate,120) + ''', ''YYYY-MM-DD HH24:MI:SS'')'
set     @MyString =  N'select    *
                     into        MyTable
                     from        openquery    (    OracleDB
                                              ,    ''' + REPLACE(@MyString, '''', '''''') + '''
                                              )'
 
print   @MyString
 
EXEC    (@MyString)

And that's how you get more cowbell from OPENQUERY

James

Brand value on the internet - a response

James.Pipe — Wed, 16 May 2007 18:32:00 GMT

In a recent blog post Jamie raised some interesting questions for debate around the value of branding on the internet. Since his comments were suitably open in an attempt to promote discussion, there was too much to respond to in a single comment, so I have posted a reply in a new blog, but you can see Jamie's original post here.

How valuable is a brand on the internet:

I see no reason why a brand wouldn't be just as valuable, if not more so, on the internet than in the 'real world', and there are a number of reasons I see for this:

Consumers can search much more quickly and widely for products and services on the internet, so it is vital for an organisation to build up a respected brand to keep consumers coming back. Retention and return visits I would expect to be especially important for sites that generate revenue through advertisements based on site traffic.

As Jamie mentions in his post, trust is an important aspect of choosing online services and this is reflected in different ways, from "I use Google because I trust it to return the search results I am interested in" to "I buy Cds from Amazon.com because I've heard of them and know I can send them back if there's a problem. I've never heard of cheapestcdsonthenet.com (just an example), how do I know I can trust them to deliver me genuine Cds?". On the high street, a store is visible and a relatively stable entity that you can go back to to make a complaint. An online store could be set up in hours, scam lots of people out of money without delivering any products over a few weeks, then disappear. Established sites (brands) leverage their proven track record to overcome this fear in people's minds.

Convenience is just as relevant on the internet as on the high street. If consumers go to a store they know to buy something because they know they can get it there, they will do the same online. Part of the reason I use the same online services is because I don't want to spend time finding alternative providers of that service and comparing which I like best. As long as the one I'm currently using does what I need, I'm happy. As easy as it is to find and compare alternatives online, people get lazy and with so much more information and distraction available online, people will seek to minimise effort expended unnecessarily to save time.

Having said that, if someone recommends a site or service to me, and I trust there opinion, then I will likely check it out and possibly switch (normally I rely on Jamie for this) and this is exactly how brands grow.

Why is Microsoft lagging in the online brand tables?

There are many ways to measure brands, but in my opinion, the only one of real relevance is within an industry or sector. The fact that Coca Cola for example is among the world's most valuable brands has no relevance to me if I'm looking to buy a car. Ford however would have a massive bearing on my decision. As one of the leading brands in the automotive industry, I have heard of it, and as a result I will look at Fords when choosing. This can be taken a step further however, because the automotive industry is very diverse, such that if I were looking for a specific type of car, such as a family hatchback, Ford is still top of the list. If however I am looking for a 4wd, then perhaps Ford doesn't feature highly at all for me, and instead I am looking at Range Rovers (probably not on my salary)

Taking this into account, we may see why Microsoft, which is unquestionably a leading brand in the IT industry, is not necessarily as strong online. The IT industry is again diverse enough such that it can be broken up into sectors that will have different leaders. What's more, when a product or service moves from one technology to another, the market leader does not necessarily move with it if they don't successfully adopt the new technology. Take encyclopedias as an example. When they were in book form, there was an industry leader, say in the form of Encyclopedia Britannica. When this product moved into digital format, Encarta did very well to lead the market. Now that it is online (and with the advent of wikis) we are seeing wikipedia dominate the market. I'll admit encyclopedias are a slightly dubious example because Encarta and Britannica have both successfully made the transition online, but hopefully my point is clear enough and it leads me to another question:

Why don't offline brands automatically convert online?

I don't have an answer to this because I'm by no means an expert in this area, but it seems to me an important question. Why for example is Amazon a leading brand for selling books online when there were established brands on the high street such as Borders or Waterstones already? There may be many reasons, such as a slow adoption of the new technology by high street retailers; an assumption that their offline brand would guarantee them success online; resultant first mover advantage to the new player online (this explains the rise of youtube, Flickr, etc, who were not necessarily the first, but significantly early to benefit); online brand value may be based on different criteria than offline brand value; etc, etc

Finding answers to these questions is going to be key for determining the success of organisations online in the future, especially for any moving from one arena to another. This doesn't have to be high street retailers moving online, instead it could be traditional software providers seeking to offer their products via the internet, such as Microsoft in response to (or advance of) Google, or even an already established online provider. How for example will YouTube respond to a competitor that may come along utilising Silverlight?

I feel I'm beginning to ramble a bit, and if you've held out this long, you're doing well, but I'd be interested to see some further discussion on this topic, so please, feel free to comment.

James

PL/SQL Calendar table

James.Pipe — Tue, 24 Apr 2007 15:00:00 GMT

In addition to the T-SQL calendar table, which I have posted seperately, I went the whole hog and figured out the equivalent for Oracle. The only value I couldn't work out was the millisecond, which is not present on the standard date type in Oracle, but which can be retrieved and I am looking into this when I have time. This query uses the code to generate a list of dates in Oracle which was my first ever blog post on this site, yet somhow I'm still blogging about dates! Once again, I hope that it is self-explanatory, but feel free to leave a comment if you would like clarification.

select   to_date('&date','YYYYMMDD')-1 + level as DateRange
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'ddd') "DAY OF YEAR"
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'day') "DAY"
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'dy') "SHORT DAY"
,        to_char(to_date('&date','YYYYMMDD')-1 + level-1, 'd') "DAY OF WEEK"
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'dd') "DAY OF MONTH"
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'ww') "WEEK"
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'mm') "MONTH"
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'month') "LONG MONTH"
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'mon') "SHORT MONTH"
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'q') "QUARTER"
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'yyyy') "YEAR"
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'hh') "HOUR"
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'mi') "MIN"
,        to_char(to_date('&date','YYYYMMDD')-1 + level, 'ss') "SEC"
from     dual
where    (to_date('&date','YYYYMMDD')-1+level) <= trunc(sysdate)
connect  by level<=1000
;

James

T-SQL Calendar table

James.Pipe — Tue, 24 Apr 2007 14:51:00 GMT

After experimentation with various datepart and datename methods following my most recent blog post, I was able to identify many if not all the necessary components for constructing a calendar table.To do this I have combined these functions with the SQL to generate a list of dates as posted by Jamie some time ago. The resultant SQL is given below, and hopefully does not require any specific explanation, however feel free to leave me a comment if you would like clarification on any part of it.

with    mycte as
        (
        select cast('2007-04-16' as datetime) DateValue
        union all
        select DateValue + 1
        from    mycte    
        where   DateValue + 1 < '2007-04-23'
        )
 
select  DateValue
,       datepart(dy, DateValue) [day of year]
,       datename(dw, DateValue) [day]
,       datepart(dw, DateValue-1) [day of week]
,       datepart(dd, DateValue) [day of month]
,       datepart(ww, DateValue) [week]
,       datepart(mm, DateValue) [month]
,       datename(mm, DateValue) [month]
,       datepart(qq, DateValue) [quarter]
,       datepart(yy, DateValue) [year]
,       datepart(HH, DateValue) [HOUR]
,       datepart(MI, DateValue) [MIN]
,       datepart(SS, DateValue) [SEC]
,       datepart(MS, DateValue) [MILLISECOND]
from    mycte
OPTION  (MAXRECURSION 0)

James

PL/SQL and T-SQL for Week Ending Date

James.Pipe — Tue, 24 Apr 2007 14:01:00 GMT

Recently my colleague challenged me to find a method for identifying the week ending date for the week which any given date belongs to; i.e. say for a gven date which happens to be a Tuesday, what is the date of the end of the week? In this example, week ending is defined as a Sunday, however depending on how your server is set up, week ending may be considered the Saturday (or more accurately week commencing may either be a Sunday or a Monday), but we easily deal with this as you will see.

T-SQL

The two methods are actually quite different as Oracle has some handy functions to help us, which T-SQL does not, therefore this method simply involves a calculation based on knowing which day of the week a given day is, and how many days are in a week, therefore how many days until the end of the week.

select   cast(CONVERT(char(10), getdate(), 110) as datetime) [today]
,        datename(dw, getdate()) [day]
,        datepart(dw, getdate()-1) [day of week]
,        datepart(dw, getdate()-1)-1 [days since monday]
,        7-datepart(dw, getdate()-1) [days until sunday]
,        cast(CONVERT(char(10), (getdate()-(datepart(dw, getdate()-1)-1)), 110) as datetime) [monday]
,        cast(CONVERT(char(10), (getdate()+(7-datepart(dw, getdate()-1))), 110) as datetime) [sunday]

The first line simply identifies the date in question, which for this example is the current date, cast to remove the time portion, then we can use the datepart and datename functions to identify further details about this date.

After identifying the name of the day, and day of the week (note that my server is configured to take Sunday as the first day of the week, but I want it to be Monday in this scenario, therefore I subtract 1 to make Monday the first day) it is relatively straightforward to calculate how many days since Monday and until Sunday. Days since is defined as 'day of week'-1, because Monday is the first day of the week; Days until is defined as 7-'day of week', because Sunday is the seventh day of the week.

Using these two numbers we can then either subtract or add them from the given date, which is then cast as a date to given the dates of the beginning and end of the week in question.

PL/SQL

In Oracle the task is much simpler because we can make use of the next_day function to identify the date of a subsequent weekday for a given date.

select   trunc(sysdate) "Today"
,        next_day(trunc(sysdate-7), 'monday') "Monday"
,        next_day(trunc(sysdate), 'sunday') "Sunday"
from     dual
;

This makes identifying the next Sunday very easy as can be seen, and whilst for some reason there isn't a corresponding 'previous day' function, by subtracting 7 from our date and then finding the next Monday, we can find the date of the previous Monday without too much trouble.

Now that we have these methods, they can be incorporated into my previous entries for date range lookups for bothe PL/SQL and T-SQL

James

T-SQL: Common date ranges; Daily, Weekly, Monthly, Yearly, Last 12 Months

James.Pipe — Thu, 01 Mar 2007 00:34:00 GMT

As promised in my original post on PL/SQL date ranges previously, below is a sql statement which can be used preceeding most queries in order to apply common date range lookups to the data the query returns. SQL Server Management Studio (SSMS) doesn't have an equivalent to the '&' functionality that TOAD has to prompt for variables, however what it does allow you to do, which I find particularly useful, is to declare and specify all of your variables upfront.

In order to make the statement more foolproof, it is designed so the user only has to enter the date view and general date of interest and the specific start and end dates are derived. For example, if date view is 'Monthly', the user need only enter the Month and Year, e.g. December 2006. The statement however requires dates be entered in an unambiguous format to make it even more robust, so December 2006 must actually be entered as '200612'.

Copy and paste the statement below into a new query in SSMS and run it to experiment with different variables. Refer to the comments for guidance on what variables are allowed. Once again enddate is derived to take account of the current date, such as if year is the current year, etc.

    1 DECLARE  @DateView       varchar (20)
    2 ,        @theDate        varchar (20)
    3 ,        @startDate      datetime
    4 ,        @endDate        datetime
    5 
    6 SELECT   @DateView       =  'Yearly'
    7 --enter the date view required: Select from 'Daily', 'Weekly', 'Monthly', 'Yearly' and 'Last 12 Months' or 'All'
    8 ,        @thedate        =  '2007'
    9 --For Daily enter the date in the format 'YYYYMMDD' - e.g. '20060901' for September 1st 2006
   10 --For Weekly enter the week ending date in the format 'YYYYMMDD'. Note that the week ends on a Sunday - e.g. '20060910' for September 10th 2006
   11 --For Monthly enter date in the format 'YYYYMM' - e.g. '200609' for September 2006
   12 --For Yearly enter the date in the format 'YYYY' - e.g. '2006'
   13 --For Last 12 Months enter the date is not required as it is calculated up to the current date
   14 --Do not update startdate and enddate below:
   15 ,        @startDate      =  case
   16                             when @DateView = 'Daily'
   17                             then cast(@thedate as datetime)
   18                             when @DateView = 'Weekly'
   19                             then dateadd(dd,-6,cast(@thedate as datetime))
   20                             when @DateView = 'Monthly'
   21                             then cast(@thedate+'01' as datetime)
   22                             when @DateView = 'Yearly'
   23                             then cast(@thedate as datetime)
   24                             when @DateView = 'Last 12 Months'
   25                             then dateadd(mm,-11,cast(cast(datepart(yyyy,getdate()) as varchar(4))+right('0'+cast(datepart(mm,getdate()) as varchar(2)),2) + '01' as datetime))
   26                             else cast('2005-01-01' as datetime)
   27                             end
   28 ,        @endDate        =  case
   29                             when @DateView = 'Daily'
   30                             then dateadd(dd, 1, @startDate)
   31                             when @DateView = 'Weekly'
   32                             then (
   33                                  case when cast(@thedate as datetime)+1 > getdate()
   34                                  then cast(CONVERT(char(10), getdate(), 110) as datetime)
   35                                  else cast(@thedate as datetime)+1
   36                                  end
   37                                  )
   38                             when @DateView = 'Monthly'
   39                             then (
   40                                  case when dateadd(mm, 1, @startDate) > getdate()
   41                                  then cast(CONVERT(char(10), getdate(), 110) as datetime)
   42                                  else dateadd(mm, 1, @startDate)
   43                                  end
   44                                  )
   45                             when @DateView = 'Yearly'
   46                             then (
   47                                  case when dateadd(mm,12,@StartDate) > getdate()
   48                                  then cast(CONVERT(char(10), getdate(), 110) as datetime)
   49                                  else dateadd(mm,12,@StartDate)
   50                                  end
   51                                  )
   52                             when @DateView = 'Last 12 Months'
   53                             then cast(CONVERT(char(10), getdate(), 110) as datetime)
   54                             else cast(CONVERT(char(10), getdate(), 110) as datetime)
   55                             end
   56 
   57 print    @thedate
   58 print    @startdate
   59 print    @enddate

One of the big advantages of this method is that only one query is required to easily return data for any of the common date ranges, because of the use of the date view variable. To use this, simply paste it above the query desired (without the print commands), and use @startdate and @enddate in the where clauses, for example:

    1 select  *
    2 from    a_table
    3 where   a_date >= @startdate
    4 and     a_date <  @enddate

James

SQl Server 2005 - more from Rowland-Jones

James.Pipe — Tue, 20 Feb 2007 18:46:00 GMT

For those that read my query performance addendum you will know that I was posting a contribution emailed to me by my colleague James Roland-Jones. Well JRJ has now started to post his own thoughts on SQL Server 2005 and SSIS, so if you would like to get the latest straight from the horse's mouth, you can do so here.

James