Query

Transact-SQL
1

not sure how to add the mapping_no column from the country table to the below query to get the output as expected.

with t as (select ret_code, country_no, day, day - row_number() over (order by ret_code, country_no, day) grp from imputed_country
    where ret_code = ( select ret_code from retailer where retailer_name = 'd2' ) ), tst as (select ret_code,
    country_no, min(day) day_start, max(day) day_end from t group by ret_code, country_no, grp )
select ret_code, country_no, day_START, day_END, ((day_end - day_start) + 1) as "No of times since last polled"
from tst where day_start <= 2272 and day_end = 2272 order by country_no, ret_code;

Current Query Output:

RET_CODE,country_no            ,day_START,  day_END,No of times since last polled
---,--------------------,----------,----------,-----------------------------
d2,106                 ,      2270,      2272,                            3
d2,1075                ,      2247,      2272,                           26
d2,1093                ,      2246,      2272,                           27
d2,1096                ,      2253,      2272,                           20
d2,1105                ,      2271,      2272,                            2

Mapping_No Table

select country_no, mapping_no from country.

country_no mapping_no
106        1000
1075       2000
1093       3000
1105       4000

Expected Output

RET_CODE,country_no,mapping_no            ,day_START,  day_END,No of days since last polled
---,-----------,---------,----------,----------,-----------------------------
d2,106,1000              ,      2270,      2272,                            3
d2,1075,2000             ,      2247,      2272,                           26
d2,1093,3000             ,      2246,      2272,                           27
d2,1096,4000             ,      2253,      2272,                           20
2

hi

i feel like the query can be simplified

is it possible to post some sample data ? thank you
Provide Sample Data DDL

3

hi

please see if this helps

;   with t as 
        (select ret_code, country_no, day, day - row_number() over (order by ret_code, country_no, day) grp from imputed_country where ret_code = ( select ret_code from retailer where retailer_name = 'd2' ) )
		, tst as 
		(select ret_code, country_no, min(day) day_start, max(day) day_end from t group by ret_code, country_no, grp )
select 
     ret_code
	 , country_no
	 , mapping_no 
	 , day_START
	 , day_END
	 , ((day_end - day_start) + 1) as "No of times since last polled"
from 
    tst a JOIN country b on a.country_no  = b.country_no 
where 
    day_start <= 2272 and day_end = 2272 
order by country_no, ret_code;
4

it works.

5

can you let me know how to include the column day_reason from the imputed_country to final query output.

imputed_coutry table
ret country_no  day     day_reason
d2		108		2043	 update
d2		1080	2043	 update
d2	        1169	2043	 update

Current Query

  with t as 
        (select ret_code, country_no, day, day - row_number() over (order by ret_code, country_no, day) grp from imputed_country where ret_code = ( select ret_code from retailer where retailer_name = 'd2' ) )
		, tst as 
		(select ret_code, country_no, min(day) day_start, max(day) day_end from t group by ret_code, country_no, grp )
select 
     ret_code
	 , country_no
	 , mapping_no 
	 , day_START
	 , day_END
	 , ((day_end - day_start) + 1) as "No of times since last polled"
from 
    tst a JOIN country b on a.country_no  = b.country_no 
where 
    day_start <= 2272 and day_end = 2272 
order by country_no, ret_code;

RET_CODE,country_no , mapping no           ,day_START,  day_END,No of times since last polled,day_reason
---,--------------------,----------,----------,---------------------------------------------
d2,106                 , 2043,     2270,      2272,                            3,       update
d2,1075                , 2056,    2247,      2272,                           26,	   update
d2,1093                ,  2077,    2246,      2272,                           27,       update
d2,1096                ,  2089,    2253,      2272,                           20,       update
d2,1105                ,  2099,     2271,      2272,                            2,       update
6

i am getting wrong output if i am including the end_week_day_Reasonn to be printed in the output. any ideas ?

WITH t
     AS (SELECT ret_code,
                country_no,
                day,
                day - Row_number()
                        OVER (
                          ORDER BY ret_code, country_no, day) grp
         FROM   ret.imputed_country
         WHERE  ret_code = (SELECT ret_code
                            FROM   ret.ret
                            WHERE  ret_name = 'd2')),
     tst
     AS (SELECT ret_code,
                country_no,
                Min(day) day_start,
                Max(day) day_end
         FROM   t
         GROUP  BY ret_code,
                   country_no,
                   grp)
SELECT ret_code,
       b.ron_census_country_no         AS "RON country",
       country_no,
       day_end                         AS "Report day",
       ( ( day_end - day_start ) + 1 ) AS "Count of Copies"
FROM   tst a
       JOIN usd_euroscan b
         ON a.country_no = b.retailer_country_no
WHERE  day_start <= 2272
       AND day_end = 2272
       AND b.ret_file_abbrev = 'd2'
ORDER  BY country_no,
          ret_code; 

Current Output :
retcode RON country	day		day_end	Count of Copies
d2		42236		1026	2272	27
d2		60007		106		2272	3
d2		42285		1075	2272	26
d2		46704		1093	2272	27
d2		46707		1096	2272	20
d2		46716		1105	2272	2

Expected Output :

retcode RON country	day		day_end	Count of Copies	end_week_day_Reason
d2		42236		1026	2272	27				update
d2		60007		106		2272	3				full
d2		42285		1075	2272	26              update
d2		46704		1093	2272	27              update
d2		46707		1096	2272	20              full
d2		46716		1105	2272	2               full

Table imputed_country


retcode day	 	day_end	end_week_day_Reason
d2		1026	2272	Update
d2		106		2272	Full
d2		1075	2272	Update
d2		1093	2272	Update
d2		1096	2272	Full
d2		1105	2272	Full
d2		1026	2271	Update
d2		106		2271	Full
d2		1075	2271	Update
d2		1093	2271	Update
d2		1096	2271	Full
d2		1105	2271	Full