Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
hbadministrator
Posting Yak Master
120 Posts |
Posted - 2012-12-06 : 10:36:05
|
Hello I am sure you can help me with this and I'm sure it will be very easy for you. I have 4 tables 3 of them I have are working and joined correctly and when I try to add the last table i get repeated data. What I am trying to do is take the first 3 tables that produce a total of 66 rows and the 4th table and that produces another 14 rows. I want those 14 lines added to the 66 lines. Now for the kicker I am using a Date between for the 1st 3 tables to get those 64 records and that date does not correlate with the 4th table. The only 2 items that match up would be Job-no and also ccode. Below Is the code I have for the 1st 3 tables and ill give you the table name for the 4th table and what fields I want to show. Also I am giving you header, sample data from the 3 tables joined and also the other table I am trying to add as well. The last part at the very bottom is a sample of what I would like that data to look like with header.SELECT joblabordetail.[Cost-Code] AS CCode, joblabordetail.[div-code] AS DIVCODE, job.[Job-no] AS JOBNO, job.Description, joblabordetail.EmployeeName,[cost-code].Description AS Expr1, joblabordetail.TotalHours, joblabordetail.RegularHours, joblabordetail.OverTimeHours,joblabordetail.TotalEarnings, joblabordetail.TotalFringes, joblabordetail.TotalPayroll, joblabordetail.RegularEarnings, joblabordetail.TransactionDate AS DateFROM [cost-code] AS [cost-code] LEFT OUTER JOIN joblabordetail AS joblabordetail ON [cost-code].[Cost-code] = joblabordetail.[Cost-Code] LEFT OUTER JOIN job AS job ON joblabordetail.[job-no] = job.[Job-no]WHERE (joblabordetail.[div-code] = '10') AND (joblabordetail.TransactionDate BETWEEN @StartDate AND @EndDate) AND (job.[Job-no] = '600044')Header------------------------------------------------------------CCode, DIVCODE, JOBNO, Description, EmployeeName, Expr1, TotalHours, RegularHours, OverTimeHours, TotalEarnings, TotalFringes, TotalPayroll, RegularEarnings, Date--------------------------------------------------------------Sample Data first 3 tables----------------------------------------------------------------0019 10 600044 AC - BOYS & GIRLS CLUB Alan S Appleby Labor 7.00 7.00 0.00 176.75 24.30 26.37 176.75 11/4/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Bradley Hardy Labor 16.00 16.00 0.00 256.00 35.20 38.20 256.00 10/14/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Bradley Hardy Labor 40.00 40.00 0.00 640.00 88.00 95.49 640.00 10/21/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Bradley Hardy Labor 40.00 40.00 0.00 640.00 88.00 95.49 640.00 10/28/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Bradley Hardy Labor 30.00 30.00 0.00 480.00 66.00 71.62 480.00 11/4/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Bradley Hardy Labor 40.00 40.00 0.00 640.00 88.00 95.49 640.00 11/11/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Wesley L Schumacher Labor 24.00 24.00 0.00 354.00 48.68 52.82 354.00 10/7/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Ryan M. Kunkel Labor 40.00 40.00 0.00 480.00 66.00 71.62 480.00 11/18/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Ryan M. Kunkel Labor 6.50 0.00 6.50 117.00 16.09 17.46 0.00 11/18/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Ryan M. Kunkel Labor 10.50 10.50 0.00 126.00 17.33 18.80 126.00 11/25/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Ryan M. Kunkel Labor 40.00 40.00 0.00 480.00 66.00 71.62 480.00 12/2/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Steven W Dyarman Labor 6.00 6.00 0.00 132.06 18.16 19.70 132.06 11/4/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Tobias A Starner Labor 6.00 6.00 0.00 129.78 17.84 19.36 129.78 11/4/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Ryan M. Kunkel Labor 32.00 32.00 0.00 384.00 52.80 57.29 384.00 10/7/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Ryan M. Kunkel Labor 40.00 40.00 0.00 480.00 66.00 71.62 480.00 10/14/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Ryan M. Kunkel Labor 40.00 40.00 0.00 480.00 66.00 71.62 480.00 10/21/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Ryan M. Kunkel Labor 40.00 40.00 0.00 480.00 66.00 71.62 480.00 10/28/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Ryan M. Kunkel Labor 30.00 30.00 0.00 360.00 49.50 53.71 360.00 11/4/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Ryan M. Kunkel Labor 40.00 40.00 0.00 480.00 66.00 71.62 480.00 11/11/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Joey W. Swartz Labor 1.00 1.00 0.00 33.50 4.61 5.00 33.50 10/28/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Joey W. Swartz Labor 1.00 1.00 0.00 33.50 4.61 5.00 33.50 11/4/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Joey W. Swartz Labor 1.00 1.00 0.00 33.50 4.61 5.00 33.50 11/11/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Joey W. Swartz Labor 1.00 1.00 0.00 33.50 4.61 5.00 33.50 11/18/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Joey W. Swartz Labor 1.00 1.00 0.00 33.50 4.61 5.00 33.50 12/2/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Ryan M. Kunkel Labor 8.00 8.00 0.00 96.00 13.20 14.32 96.00 9/30/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Jeff J Feuchtenberger Labor 17.00 17.00 0.00 411.57 56.59 61.41 411.57 11/4/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Jeff J Feuchtenberger Labor 34.00 34.00 0.00 823.14 113.18 122.81 823.14 11/11/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Jeff J Feuchtenberger Labor 32.50 32.50 0.00 786.83 108.19 117.40 786.83 11/18/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Jeff J Feuchtenberger Labor 7.00 0.00 7.00 254.24 34.96 37.93 0.00 11/18/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Jeff J Feuchtenberger Labor 31.50 31.50 0.00 762.62 104.86 113.78 762.62 11/25/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Joey W. Swartz Labor 1.00 1.00 0.00 33.50 4.61 5.00 33.50 10/21/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB John Bingaman Labor 8.00 8.00 0.00 112.00 15.40 16.71 112.00 11/25/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Jeff J Feuchtenberger Labor 24.00 24.00 0.00 581.04 79.89 86.69 581.04 9/30/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Jeff J Feuchtenberger Labor 32.00 32.00 0.00 774.72 106.52 115.59 774.72 10/7/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Jeff J Feuchtenberger Labor 31.00 31.00 0.00 750.51 103.20 111.98 750.51 10/14/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Jeff J Feuchtenberger Labor 30.50 30.50 0.00 738.41 101.53 110.17 738.41 10/21/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Jeff J Feuchtenberger Labor 16.00 16.00 0.00 387.36 53.26 57.79 387.36 10/28/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Douglas Drake Labor 4.00 4.00 0.00 110.40 15.18 16.47 110.40 10/14/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Douglas Drake Labor 8.00 8.00 0.00 220.80 30.36 32.94 220.80 10/21/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Douglas Drake Labor 1.00 1.00 0.00 27.60 3.80 4.12 27.60 10/28/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Douglas Drake Labor 4.00 4.00 0.00 110.40 15.18 16.47 110.40 11/4/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Daniel Smith Labor 6.00 6.00 0.00 90.90 12.50 13.56 90.90 11/4/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB John Bingaman Labor 8.00 8.00 0.00 112.00 15.40 16.71 112.00 11/18/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Bradley Hardy Labor 40.00 40.00 0.00 640.00 88.00 95.49 640.00 11/18/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Bradley Hardy Labor 6.50 0.00 6.50 156.00 21.45 23.28 0.00 11/18/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Bradley Hardy Labor 31.50 31.50 0.00 504.00 69.30 75.20 504.00 11/25/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Bradley Hardy Labor 32.00 32.00 0.00 512.00 70.40 76.39 512.00 12/2/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Douglas Drake Labor 17.50 17.50 0.00 483.00 66.41 72.06 483.00 9/30/2012 12:00:00 AM0019 10 600044 AC - BOYS & GIRLS CLUB Douglas Drake Labor 4.00 4.00 0.00 110.40 15.18 16.47 110.40 10/7/2012 12:00:00 AM1000 10 600044 AC - BOYS & GIRLS CLUB Eric Dupert Supervisor 4.00 4.00 0.00 136.00 18.70 20.29 136.00 10/26/2012 12:00:00 AM1000 10 600044 AC - BOYS & GIRLS CLUB Eric Dupert Supervisor 4.00 4.00 0.00 136.00 18.70 20.29 136.00 11/9/2012 12:00:00 AM1000 10 600044 AC - BOYS & GIRLS CLUB Eric Dupert Supervisor 10.00 10.00 0.00 340.00 46.75 50.73 340.00 11/16/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Nikki Wonder PM Assistant 0.25 0.25 0.00 3.25 0.00 0.00 3.25 9/11/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Nikki Wonder PM Assistant 0.50 0.50 0.00 6.50 0.89 0.97 6.50 9/21/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Nikki Wonder PM Assistant 1.25 1.25 0.00 16.25 2.23 2.42 16.25 9/28/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Nikki Wonder PM Assistant 1.00 1.00 0.00 13.00 1.79 1.94 13.00 10/12/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Nikki Wonder PM Assistant 1.25 1.25 0.00 16.25 2.23 2.42 16.25 10/19/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Nikki Wonder PM Assistant 0.75 0.75 0.00 9.75 1.34 1.45 9.75 10/26/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Tony Good PM Assistant 0.25 0.25 0.00 3.38 0.46 0.50 3.38 10/14/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Nikki Wonder PM Assistant 1.25 1.25 0.00 16.25 2.23 2.42 16.25 11/2/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Nikki Wonder PM Assistant 0.75 0.75 0.00 9.75 1.34 1.45 9.75 11/9/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Nikki Wonder PM Assistant 1.25 1.25 0.00 16.25 2.23 2.42 16.25 11/16/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Nikki Wonder PM Assistant 0.50 0.50 0.00 6.50 0.89 0.97 6.50 11/23/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Nikki Wonder PM Assistant 1.25 1.25 0.00 16.25 2.23 2.42 16.25 11/30/2012 12:00:00 AM1001 10 600044 AC - BOYS & GIRLS CLUB Tony Good PM Assistant 0.50 0.50 0.00 6.75 0.93 1.01 6.75 10/7/2012 12:00:00 AM16025 10 600044 AC - BOYS & GIRLS CLUB Tyler Doran Pre-Fab Hangers 1.00 1.00 0.00 15.00 2.53 1.83 15.00 10/14/2012 12:00:00 AM----------------------------------------------------------------Sample Data 4th table >>>>>Table Name (JobCostEstimate) <<<<<<<----------------------------------------------------------------job-no CostCode EstimateHours600044 0019 1374.00600044 0040 0.00600044 0050 0.00600044 0060 0.00600044 0070 0.00600044 1000 0.00600044 1000L 84.00600044 1001 0.00600044 1001L 10.00600044 11004 0.00600044 16025 0.00600044 18000 0.00600044 18000L 10.00600044 2503 0.00----------------------------------------------------------------example Correct data ------------------------------------------------------------------The way I would like it to look.Header---------------------------------------------------------------------CCode, DIVCODE, JOBNO, Description, EmployeeName, Expr1, TotalHours, RegularHours, OverTimeHours, TotalEarnings, TotalFringes, TotalPayroll, RegularEarnings, Date, EstimateHours----------------------------------------------------------------------Example Correct Data (In My Mind)0019 10 600044 AC - BOYS & GIRLS CLUB Alan S Appleby Labor 7.00 7.00 0.00 176.75 24.30 26.37 176.75 11/4/2012 12:00:00 AM, Null0019 Null 600044 Null Null Null Null Null Null Null Null Null Null Null 1374.00 |
|
shilpash
Posting Yak Master
103 Posts |
Posted - 2012-12-06 : 11:19:57
|
SELECT joblabordetail.[Cost-Code] AS CCode ,joblabordetail.[div-code] AS DIVCODE ,job.[Job-no] AS JOBNO ,job.Description ,joblabordetail.EmployeeName ,[cost-code].Description AS Expr1 ,joblabordetail.TotalHours ,joblabordetail.RegularHours ,joblabordetail.OverTimeHours ,joblabordetail.TotalEarnings ,joblabordetail.TotalFringes ,joblabordetail.TotalPayroll ,joblabordetail.RegularEarnings ,joblabordetail.TransactionDate AS Date ,[JobCostEstimate].EstimateHours FROM [cost-code] AS [cost-code] LEFT OUTER JOIN joblabordetail AS joblabordetail ON [cost-code].[Cost-code] = joblabordetail.[Cost-Code] LEFT OUTER JOIN job AS job ON joblabordetail.[job-no] = job.[Job-no] LEFT JOIN [JobCostEstimate] ON [JobCostEstimate].[Cost-code]=[cost-code].[Cost-code] WHERE (joblabordetail.[div-code] = '10') AND (joblabordetail.TransactionDate BETWEEN @StartDate AND @EndDate) AND (job.[Job-no] = '600044') |
|
|
hbadministrator
Posting Yak Master
120 Posts |
Posted - 2012-12-06 : 11:29:13
|
that does not work gets me 46779 total records and I should only get 80. |
|
|
hbadministrator
Posting Yak Master
120 Posts |
Posted - 2012-12-06 : 11:30:18
|
I think i figured it out using a Union but the only issue is I would need to put the Job Number 2 searches any way I can make it 1 search? here is the code.SELECT joblabordetail.[Cost-Code] AS CCode, joblabordetail.[div-code] AS DIVCODE, job.[Job-no] AS JOBNO, job.Description, joblabordetail.EmployeeName, [cost-code].Description AS Expr1, joblabordetail.TotalHours, joblabordetail.RegularHours, joblabordetail.OverTimeHours, joblabordetail.TotalEarnings, joblabordetail.TotalFringes, joblabordetail.TotalPayroll, joblabordetail.RegularEarnings, joblabordetail.TransactionDate AS Date, NULL AS EstimateHoursFROM [cost-code] AS [cost-code] LEFT OUTER JOIN joblabordetail AS joblabordetail ON [cost-code].[Cost-code] = joblabordetail.[Cost-Code] LEFT OUTER JOIN job AS job ON joblabordetail.[job-no] = job.[Job-no]WHERE (joblabordetail.[div-code] = '10') AND (joblabordetail.TransactionDate BETWEEN @StartDate AND @EndDate) AND (job.[Job-no] = '600044')UNIONSELECT CostCode AS CCode, NULL AS DIVCODE, [job-no] AS JOBNO, NULL AS Description, NULL AS EmployeeName, NULL AS Expr1, NULL AS TotalHours, NULL AS RegularHours, NULL AS OverTimeHours, NULL AS TotalEarnings, NULL AS TotalFringes, NULL AS TotalPayroll, NULL AS RegularEarnings, NULL AS Date, EstimateHoursFROM JobCostEstimateWHERE ([job-no] = '600044') |
|
|
hbadministrator
Posting Yak Master
120 Posts |
Posted - 2012-12-06 : 11:43:32
|
I am so damn close lol. |
|
|
shilpash
Posting Yak Master
103 Posts |
Posted - 2012-12-06 : 11:45:54
|
job number 2 means??In your code,,before doing that null as columnname in union figure out why its showing many records or do something where columnname is null,,as ''null as columnname'' doesn't seem good code.Isn't it a hardcoding?? |
|
|
hbadministrator
Posting Yak Master
120 Posts |
Posted - 2012-12-06 : 11:57:09
|
if I only use Job No Where clause in the first select it will show me the 66 from that select statement but in the second select statement it will show me every single record that is in the JobCostEstimate. Where as if i place the second where clause in the 2nd select as well it will give me my 80 records. I am working on creating a view that combines joblabordetail and also the jobcostestimate tables with a union then doing the where statement so it bypasses this issue. Ill keep you posted. |
|
|
hbadministrator
Posting Yak Master
120 Posts |
Posted - 2012-12-06 : 13:30:32
|
Got it working.SELECT joblabordetail.[Cost-Code] AS CCode, joblabordetail.[div-code] AS DIVCODE, job.[Job-no] AS JOBNO, job.Description, joblabordetail.EmployeeName, [cost-code].Description AS Expr1, joblabordetail.TotalHours, joblabordetail.RegularHours, joblabordetail.OverTimeHours, joblabordetail.TotalEarnings, joblabordetail.TotalFringes, joblabordetail.TotalPayroll, joblabordetail.RegularEarnings, joblabordetail.TransactionDate AS Date, NULL AS EstimateHours, NULL AS JOBNO1FROM [cost-code] AS [cost-code] LEFT OUTER JOIN joblabordetail AS joblabordetail ON [cost-code].[Cost-code] = joblabordetail.[Cost-Code] LEFT OUTER JOIN job AS job ON joblabordetail.[job-no] = job.[Job-no]WHERE (joblabordetail.[div-code] = '10') AND (joblabordetail.TransactionDate BETWEEN @StartDate AND @EndDate) AND (joblabordetail.[job-no] LIKE @job)UNIONSELECT CostCode AS CCode, NULL AS DIVCODE, [job-no] AS JOBNO1, NULL AS Description, NULL AS EmployeeName, NULL AS Expr1, NULL AS TotalHours, NULL AS RegularHours, NULL AS OverTimeHours, NULL AS TotalEarnings, NULL AS TotalFringes, NULL AS TotalPayroll, NULL AS RegularEarnings, NULL AS Date, EstimateHours, NULL AS JOBNOFROM JobCostEstimateWHERE ([job-no] LIKE @job2) |
|
|
|
|
|
|
|