Hello Swati

Hi This is Swati

• Writing a test blog post from MS Word
• Kaise ho

SQL Interview Questions - Time Series Based Problems

I was just wondering about time-series based SQL problems, and decided to put up a data sample based on the tea & chocolate consumption of Microsoft & Sun Microsystems. Let's take the sample data below.

SALESORDERID	ITEM_NAME	QUANTITY	COST_PER_UNIT	ORDERDATE	DUEDATE	ORDERSTATUS	CUSTOMERNAME
1	TEA	45.0000	1.2900	2/29/2008	3/31/2008	P	MICROSOFT
2	TEA	30.0000	1.2500	4/30/2008	7/31/2008	P	MICROSOFT
3	TEA	90.0000	1.5000	5/31/2008	7/31/2008	P	MICROSOFT
4	TEA	12.0000	2.0000	6/30/2008	8/31/2008	P	MICROSOFT
5	TEA	34.0000	4.3800	2/29/2008	4/30/2008	P	SUN
6	TEA	45.0000	3.9900	3/31/2008	5/31/2008	P	SUN
7	TEA	79.0000	3.5000	4/30/2008	7/31/2008	P	SUN
8	TEA	36.0000	5.3800	6/30/2008	7/31/2008	P	SUN
9	TEA	12.0000	4.3800	7/31/2008	7/31/2008	P	SUN
10	TEA	50.0000	4.2200	8/30/2008	9/30/2008	P	SUN
11	CHOCOLATE	110.0000	0.6900	3/31/2008	4/30/2008	P	SUN
12	CHOCOLATE	130.0000	1.2200	4/30/2008	5/31/2008	P	SUN
13	CHOCOLATE	76.0000	0.5500	5/31/2008	6/30/2008	P	SUN
14	CHOCOLATE	200.0000	0.8900	6/30/2008	7/31/2008	P	SUN
15	CHOCOLATE	230.0000	0.2300	7/31/2008	8/31/2008	P	SUN
16	CHOCOLATE	210.0000	0.7800	8/31/2008	9/30/2008	P	SUN
17	CHOCOLATE	199.0000	1.1000	9/30/2008	10/31/2008	P	SUN
18	CHOCOLATE	102.0000	2.0000	2/29/2008	3/31/2008	P	MICROSOFT
19	CHOCOLATE	245.0000	2.0000	3/31/2008	4/30/2008	P	MICROSOFT
20	CHOCOLATE	312.0000	2.0000	4/30/2008	5/31/2008	P	MICROSOFT
21	CHOCOLATE	345.0000	2.0000	5/31/2008	6/30/2008	P	MICROSOFT
22	CHOCOLATE	307.0000	2.0000	6/30/2008	7/31/2008	P	MICROSOFT
23	CHOCOLATE	120.0000	2.0000	7/15/2008	7/31/2008	P	MICROSOFT
24	CHOCOLATE	300.0000	2.0000	7/31/2008	8/31/2008	P	MICROSOFT
25	CHOCOLATE	299.0000	2.0000	8/31/2008	9/30/2008	P	MICROSOFT
26	CHOCOLATE	290.0000	2.0000	9/30/2008	10/31/2008	P	MICROSOFT
27	CHOCOLATE	10.0000	2.0000	10/5/2008	10/31/2008	P	MICROSOFT
28	CHOCOLATE	12.0000	2.0000	10/15/2008	10/31/2008	P	MICROSOFT
29	CHOCOLATE	12.0000	2.0000	10/20/2008	10/31/2008	P	MICROSOFT

 

--Write a query to get the TOTAL AMOUNT SPENT on tea consumption by each company between 03/31/2008 and 08/31/2008

SELECT CUSTOMERNAME, SUM(QUANTITY*COST_PER_UNIT) FROM #TMP_SALES

WHERE ORDERDATE BETWEEN '03/31/2008' AND '08/31/2008'

GROUP BY CUSTOMERNAME

 

--QUERY TO FIND AVERAGE NUMBER OF DAYS EACH CUSTOMER GIVES FOR AN ORDER

SELECT CUSTOMERNAME, AVG(DATEDIFF(D, ORDERDATE, DUEDATE))

FROM #TMP_SALES

GROUP BY CUSTOMERNAME

 

--QUERY TO FIND THE MONTHS WHEN EACH CUSTOMER DID NOT PLACE

--ANY ORDERS

SELECT * FROM

#TMP_SALES WHERE SALESORDERID NOT IN

(

      SELECT T_OUTER.SALESORDERID

      FROM #TMP_SALES T_OUTER

      INNER JOIN #TMP_SALES T_INNER

      ON T_OUTER.CUSTOMERNAME = T_INNER.CUSTOMERNAME

      AND T_OUTER.ITEM_NAME = T_INNER.ITEM_NAME

      AND MONTH(T_OUTER.ORDERDATE) = MONTH(DATEADD(M, -1, T_INNER.ORDERDATE))

)

 

Email Attachments using C#

How to automatically generate e-mails with attachments from your C# Code ?

Now-a-days everyone wants Emails to be generated from the applciaiton itself. in my previous blog I have already discussed how to generate simple text e-mails.

But if you want to generate e-mails with attachments , still its not difficult at all. I have done it for sending an Excel Attachment. The txt attachment again is simpler, you just need to follow the same logic as excel but less complicated to copy and send. So, Lets Start.

PART I 

The code below shows the following steps for copying the excel sheet to be posted into the mailing attachment sheet(If you already have the sheet and just need to mail it, you can skip this part):-
1. Create a new excel workbook.
2. Create a new Directory.
3. Copy the old sheet to the new workbook sheet
4. You can turn on the displays on/off for the sheet by just doing
          application.displayAlerts = false/true;
5. Save the new workbook with the name required and close the excel.For saving
          you can use
            newWorkbook.saveAs(Save Name,System.Type.Missing,
            System.Type.Missing,System.Type.Missing,false, false,
            XlSaveAsAccessMode.xlNoChange,System.Type.Missing,
            System.Type.Missing,System.Type.Missing,
            System.Type.Missing,System.Type.Missing);
          and for closing you can use
            newWorkbook.Close(false,false,System.Type.Missing);

PART II 

Now, the steps below shows the e-mailing of the excel sheet as an attachment.
1. Make an object of MailMessage.
2. Set the parameters ,
          TO(toAddress); FROM(fromAddress); SUBJECT(a string one liner);     
          BODY(message to write inside the main body of mail);
3. Set the MailAttachment object with the filename to send an simply add the
           Attachment to the mail message.
4. SmtpMail.SmptServer needs to be set. It is the address of your office mail
          server.And then finally the message needs to be send, SmtpMail.Send();

private void SendEmail()
{

    Excel.Workbook newWorkbook = this.thisApplication.Workbooks.Add
                                 (XlWBATemplate.xlWBATWorksheet);

    string windowsLogin = Environment.UserName.ToString();

    object m = System.Reflection.Missing.Value;

    string fMsg = windowsLogin + @"@gmail.com";

    string note = "";

    try
    {

         Directory.CreateDirectory("ForEmail");

         Directory.SetCurrentDirectory("ForEmail");

         oldSheet.Copy(newWorkbook.Sheets[1],System.Type.Missing);

         Excel.Worksheet emailingSheet = ExcelHelpers.GetWorksheet
                                          (newWorkbook, "Recap");

         Excel.Range tempRange= emailingSheet.UsedRange;

         Excel.Worksheet copySheet = ExcelHelpers.GetWorksheet
                                          (newWorkbook, "Sheet1");

         newWorkbook.Sheets.FillAcrossSheets
                                 (tempRange,XlFillWith.xlFillWithAll);
         string accountName = acctComboBox.Text.Trim();

         copySheet.Cells[5,2] = accountName;

         thisApplication.DisplayAlerts = false;

         emailingSheet.Delete();

         thisApplication.DisplayAlerts = true;

         copySheet.Columns.AutoFit();

         newWorkbook.SaveAs("Info.xls",System.Type.Missing,
            System.Type.Missing,System.Type.Missing,false, false,
            XlSaveAsAccessMode.xlNoChange,System.Type.Missing,
            System.Type.Missing,System.Type.Missing,
            System.Type.Missing,System.Type.Missing);

         newWorkbook.Close(false,false,System.Type.Missing);
       

       

//***************************PART TWO BEGINS HERE******************************//

         string to = null;

         for(int r = 2; r < 10; r++)

         {

             if(((Range)mEmailSheet.Cells[r, 1]).Text != null &&

                  ((string)((Range)mEmailSheet.Cells[r, 1]).Text).Length < 1)

             {

                     break;

             }

             if(to == null)
               to = (string)((Range)mEmailSheet.Cells[r, 1]).Text+";";

             else

               to = to +((Range)mEmailSheet.Cells[r, 1]).Value2.ToString()+ ";";

         }

         MailMessage message = new MailMessage();

         message.To = to;

         message.From = fMsg.ToString();

         message.Subject="Email Submitted " ;  

         message.Body ="Attachment enclosed";

         Thread.Sleep(3000);

         DirectoryInfo dirInfo = new DirectoryInfo(Directory.GetCurrentDirectory
                                                                            ());

         FileInfo[] fInfo = dirInfo.GetFiles();

         string fileName = fInfo[0].Name;

         MailAttachment attach = new MailAttachment
                                 (Directory.GetCurrentDirectory() +

                                 @"\"+fileName,
                                 System.Web.Mail.MailEncoding.Base64);

         message.Attachments.Add(attach);

         SmtpMail.SmtpServer = "xyz.CompanyDomain.com";

         SmtpMail.Send(message);

         MessageBox.Show("E-Mail send.");

         Directory.SetCurrentDirectory("..");

         Directory.Delete("ForEmail",true);

     }

     catch(Exception e)

     {

          Console.WriteLine(e);

          newWorkbook.Close(false,false,System.Type.Missing);

          Directory.SetCurrentDirectory("..");

          Directory.Delete("ForEmail",true);

     }

}

How to automatically generate E-mails from your C# code?

Generating E-mails through C# code can be very helpful to users. They just need to click a button and boom, the information is sent on the e-mail to the required people. If you want to generate a normal E-mail without attachment, these are the steps you can write:-- 1. You need to include , using System.Web.Mail; 2. The code for sending e-mail is as follows, string userID = Environment.UserName.ToString(); MailMessage message = new MailMessage(); string windowsLogin = Environment.UserName.ToString(); string fromMsg =windowsLogin+@"@companyDomainName.com"; string toMsg = null ; toMsg = (string) ((Range)sheet.Cells[i,15]).Text; // In case of Excel Application "To field can take in any constant or user supplied value from Text box etc. message.To = toMsg.ToString(); message.From = fromMsg.ToString(); message.Body = "STATUS HAS BEEN CONFIRMED. PLEASE CHECK YOUR RESPECTIVE UPDATES FROM THE APPLICATION."; message.Subject=" STATUS CONFIRMED BY " + userID.ToUpper(); SmtpMail.SmtpServer = "server-name.com"; SmtpMail.Send(message); Console.WriteLine(“E-mail Has Been Sent successfully”);

You have to know your server name so that you can send an e-mail. For knowing this, go to your Outlook Express

Click on Tools --> Send/Receive --> Send/Receive Settings --> Define Send/Receive Groups --> Edit --> Account Properties -->

There you find Microsoft Exchange Server name written in General Tab

Also in Tools --> Options --> Mail Setup --> You can find Send/Receive Settings , this will take you to the same pop-up , Define Send/Receive Groups. In case you want to Email an attachment using C# , please read my another blog topic "Emailing Attachments ".

Using Microsoft.Office.Interop.Excel, How to sort rows in a worksheet?

It is very easy to sort rows using Microft.Office.Interop.Excel in a worksheet. PART I - SORTING BASED ON 1 OR 2 COLUMNS We can use the Sort method of the NamedRange object to sort the data in a worksheet. Just create a Range object i.e. Excel.Range sRng; Also, you already have a worksheet object, Worksheet SummSheet; Now assign Range to sRng , sRng = SummSheet.get_Range("A4","N" + rowNum);// here A4 is the starting cell and N"rowNum" is the ending cell Apply this sort method

sRng.Sort(sRng.Columns[1,System.Type.Missing],XlSortOrder.xlAscending, sRng.Columns[10,System.Type.Missing], System.Type.Missing,XlSortOrder.xlAscending,  System.Type.Missing,XlSortOrder.xlAscending,XlYesNoGuess.xlNo,System.Type.Missing,System.Type.Missing,XlSortOrientation.xlSortColumns,XlSortMethod.xlPinYin,XlSortDataOption.xlSortNormal,XlSortDataOption.xlSortNormal,XlSortDataOption.xlSortNormal);

In the above method we are sorting the rows as per 1st column .In case 1st column has same values the row will be sorted according to the 10th column. The Sorting Order is Ascending order. The same can be applied for Descending Order also. Only the SortOrder needs to be changed. PART II - SORTING BASED ON MORE THAN 2 COLUMNS In case you need to sort the rows based on more than 2 columns, we can implement ICOMPARABLE Interface's CompareTo method. We can have a class implement IComparable Interface and then re-define the CompareTo method. For e.g.

public class TryCompare:IComparable
{
 public string ID
 {
  get
  {
   return Did;
  }
  set
  {
   Did = value;
  }
 }
 public string QuanDiff
 {
  get
  {
   return DQuanDiff;
  }
  set
  {
   DQuanDiff = value;
  }
 }
 public string PriceDiff
 {
  get
  {
   return DPriceDiff;
  }
  set
  {
   DPriceDiff = value;
  }
 }


 public int CompareTo(object obj)
 {
  TryCompare CompareObj =(TryCompare)obj;
  int compResults = this.ID.CompareTo(CompareObj.ID);
  if(compResults==0)
  {
   if(this.QuanDiff==CompareObj.QuanDiff)
   {
    if(this.PriceDiff==CompareObj.PriceDiff)
     return 0;
    else if(this.PriceDiff>CompareObj.PriceDiff)
     return 1;
    else if(this.PriceDiff<CompareObj.PriceDiff)
                  return -1;
   }
   else if(this.QuanDiff>CompareObj.QuanDiff)
   {
    return 1;
   }
   else if(this.QuanDiff<CompareObj.QuanDiff)
              {
               return -1;
              }
  }
  return compResults;
 }

}

So in the example above , we are sorting based on 3 values. Now we just need to call , TryCompareObj.Sort(); This will solve all our sorting requirements.

To convert an INT into ASCII Char

Converting INTEGER into an ASCII char is very simple . In C# we dont need to do anything special, just type-cast the INT into a CHAR. int i = 65; char a = (char) i; Console.WriteLine("Result : "+a.toString()); Result : A Converting ASCII char into an INTEGER is also same way but vice versa.Type-casting CHAR into an INT. char a = 'A'; int i = (int) a; Console.WriteLine("Result : "+i.toString()); Result : 65

Error : The assembly could not be found at or could not be loaded

I have an excel application created using .Net framework 1.1 and Microsoft Office Excel 2003. This application named CBlotter with the same settings and dll in all the user computers was running fine but only in 1 computer , I encountered the error saying "The assembly CBlotter could not be found at .\CBlotter_bin\ or could not be loaded. You can still edit and save the document.Contact your administrator or the author of this document for further assistance." I checked all the user permissioning of the folder. I gave the full trust policy if not already given to the folder by using Caspol.exe(Code Access Security Policy Tool). This can be done by going to the specified v1.1/v2.0 folder. If you want to grant full trust to a local folder , then type caspol -u -ag All_Code -url C:\\\* FullTrust -n "" -d "" If you want to grant full trust to a netwrok folder , then type caspol -pp off -m -ag LocalIntranet_Zone -url \\\\* FullTrust -n "" -d "" After assigning the right permissioning and full trust , usually the assembly loading/cannot find error goes away but in my case it dint. So Now, I checked for the assemblies required for my .net code to run . In VS2003 we add reference to the COM library called 'Microsoft Excel 11.0 Object Library'. This helps us use the Excel Objects and write our piece of code using VS2003. When I checked the library in assembly cache (GAC), using microsoft 1.1 configuration GUI, I was not able to locate the assembly. This actually raised a question as Microsoft Office Excel 2003 and .Net 1.1 framework was completely installed on User's PC then why were the Primary Interop Libraries (PIA) missing. After researching I found out that these libraries require re-registration or installation sometimes when some other office application initiates a setup that causes library registration to be rewritten. In my case Microsoft Excel SP3 installation had removed the PIAs altogether . So I just installed the PIAs again by going to the site http://support.microsoft.com/kb/897646 This solved my problem.