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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Union with Multiple Where statment need help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hbadministrator
Posting Yak Master

109 Posts

Posted - 12/06/2012 :  10:36:05  Show Profile  Reply with Quote
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 Date

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]

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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
0019 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 AM
1000 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 AM
1000 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 AM
1000 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 AM
1001 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 AM
1001 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 AM
1001 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 AM
1001 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 AM
1001 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 AM
1001 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 AM
1001 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 AM
1001 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 AM
1001 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 AM
1001 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 AM
1001 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 AM
1001 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 AM
1001 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 AM
16025 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 EstimateHours
600044 0019 1374.00
600044 0040 0.00
600044 0050 0.00
600044 0060 0.00
600044 0070 0.00
600044 1000 0.00
600044 1000L 84.00
600044 1001 0.00
600044 1001L 10.00
600044 11004 0.00
600044 16025 0.00
600044 18000 0.00
600044 18000L 10.00
600044 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, Null
0019 Null 600044 Null Null Null Null Null Null Null Null Null Null Null 1374.00

Edited by - hbadministrator on 12/06/2012 11:44:07

shilpash
Yak Posting Veteran

90 Posts

Posted - 12/06/2012 :  11:19:57  Show Profile  Reply with Quote
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')
Go to Top of Page

hbadministrator
Posting Yak Master

109 Posts

Posted - 12/06/2012 :  11:29:13  Show Profile  Reply with Quote
that does not work gets me 46779 total records and I should only get 80.
Go to Top of Page

hbadministrator
Posting Yak Master

109 Posts

Posted - 12/06/2012 :  11:30:18  Show Profile  Reply with Quote
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 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]
WHERE (joblabordetail.[div-code] = '10') AND (joblabordetail.TransactionDate BETWEEN @StartDate AND @EndDate) AND (job.[Job-no] = '600044')
UNION
SELECT 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,
EstimateHours
FROM JobCostEstimate
WHERE ([job-no] = '600044')
Go to Top of Page

hbadministrator
Posting Yak Master

109 Posts

Posted - 12/06/2012 :  11:43:32  Show Profile  Reply with Quote
I am so damn close lol.
Go to Top of Page

shilpash
Yak Posting Veteran

90 Posts

Posted - 12/06/2012 :  11:45:54  Show Profile  Reply with Quote
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??

Edited by - shilpash on 12/06/2012 12:00:14
Go to Top of Page

hbadministrator
Posting Yak Master

109 Posts

Posted - 12/06/2012 :  11:57:09  Show Profile  Reply with Quote
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.
Go to Top of Page

hbadministrator
Posting Yak Master

109 Posts

Posted - 12/06/2012 :  13:30:32  Show Profile  Reply with Quote
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 JOBNO1
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]
WHERE (joblabordetail.[div-code] = '10') AND (joblabordetail.TransactionDate BETWEEN @StartDate AND @EndDate) AND (joblabordetail.[job-no] LIKE @job)
UNION
SELECT 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 JOBNO
FROM JobCostEstimate
WHERE ([job-no] LIKE @job2)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000