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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Union with Multiple Where statment need help

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 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

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')
Go to Top of Page

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.
Go to Top of Page

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 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

120 Posts

Posted - 2012-12-06 : 11:43:32
I am so damn close lol.
Go to Top of Page

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??
Go to Top of Page

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.
Go to Top of Page

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 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
   

- Advertisement -