Grid report , select sum by year

Scriptcase 8 Discussion
#1

hello all

i have a table like this
Table Order_History

ID
Order_id
order_count
order_year_date

I want to get : group by Order_id and sum(order_count) like this
Order_id 2011 2012 2013 2014

To understand better please see attached photo

also if possible column year to be titled dynamically when the user chose starting year

thanks in advanced

Untitled.png

#2

okay, i found a way to get this but not dynamic

SELECT  order_id, 
        SUM(CASE WHEN YEAR(order_date) = 2011 THEN order_count ELSE 0 END) AS '2011',
        SUM(CASE WHEN YEAR(order_date) = 2012 THEN order_count ELSE 0 END) AS '2012',
        SUM(CASE WHEN YEAR(order_date) = 2013 THEN order_count ELSE 0 END) AS '2013'

FROM    order_history
GROUP BY 
        order_id

what i need is to get years dynamic, any clue ?

#3

use [GLOBALS]

Regards

#4

[QUOTE=kafecadm;33868]use [GLOBALS]

Regards[/QUOTE]

Dear Kafecadm
i did like this :
i start the Grid application as search and i create a new field with name {starting_year}and i set variable [YEAR] = {starting_year}
i changed my code to:

SELECT  order_id, 
        SUM(CASE WHEN YEAR(order_date) = [YEAR] THEN order_count ELSE 0 END) AS [YEAR],
        SUM(CASE WHEN YEAR(order_date) = [YEAR] +1 THEN order_count ELSE 0 END) AS [YEAR] +1,
        SUM(CASE WHEN YEAR(order_date) = [YEAR] +2 THEN order_count ELSE 0 END) AS [YEAR] +2

FROM    order_history
GROUP BY 
        order_id

when run after chose starting date i get error, i believe my code is wrong, can you check it?

Thanks in advanced

#5

To give you a better answer I’ll need you to provide the error message, tho, I think your problem is actually the way you building the query, those +1 and +2 are messing you up.

instead of that in the onapplicationinit of your report write some code like this.


[YEAR1]= [YEAR] + 1;
[YEAR2]= [YEAR] + 2;

Then modify your query to be something like this:



SELECT  order_id, 
        SUM(CASE WHEN YEAR(order_date) = [YEAR] THEN order_count ELSE 0 END) AS [YEAR],
        SUM(CASE WHEN YEAR(order_date) = [YEAR1] THEN order_count ELSE 0 END) AS [YEAR1],
        SUM(CASE WHEN YEAR(order_date) = [YEAR2]  THEN order_count ELSE 0 END) AS [YEAR2]

FROM    order_history
GROUP BY 
        order_id

Regards

#6

[QUOTE=kafecadm;33921]To give you a better answer I’ll need you to provide the error message, tho, I think your problem is actually the way you building the query, those +1 and +2 are messing you up.

instead of that in the onapplicationinit of your report write some code like this.


[YEAR1]= [YEAR] + 1;
[YEAR2]= [YEAR] + 2;

Then modify your query to be something like this:



SELECT  order_id, 
        SUM(CASE WHEN YEAR(order_date) = [YEAR] THEN order_count ELSE 0 END) AS [YEAR],
        SUM(CASE WHEN YEAR(order_date) = [YEAR1] THEN order_count ELSE 0 END) AS [YEAR1],
        SUM(CASE WHEN YEAR(order_date) = [YEAR2]  THEN order_count ELSE 0 END) AS [YEAR2]

FROM    order_history
GROUP BY 
        order_id

Regards[/QUOTE]

i tried to do like above i still get error

Invalid argument supplied for foreach() | Script: C:\Program Files\NetMake\v8\wwwroot\scriptcase\devel\compat
m_select_atualiza.php
and Error while accessing the database:

Also when i rewrite my SQL like it was before it keep giving me the same error “Error while accessing the database”, it will not run until i delete the grid and make another 1 ( is it bug )?

regards

#7

oh damn my bad:


SELECT  order_id,  
        SUM(CASE WHEN YEAR(order_date) = [YEAR] THEN order_count ELSE 0 END) AS '[YEAR]', 
        SUM(CASE WHEN YEAR(order_date) = [YEAR1] THEN order_count ELSE 0 END) AS '[YEAR1]', 
        SUM(CASE WHEN YEAR(order_date) = [YEAR2]  THEN order_count ELSE 0 END) AS '[YEAR2]' 

FROM    order_history 
GROUP BY  
        order_id

there… since [YEAR] and the others are numbers you cant use numbers as column names unless you use ’ =D.

Regards

#8

i got another error Parse error: syntax error, unexpected ‘$this’ (T_VARIABLE)

#9

ok im kinda confused now… where are you using that sql statemen

#10

in SQL Select Statement when creating grid

#11

share with me both screens please, the one with the error and the one with your query.

Regards

#12

as i told you i had to delete the grid and create new one and now i got this error

Untitled1.png

Untitled2.png

#13

what is the db type you are using?

kafe, this is not scriptcase issues, it is basic alphabets of programming, he is mixing lettuce with carrots

#14

Leon
db MYSQL
can you please separate the lettuce out of carrots and show me the right code :smiley:

#15

hey

yes, i have many similar programs already built and I sent you private messages to share them if i knew what you want exactly as I have a long experience in these patients and treatment medical stuff but you never answered me

instead, you keep asking questions that are not related to scriptcase, but related to SQL statments and basics of programming

cheers

#16

alo

@Leon Actually this is not and SQL thread but how to apply such SQL statement into an application so therefore I’m trying to help our college.

@walid the error message you are receibing is because you are failing to set the global variables properly, if you review the SQL error you are given by scriptcase you will find that [YEAR] and the other globals are not being replaced in the SQL statement, therefore the statement is failing.

Please checkout how are you setting your globals and verify that you have the proper configuration.

Regards

#17

Leon
first of all, i did not receive any private messages from you.
let me tell you that my knowledge about programming before 1 month was zero knowledge, and i am teaching my self, and by time i will get there,
the whole point of scriptcase and this forum is to provide help and hints from the experts people which i got from many kind people like kafecadm , aducom, Giu ,MikeDE and others. by passing their expertise to juniors like me without asking for money.
if you want to join those kind people go ahead and show me your experience in this basic issue as you said. or you can let others help

regards

#18

let me tell you how i set global variables, and tell if i am doing it right or what

as i told you before i will start the grid application as a search, then i will add new field {starting_year} type date format YYYY

onScriptInit

[YEAR]={starting_year}; // i set as out and check only _get and _post

onApplicationInit

[YEAR1] =[YEAR] +1; // i set as out and check only _get and _post
[YEAR2] =[YEAR] +2; // i set as out and check only _get and _post

i changed my SQL statement as you suggest

i selected the field what i want to show
is that right or i am missing something??

the i run the grid, search with field {starting_year} appear then i entered the year , then i got error i show you.

#19

ok there is your problem:

The SQL query must be valid before entering the Search, which means you should know the value of {starting_year} before and pass it as a parameter to your grid.

The best way to do so would be to use a blank application with a date field, then in the aftervalidate event use something like

sc_redir( my_grid, YEAR= {datefield_value} );

Please look at the SC documentation about sc_redir so you get a better understanding.

Regards

#20

@kafe please see your PM

@walid you are free to answer or not, i wanted to help because I’ve this project exactly ready you can just import it and you are ready to go, that is why I asked which database you are working for, I’ve it as mysql and mssql as well it is pretty common in medical field and I didn’t want any money dude, watch your words, I asked "what is your budget as a reply to your thread that you asked for developers in the opportunity forum that is different issue.