[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
Question
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
7.8

Need help with SQL Query

Asked by redcelltech in MS SQL Server, MS SQL DTS

Tags: query, sql, bill, excel

I have been asked to look into this query and I need some help. But first I will give some background.

This company installs cable televison into customer homes for most of the large cable providers. Work orders for the day are viewed and printed from the cable provider system. They will not allows us to electronically transfer the data. When the work order has been performed the following information is produced.

Table 1 - Provider information:
Table 1 is generated from an excel spread sheet given to the customer by the service provider and contains records of COMPLETED service orders. This is then populated into table 1 using DTS. The provider compiles this information as it thinks orders are completed.

Table 2: - Customer information
Table 2 is generated by my client inputing data into an internal application.

Here are some of the challenges with the two data sets.

Close date may be different. Meaning the cable provider may not close the job for several days while the information makes its way through their system.

Quantities of job/bill codes.

Missing service records.

They want to have the ability to run a report to find the discrepancies between the two tables. They are current using the following query with mixed results. Meaning it does not find all of the differences between the two fields. When this script is run is compares the two tables and populates a third field, invoice detail. This should contain the difference between the two tables.

A manual audit of one weeks data was performed that found numerous entries that the script did not find.

I hope this make sense, if not please ask. Any help would be appreciated.


Thanks,

Tennyson 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:

--identify invoice(s) and dates in question - dates based off of BH invoice dates and adjusted accordingly
DECLARE @StartDate	datetime
,@EndDate	datetime
,@BHInvID	int
 
SELECT @StartDate='20070929 00:00:00'
,@EndDate='20071005 00:00:00'
,@BHInvID=40
 
--get the office IDs and split sub name for the BH invoice
 
UPDATE tblBHInvDetail SET OfficeID=
	CASE OfficeName
		WHEN 'Hillsborough' THEN 4
		WHEN 'Lecanto' THEN 7
		WHEN 'Polk' THEN 5
	END
	, LastName=
	CASE CHARINDEX(',', SubName)
		WHEN 0 THEN SubName
		ELSE LEFT(SubName, CHARINDEX(',', SubName) - 1)
	END
	, FirstName=
	CASE CHARINDEX(',', SubName)
		WHEN 0 THEN NULL
		ELSE RIGHT(SubName, LEN(SubName) - CHARINDEX(',', SubName))
	END
 
--pull any jobs for the time period
INSERT INTO tblKLInvDetail (JobID
, JobQuanID
, OfficeID
, LastName
, FirstName
, Address
, JobNumber
, CloseDate
, BillCode
, Quantity
, Billing
, CompDate
, BillingStatus
, OrderNumber
)
SELECT
j.JobID
, q.JobQuanID
, j.OffID
, c.LastName
, c.FirstName
, c.Address
, j.JobNumber
, j.EnteredDate
, b.BillCode
, q.Quan
, b.Billing
, j.InstallDate
, 5
, j.RefNumber
FROM tblJobs j
	INNER JOIN tblJobsQuan q 
		INNER JOIN tblBillCodes b ON q.BCID=b.BCID
	ON j.JobID=q.JobID
	INNER JOIN tblCustomers c ON c.CustID=j.CustID
	LEFT JOIN tblJobTypes t ON j.JobTypeID=t.JobTypeID
WHERE j.InstallDate >= @StartDate 
	AND j.InstallDate < DATEADD(d, 1, @EndDate)
	AND j.ProID IN (357,358,346)
	AND j.QuoteDate IS NULL
	AND t.JobType <> 'INCO'
	AND b.BillCode NOT IN ('INCO')
	AND b.Billing > 0
	AND q.JobQuanID NOT IN (SELECT JobQuanID FROM tblKLInvDetail)
 
UPDATE tblKLInvDetail SET JobNumber=LEFT(JobNumber, 6)
 
--create variables for cursor
DECLARE @BHInvDetailID int
, @JobQuanID int
, @OfficeID int
, @TechNum varchar(50)
, @LastName varchar(50)
, @FirstName varchar(50)
, @Address varchar(75)
, @JobNumber varchar(50)
, @CloseDate datetime
, @BC varchar(50)
, @Quan int
, @CompDate datetime
, @KLInvDetailID int
, @JobID int
, @RefNum varchar(50)
 
SELECT @KLInvDetailID=0, @JobID=0
 
--create cursor using BH invoice
DECLARE InvSearch CURSOR 
	FOR SELECT BHInvDetailID, OfficeID, TechNum, LastName, FirstName, Address, JobNumber, CloseDate, BillCode, Quantity, CompDate, OrderNumber
		FROM tblBHInvDetail 
		WHERE BillingStatus NOT IN (1,2,3)
		AND BHInvID=@BHInvID
		--WHERE CloseDate >= @StartDate 
		--AND CloseDate < @EndDate
		--GROUP BY OfficeID, JobNumber, TechNum, LastName, FirstName, Address, CloseDate, CompDate, OrderNumber
OPEN InvSearch
 
--retrieve first record from cursor
FETCH NEXT FROM InvSearch 
INTO @BHInvDetailID, @OfficeID, @TechNum, @LastName, @FirstName, @Address, @JobNumber, @CloseDate, @BC, @Quan, @CompDate, @RefNum
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
----search KL invoice for job with matching job
	SELECT @JobQuanID=JobQuanID
	FROM tblKLInvDetail
	WHERE OfficeID=@OfficeID
		AND (JobNumber=@JobNumber OR (LastName=@LastName AND FirstName=@FirstName) OR Address=@Address)
		AND BillCode=@BC
		AND Quantity=@Quan
 
----if we have a record:
	IF @JobQuanID <> 0
------mark job as a full match (1)
	BEGIN
		UPDATE tblKLInvDetail SET BillingStatus=1, BHInvID=@BHInvID
		WHERE JobQuanID=@JobQuanID
 
		UPDATE tblBHInvDetail SET BillingStatus=1, KLRecordID=@JobQuanID WHERE BHInvDetailID=@BHInvDetailID
	END
	ELSE --check to see if the bill code is found, but quantity is wrong
	BEGIN
		SELECT @JobQuanID=JobQuanID
		FROM tblKLInvDetail
		WHERE OfficeID=@OfficeID
			AND (JobNumber=@JobNumber OR (LastName=@LastName AND FirstName=@FirstName) OR Address=@Address)
			AND BillCode=@BC
		IF @JobQuanID <> 0
--------mark job as a billing match (2)
		BEGIN
			UPDATE tblKLInvDetail SET BillingStatus=2, BHInvID=@BHInvID
			WHERE JobQuanID=@JobQuanID
 
			UPDATE tblBHInvDetail SET BillingStatus=2, KLRecordID=@JobQuanID WHERE BHInvDetailID=@BHInvDetailID
		END
		ELSE --check to see if the job exists in some form
		BEGIN		
			SELECT @JobQuanID=JobQuanID
			FROM tblKLInvDetail
			WHERE OfficeID=@OfficeID
				AND (JobNumber=@JobNumber OR (LastName=@LastName AND FirstName=@FirstName) OR Address=@Address)
			IF @JobQuanID <> 0
----------mark job as a job match (3)
			BEGIN
	
				UPDATE tblBHInvDetail SET BillingStatus=3, KLRecordID=@JobQuanID WHERE BHInvDetailID=@BHInvDetailID
				
			END
			ELSE
			BEGIN
 
				SELECT @JobQuanID=JobQuanID
				FROM tblKLInvDetail
				WHERE OfficeID=@OfficeID
					AND (UPPER(LEFT(Address, 8))=UPPER(LEFT(@Address, 8)) OR (UPPER(LEFT(LastName, 6))=UPPER(LEFT(@LastName,6)) AND UPPER(LEFT(FirstName, 4))=UPPER(LEFT(@FirstName, 4))))
----if we have a record:
				IF @JobID <> 0
------add the record to the detail table
				BEGIN
	
					UPDATE tblBHInvDetail SET BillingStatus=4, KLRecordID=@JobID WHERE BHInvDetailID=@BHInvDetailID
				END
				ELSE
				BEGIN
					UPDATE tblBHInvDetail SET BillingStatus=5 WHERE BHInvDetailID=@BHInvDetailID
				END
			END
		END
	END
 
----loop
	SELECT @JobID=0
	SELECT @JobQuanID=0
 
	FETCH NEXT FROM InvSearch 
	INTO @BHInvDetailID, @OfficeID, @TechNum, @LastName, @FirstName, @Address, @JobNumber, @CloseDate, @BC, @Quan, @CompDate, @RefNum
 
END
--close cursor
CLOSE InvSearch
DEALLOCATE InvSearch
 
 
--create cursor using any unfound KL jobs (does not perform complete billing)
DECLARE InvDetailSearch CURSOR 
	FOR SELECT JobQuanID, OfficeID, TechNum, LastName, FirstName, Address, JobNumber, CloseDate, CompDate 
		FROM tblKLInvDetail 
		WHERE BillingStatus NOT IN (1,2,3)
		AND CompDate >= DATEADD(d, -1, @StartDate)
		AND CompDate < @EndDate
 
 
OPEN InvDetailSearch
 
--retrieve first record from cursor
FETCH NEXT FROM InvDetailSearch 
INTO @JobQuanID, @OfficeID, @TechNum, @LastName, @FirstName, @Address, @JobNumber, @CloseDate, @CompDate
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
	SELECT @BHInvDetailID=0
	
	SELECT @BHInvDetailID=BHInvDetailID
	FROM tblBHInvDetail
	WHERE OfficeID=@OfficeID
		AND (JobNumber=@JobNumber OR (LastName=@LastName AND FirstName=@FirstName) OR Address=@Address)
		AND BillingStatus=3
	IF @BHInvDetailID <> 0
----------mark job as a job match (3)
	BEGIN
	
		UPDATE tblKLInvDetail SET BillingStatus=3, BHInvID=@BHInvID WHERE JobQUanID=@JobQUanID
				
	END
	ELSE
	BEGIN
 
		SELECT @BHInvDetailID=BHInvDetailID
		FROM tblBHInvDetail
		WHERE OfficeID=@OfficeID
		AND (UPPER(LEFT(Address, 8))=UPPER(LEFT(@Address, 8)) OR (UPPER(LEFT(LastName, 6))=UPPER(LEFT(@LastName,6)) AND UPPER(LEFT(FirstName, 4))=UPPER(LEFT(@FirstName, 4))))
		AND BillingStatus=4
----if we have a record:
		IF @BHInvDetailID <> 0
------add the record to the detail table
		BEGIN
	
			UPDATE tblKLInvDetail SET BillingStatus=4, BHInvID=@BHInvID WHERE JobQuanID=@JobQuanID
		END
		ELSE
		BEGIN
			UPDATE tblKLInvDetail SET BillingStatus=5 WHERE JobQuanID=@JobQuanID
		END
	END
 
	FETCH NEXT FROM InvDetailSearch 
	INTO @JobQuanID, @OfficeID, @TechNum, @LastName, @FirstName, @Address, @JobNumber, @CloseDate, @CompDate
 
END
 
--close cursor
CLOSE InvDetailSearch
DEALLOCATE InvDetailSearch
 
--mark any KL records not found
UPDATE tblKLInvDetail
SET BillingStatus=5 
WHERE BillingStatus=0 OR BillingStatus IS NULL
 
-- Jobs found - BH side
--SELECT OfficeID, JobNumber, Address, FirstName, LastName, CompDate, CloseDate, CompDate
--FROM tblBHInvDetail
--WHERE BillingStatus=1
--ORDER BY OfficeID, JobNumber
 
--Jobs found - KL side
--SELECT OfficeID, JobNumber, Address, FirstName, LastName, CompDate, CloseDate, CompDate
--FROM tblKLInvDetail
--WHERE kl.BillingStatus=1
--ORDER BY OfficeID, JobNumber
 
--BH Discrepancies
--SELECT CASE OfficeID
--	WHEN 4 THEN 'Hillsborough'
--	WHEN 5 THEN 'Polk'
--	ELSE 'Lecanto'
--END AS Office,
--TechNum, JobNumber, CHAR(39) + OrderNumber AS OrderNumber, Address, FirstName, LastName, BillCode, Quantity, Billing, CompDate, CloseDate, KLRecordID AS JobQuanID, BillingStatus
--FROM tblBHInvDetail
--WHERE BHInvID=@BHInvID
--ORDER BY OfficeID, BillingStatus, CloseDate, TechNum, JobNumber, OrderNumber
 
--KL Discrepancies
--SELECT CASE i.OfficeID
--	WHEN 4 THEN 'Hillsborough'
--	WHEN 5 THEN 'Polk'
--	ELSE 'Lecanto'
--END AS Office,
--e.CompanyID, i.JobNumber, CHAR(39) + OrderNumber AS OrderNumber, i.Address, i.FirstName, i.LastName, i.BillCode, i.Quantity, i.Billing, i.CompDate, i.CloseDate, JobQuanID, BillingStatus
--FROM tblKLInvDetail i
--	INNER JOIN tblJobs j
--		INNER JOIN tblEmployees e ON j.EmpID=e.EmpID
--	ON i.JobID=j.JobID
--WHERE i.CompDate >= DATEADD(d, -1, @StartDate)
--	AND i.CompDate < @EndDate
--ORDER BY i.OfficeID, BillingStatus, i.CompDate, i.CloseDate, e.CompanyID, i.JobNumber, i.OrderNumber
 
--ALL BH jobs that match something on KL invoice
--SELECT CASE OfficeID
--	WHEN 4 THEN 'Hillsborough'
--	WHEN 5 THEN 'Polk'
--	ELSE 'Lecanto'
--END AS Office,
--TechNum, JobNumber, CHAR(39) + OrderNumber AS OrderNumber, Address, FirstName, LastName, BillCode, Quantity, Billing, CompDate, CloseDate, KLRecordID AS JobQuanID, BillingStatus
--FROM tblBHInvDetail
--WHERE KLRecordID IS NOT NULL
--AND BHInvID = @BHInvID
--ORDER BY OfficeID, BillingStatus, CloseDate, TechNum, JobNumber, OrderNumber
 
--All KL jobs that match a BH record
--SELECT CASE i.OfficeID
--	WHEN 4 THEN 'Hillsborough'
--	WHEN 5 THEN 'Polk'
--	ELSE 'Lecanto'
--END AS Office,
--e.CompanyID, i.JobNumber, CHAR(39) + OrderNumber AS OrderNumber, i.Address, i.FirstName, i.LastName, i.BillCode, i.Quantity, i.Billing, i.CompDate, i.CloseDate, JobQuanID, BillingStatus
--FROM tblKLInvDetail i
--	INNER JOIN tblJobs j
--		INNER JOIN tblEmployees e ON j.EmpID=e.EmpID
--	ON i.JobID=j.JobID
--WHERE BHInvID = @BHInvID
--ORDER BY i.OfficeID, BillingStatus, i.CompDate, i.CloseDate, e.CompanyID, i.JobNumber, i.OrderNumber
 
SELECT Count(*) as BHTotalCount from tblBHInvDetail
WHERE BHInvID=@BHInvID
 
SELECT Count(*) as KLTotalCount from tblKLInvDetail
WHERE CompDate >= @StartDate
AND CompDate < DATEADD(d, 1, @EndDate)
 
--1 complete match
--2 bill code matches, quantity does not
--3 job matches, billing does not
--4 partial job match only, no billing
--5 no match found
[+][-]11/25/07 12:00 PM, ID: 20346550Accepted Solution

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

About this solution

Zones: MS SQL Server, MS SQL DTS
Tags: query, sql, bill, excel
Sign Up Now!
Solution Provided By: kselvia
Participating Experts: 3
Solution Grade: A
 
[+][-]11/24/07 09:01 AM, ID: 20343094Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/24/07 09:24 AM, ID: 20343156Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]11/24/07 09:27 AM, ID: 20343164Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]11/24/07 09:38 AM, ID: 20343195Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/24/07 11:38 AM, ID: 20343514Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/24/07 03:18 PM, ID: 20344250Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/24/07 05:35 PM, ID: 20344551Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/25/07 12:06 AM, ID: 20345059Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/25/07 02:05 AM, ID: 20345211Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/25/07 09:54 AM, ID: 20346181Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]11/25/07 09:55 AM, ID: 20346185Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]12/11/07 09:15 PM, ID: 20455219Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091111-EE-VQP-92 / EE_QW_2_20070628