Link to home
Start Free TrialLog in
Avatar of emi_sastra
emi_sastra

asked on

Delete data at the end of rows (might be several rows)) when value = 0

Hi All,

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)

Open in new window


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.
Avatar of skullnobrains
skullnobrains

you would create a list of the non empty GKAMRSM over the period

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
Avatar of emi_sastra

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.
- 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.
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.                                  

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.
It is MS SQL server.

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.
It is okay.

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.
Hi awking00,

Please provide code.
Thank you.
You try it first.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 :-). 
Hi Scott,

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.
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-03-01', 76.1298)

Open in new window

to
 ('GKAM3', '2020-05-01', 76.1298)

Thank you.
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.
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)
Hi Scott,

Great. Thank you very much for your help.