Home » SQL & PL/SQL » SQL & PL/SQL » sql (oracle 10g)
sql [message #687517] Fri, 24 March 2023 08:54 Go to next message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member
i am getting wrong output if i am including the week_reason 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 = 'edfm2')), 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

Re: sql [message #687519 is a reply to message #687517] Fri, 24 March 2023 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Format your query, if you don't know how to format the code, learn it using SQL Formatter.

Re: sql [message #687520 is a reply to message #687519] Fri, 24 March 2023 09:29 Go to previous message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member
I am expecting end_week_day_Reason to be displayed in the final output for the latest end week.


desc desc imputed_country
Name          Null     Type         
------------- -------- ------------ 
RET_CODE NOT NULL CHAR(3)      
COUNTRY_NO      NOT NULL VARCHAR2(20) 
DAY                   NUMBER(4)    
DAY_REASON            VARCHAR2(50) 

select * from imputed_county
RET_CODE, COUNTY_NO, DAY, DAY_REASON
d2	1105	2054 null	
d2	1105	2055 null	
d2	1105	2087 null	
d2	1105	2060 null	
d2	1105	2019 null	 
d2	1105	2020 null	
d2	1105	2217 null	
d2	1105	2218 null	
d2	1105	2223 null	
d2	106	2271 null	
d2	1105	2251 null	
d2	1105	2267 null	
d2	1115	2267 null	
d2	106	2268 null	
d2	1105	2271 null	
d2	1115	2271 null	
d2	1105	2216 null	
d2	1105	2245 null	
d2	1105	2220 null	
d2	1105	2246 null	
d2	1105	2021 null	
d2	1105	2219 null	
d2	1105	2018 null	
d2	1105	2086 null	
d2	1105	2061 null	
d2	1105	2058 null	
d2	1105	2059 null	
d2	106	2091 null
d2	1105	2084 null
d2	1105	2085 null	
d2	1105	2083 null	
d2	1105	2272 Update
d2	1115	2272 Update
d2	106	2270 nulll
d2	1115	2268 null
d2	1115	2269 null 
d2	1115	2270 null
d2	106	2272 Full
d2	1105	2221 null	

select * from usd_euroscan
ret_code         country_no	                               ron_census_country_no
d2			106						60007
d2			1115						46726
d2			1105						46716

CURRENT QUERY :

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 RONcountry country_no  day_end	Count of Copies

d2	60007	     106	2272	3
d2	46716	     1105	2272	2
d2	46726	     1115	2272	6

Expected output :
retcode RONcountry	country_no  day_end	Count of Copies day_reason

d2	60007	     106	2272	3               Full
d2	46716	     1105	2272	2               Update
d2	46726	     1115	2272	6               Update

[Updated on: Fri, 24 March 2023 11:20]

Report message to a moderator

Previous Topic: Min and Max values of contiguous rows (3 merged)
Next Topic: Sequence alternate
Goto Forum:
  


Current Time: Thu Mar 28 17:26:24 CDT 2024