emi_sastra
asked on
Delete data at the end of rows (might be several rows)) when value = 0
Hi All,
I have below code.
What I want to achieve it to delete rows that has value zero.
For example :
I just want to delete month apr and may.
GRSM JABODETABEK 2020-03-01 65.9999
RSM JABODETABEK 2020-04-01 0.00
RSM JABODETABEK 2020-05-01 0.00
I don't want to delete any data for this.
GRSM JABODETABEK 2020-03-01 65.9999
RSM JABODETABEK 2020-04-01 0.00
RSM JABODETABEK 2020-05-01 55.00
How could I do this ?
Thank you.
I have below code.
create table #ERV_VIEW_Data_For_GKAMRSM(
[GKAMRSM] varchar(10),
[Month] date,
[score] decimal(8,4)
)
insert into #ERV_VIEW_Data_For_GKAMRSM
values('GKAM1', '2020-03-01', 81.2022),
('GKAM1', '2020-05-01', 95.83 ),
('GKAM2', '2020-03-01', 51.9475),
('GKAM2', '2020-04-01', 87.3028),
('GKAM2', '2020-05-01', 97.29 ),
('GKAM3', '2020-03-01', 76.1298)
declare @FromDate DATE = '2020-03-01',
@ToDate DATE = '2020-05-01';
with date_list as (
select @FromDate as [date]
union all
select dateadd(month, 1, [date]) from date_list
where [date] < @ToDate
)
select GKAMRSM_list.[GKAMRSM],
date_list.[date] as [Month],
isnull(e.[score], 0) as [score]
from date_list
cross join (
select distinct GKAMRSM
from #ERV_VIEW_Data_For_GKAMRSM
where [Month] BETWEEN @FromDate and @ToDate
and GKAMRSM is not null
) GKAMRSM_list
left join #ERV_VIEW_Data_For_GKAMRSM e on date_list.[date] = e.[Month] and GKAMRSM_list.[GKAMRSM] = e.[GKAMRSM]
order by GKAMRSM_list.[GKAMRSM], date_list.[date]
option(maxrecursion 10000)
What I want to achieve it to delete rows that has value zero.
For example :
I just want to delete month apr and may.
GRSM JABODETABEK 2020-03-01 65.9999
RSM JABODETABEK 2020-04-01 0.00
RSM JABODETABEK 2020-05-01 0.00
I don't want to delete any data for this.
GRSM JABODETABEK 2020-03-01 65.9999
RSM JABODETABEK 2020-04-01 0.00
RSM JABODETABEK 2020-05-01 55.00
How could I do this ?
Thank you.
ASKER
Hi skullnobrains,
The above code is complete, nothing wrong with it and nothing want to change.
I just want to add separe query to delete latest rows with score = 0
Thank you.
The above code is complete, nothing wrong with it and nothing want to change.
I just want to add separe query to delete latest rows with score = 0
Thank you.
ASKER
- also note that creating date_list is not really useful. unless i am missing something, the same query can be written with a simple where clause
Please revise the current query if any better idea.
Thank you.
Please revise the current query if any better idea.
Thank you.
i see no point of revising if the code is "complete"
if you want to remove the latest row, rather than all the rows when all of them are zero, my above suggestion won't do. this cannot be easily done in sql, unless you want to bother creating a temporary table and use a cursor. it seems much easier to remove said rows after querying using your favorite language.
if you want to remove the latest row, rather than all the rows when all of them are zero, my above suggestion won't do. this cannot be easily done in sql, unless you want to bother creating a temporary table and use a cursor. it seems much easier to remove said rows after querying using your favorite language.
ASKER
-if you want to remove the latest row, rather than all the rows when all of them are zero, my above suggestion won't do. this cannot be easily done in sql, unless you want to bother creating a temporary table and use a cursor. it seems much easier to remove said rows after querying using your favorite language.
Great. Please provide code.
Thank you.
Great. Please provide code.
Thank you.
i have no idea what language you are using nor an sql server around at my current location. i can help but not do 100% of the work for you.
ASKER
It is MS SQL server.
Thank you.
Thank you.
i advised you do not do this in sql.
you are providing partial information and expecting me to do your work.
sorry, but i have no time for this.
you are providing partial information and expecting me to do your work.
sorry, but i have no time for this.
ASKER
It is okay.
Thank you.
Thank you.
>>- also note that creating date_list is not really useful. unless i am missing something, the same query can be written with a simple where clause
Please revise the current query if any better idea.<<
I'm also not going to re-write the entire query but eliminating the date_list and the is_null(e.score,0), you should be able to construct the query without the need to join to the date_list and the left join to capture null values.
Please revise the current query if any better idea.<<
I'm also not going to re-write the entire query but eliminating the date_list and the is_null(e.score,0), you should be able to construct the query without the need to join to the date_list and the left join to capture null values.
ASKER
You try it first.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
seems you might need an extra where clause in the subquery to filter out months with a 0 value if such entries exist in the original data
That's a bit too broad. Not filtering out all generated 0 values, just those that would be past the last actual value in the table. Rather than generating and then "deleting" such rows, it makes more sense to me to never generate them at all :-).
ASKER
Hi Scott,
Zero value in the middle of month still needed.
Thank you.
Zero value in the middle of month still needed.
Thank you.
the query @scott provided does that.
it generates max(month) for each GKAMRSM and filters out values greater than that month.
try it out
if your data does contain zeroes
"GKAMRSM is not null" --> "GKAMRSM is not null and score > 0"
will take care of remaining edge cases
@scott : the performance will not necessarily be better than filtering out : that depends on the cardinality of the source table : it is likely in most cases but the tmp tables and joins might also end up killing a lot of memory to save few network bytes in other cases. since the output is sorted by GKAMRSM, filtering out is rather trivial.
it generates max(month) for each GKAMRSM and filters out values greater than that month.
try it out
if your data does contain zeroes
"GKAMRSM is not null" --> "GKAMRSM is not null and score > 0"
will take care of remaining edge cases
@scott : the performance will not necessarily be better than filtering out : that depends on the cardinality of the source table : it is likely in most cases but the tmp tables and joins might also end up killing a lot of memory to save few network bytes in other cases. since the output is sorted by GKAMRSM, filtering out is rather trivial.
ASKER
HI Scott,
I am sorry, what if there just have value at last month, I don't want previous rows with zero value all, just like the trail one.
For example just have May Data, thus March and April are zero.
change
('GKAM3', '2020-05-01', 76.1298)
Thank you.
I am sorry, what if there just have value at last month, I don't want previous rows with zero value all, just like the trail one.
For example just have May Data, thus March and April are zero.
change
('GKAM3', '2020-03-01', 76.1298)
to ('GKAM3', '2020-05-01', 76.1298)
Thank you.
ASKER
I want the result like this.
GKAM1 2020-03-01 81.2022
GKAM1 2020-04-01 0.0000
GKAM1 2020-05-01 95.8300
GKAM2 2020-03-01 51.9475
GKAM2 2020-04-01 87.3028
GKAM2 2020-05-01 97.2900
GKAM3 2020-05-01 76.1298
Thank you.
GKAM1 2020-03-01 81.2022
GKAM1 2020-04-01 0.0000
GKAM1 2020-05-01 95.8300
GKAM2 2020-03-01 51.9475
GKAM2 2020-04-01 87.3028
GKAM2 2020-05-01 97.2900
GKAM3 2020-05-01 76.1298
Thank you.
with date_list as (
select @FromDate as [date]
union all
select dateadd(month, 1, [date]) from date_list
where [date] < @ToDate
)
select GKAMRSM_list.[GKAMRSM],
date_list.[date] as [Month],
isnull(e.[score], 0) as [score]
from date_list
cross join (
select GKAMRSM, max(month) as last_month, min(month) as first_month
from #ERV_VIEW_Data_For_GKAMRSM
where [Month] BETWEEN @FromDate and @ToDate
and GKAMRSM is not null
group by GKAMRSM
) GKAMRSM_list
left join #ERV_VIEW_Data_For_GKAMRSM e on date_list.[date] = e.[Month] and GKAMRSM_list.[GKAMRSM] = e.[GKAMRSM]
where date_list.[date] <= GKAMRSM_list.last_month and date_list.[date] >= GKAMRSM_list.first_month
order by GKAMRSM_list.[GKAMRSM], date_list.[date]
option(maxrecursion 10000)
select @FromDate as [date]
union all
select dateadd(month, 1, [date]) from date_list
where [date] < @ToDate
)
select GKAMRSM_list.[GKAMRSM],
date_list.[date] as [Month],
isnull(e.[score], 0) as [score]
from date_list
cross join (
select GKAMRSM, max(month) as last_month, min(month) as first_month
from #ERV_VIEW_Data_For_GKAMRSM
where [Month] BETWEEN @FromDate and @ToDate
and GKAMRSM is not null
group by GKAMRSM
) GKAMRSM_list
left join #ERV_VIEW_Data_For_GKAMRSM e on date_list.[date] = e.[Month] and GKAMRSM_list.[GKAMRSM] = e.[GKAMRSM]
where date_list.[date] <= GKAMRSM_list.last_month and date_list.[date] >= GKAMRSM_list.first_month
order by GKAMRSM_list.[GKAMRSM], date_list.[date]
option(maxrecursion 10000)
ASKER
Hi Scott,
Great. Thank you very much for your help.
Great. Thank you very much for your help.
select * from TABLE_NAME where [Month] BETWEEN @FromDate and @ToDate group by GKAMRSM having score > 0
... which you would add to the query as an extra cross join
also note that creating date_list is not really useful. unless i am missing something, the same query can be written with a simple where clause