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)
 Fill up the Null fields

Author  Topic 

SummerWind
Starting Member

5 Posts
Posted - 2006-07-14 : 03:26:19
Hello,
I have problem that I haven’t manage to solve myself. There’s customers, months and visits (dmphsern).
I’d like to fill up those NULL fields with previous DMPHSERN, because there’s no visits for each month. That case I’d like to show previous visit. DMPHSERN is not growing number, it can be anything.


SELECT T1.CUSTOMER, T1.YEAR_MONTH, T2.DMPHSERN,
FROM dbo.CalendarJoinCustomer T1 LEFT OUTER JOIN
dbo.Month_last_visit T2 ON T1.CustomerYearMonth = T2.CustomerYearMonth

I try to do something with CASE WHEN dmphsern is null… but no success

CUSTOMER / YEAR_MONTH / DMPHSERN
100000000042135 /200401/NULL
100000000042135 /200402 /610000000309630
100000000042135 /200403 /NULL
100000000042135 /200404 /610000000327948
100000000042135 /200405 /180000000352566
100000000042135 /200406 /NULL
100000000042135 /200407 /NULL
100000000042135 /200408 /180000000378866
100000000042135 /200409 /NULL
100000000042135 /200410 /180000000396350
100000000042135 /200411 /180000000414001
100000000042135 /200412 /180000000423207
100000000042135 /200501 /180000000430233
100000000042135 /200502 /180000000442918
100000000042135 /200503 /NULL
100000000042135 /200504 /NULL
100000000042135 /200505 /NULL
100000000042135 /200506 /180000000478431
100000000042135 /200507 /NULL
100000000042135 /200508 /NULL
100000000042135 /200509 /180000000490822
100000000042135 /200510 /NULL
100000000042135 /200511 /180000000514803
100000000042135 /200512 /NULL
100000000042135 /200601 /NULL
100000000042135 /200602 /NULL
100000000042135 /200603 /180000000548683
100000000042135 /200604 /NULL
100000000042135 /200605 /NULL
100000000042135 /200606 /180000000564980
100000000042135 /200607 /NULL
998000000002070 /200401 /NULL
998000000002070 /200402 /NULL
998000000002070 /200403 /610000000317721
998000000002070 /200404 /610000000326862
998000000002070 /200405 /610000000331684
998000000002070 /200406 /610000000348823
998000000002070 /200407 /NULL
998000000002070 /200408 /610000000353790


Regards
SummerWind

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2006-07-14 : 03:36:50
What you want to show instead of NULL?

You can try IsNull() if you want to handle NULL values?

can you provide some more details on this?

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

SummerWind
Starting Member

5 Posts
Posted - 2006-07-14 : 03:50:01
I'd like to show previous visit serial number (dmphsern) if there is one for that customer.

CUSTOMER /YEAR_MONTH /DMPHSERN ->WHAT I WANT
100000000042135 200401 NULL ->NULL
100000000042135 200402 610000000309630 -> 610000000309630
100000000042135 200403 NULL -> 610000000309630
100000000042135 200404 610000000327948 ->610000000327948
100000000042135 200405 180000000352566 ->180000000352566
100000000042135 200406 NULL ->180000000352566
100000000042135 200407 NULL ->180000000352566
100000000042135 200408 180000000378866 ->180000000378866
100000000042135 200409 NULL ->180000000378866
100000000042135 200410 180000000396350 ->180000000396350
100000000042135 200411 180000000414001 ->180000000414001
.
.
.
998000000002070 200401 NULL ->NULL
998000000002070 200402 NULL ->NULL
998000000002070 200403 610000000317721 ->610000000317721
998000000002070 200404 610000000326862 ->610000000326862
998000000002070 200405 610000000331684 ->610000000331684
998000000002070 200406 NULL ->610000000331684


Summer
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2006-07-14 : 04:20:03
Do this...


select T1.CUSTOMER, T1.YEAR_MONTH, T2.DMPHSERN
into #test 
FROM dbo.CalendarJoinCustomer T1 LEFT OUTER JOIN
dbo.Month_last_visit T2 ON T1.CustomerYearMonth = T2.CustomerYearMonth

alter table #test
add i int identity(1,1)

select t1.Customer,T1.YEAR_MONTH, (case when t1.a is NULL then (select max(t2.a) from #test t2 where t2.i<t1.i and t2.a is not null) else t1.a end) as DMPHSERN from #test t1


First statement is to temporarily store the output of your first select statement in some temp table.

Add identity column in that table and last query will give you desired output !

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

SummerWind
Starting Member

5 Posts
Posted - 2006-07-14 : 06:51:23
Thanks a lot for your reply.

I tried to test this... if I undestand you correctly.

First about this select
select t1.Customer,T1.YEAR_MONTH, (case when t1.a is NULL then (select max(t2.a) from #test t2 where t2.i<t1.i and t2.a is not null) else t1.a end) as DMPHSERN from #test t1

Did you mean that a is dmphsern, otherwise I can't get it work at all.

Result of a that query was that it didn't care when the customer changed, it always gave a previous dmphsern?
Query should also give null if customer don't have any dmphsern.

Best Regards
SummerWind
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2006-07-14 : 07:27:40
yes a = dmphsern

To get the output you want, change the query as follows:

select T1.Customer, T1.YEAR_MONTH, (case when t1.a is NULL then (select max(t2.a) from #test t2 where t2.i<t1.i and t2.a is not null and t1.Customer = t2.Customer ) else t1.a end) from #test t1


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

SummerWind
Starting Member

5 Posts
Posted - 2006-07-14 : 14:20:00
Thanks a lot.

I think that might be right solution.
I will test it as soon I get back to my work and that's after my 4 weeks summer holiday.

Have a nice summer =)

Regards
SummerWind
Go to Top of Page

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts
Posted - 2006-07-17 : 06:48:49
Here's my effort (based on the sample data provided).

SummerWind - if you give the underlying ddl and data for it, we can give you a query based on that. Have a good summer.

--data
declare @t table (CUSTOMER bigint, YEAR_MONTH int, DMPHSERN bigint)
insert @t
          select 100000000042135, 200401, NULL
union all select 100000000042135, 200402, 610000000309630
union all select 100000000042135, 200403, NULL
union all select 100000000042135, 200404, 610000000327948
union all select 100000000042135, 200405, 180000000352566
union all select 100000000042135, 200406, NULL
union all select 100000000042135, 200407, NULL
union all select 100000000042135, 200408, 180000000378866
union all select 100000000042135, 200409, NULL
union all select 100000000042135, 200410, 180000000396350
union all select 100000000042135, 200411, 180000000414001
union all select 100000000042135, 200412, 180000000423207
union all select 100000000042135, 200501, 180000000430233
union all select 100000000042135, 200502, 180000000442918
union all select 100000000042135, 200503, NULL
union all select 100000000042135, 200504, NULL
union all select 100000000042135, 200505, NULL
union all select 100000000042135, 200506, 180000000478431
union all select 100000000042135, 200507, NULL
union all select 100000000042135, 200508, NULL
union all select 100000000042135, 200509, 180000000490822
union all select 100000000042135, 200510, NULL
union all select 100000000042135, 200511, 180000000514803
union all select 100000000042135, 200512, NULL
union all select 100000000042135, 200601, NULL
union all select 100000000042135, 200602, NULL
union all select 100000000042135, 200603, 180000000548683
union all select 100000000042135, 200604, NULL
union all select 100000000042135, 200605, NULL
union all select 100000000042135, 200606, 180000000564980
union all select 100000000042135, 200607, NULL
union all select 998000000002070, 200401, NULL
union all select 998000000002070, 200402, NULL
union all select 998000000002070, 200403, 610000000317721
union all select 998000000002070, 200404, 610000000326862
union all select 998000000002070, 200405, 610000000331684
union all select 998000000002070, 200406, 610000000348823
union all select 998000000002070, 200407, NULL
union all select 998000000002070, 200408, 610000000353790

--calculation
select
    CUSTOMER,
    YEAR_MONTH,
    (select top 1 DMPHSERN from @t 
    where CUSTOMER = a.CUSTOMER and YEAR_MONTH <= a.YEAR_MONTH and DMPHSERN is not null
    order by YEAR_MONTH desc) as DMPHSERN
from @t a

/*results
CUSTOMER             YEAR_MONTH  DMPHSERN             
-------------------- ----------- -------------------- 
100000000042135      200401      NULL
100000000042135      200402      610000000309630
100000000042135      200403      610000000309630
100000000042135      200404      610000000327948
100000000042135      200405      180000000352566
100000000042135      200406      180000000352566
100000000042135      200407      180000000352566
100000000042135      200408      180000000378866
100000000042135      200409      180000000378866
100000000042135      200410      180000000396350
100000000042135      200411      180000000414001
100000000042135      200412      180000000423207
100000000042135      200501      180000000430233
100000000042135      200502      180000000442918
100000000042135      200503      180000000442918
100000000042135      200504      180000000442918
100000000042135      200505      180000000442918
100000000042135      200506      180000000478431
100000000042135      200507      180000000478431
100000000042135      200508      180000000478431
100000000042135      200509      180000000490822
100000000042135      200510      180000000490822
100000000042135      200511      180000000514803
100000000042135      200512      180000000514803
100000000042135      200601      180000000514803
100000000042135      200602      180000000514803
100000000042135      200603      180000000548683
100000000042135      200604      180000000548683
100000000042135      200605      180000000548683
100000000042135      200606      180000000564980
100000000042135      200607      180000000564980
998000000002070      200401      NULL
998000000002070      200402      NULL
998000000002070      200403      610000000317721
998000000002070      200404      610000000326862
998000000002070      200405      610000000331684
998000000002070      200406      610000000348823
998000000002070      200407      610000000348823
998000000002070      200408      610000000353790
*/


Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site