April 22, 2014 at 8:40 am
Hello All ,
I need some expert help in writing the sql query for the below scenario.
I have a table in the following format with the data types as below
EmpId - Int ( Not Null)
Jan -Sept - Int ( Allow null = true)
Sample table is below
EmpId | Jan | Feb | March | Apr | May | Jun | Jul | Aug | Sept |
101 | 2 | 3 | 4 | 3 | 3 | 3 | 4 | 5 | 3 |
102 | NULL| 2 | 4 | 3 | NULL | 3 | 4 | 5 | 3 |
103 | NULL| NULL | 4 | 4 | 3 | 4 | 2 | 3 | 3 |
Q1 Avg = DataType = Decimal(5,2)Average of Jan , Feb , March
Q2 Avg = DataType = Decimal(5,2)Average of Apr, May, June
Q3 Avg = DataType = Decimal(5,2)Average of Jul , Aug , Sept
YTD Avg = DataType = Decimal(5,2)Average of Jan - Sept
I need the output for the above table in the following format
EmpId | Jan | Feb | March | Q1 Avg | Apr | May | Jun | Q2 Avg | Jul | Aug | Sept | Q3 Avg | YTD Avg
101 | 2 | 3 | 4 | 3.00 | 3 | 3 | 3 | 3.00 | 4 | 5 | 3 | 4.00 | 3.33
102 | NULL| 2 | 4 | 3.00 | 3 | NULL | 3 | 3.00 | 4 | 5 | 3 | 4.00 | 3.42
Q1 average for empId 101 would be (2+3+4)/3 = 3.00
Averages should not include the count of NULL's for example Q1 average for empId 102 would be just (2+4)/2 = 3.00
Please use the following script for creation and Insertion of Data into the primary table
CREATE TABLE [dbo].[EmpMonths](
[EmpId] [int] NOT NULL,
[Jan] [int] NULL,
[Feb] [int] NULL,
[March] [int] NULL,
[April] [int] NULL,
[May] [int] NULL,
[June] [int] NULL,
[July] [int] NULL,
[Aug] [int] NULL,
[Sept] [int] NULL
) ON [PRIMARY]
INSERT INTO [EmpMonths]
([EmpId],[Jan],[Feb],[March],[April],[May],[June],[July],[Aug],[Sept]) VALUES
(101 ,2,3,4,3 ,3,3 ,4 ,4 ,3)
GO
INSERT INTO [EmpMonths]
([EmpId],[Jan],[Feb],[March],[April],[May],[June],[July] ,[Aug],[Sept])VALUES
(102,NULL,2,4 ,3 ,NULL,3 ,4 ,5,3)
GO
April 22, 2014 at 8:56 am
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2014 at 9:32 am
As a hint, there are AVG aggregates you can use in T-SQL, as well as the datepart function which can support quarters, which can be used in a GROUP BY function.
However, as Sean mentioned, you need to include a little more detail in setup and also show us what you've done. You should make an attempt to write a query to get this data.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply