asked
How do I aggregate employee data by month and day
--create employees1 table CREATE TABLE Employees1( emp_no char(8) NOT NULL primary key, emp_type char(4) NOT NULL, emp_dtls varchar(50) NULL) -- create emp_busns1 table CREATE TABLE [dbo].[emp_busns1]( [emp_id] [char](8) NOT NULL, [trandate] [numeric](8, 0) NOT NULL, [bsns_amt] [numeric](17, 2) NOT NULL, CONSTRAINT [PK_emp_busns1] PRIMARY KEY CLUSTERED ( [emp_id] ASC, [trandate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[emp_busns1] WITH CHECK ADD CONSTRAINT [FK_emp_busns1_Employees1] FOREIGN KEY([emp_id]) REFERENCES [dbo].[Employees1] ([emp_no]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[emp_busns1] CHECK CONSTRAINT [FK_emp_busns1_Employees1] --Insert data into employees1 table** insert into employees1(emp_no,emp_type) values (101,'ag') insert into employees1(emp_no,emp_type) values(102 ,'ca') insert into employees1(emp_no,emp_type) values(103 ,'ag') insert into employees1(emp_no,emp_type) values(104 ,'ag') insert into employees1(emp_no,emp_type) values(105 ,'ag') insert into employees1(emp_no,emp_type) values(106 ,'ag') insert into employees1(emp_no,emp_type) values(108 ,'ca') insert into employees1(emp_no,emp_type) values(109 ,'ag') insert into employees1(emp_no,emp_type) values(110 ,'ag') --Insert data into emp_busns1 table** insert into emp_busns1 values(100, 20120804, 563.00) insert into emp_busns values(100, 20120812, 988.00) insert into emp_busns1 values(100, 20120822, 6532.00) insert into emp_busns1 values(103, 20120822, 8563.00) insert into emp_busns1 values(105 , 20120818, 4563.00) insert into emp_busns1 values(106 , 20120803, 8653.00) insert into emp_busns1 values(108 , 20120821, 788.00) insert into emp_busns1 values(110 , 20120812, 6543.00) insert into emp_busns1 values(110, 20120829, 7856.00) insert into emp_busns1 values(110 , 20120831, 1000.00) insert into emp_busns1 values(104 , 20120820, 3331.00) insert into emp_busns1 values(109 , 20120831, 123456.00) insert into emp_busns1 values(109 , 20120812, 466.00) /* Now Excute query for which gives count of those employees which are agents(‘ag’) and also belongs to employees of month. */ SELECT COUNT(emp_no) FROM employees1 WHERE emp_no IN (SELECT Mnth FROM (SELECT CASE WHEN trandate = '20120831' THEN emp_id END AS day, CASE WHEN trandate BETWEEN '20120801' AND '20120831' THEN emp_id END AS Mnth FROM emp_busns1) temp) AND emp_type = 'ag' ---- this is a wrorking query, but not complete. I am unable to find a soulution to make a query which give count of those employees which belongs to month and count of those employess which belongs to day and also they should be agents(‘ag’), in a single query. please give some suggestions.soon. thank you
i just want to clear my objective that the query given by me is just giving count of employees who are agent as well as they've procured business during the month.and i want solution which gives count of agent('ag') employees who procured business on a particular day of month and count of agent('ag')employees who've procured business during month in a single query. In my given query emp_no is just maped with Mnth column but i want to map emp_no to 'Mnth' column and as well as 'day' column in same time in single query. how can i do this???please guide me as soon as psble....
I'm a bit baffled. My query gives the total number per day, and per month of employees who are agents who procure business. It does this in a single query. It lists the month and the day. You say (in a message) that it is not the required output. Why not tell us what the required output is, preferably by giving an example, and we can help
Example for my question: Firstly Execute query given below: SELECT COUNT(emp_no)as 'Active_Agents_Of_Month' FROM employees1 WHERE emp_no IN (SELECT Mnth FROM (SELECT CASE WHEN trandate = '20120831' THEN emp_id END AS day, CASE WHEN trandate BETWEEN '20120801' AND '20120831' THEN emp_id END AS Mnth FROM emp_busns1) temp) AND emp_type = 'ag' It Gives Output: Active_Agents_Of_Month 7 Now Execute this query: SELECT COUNT(emp_no) as 'Active_Agents_Of_Day' FROM employees1 WHERE emp_no IN (SELECT Day FROM (SELECT CASE WHEN trandate = '20120831' THEN emp_id END AS Day, CASE WHEN trandate BETWEEN '20120801' AND '20120831' THEN emp_id END AS Mnth FROM emp_busns1) temp) AND emp_type = 'ag' It Gives Output: Active_Agents_Of_Day 2 Now i want to make a query from given tables which give output like this: Active_Agents_Of_Day| Active_Agents_Of_Month 2 | 7
If this is a new system then I would suggest fixing the issues with datatypes first. Using char(8) for data that is an INTEGER and Numeric for data that is a DATE is only going to give you headaches and performance issues. For a start you cant directly apply Date related functions to the Numeric datatype so conversion to DATE is required. Once these issues are fixed then JOINS will become more effective and getting Month and Day parts from the Dates will become trivial.
+1 from me as well which makes +2. Let us hope others will make it +100 ;-)
Well I wouldn't design it this way, but if I've understood what you want then this might work... SELECT COALESCE(SUBSTRING(Cast(trandate as CHAR(8)),7,2),'Total') AS [Day], COALESCE(SUBSTRING(Cast(trandate as CHAR(8)),5,2),'Total') AS [month], COUNT(*) AS Employees FROM emp_busns1 INNER JOIN employees1 ON emp_busns1.emp_ID=employees1.emp_no WHERE emp_type='ag' GROUP BY SUBSTRING(Cast(trandate as CHAR(8)),5,2), SUBSTRING(Cast(trandate as CHAR(8)),7,2) WITH rollup
12 People are following this question.
