Query to Find First and Last Day of Previous, Current and Next Month - SQL Server

Hello All,

This is normal requirement to find First and Last day of Month. I got one in which I have to find not only current month or given month but Previous and Next month as well.

Following query will gives start date and end date for previous, current and next month respectively. That is First Day of Previous Month, Last Day of Previous Month, First Day of Current Month, Last Day of Current Month, First Day of Next Month and Last Day of Next Month.

DECLARE @PreviousMonthFirstDay DATETIME
DECLARE @PreviousMonthLastDay DATETIME
DECLARE @CurrentMonthFirstDay DATETIME
DECLARE @CurrentMonthLastDay DATETIME
DECLARE @NextMonthFirstDay DATETIME
DECLARE @NextMonthLastDay DATETIME

DECLARE @CurrentDate AS DATETIME = CONVERT( DATETIME, CONVERT( DATE, GetDate() ) )

SELECT @CurrentMonthFirstDay = DATEADD(dd, -(DAY(@CurrentDAte)) + 1, @CurrentDate)
SELECT @CurrentMonthLastDay = DATEADD(d, -1, DATEADD(mm, 1, @CurrentMonthFirstDay))

SELECT @PreviousMonthFirstDay = DATEADD(m, -1, @CurrentMonthFirstDay)
SELECT @PreviousMonthLastDay = DATEADD(d, -1, @CurrentMonthFirstDay)

SELECT @NextMonthFirstDay = DATEADD(d, 1, @CurrentMonthLastDay)
SELECT @NextMonthLastDay = DATEADD(d, -1, DATEADD(mm, 1, @NextMonthFirstDay))

SELECT 
     @PreviousMonthFirstDay PreviousMonthFirstDay
    ,@PreviousMonthLastDay PreviousMonthLastDay
    ,@CurrentMonthFirstDay CurrentMonthFirstDay
    ,@CurrentMonthLastDay CurrentMonthLastDay
    ,@NextMonthFirstDay NextMonthFirstDay
    ,@NextMonthLastDay NextMonthLastDay 

Output as of 12-Dec-2012
You can make this more generic by replacing GetDate() with perticular date while setting up value of @CurrentDate

Operation is not valid due to the current state of the object

Hello All,

While working with web application, there may be case we got ‘Operation is not valid due to the current state of the object error’ or ‘The URL-encoded form data is not valid’ while doing postback

There may be other reason but one of the reason is form collection reaches its limit of maximum number of keys that it can hold a time. Means you have huge amount of data and you are trying to postback that data to server which end up with this error

‘Operation is not valid due to the current state of the object error’ or ‘The URL-encoded form data is not valid’

Microsoft has added this as security for Form can have 1000 items at a time, however thanks Microsoft also provides way to change this

To change this add following setting into your configuration file

<appSettings>
  <add key="aspnet:MaxHttpCollectionKeys" value="10000" />    
</appSettings>

In case you have issue with Jason data then you have to add following settings

<appSettings>
  <add key="aspnet:MaxJsonDeserializerMembers" value="10000" /></appSettings>

Hope this helps

Enumeration Types as Bit Flags

An enum is best practice to give internal constants to readable string format, example

It’s very handy to use string rather to remember int value and it also helps to maintain code if down the road we need to change integer value it wont be difficult. I am sure developer knows very well about how to use enum in their code.

The post is all about answering few question comes while development or requirement, How you pass multiple value by using enum? or How to assign multiple enum values to variable? or How can I store combination of enum values using single enum variable?

All question have single answer, to use enum as bit flags. In order to achieve this, you have to add new attribute Flags to your enum declaration. As these values are bit you can use bitwise operation which are AND, OR, XOR and NOT.

Let’s see useful example of Flag enum using normal coding and then we will see same thing using Flags.

In normal coding if I want to calculate discount for four country and more then one at same time, I rather create for methods and call four individual functions

OR I can add bool into signature for calculating particular discount.

Drawback of first method is you have to keep adding new functions if you introduce new country and in second you have to pass false to country which you don’t want to do calculation, so signature will get increase in case we have more countries

Now lets use Flag enumeration to avoid mention drawbacks.

This will how your new enum will look like

A new attribute called Flags is added and we have assigned constants in power or TWO, that is 1,2,4,8 and so on, so when we use in combination no one get overlap.

Lets go ahead and create CalculateDiscount function and use into our program.

So single function CalculateDiscount is responsible to calculate discount for four different country at a time. If I want to calculate discount for German and Canada, you can do that by passing both in single argument using ‘|’ operator, its bitwise OR operator. You can use ‘&’ bitwise AND operator to determine whether a specific flag is set or not.

Schema and Data Compare Scripts in MS SQL

Hello All,

I was searching for a tool which gives me the Schema and or data comparison for SQL Server, its really needed while we are updating staging or updating our live site. There may be lots of tool available on net but I found one tool which is part of Microsoft® Visual Studio Team System 2008 Database Edition GDR R2.

Team Edition provides lots of functionality, mainly we are using to write Test Case which is very useful for Test Driven Development.

On top of this if you have Database edition then you can do lots of things with it. One of the feature is Schema and Data Compare. This blog post will walk you thru steps to compare data and schema.

Schema Comparison

1. Go to New Schema Comparison form Data menu

2. Next select the two database, one is source and other is target

 

3. When press OK will give you the Schema Compare of all the objects including Tables, Views, Stored Procedures and many more.

Here you can see the list of Objects and the Object Definitions windows

4. In following image Table is expanded so you can see the changes in details on what table what action is held.

 

5. To see more in details select one of the updated row and you can see the definition in Object Definitions window; it will generate Create script and highlight the difference. There is also Schema Update Script which will have alter script of all the changes that is detected during Schema Compare.

6. You can either copy the script or export to file or editor by using Export To Edition command on tool box.

This was all about how to detect schema change, its very handy with lots of options.

Data Comparison

Now same way we have Data Comparison, which is use to compare data, it provides tons of details and script for insert/update and delete. The steps are pretty much starlight forward. I have listed the steps here.

1. Click on New Data Comparison from Data menu

2. It will ask for Source and Target tables

3. On click of Finish, it will provide you all the information that is differ in between two database tables

You can see here it shows 2 rows are only in source, means two rows are added to SB_mst_Group table. It also provides the what exact rows are added in middle section along with other options.

In third last section which is Data Update Script; provides you the script for the action, here we have addition, so its provide the insert statements which we can run in UAT to get the date.

I found it very useful and easy to generate required script in few mouse click!

Create your own shortcut in SQL Management Studio 2008

Hi All,

Shortcut is good way to increase the speed of development and work. SQL Server Management Studio 2008 have numerous shortcuts.

We are writing various SQL statement, few statements are common like, SELECT * FROM [TABLE_NAME] which we are using very frequent. its always tedious job to write SELECT * FROM statement, can’t we create shortcut for that? Yes we can. Here is the steps to create your own shortcut with screenshot may be it helps you to increase productivity.

Step 1: Find the Keyboard setting [Tools – Options – Keyboard]

Step 2: Pick the available Shortcut [Query Shortcuts]

Here you can see the default query shortcuts. Find the available and create your own.

Lets create one shortcut for SELECT Statement

 

Press OK, you are done with your first shortcut. Before we use the shortcut and it get applies we need to restart IDE so our shortcut will get effected.

After restarting IDE, in Query write the table name, select it and apply your shortcut [Ctrl+f1].

You can see it works like SELECT * FROM EmpMaster. Now let’s apply WHERE clause and see if its works or not?

It’s working perfectly. Use shortcuts be more productive.

Get query string value using JavaScript

Here is the JavaScript function getQuerystring which finds the key form query string and returns the value.

/*
* <summary>
* Get the querystring value
* </summary>
* <param name="key">A string contains the querystring key</param>
* <param name="defaultVal">Object which get returns when there is not key</param>
**/
function getQuerystring(key, defaultVal) {
    if (defaultVal == null) {
        defaultVal = "";
    }
    key = key.replace(/[\[]/, "\\\[").replace(/[\]]/, "\\\]");
    var regex = new RegExp("[\\?&]" + key + "=([^&#]*)");
    var qs = regex.exec(window.location.href);
    if (qs == null) {
        return defaultVal;
    }
    else {
        return qs[1];
    } 
}

Long word breaks the UI

Hi All,

Working with long word into Web application some times break the UI. There always be a question for How to break long words?? As a long word without space kills your user interface.

I found the reason, in general when you system is tested by QA first thing he is going to break if you don’t limit the size and then it try to break UI with adding a loooooong space less word :)

There can be lots of other solutions, like create function which finds the long word and break it out. But I like is Regx, yes the regular expression is very optimum, but you have to learn them which is not like eating piece of cake :)

Following is the Regx which validate the text box and allow user to add 35 character long word although it seems to be not possible :)

<asp:RegularExpressionValidator ID="regNoLongWords" runat="server" ControlToValidate="txtText"
    ErrorMessage="Word is too long" Display="None" 
    ValidationExpression="(?!.*?\S{36,}).*"></asp:RegularExpressionValidator>

Have fun with Regx !!!

Best way to hiding Telerik Grid Column

Hi All, I was just looking into list of function while applying some logic for show/hide column of Telerik it provides two methods to do so.

public GridColumn FindByUniqueName(string UniqueName);
public GridColumn FindByUniqueNameSafe(string UniqueName);

As name suggest first method is not safe, it can throw GridExceptions if no column is found, where second one is returning null if no column is found. Along with these tow method, it have one property name UniqueName; this property helps FindByUniqueName and FindByUniqueNameSafe to find the column. So now we don’t need to remember the column id also we can now easily change the sequence of column as they are not referring with unique name.

<telerik:GridTemplateColumn UniqueName="Clients" HeaderText="Clients">

And server side write following code.

GridColumn gridColumn = dgTest.Columns.FindByUniqueNameSafe("Clients");
if (gridColumn != null)
    gridColumn.Visible = false;

Using let in LINQ to Objects – Performance killer if used wrong way

C# 3.0 LINQ has one more hidden and powerful feature; which provides you to store result of sub-expression in order to use subsequence clause. You can achieve this with the help of let keyword. The variable created using let is readonly; once initialized it can’t use to store another value only good this is it can be queried.

Let’s see this with example. We have employees details in text file delimited my ‘:’, also each employee have it’s own details separated by ‘,’.

string strEmployees = "1, John, Methew:2, Nick, Althoff:3, David, Oliver:4, Sam, Peterson";

Lets write query to grab all the employee details.

string strEmployees = "1, John, Methew:2, Nick, Althoff:3, David, Oliver:4, Sam, Peterson";

//Split with :
var query = from empData in strEmployees.Split(':')
            select empData;

//Split with ,
foreach (var q in query)
{
    var e = q.Split(',');
    Console.WriteLine("Id - {0}, First Name - {1}, Last Name - {2}",
        e[0], e[1], e[2]);
}

As you can see we have to write two different logic to split one more time employee details, now let’s use let keyword and make coding easy.

string strEmployees = "1, John, Methew:2, Nick, Althoff:3, David, Oliver:4, Sam, Peterson";

var query = from empData in strEmployees.Split(':')
            let emp = empData.Split(',')
            select new { Id = emp[0], FName = emp[1], LName = emp[2] };

foreach (var q in query)
{
    Console.WriteLine("Id - {0}, First Name - {1}, Last Name - {2}",
        q.Id, q.FName, q.LName);
}

In both query output will be same.



emp is intermediate enumerable type which we are using in next line! This is very simple example, now what compiler treats the code above? Compiler will create one sub-query that returns the anonymous type composed of the original value along with new value specified by the let.

As its creating sub-query if you write bunch of let statements, it will kill your performance. If its implemented in proper way let is very good option to go with, the scenario where you need some function which operates on your select clause more then 2-3 times, you can create let variable and use them into your select which makes your faster.

static void SummOfferNoLet()
{
    var q = from c in Products
            where SumOffers(c) < 10000 && SumOffers(c) > 1000
            select c;
    int count = q.Count();
}
static void SummOfferWithLet()
{
    var q = from c in Products
            let offerValue = SumOffers(c)
            where offerValue < 10000 && offerValue > 100
            select c;
    int count = q.Count();
}

In this case SummOfferWithLet will faster as you can see SummOfferNoLet we need to call SumOffers twice.

Conclusion: Using let is powerful but if you used wrong way then it will kill the performance.

Issues with Web.config in IIS 7 and Modules (in Vista)

A simple web application runs fine on internal web server, but the time we put it on IIS7 specially in Windows Vista, its start giving configuration error, first and foremost is issue with Modules.

This configuration section cannot be used at this path. This happens when the section is locked at a parent level. Locking is either by default (overrideModeDefault="Deny"), or set explicitly by a location tag with overrideMode="Deny" or the legacy allowOverride="false".

And its points to <handlers> section under <system.webServer> section. The issue related to IIS don’t have ASP.NET installed. You can check your Windows feature, although you have installed IIS7 and compatibility for IIS6, ASP.NET is not getting installed automatically. Here is the Windows feature should look like as in following image.

You can find this window from Control Panel –> Programs and Features –> Turn Windows features on or off [Left panel]

If you have ASP.NET installed on your IIS 7.0 then you have to change the configuration from applicationHost.config file, which resides in %windir%\system32\inetsrv\config\applicationHost.config. You can find the entry for handlers and it has value Deny for property overrideModeDefault, change it to Allow.

<section name="handlers" overrideModeDefault="Deny" /> to <section name="handlers" overrideModeDefault="Allow" />

While saving file I am pretty much sure that it asks for Administrator account although your role is Administrators as you are not owner of that file you can’t make change to file, so solution for this is login as Administrator and do the changes. For vista Administrator is not active for login so find my post which help you to login as Administrator.

How to login as Administrator in Windows Vista

Hi All,

In working with Visa, you always get Alert saying “You don’t have permission to access this folder, click continue to get access”, or “Windows needs your permission to continue” or “Destination Folder Access Denied” or due to security you are not able to save file or change who owned by System…. Lots more. Although you have Administrative privileges still its says sometime “You should have administrative permission”. Or if a program needs Admin permission then you can run that application using “Run as Administrator”!!!!

So we need to do login using Administrator account, but the question is from where?

In Vista, the Administrator (or an administrator) is no longer the most trusted object in the operating system. Yes this is to "ostensibly" protect the system, and is part of a general concept of protecting the integrity of the system. The Administration is not activated and you don’t find any User Interface [Up to now, I didn’t fine] which make it active!!! Does it means you can’t make it Active? NO.

To make it active, you have to run command prompt with Administrator permission. Go to Start –> All Programs –> Accessories –> Command Prompt [right click and RUN AS ADMINISTRATOR]

Write following commands to make Administrator active and set the password.

 

First command will make Administrator account active and another will set yourpassword as Administration account password. Last command Exit.

Now, reboot or switch user or logoff from current user; you can see it will ask for Administrator account password.

USE AT YOUR OWN RISK.

Enum in JavaScript

We are using enum in server side scripting language, recently I come across requirement where I have to write lots of if-else if clause, then I found the interesting thing which is enum.

Lets see how we can declare enum.

var Technology = 
{ 
    Microsoft: 0,
    PHP: 1,
    ROR: 2,
    Java: 3
}

Minor change in declaration, now lets see how we can use enum.

function Show(tech) {

    var msg = 'Welcome to the world of {0}';
    switch (Number(tech)) {
        case Technology.Microsoft:
            alert(String.format(msg, 'Microsoft'));
            break;
        case Technology.PHP:
            alert( String.format(msg, 'PHP') );
            break;
        case Technology.ROR:
            alert(String.format(msg, 'ROR') );
            break;
    }

}


//And here is the function call
Show(0);
Show(Technology.PHP);
Show(Technology.Microsoft);
Show(2);

Sequence contains no element - LINQ

Recently using Aggregate function in LINQ I come across sequence contains no element error. After checking I found the list on which I am calling Aggregate function does not have any element!!!

Problem:
ERROR : Sequence contains no element, while using Aggregate function in LINQ

Lets see with example.

I have created AssignMe function which returns the collection of string, here is the function body.

private static string[] AssignMe()
{
    return new string[] { };
}

For generating error AssignMe is returning nothing, now lets apply Aggregate function on this

string[] sList;
sList = AssignMe();
Console.Write(sList.Aggregate((first, second) => first + second));

This will throw InvalidOperationException => "Sequence contains no elements".

Lets change the AssignMe function little bit.

private static string[] AssignMe()
{
    return new string[] { "NoException" };
}

This will work perfectly, and gives you output as NoException, don't get confused with NoException, Its returning result.

Solution:

We should check the item Count before applying Aggregate function, as we seen it's working well for single element. Lets put back our original AssignMe function and change the calling method.

string[] sList;
sList = AssignMe();

if (sList.Count() > 0)
    Console.Write(sList.Aggregate((first, second) => first + second));

Working fine without an error :)

Conclusion:
While using Aggregate function or any other function like lamda expression or delegate; which accept argument then its better to check the Count of collection before applying such function.

File Upload not working with update panel

I was having problem while using File upload control which is inside the update panel and which was placed in user control. While doing post-back the value of PostedFile was blank, although page was properly valid.

Then I checked the Form property of Request object, the file upload control was missing in the AllKeys collection

We all know that if you want file uploads the form encode must be multipart/form-data. This is the reason why File Upload is not working properly inside Update Panel, lets understand this with following example.

Problem:

File upload is not working with update panel + asp.net

You are having two controls which needs to get display after one-another or post-back, first control does not have File upload and Second control does. Things to notice here is form tag will be reside either in ASPX page or Master page not in UserControl; and our File Upload is inside user control. Now when we load first control which does not have File Upload so the form will not encoded with multipart/form-data, now when you load second control which has the File Upload hence fort encode must be multipart/form-data, which is unfortunately not changing due to Update Panel.

Solution:

In problem definition we come to know that form must be encoded with multipart/form-data if we want to upload the file; solution is very simple, we just need to change encoded attribute of form with following single line!!!

Page.Form.Attributes.Add("enctype", "multipart/form-data");

String Concatenation in SQL Group By clause

I got one problem in Ahmedabad SQLServer UserGroup.

The problem was something like... there are grouped data and requirement was making sum of string collection, means concatenation of string which are in the same group. Here is the question along with solutionSource Table

Field1     Field2
---------- ----------
Group 1    Member 1
Group 1    Member 2
Group 1    Member 3
Group 2    Member 1
Group 2    Member 2
Group 3    Member 1
Group 3    Member 2

Need output as

Field1     Field2
---------- ----------------------------
Group 1    Member 1,Member 2,Member 3
Group 2    Member 1,Member 2
Group 3    Member 1,Member 2

Solution:

SET NOCOUNT ON

--Creating Tables
DECLARE @Fields AS TABLE
(
    Field1 VARCHAR(10),
    Field2 VARCHAR(10)
)

--Inserting some values
INSERT INTO @Fields VALUES ('Group 1','Member 1')
INSERT INTO @Fields VALUES ('Group 1','Member 2')
INSERT INTO @Fields VALUES ('Group 1','Member 3')
INSERT INTO @Fields VALUES ('Group 2','Member 1')
INSERT INTO @Fields VALUES ('Group 2','Member 2')
INSERT INTO @Fields VALUES ('Group 3','Member 1')
INSERT INTO @Fields VALUES ('Group 3','Member 2')

--T-Sql
SELECT Field1, Substring(Field2, 2, LEN(Field2)) AS Field2 FROM
(
    SELECT
        [InnerData].Field1,
        (SELECT  ',' + Field2 FROM @Fields WHERE Field1=[InnerData].Field1 FOR XML PATH('')) AS Field2
        FROM
        (
            SELECT DISTINCT Field1 FROM @Fields
        ) AS [InnerData]
) AS OuterData

Display multiple comma separated value into single column output Part II

Here is the data:

Field1      Field2
----------- --------------------
1           A,B,C
2           A
3           D,G

And we need output as following

Field1               ID
-------------------- ----
1                    A
1                    B
1                    C
2                    A
3                    D
3                    G

Lets create data first.

DECLARE @Fields AS TABLE
(
    Field1 INT,
    Field2 VARCHAR(20)
)

INSERT INTO @Fields VALUES (1,'A,B,C')
INSERT INTO @Fields VALUES (2,'A')
INSERT INTO @Fields VALUES (3,'D,G')

Here is the query for getting expected result.

SET @AnswerXML =
    REPLACE(REPLACE((SELECT
                        Field1, '<Ids><Id value="' + REPLACE(Field2, ',','" /><Id value="') + '" /></Ids>'
                    FROM @Fields
                    FOR XML PATH ('F')
                ), '&lt;', '<'), '&gt;', '>')

SELECT
     Answer.value('Field1[1]', 'BIGINT') as Field1
    ,y.value('@value[1]', 'VARCHAR(1)') AS ID
FROM
    @AnswerXML.nodes('/F') p(Answer)
OUTER APPLY Answer.nodes('Ids/Id') o(y)

Also check Display multiple comma separated value into single column output Part I

Creating comma separated string from Array - LINQ

Its common requirement to create comma separated string from Array. Using extension methods of LINQ we can do it in single line. Lets create Array first from comma separated values, its simple as we just need to split them using Split function.

string strTest = "1,2,4,6";
string[] Nums = strTest.Split(',');

let's create comma separated string from string Array Nums,

Generally people use foreach loop to create the comma separated string,

But here is the single line code using Lambda Expression to create the same thing.

Console.Write(Nums.Aggregate<string>((first, second) => first + "," + second));
//OUTPUT:
//1,2,4,6

Here the Lambda expression which is accepting two argument and returning the string having concatenation along with delimiter.

RadGridNamespace is undefined

I got error RadGridNamespace is undefined while working with RadGrid and Themes all to gather.

Problem was, It was not able to find the scripts and related files form Themes folder. The default path for Script and Theme for Rad controls are fixed and which is under /RadControls. It goes to that directory but its obvious and convention says that if we are using Themes and Skins we put all the scripts, images and skins in App_Themes folder.

So we need to override the default Rad control default path property RadControlsDir in OnInit method

protected override void OnInit(EventArgs e)
{
    rgTest.RadControlsDir = string.Format("~/App_Themes/{0}/RadControls/", this.Page.Theme);
}

Note: If you are having multiple RadGrid on same page, then you have to set property RadControlsDir for all the grids in OnInit method.

Display multiple comma separated value into single column output

Here is the data:

Field1      Field2
----------- --------------------
1           A,B,C
2           A
3           D,G

And we need output as following

output
------
A
B
C
A
D
G

Lets create data first.

DECLARE @Fields AS TABLE
(
    Field1 INT,
    Field2 VARCHAR(20)
)

INSERT INTO @Fields VALUES (1,'A,B,C')
INSERT INTO @Fields VALUES (2,'A')
INSERT INTO @Fields VALUES (3,'D,G')

Here is the query for getting expected result.

DECLARE @FieldXml AS XML

DECLARE @v VARCHAR(MAX)

--Creates single row for Field2
SELECT @v = (SELECT ',' + Field2 FROM @Fields FOR XML PATH(''))
--Remove the first comma 
SELECT @v = SUBSTRING(@v, 2, LEN(@v))

--Add the XML tag
SELECT @FieldXml = '<F value="' + REPLACE(@v , ',', '" /><F value="') + '" />'

--List single attribute value
SELECT x.value('@value', 'VARCHAR(1)') AS [output]
FROM @FieldXml.nodes('/F') p(x)

Getting value form pervious page into new window

I saw lots of question posted on http://forums.asp.net/ about how to get the value from pervious page into newly opened window. Its very simple, you have few ways to pass variables to newly opened window. First and foremost is passing variable into query string.

You can get all full form collection; not only this you can also read the value of global variable of parent page in to newly opened window. Let's see how.

I created one variable into parent page. And somewhere I call window.open() to open new page into new window.

<script language="javascript" type="text/javascript">
    var gblPageTitle;
    gblPageTitle = 'Welcome to ';
</script>

In parent page I just have to write following line to get that variable.

<script type="text/javascript">
    document.title = 'Questions on '+ window.opener.gblPageTitle;
</script>

And now lets get the full document of parent page or specifically form collection.

<script type="text/javascript">
    var previousForm = window.opener.document.forms;
</script>

Have fun with window.opener :)