0

I'm not really experienced with sql. So I created a query that produces the percentage value of data entries containing specific informations compared to all entries made at a certain day. It takes about 20 seconds to finish, i think that's not normal, so i think i made it pretty badly.

Also how can I declare the date '2021-06-29' as a variable?

SELECT Round((Cast(Count(scanid) as numeric)-(Select Cast(Count(scanid) as numeric) From public.scan where scantimestamp > '2021-06-28' and scantimestamp < '2021-06-29' 
and (code isnull or length(CAST(code AS TEXT)) < 9)))*100/Cast(Count(scanid) as numeric), 2) as all,
(SELECT Round((Select Cast(Count(scanid) as numeric) From public.scan where scantimestamp > '2021-06-28' and scantimestamp < '2021-06-29' 
and length(CAST(code AS TEXT)) = 12)*100/Cast(Count(scanid) as numeric), 2)) as bbb,
(SELECT Round((Select Cast(Count(scanid) as numeric) From public.scan where scantimestamp > '2021-06-28' and scantimestamp < '2021-06-29'
and length(CAST(barcode AS TEXT)) = 9)*100/Cast(Count(scanid) as numeric), 2)) as ccc,
(SELECT Round((Select Cast(Count(scanid) as numeric) From public.scan where scantimestamp > '2021-06-28' and scantimestamp < '2021-06-29' 
and length(CAST(barcode AS TEXT)) > 12)*100/Cast(Count(scanid) as numeric), 2)) as ddd
FROM public.scan where scantimestamp > '2021-06-28' and scantimestamp < '2021-06-29'
Improve this question

2 Answers 2

Reset to default
1

Your SQL is rather confusing, but If I understood your question correctly, then what you're looking for is to calculate the percentage of scans based on the length of the code string for a particular day. If so I recreated your example with


create table scan (code varchar, scanid int, scantimestamp timestamp);
insert into scan values ('1234567891',1,'2021-06-28 10:00:00');
insert into scan values ('12345678912345',1,'2021-06-28 09:00:00');
insert into scan values ('1234567891234561',2,'2021-06-28 15:00:00');
insert into scan values ('1234567',4,'2021-06-28 19:00:00');
insert into scan values ('123456789',3,'2021-06-28 19:00:00');
insert into scan values ('12345678',3,'2021-06-29 17:00:00');
insert into scan values ('12345679999999',5,'2021-06-30 20:00:00');

which is giving a nice dataset, the following query also calulated the legth of the code column

select *,
length(code)
from public.scan 
order by scantimestamp;

Result

       code       | scanid |    scantimestamp    | length 
------------------+--------+---------------------+--------
 12345678912345   |      1 | 2021-06-28 09:00:00 |     14
 1234567891       |      1 | 2021-06-28 10:00:00 |     10
 1234567891234561 |      2 | 2021-06-28 15:00:00 |     16
 1234567          |      4 | 2021-06-28 19:00:00 |      7
 123456789        |      3 | 2021-06-28 19:00:00 |      9
 12345678         |      3 | 2021-06-29 17:00:00 |      8
 12345679999999   |      5 | 2021-06-30 20:00:00 |     14
(7 rows)

Now, If I read your query properly, the below should solve the issue

select 
    count(case when code isnull or length(code) < 9 then scanid end) *100.0 / count(scanid) as all,
    count(case when length(code) = 12 then scanid end)* 100.0 / count(scanid) as bbb,
    count(case when length(code) = 9 then scanid end) * 100.0 / count(scanid) as ccc,
    count(case when length(code) > 12 then scanid end)* 100.0 / count(scanid) as ddd
from public.scan 
where scantimestamp > '2021-06-28' and scantimestamp < '2021-06-29';

With results

         all         |          bbb           |         ccc         |         ddd         
---------------------+------------------------+---------------------+---------------------
 20.0000000000000000 | 0.00000000000000000000 | 20.0000000000000000 | 40.0000000000000000
(1 row)

As you can see the overall sum is not 100% since the definition you make are not covering the whole data spectrum of code lengths.

If you want to make the day as parameter always relative to the current date, you could use the CURRENT_DATE in your query like

select 
    count(case when code isnull or length(code) < 9 then scanid end) *100.0 / count(scanid) as all,
    count(case when length(code) = 12 then scanid end)* 100.0 / count(scanid) as bbb,
    count(case when length(code) = 9 then scanid end) * 100.0 / count(scanid) as ccc,
    count(case when length(code) > 12 then scanid end)* 100.0 / count(scanid) as ddd
from public.scan 
where scantimestamp > CURRENT_DATE -2 and  scantimestamp < CURRENT_DATE -1;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

oh yes, you understood it exactly how im doing it. Thank you. Current Date looks automatically at 0:00:00 ? Or does it also look at the current time? If so how can i offset that to 0:00:00?
CURRENT_DATE is a date, looks automatically at 0:00:00, if you want to play with current time use CURRENT_TIMESTAMP
count(case when length(code) = 12 then scanid end) can also be written as count(*) filter (where length(code) = 12)
0

Appreciate that you wrote such deep nested query :-). However, these type of calculations are better performed using Stored procedures or Functions, all standard DBMS supports, it will perform better. Also, you need not hard code the dates, instead you could pass them as input arguments.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.