Need SQL Script dont use functions

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

Transact-SQL (2000)

Need SQL Script dont use functions

Author

Topic

Hillside
Starting Member

23 Posts

Posted - 2007-08-04 : 08:46:33

Hi all

I have 2 tables as follows:

TABLE 1

ID SmallDate DecimalValue
59 21/12/2005 00:00 3.6600
59 28/12/2005 00:00 3.6033
59 04/01/2006 00:00 3.5793
59 11/01/2006 00:00 3.5544

59 15/02/2006 00:00 3.7262
59 22/02/2006 00:00 3.6275
59 01/03/2006 00:00 3.7079
59 08/03/2006 00:00 3.8002
59 15/03/2006 00:00 3.9038
59 22/03/2006 00:00 3.8700
59 29/03/2006 00:00 3.9738
59 05/04/2006 00:00 4.0845

59 17/05/2006 00:00 4.2838
59 24/05/2006 00:00 4.1270
59 31/05/2006 00:00 4.2867
59 07/06/2006 00:00 4.3417
59 14/06/2006 00:00 4.2423
59 21/06/2006 00:00 4.3489
59 28/06/2006 00:00 4.4291
59 05/07/2006 00:00 4.4821
59 12/07/2006 00:00 4.4294
59 19/07/2006 00:00 4.3523
59 26/07/2006 00:00 4.3005
59 02/08/2006 00:00 4.2806

59 20/09/2006 00:00 4.1319
59 27/09/2006 00:00 4.0452
59 04/10/2006 00:00 4.0837
59 11/10/2006 00:00 4.1832
59 18/10/2006 00:00 4.1743

Table2
TABLE 2

ID SmallDate
1 21/12/2005 00:00
2 28/12/2005 00:00
3 04/01/2006 00:00
4 11/01/2006 00:00
5 18/01/2006 00:00
6 25/01/2006 00:00
7 01/02/2006 00:00
8 08/02/2006 00:00
9 15/02/2006 00:00
10 22/02/2006 00:00
11 01/03/2006 00:00
12 08/03/2006 00:00
13 15/03/2006 00:00
14 22/03/2006 00:00
15 29/03/2006 00:00
16 05/04/2006 00:00
17 12/04/2006 00:00
18 19/04/2006 00:00
19 26/04/2006 00:00
20 03/05/2006 00:00
21 10/05/2006 00:00
22 17/05/2006 00:00
23 24/05/2006 00:00
24 31/05/2006 00:00
25 07/06/2006 00:00
26 14/06/2006 00:00
27 21/06/2006 00:00
28 28/06/2006 00:00
29 05/07/2006 00:00
30 12/07/2006 00:00
31 19/07/2006 00:00
32 26/07/2006 00:00
33 02/08/2006 00:00
34 09/08/2006 00:00
35 16/08/2006 00:00
36 23/08/2006 00:00
37 30/08/2006 00:00
38 06/09/2006 00:00
39 13/09/2006 00:00
40 20/09/2006 00:00
41 27/09/2006 00:00
42 04/10/2006 00:00
43 11/10/2006 00:00
44 18/10/2006 00:00
Table 2 has a full set of date series. Table 1 as missing date series that need to be filled.Wherever there are gaps in table 1 I need values to be inserted. The logic to insert values is -

The columns ID and DecimalValue should have values same as the row just before where the gap starts. The column SmallDate in table 1 should have corresponding values from column SmallDate of table 2.

For eg, at the first gap in table 1 values that need to be filled in would be :

TABLE 1
ID SmallDate DecimalValue
59 18/01/2006 00:00 3.5544
59 25/01/2006 00:00 3.5544
59 01/02/2006 00:00 3.5544
59 08/02/2006 00:00 3.5544

I need to write sql query to do the above. Please could someone assist. Thanks in adance

I look forward to hear...soon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-04 : 12:38:53

Use LEFT JOIN to get the missing values/records.
Use a subquery to get latest record not missing.

E 12°55'05.25"
N 56°04'39.16"

Hillside
Starting Member

23 Posts

Posted - 2007-08-04 : 12:53:47

I can get the missing values by using left join but how to get lateast record not missing.The problme is it not consistent gaps and it may occur any place of the table.

Your help apreicated

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-04 : 13:58:54

Something like
select t1.*,
(select top 1 decimalvalue from table1 as t0 where t2.id = t0.id and t2.smalldate <= t0.smalldate order by t0.smalldate desc)
from table1 as t1
left join table2 as t2 on t2.id = t1.id and t2.smalldate = t1.smalldate
where t2.id is null

E 12°55'05.25"
N 56°04'39.16"

Hillside
Starting Member

23 Posts

Posted - 2007-08-04 : 16:49:43

Thank you very much for your help and I got idea now how to get the data.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-04 : 18:45:40

[code]-- Prepare sample date
SET DATEFORMAT DMY

DECLARE @Table1 TABLE (ID TINYINT, SmallDate SMALLDATETIME, DecimalValue SMALLMONEY)

INSERT @Table1
SELECT 59, '21/12/2005 00:00', 3.6600 UNION ALL
SELECT 59, '28/12/2005 00:00', 3.6033 UNION ALL
SELECT 59, '04/01/2006 00:00', 3.5793 UNION ALL
SELECT 59, '11/01/2006 00:00', 3.5544 UNION ALL
SELECT 59, '15/02/2006 00:00', 3.7262 UNION ALL
SELECT 59, '22/02/2006 00:00', 3.6275 UNION ALL
SELECT 59, '01/03/2006 00:00', 3.7079 UNION ALL
SELECT 59, '08/03/2006 00:00', 3.8002 UNION ALL
SELECT 59, '15/03/2006 00:00', 3.9038 UNION ALL
SELECT 59, '22/03/2006 00:00', 3.8700 UNION ALL
SELECT 59, '29/03/2006 00:00', 3.9738 UNION ALL
SELECT 59, '05/04/2006 00:00', 4.0845 UNION ALL
SELECT 59, '17/05/2006 00:00', 4.2838 UNION ALL
SELECT 59, '24/05/2006 00:00', 4.1270 UNION ALL
SELECT 59, '31/05/2006 00:00', 4.2867 UNION ALL
SELECT 59, '07/06/2006 00:00', 4.3417 UNION ALL
SELECT 59, '14/06/2006 00:00', 4.2423 UNION ALL
SELECT 59, '21/06/2006 00:00', 4.3489 UNION ALL
SELECT 59, '28/06/2006 00:00', 4.4291 UNION ALL
SELECT 59, '05/07/2006 00:00', 4.4821 UNION ALL
SELECT 59, '12/07/2006 00:00', 4.4294 UNION ALL
SELECT 59, '19/07/2006 00:00', 4.3523 UNION ALL
SELECT 59, '26/07/2006 00:00', 4.3005 UNION ALL
SELECT 59, '02/08/2006 00:00', 4.2806 UNION ALL
SELECT 59, '20/09/2006 00:00', 4.1319 UNION ALL
SELECT 59, '27/09/2006 00:00', 4.0452 UNION ALL
SELECT 59, '04/10/2006 00:00', 4.0837 UNION ALL
SELECT 59, '11/10/2006 00:00', 4.1832 UNION ALL
SELECT 59, '18/10/2006 00:00', 4.1743

DECLARE @Table2 TABLE (ID TINYINT, SmallDate SMALLDATETIME)

INSERT @Table2
SELECT 1, '21/12/2005 00:00' UNION ALL
SELECT 2, '28/12/2005 00:00' UNION ALL
SELECT 3, '04/01/2006 00:00' UNION ALL
SELECT 4, '11/01/2006 00:00' UNION ALL
SELECT 5, '18/01/2006 00:00' UNION ALL
SELECT 6, '25/01/2006 00:00' UNION ALL
SELECT 7, '01/02/2006 00:00' UNION ALL
SELECT 8, '08/02/2006 00:00' UNION ALL
SELECT 9, '15/02/2006 00:00' UNION ALL
SELECT 10, '22/02/2006 00:00' UNION ALL
SELECT 11, '01/03/2006 00:00' UNION ALL
SELECT 12, '08/03/2006 00:00' UNION ALL
SELECT 13, '15/03/2006 00:00' UNION ALL
SELECT 14, '22/03/2006 00:00' UNION ALL
SELECT 15, '29/03/2006 00:00' UNION ALL
SELECT 16, '05/04/2006 00:00' UNION ALL
SELECT 17, '12/04/2006 00:00' UNION ALL
SELECT 18, '19/04/2006 00:00' UNION ALL
SELECT 19, '26/04/2006 00:00' UNION ALL
SELECT 20, '03/05/2006 00:00' UNION ALL
SELECT 21, '10/05/2006 00:00' UNION ALL
SELECT 22, '17/05/2006 00:00' UNION ALL
SELECT 23, '24/05/2006 00:00' UNION ALL
SELECT 24, '31/05/2006 00:00' UNION ALL
SELECT 25, '07/06/2006 00:00' UNION ALL
SELECT 26, '14/06/2006 00:00' UNION ALL
SELECT 27, '21/06/2006 00:00' UNION ALL
SELECT 28, '28/06/2006 00:00' UNION ALL
SELECT 29, '05/07/2006 00:00' UNION ALL
SELECT 30, '12/07/2006 00:00' UNION ALL
SELECT 31, '19/07/2006 00:00' UNION ALL
SELECT 32, '26/07/2006 00:00' UNION ALL
SELECT 33, '02/08/2006 00:00' UNION ALL
SELECT 34, '09/08/2006 00:00' UNION ALL
SELECT 35, '16/08/2006 00:00' UNION ALL
SELECT 36, '23/08/2006 00:00' UNION ALL
SELECT 37, '30/08/2006 00:00' UNION ALL
SELECT 38, '06/09/2006 00:00' UNION ALL
SELECT 39, '13/09/2006 00:00' UNION ALL
SELECT 40, '20/09/2006 00:00' UNION ALL
SELECT 41, '27/09/2006 00:00' UNION ALL
SELECT 42, '04/10/2006 00:00' UNION ALL
SELECT 43, '11/10/2006 00:00' UNION ALL
SELECT 44, '18/10/2006 00:00'

-- Show the expected result
SELECT 59 AS ID,
t2.SmallDate,
(SELECT TOP 1 x.DecimalValue FROM @Table1 AS x WHERE x.SmallDate <= t2.SmallDate ORDER BY x.SmallDate DESC) AS DecimalValue
FROM @Table2 AS t2
LEFT JOIN @Table1 AS t1 ON t1.SmallDate = t2.SmallDate
WHERE t1.ID IS NULL
ORDER BY t2.SmallDate[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources