How to calculate Average between two Dates in Excel

Excel AverageIf Function to calculate Average between two Dates
Suppose we have a Sales data table as follows :

Prod_Id	Sal_Date	Net Sales
359	5/15/2015	1472
264	5/16/2015	6266
435	5/17/2015	8877
359	5/18/2015	3881
264	5/19/2015	7190
435	5/20/2015	1778
359	5/21/2015	9394
264	5/22/2015	4185
435	5/23/2015	3560

Suppose if we want to find the Average of Net Sales between two dates , we can find generally using AverageIf or AverageIfs Functions.

Also we can calculate the Average between two Dates using an Array based Average and If  Combo Formula as shown below :

Cond_1:	Cond_2:	AVERAGE FORMULA	Result
5/18/2015	21-May-15	=AVERAGE(IF(($B$1:$B$10>=E2)*($B$1:$B$10<F2),C1:C10))	4283

Examples :

Source Data as follows :

Prod_Id	Month_No	Sales
359	3	1472
264	5	6266
435	7	8877
359	5	3881
264	4	7190
435	2	1778
359	5	9394
264	3	4185
435	2	3560

Calculating Average based of Different Criteria as follows :

Cond_1:	Cond_2:	AVERAGE FORMULA	Result
3	5	{=AVERAGE(IF($B$1:$B$10={3,5},C1:C10))}	5039.6
359	5	{=AVERAGE(IF(($B$1:$B$10=5)*($A$1:$A$10=359), C1:C10))}	6637.5
">=3" &<"5"	264	{=AVERAGE(IF(($B$1:$B$10>=3)*($B$1:$B$10<5)* ($A$1:$A$10=264),C1:C10))}	5687.5

Note :
To understand more about the AverageIf and AverageIfs , please go through the below link of this Blog Post.

http://excelkingdom.blogspot.in/2013/03/excel-averageif-and-averageifs-functions.html

Thanks,

TAMATAM

SQL Server Query to find Highest or Maximum Salary in each Department

SQL Query to Retrieve Highest or Maximum Salary drawing Employee Details from each Department

Example :
Suppose we have a Employee Table as follows:

EMP Table :

EMP_ID	F_Name	L_Name	Dept_ID	Cur_Address	Perm_Address	Salary
1001	Ravi	Kumar	1	Hyderabad	Hyderabad	20000
1002	David	Smith	2	Bangalore	Pune	35000
1003	Victory	Venkatesh	1	Hyderabad	Hyderabad	50000
1004	Tamatam	Reddy	3	Bangalore	Hyderabad	25000
1005	William	Smith	2	Hyderabad	Bangalore	40000
1006	King	Fisher	6	Bangalore	Bangalore	30000

Now if we want to Retrieve Highest or Maximum Salary drawing Employee Details of each Department , from the above table , we can use the following SQL Sub Query :

SELECT E_Id,(F_Name+'_'+L_Name) as E_Name,Dept_Id,Salary From EMP Where Salary IN (SELECT MAX(Salary)AS Max_Sal From EMP GROUP BY Dept_Id)

Output :

E_Id	E_Name	Dept_Id	Salary
1003	Victory_Venkatesh	1	50000
1004	Tamatam_Reddy	3	25000
1005	William_Smith	2	40000
1006	King_Fisher	6	30000

Thanks,

TAMATAM

SQL SERVER CARTESIAN JOIN or CROSS JOIN Syntax and Example

SQL CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables.
when a join condition is omitted when getting result from two tables then that kind of query gives us Cartesian product, in which all combination of rows displayed. All rows in the first table is joined to all rows of second table.

Syntax :

Select* from Table_I , Table_2....

OR

Select* from  T1.Column1,T1.Column2, T2.Column3,T2.Column4 from Table_I  T1, Table_2 T2

Example :

Suppose we have a Two Tables as follows:

EMP Table :

EMP_ID	F_Name	L_Name	Dept_ID	Cur_Address	Perm_Address	Salary
1001	Ravi	Kumar	1	Hyderabad	Hyderabad	20000
1002	David	Smith	2	Bangalore	Pune	35000
1003	Victory	Venkatesh	1	Hyderabad	Hyderabad	50000
1004	Tamatam	Reddy	3	Bangalore	Hyderabad	25000
1005	William	Smith	2	Hyderabad	Bangalore	40000
1006	King	Fisher	6	Bangalore	Bangalore	30000

DEPT Table :

Dept_Id	Dept_Name
1	Production
2	Sales
3	Technology

Cartesian or Cross Join Query :

SELECT E_Id,(F_Name+'_'+L_Name) as E_Name , D.Dept_Id,D.Dept_Name 
from EMP E, DEPT D

Output :

As the Table_1(EMP) contains 6 records and Table_2(DEPT) has 3 records , the Cartesian/Cross Join gives an output of 18 (6*3) records as shown below :

E_Id	E_Name	Dept_Id	Dept_Name
1001	Ravi_Kumar	1	Production
1002	David_Smith	1	Production
1003	Victory_Venkatesh	1	Production
1004	Tamatam_Reddy	1	Production
1005	William_Smith	1	Production
1006	King_Fisher	1	Production
1001	Ravi_Kumar	2	Sales
1002	David_Smith	2	Sales
1003	Victory_Venkatesh	2	Sales
1004	Tamatam_Reddy	2	Sales
1005	William_Smith	2	Sales
1006	King_Fisher	2	Sales
1001	Ravi_Kumar	3	Technology
1002	David_Smith	3	Technology
1003	Victory_Venkatesh	3	Technology
1004	Tamatam_Reddy	3	Technology
1005	William_Smith	3	Technology
1006	King_Fisher	3	Technology

Thanks,

TAMATAM