SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Accummulated Man-Hours
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

mateenmohd
Constraint Violating Yak Guru

United Arab Emirates
297 Posts

Posted - 07/14/2005 :  11:02:56  Show Profile
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  Show Profile
Could You provide some insert statements to create test data like in Your example ?

rockmoose
Go to Top of Page

mateenmohd
Constraint Violating Yak Guru

United Arab Emirates
297 Posts

Posted - 07/14/2005 :  11:34:47  Show Profile
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





Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 07/14/2005 :  13:11:36  Show Profile
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
Go to Top of Page

mateenmohd
Constraint Violating Yak Guru

United Arab Emirates
297 Posts

Posted - 07/15/2005 :  10:28:10  Show Profile
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
Go to Top of Page

mateenmohd
Constraint Violating Yak Guru

United Arab Emirates
297 Posts

Posted - 07/25/2005 :  08:21:30  Show Profile
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/25/2005 :  08:57:04  Show Profile  Visit jsmith8858's Homepage
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
Go to Top of Page

mateenmohd
Constraint Violating Yak Guru

United Arab Emirates
297 Posts

Posted - 07/25/2005 :  10:03:03  Show Profile
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 07/25/2005 :  21:49:22  Show Profile
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
Go to Top of Page

mateenmohd
Constraint Violating Yak Guru

United Arab Emirates
297 Posts

Posted - 07/26/2005 :  02:03:19  Show Profile
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


Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 07/26/2005 :  09:05:53  Show Profile
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
Go to Top of Page

mateenmohd
Constraint Violating Yak Guru

United Arab Emirates
297 Posts

Posted - 07/27/2005 :  08:05:05  Show Profile
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









Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 07/27/2005 :  08:29:39  Show Profile
>>
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
Go to Top of Page

mateenmohd
Constraint Violating Yak Guru

United Arab Emirates
297 Posts

Posted - 07/27/2005 :  09:26:45  Show Profile
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

Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 07/27/2005 :  09:39:41  Show Profile
Post the query that gave the duplicate data as well.

rockmoose
Go to Top of Page

mateenmohd
Constraint Violating Yak Guru

United Arab Emirates
297 Posts

Posted - 07/27/2005 :  09:43:42  Show Profile
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
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 07/27/2005 :  09:50:02  Show Profile
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
Go to Top of Page

mateenmohd
Constraint Violating Yak Guru

United Arab Emirates
297 Posts

Posted - 07/28/2005 :  08:04:25  Show Profile
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






Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 07/28/2005 :  08:27:53  Show Profile
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
Go to Top of Page

mateenmohd
Constraint Violating Yak Guru

United Arab Emirates
297 Posts

Posted - 07/28/2005 :  09:16:24  Show Profile
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

Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 07/28/2005 :  10:03:29  Show Profile
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000