| Author |
 Topic  |
|
mateenmohd
Constraint Violating Yak Guru
United Arab Emirates
297 Posts |
 Posted - 07/14/2005 : 11:02:56
|
How can calculate the Employees ACCUMMULATED MAN-HOURs ?
This is another scenario.
Tables structure like this.
CREATE TABLE [dbo].[BH] (
[RECID] [int] IDENTITY (1, 1) NOT NULL ,
[DATED] [smalldatetime] NULL ,
[EMPNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROJNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BH] [int] NULL ,
[USERID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MH1] (
[RECID] [int] IDENTITY (1, 1) NOT NULL ,
[DATED] [smalldatetime] NULL ,
[EMPNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROJNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MH1] [int] NULL ,
[USERID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[EMP] (
[RECID] [int] IDENTITY (1, 1) NOT NULL ,
[EMPNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USERID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POSIT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEPTCODE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEPT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DEPT] (
[RECID] [int] IDENTITY (1, 1) NOT NULL ,
[DEPTCODE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DEPT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
Data in table (MH1) like this.
empno....projno……mh1……dated…….AccummulatedHours
---------------------------------------------------
012125…240……...10………1/6/2005
012125….240……..10………1/13/2005
012125….240……..10………1/20/2005
012125….240……..10………1/27/2005………..40
012125…240….…..10………2/3/2005
012125….240……..10………2/10/2005
012125….240……..10………2/17/2005
012125….240……..10………2/24/2005………80
012125…240……. 10……….3/3/2005
012125….240……..10………3/10/2005
012125….240……..10………3/17/2005
012125….240……..10………3/24/2005
012125….240……..10………3/31/2005……….130
....
... and so on....
cont end of year..
Data in BH (Budgeted Hour) table like this.
Empno…..BH…..projno…..
---------------------------------
012125…..200…..240
012125…..170…..233
012125…..200…..967
….
..
(Accummulated column not in tables)
How can retrieve the data from above tables like this.
Result.
Project No.240
Empno….BH…..totalHours...Accumulated MH1………date
----------------------------------------------------------
012125….200……….40………….40. …… 1/27/2005
012125….170……….40………….80…………………12/24/2005
012125….200………50………….130…………………12/31/2005
….
……..
And so on……
BH=Budgeted Hours
MH1=Man-hours
Totalhours=total month hour
Date= month end date (MH1)
What query will use ?
Please help.
regards.
Mateen
|
Edited by - mateenmohd on 07/14/2005 11:15:00
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 07/14/2005 : 11:14:36
|
Could You provide some insert statements to create test data like in Your example ?
rockmoose |
 |
|
|
mateenmohd
Constraint Violating Yak Guru
United Arab Emirates
297 Posts |
Posted - 07/14/2005 : 11:34:47
|
Thanks for your response.
table MH1
---------
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'1/6/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'1/13/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'1/20/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'1/27/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'2/3/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'2/10/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'2/17/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'2/24/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'3/3/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'3/10/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'3/17/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'3/24/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'3/31/2005','martin')
.....
Table BH
---------
insert into BH (projno,empno,BH,dated,userid)
values ('240','06367',200,'1/27/2005','martin')
insert into BH (projno,empno,BH,dated,userid)
values ('240','06367',170,'2/24/2005','martin')
insert into BH (projno,empno,BH,dated,userid)
values ('240','06367',200,'3/31/2005','martin')
....
(emp table is employee empno,name etc...
dept table dept name....)
Regards.
Mateen
|
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 07/14/2005 : 13:11:36
|
Try this out:
::::::::::::::::::::::::::::::::::::::::::::::
Use a subquery to create the [Accumulated MH1]
Use a derived table to create the [totalHours]
::::::::::::::::::::::::::::::::::::::::::::::
SELECT
bh.EMPNO,bh.PROJNO,bh.BH,[totalHours],bh.DATED,
( SELECT SUM(mh.MH1)
FROM MH1 mh
WHERE mh.EMPNO = bh.EMPNO
AND mh.PROJNO = bh.PROJNO
AND DATEPART(YEAR,bh.DATED) = DATEPART(YEAR,mh.DATED)
AND DATEPART(MONTH,bh.DATED) >= DATEPART(MONTH,mh.DATED) ) AS [Accumulated MH1] -- Yearly accumulation
FROM
BH bh
JOIN
(
SELECT EMPNO,PROJNO,SUM(MH1) AS [totalHours],DATEPART(YEAR,DATED) AS YR,DATEPART(MONTH,DATED) AS MTH
FROM MH1 GROUP BY EMPNO,PROJNO,DATEPART(YEAR,DATED),DATEPART(MONTH,DATED)
) AS acc_monthly
ON bh.EMPNO = acc_monthly.EMPNO
AND bh.PROJNO = acc_monthly.PROJNO
AND DATEPART(YEAR,bh.DATED) = acc_monthly.YR
AND DATEPART(MONTH,bh.DATED) = acc_monthly.MTH
EMPNO PROJNO BH totalHours DATED Accumulated MH1
----- ------ --- -------- ------------------------------ ---------------
06367 240 200 40 2005-01-27 00:00:00 40
06367 240 170 40 2005-02-24 00:00:00 80
06367 240 200 50 2005-03-31 00:00:00 130
rockmoose |
|
|
|
mateenmohd
Constraint Violating Yak Guru
United Arab Emirates
297 Posts |
Posted - 07/15/2005 : 10:28:10
|
Thanks you very much for your help.
Query is really nice.
It retrieved the data as I need.
We want that Data should be retrived base on MH1 dated not BH dated.
BH (Budgeted hour) data will insert only one time.
When we get project.
MH1 data will insert weekly basic.
At the end of each month we want to find out how may man-hours
Work in that project.
Please calculate the accumulated man-hour base on MH1. Dated i.e.
SELECT
bh.EMPNO,bh.PROJNO,bh.BH,[totalHours],bh.DATED,
( SELECT SUM(mh.MH1)
FROM MH1 mh
WHERE mh.EMPNO = bh.EMPNO
AND mh.PROJNO = bh.PROJNO
AND DATEPART(YEAR,bh.DATED) = DATEPART(YEAR,mh.DATED)
AND DATEPART(MONTH,bh.DATED) >= DATEPART(MONTH,mh.DATED) ) AS [Accumulated MH1] -- Yearly accumulation
FROM
BH bh
JOIN
(
SELECT EMPNO,PROJNO,SUM(MH1) AS [totalHours],DATEPART(YEAR,DATED) AS YR,DATEPART(MONTH,DATED) AS MTH
FROM MH1 GROUP BY EMPNO,PROJNO,DATEPART(YEAR,DATED),DATEPART(MONTH,DATED)
) AS acc_monthly
ON bh.EMPNO = acc_monthly.EMPNO
AND bh.PROJNO = acc_monthly.PROJNO
AND DATEPART(YEAR,bh.DATED) = acc_monthly.YR
AND DATEPART(MONTH,bh.DATED) = acc_monthly.MTH
where MH1.projno='240'
and MH1.dated='3/31/2005'
EMPNO PROJNO BH totalHours (MH1 DATED) Accumulated MH1
----- ------ --- -------- ------------------------------ ---------------
06367 240 200 40 2005-01-27 00:00:00 40
06367 240 170 40 2005-02-24 00:00:00 80
06367 240 200 50 2005-03-31 00:00:00 130
Sorry, it it my mistake I post three dates of BH.
Data insert in BH table like this.
Table BH
---------
insert into BH (projno,empno,BH,userid)
values ('240','06367',200,’1/27/2005’,'martin')
insert into BH (projno,empno,BH,dated,userid)
values ('223','06367',170, ’1/27/2005’,'martin')
insert into BH (projno,empno,BH,dated,userid)
values ('967','06367',200,’1/27/2005’,'martin')
....
….
You query is absolutely right, but data should be retrieve base on MH1.DATED not BH.DATED how ?
What change you query
select mh1.EMPNO,mh1.PROJNO,bh.BH,[totalHours],mh1.DATED,
(…...
…….
......
where mh1.projno='240'
and mh1.dated='3/31/2005'
with sincere regards.
Mateen
|
Edited by - mateenmohd on 07/16/2005 01:43:21 |
|
|
|
mateenmohd
Constraint Violating Yak Guru
United Arab Emirates
297 Posts |
Posted - 07/25/2005 : 08:21:30
|
Thanks for your response and Query.
how can calculate the Weekly Manhour of employee ?
I have to retrieve Weekly Manhours data like this.
FIRST WEEKLY (MH1 DATED = 2005-01-06)
EMPNO..PROJNO…BH…..totalHours .Accumulated MH1
--------------------------------------------
06367……240……..200……40…… .….….. 40
12455……240…….170…….40……. …………….80
12456……240……..180……50 …… …………….130
80802……240……..200……60……. …....190
SECOND WEEKLY (MH1 DATED = 2005-01-13)
EMPNO..PROJNO…BH…..totalHours....Accumulated MH1
------------------------------------------------
06367……240……..200……40…… ……. …….. 230
12455……240…….170…….50……. ……………….280
12456……240……..180……60 …… ….…………….340
80802……240……..200……50……. ….......390
THIRD WEEKLY (MH1 DATED = 2005-01-20)
EMPNO..PROJNO…BH…..totalHours.....Accumulated MH1
----------------------------------------------------
06367……240……..200……40…… ....…….. 430
12455……240…….170…….50……. ..……………….480
12456……240……..180……60 …… ..……………….540
80802……240……..200……50……. .........590
...
and so on...
...
Please help me to retrieve data base on weekly date
(MH1 Manhour Date) ?
I am trying, but I could not get result.
You are experience sql programmer, will help me.
with regards.
Mateen
|
Edited by - mateenmohd on 07/25/2005 08:25:55 |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 07/25/2005 : 08:57:04
|
The Accumulated column -- what is that for? For display/reporting purposes only, or are you storing in sql server or doing further T-SQL calculations with it?
If you are just displaying it, calculating running totals is much easier and much more efficient at the client side. What client application are you using to present this data?
- Jeff |
|
|
|
mateenmohd
Constraint Violating Yak Guru
United Arab Emirates
297 Posts |
Posted - 07/25/2005 : 10:03:03
|
Thanks for your response.
I want to calculate the Accumulated manhour weekly.
now it is for display purpose and as well as I also use doing further T-SQL calculate.
I have to retrieve data from sql server and display data in ASP Program.
I could not retrieve/calculate the Accumulated manhour by MH1 DATED weekly (Manhour Date)
display data by weekly dated (MH1 DATED).
Mr. Rockmoose Query absulately right.
this query retrieve data base on BH (budgeted hour date,Monthly basic)
I want to retrieve data base on MH1 (Manhour Date)
with weekly basic.
ie. each week how may work hour work each employee ?
regards.
Mateen
|
Edited by - mateenmohd on 07/25/2005 10:12:12 |
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 07/25/2005 : 21:49:22
|
To take it in small steps.
Go from the MH1 table;
Create a query that sums the hours by: projno, empno, year, week.
When You have that, the next step is to create an accumulated column for that query.
--------------------------------------------
(If that is difficult, You can do another post with just that part).
To make it easier post, some test data (just MH1 table is enough), that will give the example results You posted 2 posts up.
--------------------------------------------
After You have achieved this, You can start worrying about the BH table,
(I think You might be able to get the BH column with a subquery).
Thought, i'd post a reply after so many days, at least.
rockmoose |
|
|
|
mateenmohd
Constraint Violating Yak Guru
United Arab Emirates
297 Posts |
Posted - 07/26/2005 : 02:03:19
|
Thanks for your response.
sample data BH1 table.
-----------------------
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'1/6/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'1/13/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'1/20/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'1/27/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'2/3/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',0,'2/10/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',10,'2/17/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','06367',0,'2/24/2005','martin')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','73322',10,'1/6/2005','peter')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','73322',10,'1/13/2005','peter')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','73322',10,'1/20/2005','peter')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','73322',10,'1/27/2005','peter')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','73322',0,'2/3/2005','peter')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','73322',10,'2/10/2005','peter')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','73322',10,'2/17/2005','peter')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','73322',0,'2/24/2005','peter')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','81986',10,'1/6/2005','mark')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','81986',10,'1/13/2005','mark')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','81986',10,'1/20/2005','mark')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','81986',10,'1/27/2005','mark')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','81986',0,'2/3/2005','mark')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','81986',10,'2/10/2005','mark')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','81986',10,'2/17/2005','mark')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','81986',0,'2/24/2005','mark')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','98895',0,'1/6/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','98895',10,'1/13/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','98895',0,'1/20/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','98895',10,'1/27/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','98895',0,'2/3/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','98895',10,'2/10/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','98895',0,'2/17/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','98895',10,'2/24/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','34585',10,'1/6/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','34585',10,'1/13/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','34585',10,'1/20/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','34585',10,'1/27/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','34585',10,'2/3/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','34585',0,'2/10/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','34585',10,'2/17/2005','ma')
insert into mh1 (projno,empno,mh1,dated,userid)
values ('240','34585',00,'2/24/2005','ma')
sample data BH table
---------------------
insert into BH (projno,empno,BH,dated,userid)
values ('240','06367',200,'1/1/2005','martin')
insert into BH (projno,empno,BH,dated,userid)
values ('240','73322',170,'1/1/2005','martin')
insert into BH (projno,empno,BH,dated,userid)
values ('240','81986',200,'1/1/2005','martin')
insert into BH (projno,empno,BH,dated,userid)
values ('240','98895',200,'1/1/2005','martin')
insert into BH (projno,empno,BH,dated,userid)
values ('240','34585',180,'1/1/2005','martin')
insert into BH (projno,empno,BH,dated,userid)
values ('240','81852',190,'1/1/2005','martin')
sample data EMP table
-----------------------
insert into emp (empno,name,dept)
values ('06367','Martin','Highway')
insert into emp (empno,name,dept)
values ('73322','Peter','Highway')
insert into emp (empno,name,dept)
values ('81986','Mark','Highway')
insert into emp (empno,name,dept)
values ('34585','Mr','Transportation')
insert into emp (empno,name,dept)
values ('98895','Pk','Transportation')
insert into emp (empno,name,dept)
values ('81852','Pk','Transportation')
Sample Result like this.
------------------------
Manhour Dated (MH1 date) 1/6/2005
Highway Dept
------------
EMPNO…ROJNO….BH …MH1… Accumulated MH1
------------------------------------------------------------
06367….240……200……….10……….10
73322…..240…….170……10…………10
81986…..240…….200…….10……….10
06367….240……200……….10………10
73322…..240…….180………0………0
81986…..240…….190……….0………0
Manhour Dated (MH1 date) 1/13/2005
Highway Dept
--------------------
EMPNO…ROJNO….BH…..MH1…….. Accumulated MH1
------------------------------------------------------------
06367…. 240………200……….10………….20……….//(10+10=20)
73322…..240……..170………10……………20
81986…..240……..200……….10……………20
06367….240………200……….10……………20
73322…..240……..180………10…………….10
81986…..240……..190……….10 …………..10
Manhour Dated (MH1 date) 1/20/2005
Highway Dept
------------------
EMPNO…ROJNO…BH……MH1…. Accumulated MH1
------------------------------------------------------------
06367….240………200……...10……….30…………….//(20+10=30)
73322…..240……..170………10……….30
81986…..240……..200……….0………. 20
06367….240………200……….0……… 20
73322…..240……..180…..…10……….20
81986…..240……..190……….10 ……..20
…..
And so on…..
….
---------------------------------
Manhour Dated (MH1 date) 1/6/2005
Transportation Dept
------------------
EMPNO…ROJNO….BH………MH1…. Accumulated MH1
----------------------------------------------------------
81852….240………200……….10……….10
38040…..240……..170………10………..10
81714…..240……..200……….10………..10
12123….240……..200……….10…..…….10
81673…..240……..180………0…………..0
34838…..240……..190……….0…………..0
Manhour Dated (MH1 date) 1/13/2005
Transportation Dept
------------------
EMPNO…ROJNO…BH……MH1…. Accumulated MH1
-------------------------------------------------------------
81852…240………100…….10………20………….//(10+10=20)
38040…..240……..200……10………20
81714…..240……..100…….10………20
12123….240………200…….10………20
81673…..240……..100……10………10
34838…..240……..200……10 …….10
Manhour Dated (MH1 date) 1/20/2005
Transportation Dept
------------------
EMPNO…ROJNO…BH……MH1….. Accumulated MH1
--------------------------------------------------------------
81852…..240…….100…….10…………30…………….//(20+10=30)
38040…..240……..200……10…………30
81714…..240……..100…….0…………. 20
12123….240………200…….0.………. 20
81673…..240……..100……10………..20
34838…..240……..290…….10 ……..20
…….
And so on….
BH will remain same.
waiting you response.
with regards.
Mateen
|
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 07/26/2005 : 09:05:53
|
You are storing the week "dates" in the tables for MH1
I suggest You create a table with valid week dates, and reference that table so that no "non week dates"
can be entered by mistake.
The BH table should have a UNIQUE constraint on: [DATED] + [EMPNO]+ [PROJNO]
Also a CHECK constraint to make sure that [DATED] only is 1/1 yyyy. ( If that is the business rule)
Try something like this:
(using 2 subqueries, there are other ways I guess, but this is pretty straightforward)
Use the WHERE filter to choose a DEPT + Week.
SELECT
emp.DEPT
,mh1.EMPNO
,mh1.PROJNO
,( SELECT BH
FROM BH bh
WHERE mh1.EMPNO = bh.EMPNO
AND mh1.PROJNO = bh.PROJNO
AND YEAR(mh1.DATED) = YEAR(bh.DATED) )AS [BH]
,mh1.MH1
,mh1.DATED
,( SELECT SUM(MH1)
FROM MH1 mh2
WHERE mh1.EMPNO = mh2.EMPNO
AND mh1.PROJNO = mh2.PROJNO
AND YEAR(mh1.DATED) = YEAR(mh2.DATED)
AND mh1.DATED >= mh2.DATED ) AS [Accumulated MH1]
FROM
MH1 mh1
JOIN EMP emp
ON mh1.EMPNO = emp.EMPNO
--WHERE
--emp.DEPT = 'Highway'
--AND mh1.DATED = '20050120'
ORDER BY
emp.DEPT
,mh1.DATED
,mh1.EMPNO
,mh1.PROJNO
rockmoose |
|
|
|
mateenmohd
Constraint Violating Yak Guru
United Arab Emirates
297 Posts |
Posted - 07/27/2005 : 08:05:05
|
Thanks you very much for your help and query.
we are insert only Thursday date in MH1 table.
1. only for data entry I make separate week table.
user select week date from that table ?
2. data entry in m/d/yy formate.
3. In sql it show date yy/m/d it may create problem in calculation ?
You query is nice, it also calculate Accumulated Manhours.
Accumulated Manhour is OK,
And MH1 data is also OK, BH data is ok.
It retrieve correct data but retrieving double data why ?
But it retrieve/ display some duplicate/duble data.
like this.
DEPT--------------EMPNO----NAME---.......ROJNO--...BH--- MH1--- DATED---ACCUMUATED MH1
------------------------------------------------------------------------------------------
LANDSCAPE SECTION..3066.....Ali Suliman..240..200.......10... 11/26/2004 0:00...80
LANDSCAPE SECTION..3066 ....Ali Suliman..240.. 200.....10......11/26/2004 0:00 ..80
LANDSCAPE SECTION..97751...C. Murray *..240.. 170.. 0..... 11/26/2004 0:00...120
LANDSCAPE SECTION..97990...M. Ginev * 240.. 200.. 0.... 11/26/2004 0:00 ...100
QUANTITIES----- -..34585....K. Sekharan .240.. 200.. 10......11/26/2004 0:00 ...130
QUANTITIES-------..34585....K. Sekharan .240.. .200.. 0.... 11/26/2004 0:00 ...130
QUANTITIES-------..34585....K. Sekharan .240.. 200.. 15.... 11/26/2004 0:00 ...130
QUANTITIES------...98895...R. Minimol * .240.. 180.. 0.... 11/26/2004 0:00 ...110
QUANTITIES.........98895....R. Minimol ..240.. 180.. 16.... 11/26/2004 0:00 ...110
QUANTITIES.........81852....S. Kurain .240.. 170.. 0.......11/26/2004 0:00 ...140
QUANTITIES.........81852....S. Kurain .240.....170.. 0..... 11/26/2004 0:00 ...140
but problem is that it retrieve some dublicate data / dublicate employee data
How I can control this ?
with sincere regards.
Mateen
|
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 07/27/2005 : 08:29:39
|
>>
1. only for data entry I make separate week table.
user select week date from that table ?
2. data entry in m/d/yy formate.
3. In sql it show date yy/m/d it may create problem in calculation ?
If You already have a separate week table You can create a FK constraint between that table and tables using the week date.
This is just to ensure data integrity.
The format 'yyyymmdd' is ISO standard, and SQL Server will always interpret it like yyyymmdd
regardless of regional settings.
>> It retrieve correct data but retrieving double data why ?
Is EMPNO unique in the EMP table?
Check that You don't have duplicate data in any of Your tables, my guess is that that might be the problem.
rockmoose |
|
|
|
mateenmohd
Constraint Violating Yak Guru
United Arab Emirates
297 Posts |
Posted - 07/27/2005 : 09:26:45
|
Thanks for response.
1. I don't have separate week table.
2. Yes empno is unique (PK) in emp table.
no duplicate empno in emp table.
my table structure like this.
EMP TABLE
-----------
RECID...INT
EMPNO..VARCHAR (PK)
NAME...VARCHAR
DEPT...VARCHAR
MH1 TABLE
-----------
RECID...INT (PK)
EMPNO...VARCHAR
PROJNO..VARHCAR
MH1.....INT
DATED...SMALLDATETIME
PROJECT TABLE
-------------
RECID..INT
PROJNO..VARCHAR.(PK)
BH TABLE
--------------
RECID..INT (PK)
EMPNO...VARCHAR
PROJNO..VARCHAR
BH......INT
BH table => one employee work multiple project
we assign all employees budged manhour
problem in my table structure ?
regards.
Mateen
|
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 07/27/2005 : 09:39:41
|
Post the query that gave the duplicate data as well.
rockmoose |
|
|
|
mateenmohd
Constraint Violating Yak Guru
United Arab Emirates
297 Posts |
Posted - 07/27/2005 : 09:43:42
|
Query is..
SELECT
emp.DEPT
,mh1.EMPNO
,emp.name
,mh1.PROJNO
,( SELECT BH
FROM BH bh
WHERE mh1.EMPNO = bh.EMPNO
AND mh1.PROJNO = bh.PROJNO
AND YEAR(mh1.DATED) = YEAR(bh.DATED) )AS [BH]
,mh1.MH1
,mh1.DATED
,( SELECT SUM(MH1)
FROM MH1 mh2
WHERE mh1.EMPNO = mh2.EMPNO
AND mh1.PROJNO = mh2.PROJNO
AND YEAR(mh1.DATED) = YEAR(mh2.DATED)
AND mh1.DATED >= mh2.DATED ) AS AccumulatedMH1
FROM
MH1 mh1
JOIN EMP emp
ON mh1.EMPNO = emp.EMPNO
WHERE
mh1.projno='233'
AND mh1.DATED = '11/26/2004'
ORDER BY
emp.DEPT
,emp.name
,mh1.PROJNO
regards.
Mateen
|
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 07/27/2005 : 09:50:02
|
What do these queries give ?
Each should retrieve 1 record.
SELECT * FROM EMP WHERE EMPNO = 3066
SELECT * FROM MH1 WHERE EMPNO = 3066 PROJNO = 233 AND DATED = '11/26/2004'
Also try with PROJNO = 240, and maybe some other EMPNO + DATED that You recieved duplicates for.
rockmoose |
|
|
|
mateenmohd
Constraint Violating Yak Guru
United Arab Emirates
297 Posts |
Posted - 07/28/2005 : 08:04:25
|
Excellent Query. I really appreciate your sql query written skill.
You also reduce my un-necessary data entries to insert zero hours
by left join and coalesce clause.
Query is nice. it given right result, BH, MH1 and Accumulated Manhours.
I make a table theweekdate and store weekly dates ie.
12/2/2004, 12/9/2004, 12/16/2004,......,1/6/2005,....1/13/2005,..
Problem was in my MH1 dates, there was doublicate dates store. Now it
Is ok.
One problem is there. Query is given right result. If MH1 dates 2004, Weekly data of MH1 2004 is ok.
But when I use current year date 2005 ie.
SELECT
emp.DEPT
,emp.EMPNO
,emp.name
,proj.PROJNO
,( SELECT BH
FROM BH bh
WHERE mh1.EMPNO = bh.EMPNO
AND mh1.PROJNO = bh.PROJNO
AND YEAR(mh1.DATED) = YEAR(bh.DATED) )AS [BH]
,COALESCE( mh1.MH1 , 0 ) AS MH1
,theWeekDate.wk AS DATED
,( SELECT SUM(MH1)
FROM MH1 mh2
WHERE mh1.EMPNO = mh2.EMPNO
AND mh1.PROJNO = mh2.PROJNO
AND YEAR(mh1.DATED) = YEAR(mh2.DATED)
AND mh1.DATED >= mh2.DATED ) AS AccumulatedMH1
FROM
EMP emp
CROSS JOIN PROJECT proj
CROSS JOIN ( SELECT CAST('1/6/2005' AS DATETIME) AS wk ) theWeekDate
LEFT JOIN MH1 mh1
ON emp.EMPNO = mh1.EMPNO
AND proj.PROJNO = mh1.PROJNO
AND theWeekDate.wk = mh1.DATED
WHERE
proj.PROJNO = '233'
ORDER BY
emp.DEPT
,emp.name
,proj.PROJNO
then it not retrieved AccummulatedMH and BH data why ?
it return null in BH,AccummulatedMH.
it retrieving projno,empno,name,dept.
our project continue two/thee years.
there is also MH1.dated ie. 1/6/2005, 1/13/2005,....in MH1 table.
what change in query regarding next year date,
it should be continue retrived data next year ie. 2005,
it should not depend one year date.
...
AND YEAR(mh1.DATED) = YEAR(mh2.DATED)
AND mh1.DATED >= mh2.DATED ) AS AccumulatedMH1
...
with sincere regards.
Mateen
|
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 07/28/2005 : 08:27:53
|
Have You tried removing this:
AND YEAR(mh1.DATED) = YEAR(mh2.DATED)
From the query.
That should solve the AccumulatedMH problem. Does it?
I think we might need to alter the query a little to get the BH for every year.
Am I right in thinking that BH has to be accumulated as well ? (BH for 2004 + BH for 2005)
rockmoose |
|
|
|
mateenmohd
Constraint Violating Yak Guru
United Arab Emirates
297 Posts |
Posted - 07/28/2005 : 09:16:24
|
I tried removing
AND YEAR(mh1.DATED) = YEAR(mh2.DATED)
but it not retrieve AccumulatedMH and BH.
Please remove date depending query of BH (Budgeted date)
in BH table only three fields
ie.
BH table
----------
empno
projno
bh
there is no date field in BH table.
we are not depend on BH date.
management assign each employee to BH budgeted hour and
project. and want to see what is the weekly progress of each employee
on each project.
budgeted hour date create problem.
I check some other projects, it create problem to retrive accumulatedmh1 and BH, some BH and accumulatedMH column display null value. when as in the MH1 and BH table values are store.
Yes (BH for 2004 + BH for 2005) should be accumulated to next
year.
Please help to generate query which not depend BH date ?
with regards.
Mateen
|
|
|
|
rockmoose
SQL Natt Alfen
Sweden
3279 Posts |
Posted - 07/28/2005 : 10:03:29
|
quote:
Please remove date depending query of BH (Budgeted date)
in BH table only three fields
ie.
BH table
----------
empno
projno
bh
Ok, so there is no date in BH table.
So if a Project takes 3 years, You put the total # of bh for the project per employee in the table.
(For the whole 3 years?)
Try:
SELECT
emp.DEPT
,emp.EMPNO
,emp.name
,proj.PROJNO
,( SELECT COALESCE(BH,0)
FROM BH bh
WHERE mh1.EMPNO = bh.EMPNO
AND mh1.PROJNO = bh.PROJNO ) AS [BH]
,COALESCE( mh1.MH1 , 0 ) AS MH1
,theWeekDate.wk AS DATED
,( SELECT COALESCE(SUM(MH1),0)
FROM MH1 mh2
WHERE mh1.EMPNO = mh2.EMPNO
AND mh1.PROJNO = mh2.PROJNO
AND mh1.DATED >= mh2.DATED ) AS AccumulatedMH1
FROM
EMP emp
CROSS JOIN PROJECT proj
CROSS JOIN ( SELECT CAST('1/6/2005' AS DATETIME) AS wk ) theWeekDate
LEFT JOIN MH1 mh1
ON emp.EMPNO = mh1.EMPNO
AND proj.PROJNO = mh1.PROJNO
AND theWeekDate.wk = mh1.DATED
WHERE
proj.PROJNO = '233'
ORDER BY
emp.DEPT
,emp.name
,proj.PROJNO
rockmoose |
|
|
|
Topic |
|
|
|
Accummulated Man-Hours
Forum Locked
