T SQL Script Help

Question

Hi,

Im new to this forum and would like some assistance please?

I have 2 tables and  would like to perform the following functions in one querry.

The primary key will be the account number and I will do an inner join to get only matching records from both tables. 

1. all records exceeding 3000 km per month

2. all records between 2083 and 3000 per month

3 rand value of arears broken down as follows

Mileage   Per Month	1 month arrears	2 month arrears	3 month arrears
<2083
2083   – 3000
>3000

4.license renewals every 12 months ( so i will need an indicator that will notify me when the license will be renewed) (from contract date)

5. same  with every 15 000 km service intervals ( from starting odoreading)

Here are my Tables:

--Payments_Tbl--

AccNo	Paymentmade	Paymentdue	Arrears	MonthNo
11111111	R 100.00	R 200.00	R 100.00	1
22222222	R 0.00	R 50.00	R 50.00	2
33333333	R 50.00	R 50.00	R 0.00	3
44444444	R 0.00	R 250.00	R 250.00	4
55555555	R 200.00	R 100.00	-R 100.00	1

--Policies_Tbl--

AccNo	ContractDate	OdoReading km	Monthno
11111111	2011/06/11	9116	1
22222222	2011/12/04	31394	2
33333333	2011/10/03	26512	3
44444444	2011/07/06	10215	2
55555555	2011/11/11	28376	1

Please can you assist

Andrevw23

Answer 1

DECLARE @Payments_Tbl TABLE
(
AccNo VARCHAR(15),
Paymentmade VARCHAR(50),
Paymentdue VARCHAR(50),
Arrears VARCHAR(50),
MonthNo   VARCHAR(50)
)

DECLARE @Policies_Tbl TABLE
(
AccNo VARCHAR(50),
ContractDate DATETIME,
OdoReadingkm VARCHAR(50),
Monthno VARCHAR(50)
)

INSERT @Payments_Tbl
SELECT '11111111','R 100.00','R 200.00' ,'R 100.00' ,1 UNION ALL
SELECT '22222222','R 0.00' ,'R 50.00' ,'R 50.00' ,2 UNION ALL
SELECT '33333333','R 50.00' ,'R 50.00' ,'R 0.00' ,3 UNION ALL
SELECT '44444444','R 0.00' ,'R 250.00' ,'R 250.00' ,4 UNION ALL
SELECT '55555555','R 200.00','R 100.00' ,'R 100.00' ,1 

INSERT @Policies_Tbl
SELECT '11111111' ,'2011/06/11' ,'9116' ,1 UNION ALL
SELECT '22222222' ,'2011/12/04' ,'31394', 2 UNION ALL
SELECT '33333333' ,'2011/10/03' ,'26512', 3 UNION ALL
SELECT '44444444' ,'2011/07/06' ,'10215', 2 UNION ALL
SELECT '55555555' ,'2011/11/11' ,'28376', 1 ;


SELECT CASE WHEN t2.OdoReadingkm < '2083' THEN t1.Arrears END AS '<2083',
CASE WHEN t2.OdoReadingkm  BETWEEN '2083' AND '3000' THEN t1.Arrears END AS '2083-3000',
CASE WHEN t2.OdoReadingkm > '3000' THEN t1.Arrears END AS '>3000'
FROM @Payments_Tbl t1 
INNER JOIN @Policies_Tbl t2 ON t1.AccNo = t2.AccNo

From the Result u need to Pivot the result set

---

Please have look on the comment

Answer 2

Thank you for your response.

this helps with the km split  and i understand to do the pivot as well.

how do i get the 15 000 km service intervals and every twelve month license renewals.

Regards

Andrevw23

Answer 3

I did not undestand ..c an u explain me even more

---

Please have look on the comment

Answer 4

Hi,

Ok , example . if i have an odometer reading thats on 1000 i want a column that wil say that i have 14000km to go before my next service. Once the 15 000 km has been reached it has to inform me that i have 15 000 km to go before my next service ( which will be) 30 000. so it has to calculate every 15 000km.

so it will look something like this:

Odo	km to go before next service	month
1000	14000	1
2000	13000	2
5000	10000	3

Thank you 

Hope this helps

Andrevw23