Sunday, 31 May 2015

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

Friday, 29 May 2015

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

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts