Advertisement

11.24.2007 at 08:48AM PST, ID: 22980239
[x]
Attachment Details

Need help with SQL Query

Asked by redcelltech in MS SQL Server, MS SQL DTS

Tags: , , ,

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,

TennysonStart Free Trial 
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.24.2007 at 09:01AM PST, ID: 20343094

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11.24.2007 at 09:24AM PST, ID: 20343156

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 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]11.24.2007 at 09:27AM PST, ID: 20343164

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 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]11.24.2007 at 09:38AM PST, ID: 20343195

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11.24.2007 at 11:38AM PST, ID: 20343514

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11.24.2007 at 03:18PM PST, ID: 20344250

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11.24.2007 at 05:35PM PST, ID: 20344551

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11.25.2007 at 12:06AM PST, ID: 20345059

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11.25.2007 at 02:05AM PST, ID: 20345211

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 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11.25.2007 at 09:54AM PST, ID: 20346181

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 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]11.25.2007 at 09:55AM PST, ID: 20346185

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 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]11.25.2007 at 12:00PM PST, ID: 20346550

View this solution now by starting your 7-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
 
 
[+][-]12.11.2007 at 09:15PM PST, ID: 20455219

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 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628