Author |
Topic |
mateenmohd
Constraint Violating Yak Guru
297 Posts |
Posted - 2005-07-14 : 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]GOCREATE 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]GOCREATE 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]GOCREATE 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]GOData in table (MH1) like this.empno....projno……mh1……dated…….AccummulatedHours---------------------------------------------------012125…240……...10………1/6/2005012125….240……..10………1/13/2005012125….240……..10………1/20/2005012125….240……..10………1/27/2005………..40012125…240….…..10………2/3/2005012125….240……..10………2/10/2005012125….240……..10………2/17/2005012125….240……..10………2/24/2005………80012125…240……. 10……….3/3/2005012125….240……..10………3/10/2005012125….240……..10………3/17/2005012125….240……..10………3/24/2005012125….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…..240012125…..170…..233012125…..200…..967…...(Accummulated column not in tables)How can retrieve the data from above tables like this.Result.Project No.240Empno….BH…..totalHours...Accumulated MH1………date----------------------------------------------------------012125….200……….40………….40. …… 1/27/2005012125….170……….40………….80…………………12/24/2005012125….200………50………….130…………………12/31/2005….……..And so on……BH=Budgeted HoursMH1=Man-hoursTotalhours=total month hourDate= month end date (MH1)What query will use ?Please help.regards.Mateen |
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-14 : 11:14:36
|
Could You provide some insert statements to create test data like in Your example ?rockmoose |
|
|
mateenmohd
Constraint Violating Yak Guru
297 Posts |
Posted - 2005-07-14 : 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
3279 Posts |
Posted - 2005-07-14 : 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 accumulationFROM 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.MTHEMPNO PROJNO BH totalHours DATED Accumulated MH1 ----- ------ --- -------- ------------------------------ --------------- 06367 240 200 40 2005-01-27 00:00:00 4006367 240 170 40 2005-02-24 00:00:00 8006367 240 200 50 2005-03-31 00:00:00 130 rockmoose |
|
|
mateenmohd
Constraint Violating Yak Guru
297 Posts |
Posted - 2005-07-15 : 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-hoursWork 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 accumulationFROM 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.MTHwhere 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 4006367 240 170 40 2005-02-24 00:00:00 8006367 240 200 50 2005-03-31 00:00:00 130Sorry, 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 |
|
|
mateenmohd
Constraint Violating Yak Guru
297 Posts |
Posted - 2005-07-25 : 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…… .….….. 4012455……240…….170…….40……. …………….8012456……240……..180……50 …… …………….13080802……240……..200……60……. …....190SECOND WEEKLY (MH1 DATED = 2005-01-13)EMPNO..PROJNO…BH…..totalHours....Accumulated MH1 ------------------------------------------------ 06367……240……..200……40…… ……. …….. 23012455……240…….170…….50……. ……………….28012456……240……..180……60 …… ….…………….34080802……240……..200……50……. ….......390THIRD WEEKLY (MH1 DATED = 2005-01-20)EMPNO..PROJNO…BH…..totalHours.....Accumulated MH1 ---------------------------------------------------- 06367……240……..200……40…… ....…….. 43012455……240…….170…….50……. ..……………….48012456……240……..180……60 …… ..……………….54080802……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 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-07-25 : 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
297 Posts |
Posted - 2005-07-25 : 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 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-25 : 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
297 Posts |
Posted - 2005-07-26 : 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/2005Highway Dept ------------EMPNO…ROJNO….BH …MH1… Accumulated MH1 ------------------------------------------------------------ 06367….240……200……….10……….1073322…..240…….170……10…………1081986…..240…….200…….10……….1006367….240……200……….10………1073322…..240…….180………0………081986…..240…….190……….0………0Manhour Dated (MH1 date) 1/13/2005Highway Dept--------------------EMPNO…ROJNO….BH…..MH1…….. Accumulated MH1 ------------------------------------------------------------ 06367…. 240………200……….10………….20……….//(10+10=20)73322…..240……..170………10……………2081986…..240……..200……….10……………2006367….240………200……….10……………2073322…..240……..180………10…………….1081986…..240……..190……….10 …………..10Manhour Dated (MH1 date) 1/20/2005Highway Dept------------------EMPNO…ROJNO…BH……MH1…. Accumulated MH1 ------------------------------------------------------------06367….240………200……...10……….30…………….//(20+10=30)73322…..240……..170………10……….3081986…..240……..200……….0………. 2006367….240………200……….0……… 2073322…..240……..180…..…10……….2081986…..240……..190……….10 ……..20…..And so on…..….---------------------------------Manhour Dated (MH1 date) 1/6/2005Transportation Dept ------------------EMPNO…ROJNO….BH………MH1…. Accumulated MH1 ---------------------------------------------------------- 81852….240………200……….10……….1038040…..240……..170………10………..1081714…..240……..200……….10………..1012123….240……..200……….10…..…….1081673…..240……..180………0…………..034838…..240……..190……….0…………..0Manhour Dated (MH1 date) 1/13/2005Transportation Dept------------------EMPNO…ROJNO…BH……MH1…. Accumulated MH1 ------------------------------------------------------------- 81852…240………100…….10………20………….//(10+10=20)38040…..240……..200……10………2081714…..240……..100…….10………2012123….240………200…….10………2081673…..240……..100……10………1034838…..240……..200……10 …….10Manhour Dated (MH1 date) 1/20/2005Transportation Dept------------------EMPNO…ROJNO…BH……MH1….. Accumulated MH1 -------------------------------------------------------------- 81852…..240…….100…….10…………30…………….//(20+10=30)38040…..240……..200……10…………3081714…..240……..100…….0…………. 2012123….240………200…….0.………. 2081673…..240……..100……10………..2034838…..240……..290…….10 ……..20…….And so on….BH will remain same.waiting you response.with regards.Mateen |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-26 : 09:05:53
|
You are storing the week "dates" in the tables for MH1I 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
297 Posts |
Posted - 2005-07-27 : 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...80LANDSCAPE SECTION..3066 ....Ali Suliman..240.. 200.....10......11/26/2004 0:00 ..80LANDSCAPE SECTION..97751...C. Murray *..240.. 170.. 0..... 11/26/2004 0:00...120LANDSCAPE SECTION..97990...M. Ginev * 240.. 200.. 0.... 11/26/2004 0:00 ...100QUANTITIES----- -..34585....K. Sekharan .240.. 200.. 10......11/26/2004 0:00 ...130QUANTITIES-------..34585....K. Sekharan .240.. .200.. 0.... 11/26/2004 0:00 ...130QUANTITIES-------..34585....K. Sekharan .240.. 200.. 15.... 11/26/2004 0:00 ...130QUANTITIES------...98895...R. Minimol * .240.. 180.. 0.... 11/26/2004 0:00 ...110QUANTITIES.........98895....R. Minimol ..240.. 180.. 16.... 11/26/2004 0:00 ...110QUANTITIES.........81852....S. Kurain .240.. 170.. 0.......11/26/2004 0:00 ...140QUANTITIES.........81852....S. Kurain .240.....170.. 0..... 11/26/2004 0:00 ...140but problem is that it retrieve some dublicate data / dublicate employee dataHow I can control this ?with sincere regards.Mateen |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-27 : 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 yyyymmddregardless 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
297 Posts |
Posted - 2005-07-27 : 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...INTEMPNO..VARCHAR (PK)NAME...VARCHARDEPT...VARCHARMH1 TABLE-----------RECID...INT (PK)EMPNO...VARCHARPROJNO..VARHCARMH1.....INTDATED...SMALLDATETIMEPROJECT TABLE-------------RECID..INTPROJNO..VARCHAR.(PK)BH TABLE--------------RECID..INT (PK)EMPNO...VARCHARPROJNO..VARCHARBH......INTBH table => one employee work multiple project we assign all employees budged manhourproblem in my table structure ?regards.Mateen |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-27 : 09:39:41
|
Post the query that gave the duplicate data as well.rockmoose |
|
|
mateenmohd
Constraint Violating Yak Guru
297 Posts |
Posted - 2005-07-27 : 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 AccumulatedMH1FROM MH1 mh1 JOIN EMP emp ON mh1.EMPNO = emp.EMPNOWHERE mh1.projno='233' AND mh1.DATED = '11/26/2004' ORDER BY emp.DEPT ,emp.name ,mh1.PROJNOregards.Mateen |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-27 : 09:50:02
|
What do these queries give ?Each should retrieve 1 record.SELECT * FROM EMP WHERE EMPNO = 3066SELECT * 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
297 Posts |
Posted - 2005-07-28 : 08:04:25
|
Excellent Query. I really appreciate your sql query written skill.You also reduce my un-necessary data entries to insert zero hoursby 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 itIs 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 AccumulatedMH1FROM 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.DATEDWHERE proj.PROJNO = '233' ORDER BY emp.DEPT ,emp.name ,proj.PROJNOthen 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
3279 Posts |
Posted - 2005-07-28 : 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
297 Posts |
Posted - 2005-07-28 : 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 fieldsie.BH table----------empnoprojnobhthere is no date field in BH table.we are not depend on BH date.management assign each employee to BH budgeted hour andproject. and want to see what is the weekly progress of each employeeon 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
3279 Posts |
Posted - 2005-07-28 : 10:03:29
|
quote: Please remove date depending query of BH (Budgeted date)in BH table only three fieldsie.BH table----------empnoprojnobh
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 AccumulatedMH1FROM 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.DATEDWHERE proj.PROJNO = '233'ORDER BY emp.DEPT ,emp.name ,proj.PROJNO rockmoose |
|
|
Next Page
|
|
|