5



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
namespace ConsoleApplication3
{
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection(@"Data Source=ANT34;Integrated Security=SSPI;Initial Catalog=Northwind"),
conn1 = new SqlConnection(@"Data Source=ANT34;Integrated Security=SSPI;Initial Catalog=Northwind");
SqlCommand cmd = new SqlCommand("select distinct Year(OrderDate), Month(OrderDate) from orders",conn);
conn.Open(); conn1.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{

int year=rdr.GetInt32(0), month=rdr.GetInt32(1);
Console.Write(year);
Console.Write("\t");
Console.Write(month);
Console.Write("\t");


SqlCommand cmd1 = new SqlCommand(

String.Format(
"select c.CategoryName from Categories c, "+
"(select RANK() OVER (ORDER BY s.q3) AS Ran, s.q1 as qq1 from "+
"(select p.CategoryID as q1, Sum(od.UnitPrice*Quantity) as q3 "+
"from Orders o, [Order Details] od, Products p "+
"where o.OrderID=od.OrderID and od.ProductID=p.ProductID and year(o.OrderDate)={0} and month(o.OrderDate)={1} "+
"group by p.CategoryID) s) ss "+
"where ss.Ran<2 and ss.qq1=c.CategoryID ",year,month)
,conn1);

Console.Write(cmd1.ExecuteScalar());

Console.Write("\n");
}
conn.Close();
Console.WriteLine();

Console.ReadKey();
conn.Close();
}
}
}



-----------------------------------------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Sql;
using System.Data.SqlClient;
namespace ConsoleApplication3
{
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection(@"Data Source=ANT34;Integrated Security=SSPI;Initial Catalog=Northwind"),
conn1 = new SqlConnection(@"Data Source=ANT34;Integrated Security=SSPI;Initial Catalog=Northwind");
SqlCommand cmd = new SqlCommand("select distinct Year(OrderDate), Month(OrderDate) from orders",conn);
conn.Open(); conn1.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{

int year=rdr.GetInt32(0), month=rdr.GetInt32(1);
Console.Write(year);
Console.Write("\t");
Console.Write(month);
Console.Write("\t");


SqlCommand cmd1 = new SqlCommand(

String.Format(
"select CategoryID from Orders join [Order Details] on Orders.OrderID=[Order Details].OrderID " +
"join Products on Products.ProductID=[Order Details].ProductID " +
"where YEAR(OrderDate)={0} and MONTH(OrderDate)={1} group by CategoryID " +
"having Sum(Quantity*[Order Details].UnitPrice)= " +
"(select max(totals) from " +
"(select Sum(Quantity*[Order Details].UnitPrice) totals from Orders join [Order Details] on Orders.OrderID=[Order Details].OrderID " +
"join Products on Products.ProductID=[Order Details].ProductID " +
"where YEAR(OrderDate)={0} and MONTH(OrderDate)={1} group by CategoryID) t )",year,month)
,conn1);
Console.Write(cmd1.ExecuteScalar());

Console.Write("\n");
}
conn.Close();
Console.WriteLine();

Console.ReadKey();
conn.Close();
}
}
}



------------------------------------------------------------------------------------------------------------------------------------------------------------