none
Query problem

    Question

  • I have a table province_location which tells me which province has which locations. A location can only be in one province.
    create table province_location (province_location_id int not null primary key, province_code int not null, location_code int not null)
    

    This is its data:

    province_location_id province_code location_code
    1 6 101
    2 6 102
    3 6 103
    4 6 104
    5 6 105
    6 7 110
    7 7 111
    8 7 112


    I have a table holiday_setup which contains holidays (holiday_code) setup for a province (province_code) and a location (location_code).
    create table holiday_setup (holiday_setup_id int not null primary key, holiday_code int not null, province_code int not null, location_code int null, holiday_date datetime)
    

    location_code null means all locations for that holiday and province (unless there is a specific location defined for that holiday and province). This is sample data:

    holiday_setup_id holiday_code province_code location_code holiday_date
    1 250 6 null 11/30/2010
    2 250 6 102 11/29/2010
    3 250 6 103 11/29/2010
    4 250 7 null 11/30/2010

    In the above data:

    For 2 locations '102' and '103' of province '6', the holiday '250' is setup as 11/29/2010; for all other locations in province '6', the holiday '250' is setup as 11/30/2010.

    For all locations in province '7', the holiday '250' is setup as 11/30/2010.


    I need to retreive data of holiday_setup table so that location_code is not null in my result set.

    holiday_setup_id holiday_code province_code location_code holiday_date
    1 250 6 101 11/30/2010
    2 250 6 102 11/29/2010
    3 250 6 103 11/29/2010
    1 250 6 104 11/30/2010
    1 250 6 105 11/30/2010
    4 250 7 110 11/30/2010
    4 250 7 111 11/30/2010
    4 250 7 112 11/30/2010

    I have a solution but for that I am defining a cursor and looping through each row. Is it possible to do it without cursor?

    Thanks
    Thursday, March 11, 2010 6:10 PM

Answers

  • Hello - See if this works. Verified this with 3 records into holiday_setup table and results are coming as you expected.

    SELECT  HS.holiday_setup_id, HS.holiday_code, PL.province_code, PL.location_code, HS.holiday_date
    FROM    province_location PL
    JOIN    holiday_setup HS
    ON      PL.province_code    = HS.province_code
    WHERE   (
                (
                    HS.location_code    IS NOT NULL
                AND PL.location_code    = HS.location_code
                )
            OR  (
                    HS.location_code    IS NULL
                AND NOT EXISTS  
                    (
                        SELECT  1
                        FROM    holiday_setup HSInner
                        WHERE   HS.holiday_code     = HSInner.holiday_code
                        AND     PL.province_code    = HSInner.province_code
                        AND     PL.location_code    = HSInner.location_code
                    )
                )
            )    
    ORDER BY    holiday_code, province_code, location_code

    Thanks!

    Friday, March 12, 2010 10:02 AM

All replies

  • add this to your query

    where location_code is not null
    Thursday, March 11, 2010 6:13 PM
  • select l.holiday_setup_id,l.holiday_code,p.province_code,p.location_code,l.holiday_date
    from province_location as p
    join holiday_setup as l on l.province_code=p.province_code and l.location_code=p.location_code
    Thursday, March 11, 2010 6:17 PM
  • Can you elaborate a little bit? location_code is not null will give me only 2 rows from holiday_setup table.

    This is what I want my result set to be:

    holiday_setup_id holiday_code province_code location_code holiday_date
    1 250 6 101 11/30/2010
    2 250 6 102 11/29/2010
    3 250 6 103 11/29/2010
    1 250 6 104 11/30/2010
    1 250 6 105 11/30/2010
    4 250 7 110 11/30/2010
    4 250 7 111 11/30/2010
    4 250 7 112 11/30/2010

    I am planning to write it in a stored proc so it does not have to be one single SELECT statement.
    Thursday, March 11, 2010 6:21 PM
  • sorry i misread your request, need to work on it. ignore my earlier query
    Thursday, March 11, 2010 6:25 PM
  • This is the patch to insert data in the above 2 table:

    insert into province_location values (1, 6, 101)
    insert into province_location values (2, 6, 102)
    insert into province_location values (3, 6, 103) 
    insert into province_location values (4, 6, 104)
    insert into province_location values (5, 6, 105) 
    insert into province_location values (6, 7, 110) 
    insert into province_location values (7, 7, 111) 
    insert into province_location values (8, 7, 112)
    
    insert into holiday_setup values (1, 250, 6, null, '11/30/2010') 
    insert into holiday_setup values (2, 250, 6, 102, '11/29/2010')
    insert into holiday_setup values (3, 250, 6, 103, '11/29/2010') 
    insert into holiday_setup values (4, 250, 7, null, '11/30/2010')
    
    Thursday, March 11, 2010 6:29 PM
  • select case when l.holiday_setup_id is null then n.holiday_setup_id else l.holiday_setup_id end,
    case when l.holiday_code is null then n.holiday_code else n.holiday_code end,p.province_code,p.location_code,case when l.holiday_date is null then n.holiday_date else l.holiday_date end
    from  #province_location as p 
    left join #holiday_setup as l on l.province_code=p.province_code and l.location_code=p.location_code
    left join (select province_code,holiday_setup_id,holiday_code, holiday_date from #holiday_setup where location_code is null) as n on n.province_code=p.province_code 


    this will work :)
    • Proposed as answer by Parry2k Thursday, March 11, 2010 6:32 PM
    • Marked as answer by 1970us Thursday, March 11, 2010 6:59 PM
    • Unmarked as answer by 1970us Thursday, March 11, 2010 10:11 PM
    Thursday, March 11, 2010 6:31 PM
  • create table #province_location (province_location_id int not null primary key, province_code int not null, location_code int not null)
    
    create table #holiday_setup (holiday_setup_id int not null primary key, holiday_code int not null, province_code int not null, location_code int null, holiday_date datetime)
    
    insert into #province_location
    select 1, 6, 101 
    union all
    select 2, 6, 102 
    union all
    select 3, 6, 103
    union all
    select 4, 6, 104
    union all
    select 5, 6, 105
    union all
    select 6, 7, 110
    union all
    select 7, 7, 111
    union all
    select 8, 7, 112 
    
    insert into #holiday_setup
    select 1, 250, 6, null, '11/30/2010'
    union all select 2, 250, 6, 102, '11/29/2010'
    union all select 3, 250, 6, 103, '11/29/2010' 
    union all select 4, 250, 7, null, '11/30/2010' 
    
    select case when l.holiday_setup_id is null then n.holiday_setup_id else l.holiday_setup_id end,
    case when l.holiday_code is null then n.holiday_code else n.holiday_code end,p.province_code,p.location_code,case when l.holiday_date is null then n.holiday_date else l.holiday_date end
    from  #province_location as p 
    left join #holiday_setup as l on l.province_code=p.province_code and l.location_code=p.location_code
    left join (select province_code,holiday_setup_id,holiday_code, holiday_date from #holiday_setup where location_code is null) as n on n.province_code=p.province_code 
    here is the complete script to test with temp tables
    Thursday, March 11, 2010 6:36 PM
  • Thanks. It works perfect. I made one change:

    select 
    case when l.holiday_setup_id is null then n.holiday_setup_id else l.holiday_setup_id end,
    case when l.holiday_code is null then n.holiday_code else n.holiday_code end,
    p.province_code,
    p.location_code,
    case when l.holiday_date is null then n.holiday_date else l.holiday_date end
    from  
    province_location as p 
    left join holiday_setup as l on l.province_code=p.province_code and l.location_code=p.location_code
    left join holiday_setup as n on n.province_code=p.province_code and n.location_code is null
    
    
    Thursday, March 11, 2010 7:02 PM
  • I add a row in holiday_setup table for another holiday '260'.

    This is what I add:

    insert into holiday_setup values (5, 260, 6, null, '12/05/2010')
    This is the result set I was expecting from the query:

    holiday_setup_id holiday_code province_code location_code holiday_date
    1 250 6 101 11/30/2010
    2 250 6 102 11/29/2010
    3 250 6 103 11/29/2010
    1 250 6 104 11/30/2010
    1 250 6 105 11/30/2010
    4 250 7 110 11/30/2010
    4 250 7 111 11/30/2010
    4 250 7 112 11/30/2010
    5 260 6 101 12/05/2010
    5 260 6 102 12/05/2010
    5 260 6 103 12/05/2010
    5 260 6 104 12/05/2010
    5 260 6 105 12/05/2010

    But this is what I get after running your query:

    holiday_setup_id holiday_code province_code location_code holiday_date
    1 250 6 101 11/30/2010
    2 250 6 102 11/29/2010
    3 250 6 103 11/29/2010
    1 250 6 104 11/30/2010
    1 250 6 105 11/30/2010
    4 250 7 110 11/30/2010
    4 250 7 111 11/30/2010
    4 250 7 112 11/30/2010
    5 260 6 101 12/05/2010
    2 260 6 102 11/29/2010
    3 260 6 103 11/29/2010
    5 260 6 104 12/05/2010
    5 260 6 105 12/05/2010

    So I unmarked your query as the answer. Its failing for Holiday '260' for locations '102' and '103'. What do you think?

    Thursday, March 11, 2010 10:22 PM
  • didn't get, i added new record as per your post and i get following result, not sure what is the issue

    1 250 6 101 2010-11-30 00:00:00.000
    1 250 6 104 2010-11-30 00:00:00.000
    1 250 6 105 2010-11-30 00:00:00.000
    2 250 6 102 2010-11-29 00:00:00.000
    2 260 6 102 2010-11-29 00:00:00.000
    3 250 6 103 2010-11-29 00:00:00.000
    3 260 6 103 2010-11-29 00:00:00.000
    4 250 7 110 2010-11-30 00:00:00.000
    4 250 7 111 2010-11-30 00:00:00.000
    4 250 7 112 2010-11-30 00:00:00.000
    5 260 6 105 2010-12-05 00:00:00.000
    5 260 6 104 2010-12-05 00:00:00.000
    5 260 6 101 2010-12-05 00:00:00.000


    you may be required to add order by clause in query to sort the data you want.
    Thursday, March 11, 2010 10:41 PM
  • The results you got is same as mine (mine is just ordered by holiday_code, province_code and location_code). And the results are wrong. For example

    In your result the 5th row is wrong. Instead of
    2, 260, 6, 102, 2010-11-29 00:00:00.000
    it should be 
    5, 260, 6, 102, 2010-12-05 00:00:00.000


    Similarly in your result the 7th row is wrong. Instead of
    3, 260, 6, 103, 2010-11-29 00:00:00.000
    it should be 
    5, 260, 6, 103, 2010-12-05 00:00:00.000

    Thursday, March 11, 2010 11:09 PM
  • just want to clarify
    now there are tow record for location_code = 6 and location code is null and dates 11/30/2010 and 12/5/2010

    now you want to return the both the location code with null or you want to return the latest one
    Thursday, March 11, 2010 11:30 PM
  • insert into province_location values (1, 6, 101)
    insert into province_location values (2, 6, 102)
    insert into province_location values (3, 6, 103) 
    insert into province_location values (4, 6, 104)
    insert into province_location values (5, 6, 105) 
    insert into province_location values (6, 7, 110) 
    insert into province_location values (7, 7, 111) 
    insert into province_location values (8, 7, 112)
    
    insert into holiday_setup values (1, 250, 6, null, '11/30/2010') 
    insert into holiday_setup values (2, 250, 6, 102, '11/29/2010')
    insert into holiday_setup values (3, 250, 6, 103, '11/29/2010') 
    insert into holiday_setup values (4, 250, 7, null, '11/30/2010')
    insert into holiday_setup values (5, 260, 6, null, '12/05/2010')
    

    And it should return (order by holiday_code, province_code, location_code)

    holiday_setup_id, holiday_code, province_code, location_code, holiday_date
    1, 250, 6, 101, 11/30/2010
    2, 250, 6, 102, 11/29/2010
    3, 250, 6, 103, 11/29/2010
    1, 250, 6, 104, 11/30/2010
    1, 250, 6, 105, 11/30/2010
    4, 250, 7, 110, 11/30/2010
    4, 250, 7, 111, 11/30/2010
    4, 250, 7, 112, 11/30/2010
    5, 260, 6, 101, 12/05/2010
    5, 260, 6, 102, 12/05/2010
    5, 260, 6, 103, 12/05/2010
    5, 260, 6, 104, 12/05/2010
    5, 260, 6, 105, 12/05/2010
    Thursday, March 11, 2010 11:47 PM
  • i think this is what you want, now it will pick the latest date and show the record against it.

    select * from #province_location order by province_code
    go
    select case when l.holiday_setup_id is null then n.holiday_setup_id else l.holiday_setup_id end as Holiday_Setup_ID,
    case when l.holiday_code is null then n.holiday_code else l.holiday_code end as Holiday_Code,
    p.province_code,p.location_code,
    case when l.holiday_date is null then n.holiday_date else l.holiday_date end as Holiday_Date
    from  #province_location as p 
    left join 
    (select  row_number() over (partition by province_code order by holiday_Date desc) rown ,province_code,holiday_setup_id,holiday_code, holiday_date,location_code
    from #holiday_setup where location_code is not null) as l on l.province_code=p.province_code and l.location_code=p.location_code and l.rown=1
    left join 
    (select row_number() over (partition by province_code order by holiday_Date desc) rown ,province_code,holiday_setup_id,holiday_code, holiday_date
    from #holiday_setup where location_code is null) as n on n.province_code=p.province_code and n.rown=1
    order by case when l.holiday_code is null then n.holiday_code else l.holiday_code end,
    p.province_code,p.location_code,
    case when l.holiday_date is null then n.holiday_date else l.holiday_date end
    Friday, March 12, 2010 12:18 AM
  • Hello - See if this works. Verified this with 3 records into holiday_setup table and results are coming as you expected.

    SELECT  HS.holiday_setup_id, HS.holiday_code, PL.province_code, PL.location_code, HS.holiday_date
    FROM    province_location PL
    JOIN    holiday_setup HS
    ON      PL.province_code    = HS.province_code
    WHERE   (
                (
                    HS.location_code    IS NOT NULL
                AND PL.location_code    = HS.location_code
                )
            OR  (
                    HS.location_code    IS NULL
                AND NOT EXISTS  
                    (
                        SELECT  1
                        FROM    holiday_setup HSInner
                        WHERE   HS.holiday_code     = HSInner.holiday_code
                        AND     PL.province_code    = HSInner.province_code
                        AND     PL.location_code    = HSInner.location_code
                    )
                )
            )    
    ORDER BY    holiday_code, province_code, location_code

    Thanks!

    Friday, March 12, 2010 10:02 AM
  • 1970us - I have proposed Parry2k's solution as an answer by mistake (clicked incorrect link). However, I have not verified Parry2k's solution and leaving it up to you for any further verifications.

    Parry2k - for your information.

    Thanks!

    Friday, March 12, 2010 10:12 AM
  • Parry2k your query is wrong. It is giving me this result set:

    holiday_setup_id, holiday_code, province_code, location_code, holiday_date
    2, 250, 6, 102, 11/29/2010
    4, 250, 7, 110, 11/30/2010
    4, 250, 7, 111, 11/30/2010
    4, 250, 7, 112, 11/30/2010
    5, 260, 6, 101, 12/05/2010
    5, 260, 6, 103, 12/05/2010
    5, 260, 6, 104, 12/05/2010
    5, 260, 6, 105, 12/05/2010


    The right query should give this result set:

    holiday_setup_id, holiday_code, province_code, location_code, holiday_date
    1, 250, 6, 101, 11/30/2010
    2, 250, 6, 102, 11/29/2010
    3, 250, 6, 103, 11/29/2010
    1, 250, 6, 104, 11/30/2010
    1, 250, 6, 105, 11/30/2010
    4, 250, 7, 110, 11/30/2010
    4, 250, 7, 111, 11/30/2010
    4, 250, 7, 112, 11/30/2010
    5, 260, 6, 101, 12/05/2010
    5, 260, 6, 102, 12/05/2010
    5, 260, 6, 103, 12/05/2010
    5, 260, 6, 104, 12/05/2010
    5, 260, 6, 105, 12/05/2010
    Friday, March 12, 2010 3:26 PM
  • Thanks Deepak Munigela. Your query is giving me the right result set. I have also come up with a solution which is also giving me the right result set. This is my solution:

    declare @HS Table
    (
    	holiday_setup_id	int,
    	holiday_code		int,
    	province_code		int,
    	location_code		int,
    	holiday_date		datetime
    )
    
    insert into @HS
    select 
    	hs.holiday_setup_id, hs.holiday_code, pl.province_code, pl.location_code, hs.holiday_date
    from
    	province_location pl 
    		inner join holiday_setup hs on 
    			pl.province_code = hs.province_code
    where
    	(hs.location_code is null)
    
    update 
    	@HS
    set 
    	holiday_setup_id = hs.holiday_setup_id,
    	holiday_date = hs.holiday_date
    from 
    	holiday_setup hs 
    		inner join @HS hs1 on 
    			hs.holiday_code = hs1.holiday_code and 
    			hs.province_code = hs1.province_code and 
    			hs.location_code = hs1.location_code
    where 
    	hs.location_code is not null  
    	
    select * from @HS order by holiday_code, province_code, location_code
    

    Which solution is better?
    Friday, March 12, 2010 5:17 PM
  • Hi,
    There would a problem with the approach you mentioned when you have data for all the possible location_codes of a province_code in holiday_setup table for a particular holiday_code.

    For example, observe the query result after inserting below records into holiday_setup table (however I am not sure on the possibility of existence of such data in your case).

    insert into holiday_setup values (6, 270, 7, 110, '11/29/2010') 
    insert into holiday_setup values (7, 270, 7, 111, '11/30/2010')
    insert into holiday_setup values (8, 270, 7, 112, '12/05/2010')
    After inserting the above mentioned set of records, you won't be able to retrieve data for holiday_code = 270 with your approach, as we don't have atleast a single record with location_code = null for the corresponding holiday_code.


    Thanks!
    Wednesday, March 17, 2010 4:04 PM