With cte as ( select * , count (category) over (order by (select null) rows between unbounded preceding and current row) as rn from brands ) Select coalesce (category, max(category) over (partition by rn)) as category, Brand_name From cte;
with cte_null as ( select *,ROW_NUMBER() over(order by (select null)) as rn from brands ), cte_2 as ( select *,case when category is null then 0 else 1 end cnt from cte_null ), cte_3 as ( select category,brand_name,sum(cnt) over(order by rn) as mn from cte_2 ) select first_value(category) over (partition by mn order by mn) as category,brand_name from cte_3
with cte as(select * , 1 as num from brands), cte2 as (select *,row_number()over(order by num) as rn from cte), cte3 as(select *,min(category) over(order by rn asc) as category2 from cte2) select category2 as category,brand_name from cte3
with CTE as ( Select row_number() over (order by (select null))as rn, category,Brand_name, (case when category is null then 0 else 1 end) as m from brands ), cte2 as (Select rn,category,Brand_name,sum(m) over (order by rn)as n from cte) select rn,first_value(category)over(partition by n order by rn ) as category,Brand_name from cte2
Can use NTIle with first_value also : Select first_value(category) over(partition by s.n) as category, brand_name from (Select *, ntile(2) over() as n from brands) as s;
My Solution: select case when brand_name in ('5-star','dairymilk','perk','eclair') then 'chocolate' when brand_name in ('Britania','goodday','boost') then 'Biscuits' end as category,brand_name from chocolate_category;
Select first_value(Category) over (Partition by Seg order by RN) as Category,Brand_Name from (Select *,Sum(Flag) over (Order by Rn) as Seg from (Select *,Case When Category Is not null then 1 else 0 end as Flag,row_number() over (Order by (Select Null)) as Rn from Brands)N)N1;
-- Solution using approach unbounded preceding WITH brand_vw AS ( SELECT *, LAST_VALUE(category IGNORE NULLS) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS new_category FROM brands ) SELECT new_category, brand_name FROM brand_vw ORDER BY id;
with cte as( select * ,case when category is null then 0 else 1 end nn,row_number() over(order by (select null)) rn from brands ),f as( select category,brand_name,sum(nn) over(order by rn)m,rn from cte) select first_value(category) over(partition by m order by rn)category ,brand_name from f
my solution in MSSQL DB :: with cte as ( Select *,count(category)over( order by (select null) rows between unbounded preceding and 0 following ) as new from brands ) Select FIRST_VALUE(category)over(partition by new order by category desc) as category,brand_name from cte
You can opt simple solution using LAG() and CAse. SELECT CASE WHEN category is null THEN LAG(category) over() ELSE category END AS category, brand_name FROM brands;
@@DE_Pranav 1 SELECT 2 CASE 3 WHEN CATEGORY IS NOT NULL THEN CATEGORY 4 ELSE LAG(CATEGORY IGNORE NULLS) OVER (ORDER BY ROWNUM) 5 END "CATEGEORY", 6 BRAND_NAME "BRAND NAME" 7 FROM 8* brands 9 / CATEGEORY BRAND NAME -------------------------------------------------- -------------------------------------------------- chocolates 5-star chocolates dairy milk chocolates perk chocolates eclair Biscuits Britania Biscuits good day Biscuits boost 7 rows selected.
with cte as( select *,ROW_NUMBER() over(order by (select null)) as flag from brands) , cte1 as( select *,sum(case when category is not null then 1 else 0 end) over (order by flag) as new_flag from cte) select FIRST_VALUE(category) over(partition by new_flag order by flag) as category,brand_name from cte1
with cte_null AS (SELECT *, ROW_NUMBER() over(order by (select null)) AS rn FROM brands) SELECT first_value(category) OVER(PARTITION BY cn ORDER BY rn) AS category, brand_name FROM (SELECT *, COUNT(category) OVER(ORDER BY rn) AS cn FROM cte_null) category_cnt;
provide your mail address in the description box so that someone can mail you if he/she is having any question to ask. please try to solve this question. id (timestamp) emp_id (integer) 1/13/2024 9:25 10 1/13/2024 19:35 10 1/16/2024 9:10 10 1/16/2024 18:10 10 2/11/2024 9:07 10 2/11/2024 19:20 10 2/17/2024 8:40 17 2/17/2024 18:04 17 3/23/2024 9:20 10 3/23/2024 18:30 10 query to find the employees total working hours during weekends. output should be emp_id Total weekend Working_hours 17 9.4 10 29.55
Thanks for suggestion Chandan, I added email id in the description box now. I also noted down the questions you are posting. I will post the videos on those questions soon.
Try this code - with cte as( select emp_id,CAST(datehour AS date)date,CAST(datehour AS time)hm,DATEPART(WEEKDAY,datehour)wd from emp_work ),cte2 as( select DATE, emp_id,MIN(hm)start_time,MAX(hm)end_time from cte where wd in(1,7) group by DATE ,emp_id ),cte3 as( select *,DATEDIFF(MINUTE,start_time,end_time)*1.0/60 diff from cte2 )select distinct(emp_id),SUM(diff) over (partition by emp_id)Total_Weekend_Working_Hours from cte3
-- Create the table CREATE TABLE work_hours ( id TIMESTAMP, emp_id INTEGER ); -- Insert the records INSERT ALL INTO work_hours VALUES (TO_TIMESTAMP('2024-01-13 09:25:00', 'YYYY-MM-DD HH24:MI:SS'), 10) INTO work_hours VALUES (TO_TIMESTAMP('2024-01-13 19:35:00', 'YYYY-MM-DD HH24:MI:SS'), 10) INTO work_hours VALUES (TO_TIMESTAMP('2024-01-16 09:10:00', 'YYYY-MM-DD HH24:MI:SS'), 10) INTO work_hours VALUES (TO_TIMESTAMP('2024-01-16 18:10:00', 'YYYY-MM-DD HH24:MI:SS'), 10) INTO work_hours VALUES (TO_TIMESTAMP('2024-02-11 09:07:00', 'YYYY-MM-DD HH24:MI:SS'), 10) INTO work_hours VALUES (TO_TIMESTAMP('2024-02-11 19:20:00', 'YYYY-MM-DD HH24:MI:SS'), 10) INTO work_hours VALUES (TO_TIMESTAMP('2024-02-17 08:40:00', 'YYYY-MM-DD HH24:MI:SS'), 17) INTO work_hours VALUES (TO_TIMESTAMP('2024-02-17 18:04:00', 'YYYY-MM-DD HH24:MI:SS'), 17) INTO work_hours VALUES (TO_TIMESTAMP('2024-03-23 09:20:00', 'YYYY-MM-DD HH24:MI:SS'), 10) INTO work_hours VALUES (TO_TIMESTAMP('2024-03-23 18:30:00', 'YYYY-MM-DD HH24:MI:SS'), 10) SELECT * FROM dual; SQL> SQL> SELECT 2 emp_id, 3 ROUND(SUM(EXTRACT(HOUR FROM working_hours) + EXTRACT(MINUTE FROM working_hours) / 60), 2) AS Total_weekend_Working_hours 4 FROM ( 5 SELECT 6 emp_id, 7 MAX(id) - MIN(id) AS working_hours 8 FROM 9 work_hours 10 WHERE 11 TO_CHAR(id, 'DY', 'NLS_DATE_LANGUAGE=American') IN ('SAT', 'SUN') -- Filter weekends 12 GROUP BY 13 emp_id, 14 TO_CHAR(id, 'YYYY-MM-DD') -- Group by date to get total hours for each weekend day 15 ) weekends 16 GROUP BY 17 emp_id 18 ORDER BY 19 emp_id; EMP_ID TOTAL_WEEKEND_WORKING_HOURS ---------- --------------------------- 10 29.55 17 9.4
with cte as(select *,date(id) as date_r ,coalesce(lead(id) over(order by id),0)as logout_time, dayname(id)as day_name from employee_logs), cte2 as(select * from cte where day_name 'Tuesday' group by date_r), cte3 as(select *,timestampdiff(hour,id,logout_time)as hrs from cte2 order by emp_id) select emp_id,sum(hrs) as total_weekend_hrs from cte3 group by emp_id
------ solution 1 --------------------------------------------------------------------------------------- with cte as ( select * ,count(category) over (order by brand_name) as grp from #brands ) select first_value(category) over (partition by grp order by grp) as filled_category ,brand_name from cte ------ solution 2 --------------------------------------------------------------------------------------- with cte as ( select * ,count(category) over (order by brand_name) as grp from #brands ) SELECT isnull( category, max(category) over(partition by grp order by grp)) AS category , brand_name FROM cte;
![[LIVE] : ONE ลุมพินี 89 | คู่เอก "ยอดไอคิว vs คิริลล์"](http://i.ytimg.com/vi/Z3vYkHJ_5Ig/mqdefault.jpg)
With cte as ( select * , count (category) over (order by (select null) rows between unbounded preceding and current row) as rn from brands )
Select coalesce (category, max(category) over (partition by rn)) as category,
Brand_name
From cte;
with cte_null as
(
select *,ROW_NUMBER() over(order by (select null)) as rn from brands
),
cte_2 as
(
select *,case when category is null then 0 else 1 end cnt from cte_null
),
cte_3 as
(
select category,brand_name,sum(cnt) over(order by rn) as mn from cte_2
)
select first_value(category) over (partition by mn order by mn) as category,brand_name from cte_3
with cte as(select * , 1 as num from brands),
cte2 as (select *,row_number()over(order by num) as rn from cte),
cte3 as(select *,min(category) over(order by rn asc) as category2 from cte2)
select category2 as category,brand_name from cte3
Yes, it works. Thanks for posting the different approach Ram.
with CTE as
(
Select
row_number() over (order by (select null))as rn,
category,Brand_name,
(case when category is null then 0 else 1 end) as m
from brands
), cte2 as
(Select rn,category,Brand_name,sum(m) over (order by rn)as n from cte)
select rn,first_value(category)over(partition by n order by rn ) as category,Brand_name from cte2
Can use NTIle with first_value also :
Select first_value(category) over(partition by s.n) as category, brand_name
from (Select *, ntile(2) over() as n from brands) as s;
@TheBlacklist244, thanks for posting different approach. But your query seems incomplete. Can you share the full query here?
My Solution:
select case when brand_name in ('5-star','dairymilk','perk','eclair') then 'chocolate'
when brand_name in ('Britania','goodday','boost') then 'Biscuits' end as category,brand_name
from chocolate_category;
You can think of any other better solution.
Sir u are a nice person
Thanks for sharing your valuable feedback
Nice explanation
Select first_value(Category) over (Partition by Seg order by RN) as Category,Brand_Name from
(Select *,Sum(Flag) over (Order by Rn) as Seg from
(Select *,Case When Category Is not null then 1 else 0 end as Flag,row_number() over (Order by (Select Null)) as Rn
from Brands)N)N1;
-- Solution using approach unbounded preceding
WITH brand_vw AS (
SELECT
*,
LAST_VALUE(category IGNORE NULLS) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS new_category
FROM
brands
)
SELECT new_category, brand_name
FROM brand_vw
ORDER BY id;
Hey brother you have not been uploading vedio since last week
Why are you not uploading video please upload
@rajkumarpanigrahi2013, Thanks for the comment.
New video on SQL is out now. Please watch and like the video.
with cte as(
select * ,case when category is null then 0 else 1 end nn,row_number() over(order by (select null)) rn
from brands
),f as(
select category,brand_name,sum(nn) over(order by rn)m,rn
from cte)
select first_value(category) over(partition by m order by rn)category ,brand_name
from f
Thanks for sharing different approach Vaibhav.
Wecan use lag and case for this query,
Thanks Nandhini. It would be much appreciated if you could share the query here.
can you please share query.
my solution in MSSQL DB ::
with cte as
(
Select *,count(category)over( order by (select null) rows between unbounded preceding and 0 following ) as new from brands
)
Select FIRST_VALUE(category)over(partition by new order by category desc) as category,brand_name from cte
Thanks for posting different approach Vijay. Keep posting different approaches for upcoming videos as well.
You can opt simple solution using LAG() and CAse.
SELECT CASE
WHEN category is null THEN LAG(category) over()
ELSE category
END AS category, brand_name
FROM brands;
not getting required op with this. can you check?
@LeaningGCP, your query seems incomplete. Can you share the full query here?
@@CloudChallengers @DE_Pranav Correct, My bad. It's not full filling the desired outcome. Tested with just one row update :(
@@DE_Pranav
1 SELECT
2 CASE
3 WHEN CATEGORY IS NOT NULL THEN CATEGORY
4 ELSE LAG(CATEGORY IGNORE NULLS) OVER (ORDER BY ROWNUM)
5 END "CATEGEORY",
6 BRAND_NAME "BRAND NAME"
7 FROM
8* brands
9 /
CATEGEORY BRAND NAME
-------------------------------------------------- --------------------------------------------------
chocolates 5-star
chocolates dairy milk
chocolates perk
chocolates eclair
Biscuits Britania
Biscuits good day
Biscuits boost
7 rows selected.
with cte as(
select *,ROW_NUMBER() over(order by (select null)) as flag from brands)
,
cte1 as(
select *,sum(case when category is not null then 1 else 0 end) over (order by flag) as
new_flag
from cte)
select FIRST_VALUE(category) over(partition by new_flag order by flag) as category,brand_name from cte1
Thanks for quick response Ajay. This query works as well.
you have used CTE not view.
That's right Sai. Thanks for highlighting.
with cte_null AS
(SELECT
*,
ROW_NUMBER() over(order by (select null)) AS rn
FROM brands)
SELECT
first_value(category) OVER(PARTITION BY cn ORDER BY rn) AS category,
brand_name
FROM
(SELECT
*,
COUNT(category) OVER(ORDER BY rn) AS cn
FROM cte_null) category_cnt;
provide your mail address in the description box so that someone can mail you if he/she is having any question to ask.
please try to solve this question.
id (timestamp) emp_id (integer)
1/13/2024 9:25 10
1/13/2024 19:35 10
1/16/2024 9:10 10
1/16/2024 18:10 10
2/11/2024 9:07 10
2/11/2024 19:20 10
2/17/2024 8:40 17
2/17/2024 18:04 17
3/23/2024 9:20 10
3/23/2024 18:30 10
query to find the employees total working hours during weekends.
output should be
emp_id Total weekend
Working_hours
17 9.4
10 29.55
Thanks for suggestion Chandan, I added email id in the description box now. I also noted down the questions you are posting. I will post the videos on those questions soon.
Try this code -
with cte as(
select emp_id,CAST(datehour AS date)date,CAST(datehour AS time)hm,DATEPART(WEEKDAY,datehour)wd
from emp_work
),cte2 as(
select DATE, emp_id,MIN(hm)start_time,MAX(hm)end_time from cte
where wd in(1,7)
group by DATE ,emp_id
),cte3 as(
select *,DATEDIFF(MINUTE,start_time,end_time)*1.0/60 diff from cte2
)select distinct(emp_id),SUM(diff) over (partition by emp_id)Total_Weekend_Working_Hours from cte3
-- Create the table
CREATE TABLE work_hours (
id TIMESTAMP,
emp_id INTEGER
);
-- Insert the records
INSERT ALL
INTO work_hours VALUES (TO_TIMESTAMP('2024-01-13 09:25:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-01-13 19:35:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-01-16 09:10:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-01-16 18:10:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-02-11 09:07:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-02-11 19:20:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-02-17 08:40:00', 'YYYY-MM-DD HH24:MI:SS'), 17)
INTO work_hours VALUES (TO_TIMESTAMP('2024-02-17 18:04:00', 'YYYY-MM-DD HH24:MI:SS'), 17)
INTO work_hours VALUES (TO_TIMESTAMP('2024-03-23 09:20:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
INTO work_hours VALUES (TO_TIMESTAMP('2024-03-23 18:30:00', 'YYYY-MM-DD HH24:MI:SS'), 10)
SELECT * FROM dual; SQL>
SQL> SELECT
2 emp_id,
3 ROUND(SUM(EXTRACT(HOUR FROM working_hours) + EXTRACT(MINUTE FROM working_hours) / 60), 2) AS Total_weekend_Working_hours
4 FROM (
5 SELECT
6 emp_id,
7 MAX(id) - MIN(id) AS working_hours
8 FROM
9 work_hours
10 WHERE
11 TO_CHAR(id, 'DY', 'NLS_DATE_LANGUAGE=American') IN ('SAT', 'SUN') -- Filter weekends
12 GROUP BY
13 emp_id,
14 TO_CHAR(id, 'YYYY-MM-DD') -- Group by date to get total hours for each weekend day
15 ) weekends
16 GROUP BY
17 emp_id
18 ORDER BY
19 emp_id;
EMP_ID TOTAL_WEEKEND_WORKING_HOURS
---------- ---------------------------
10 29.55
17 9.4
video is out on this this question. th-cam.com/video/qPIMa7YtXqg/w-d-xo.htmlsi=YKGISgDmBN-NP3Cy
with cte as(select *,date(id) as date_r ,coalesce(lead(id) over(order by id),0)as logout_time,
dayname(id)as day_name from employee_logs),
cte2 as(select * from cte where day_name 'Tuesday' group by date_r),
cte3 as(select *,timestampdiff(hour,id,logout_time)as hrs from cte2 order by emp_id)
select emp_id,sum(hrs) as total_weekend_hrs from cte3 group by emp_id
------ solution 1 ---------------------------------------------------------------------------------------
with cte as (
select *
,count(category) over (order by brand_name) as grp
from #brands
)
select first_value(category) over (partition by grp order by grp) as filled_category
,brand_name
from cte
------ solution 2 ---------------------------------------------------------------------------------------
with cte as (
select *
,count(category) over (order by brand_name) as grp
from #brands
)
SELECT isnull( category, max(category) over(partition by grp order by grp)) AS category
, brand_name
FROM cte;
@shashank_1180, these queries are not giving expected output. Bcz of the reason, you are doing sorting on brand_name.