i need to fill the listview1 with a SQL (i'm on VB6)
DAL is the first day AL the last day
NRGG is the number of days, in this case 8
PREZZO is the single price based ANNO, MESE, GIORNO, FILA, NUMERO
TOTALE is the PREZZO * NRGG
for ALBERTO:
ANNO MESE DAL AL NRGG ID NOMINATIVO FILA NUM PREZZO TOTALE
2021 7 01/07/2021 08/07/2021 8 08092021161012 ALBERTO 5 3 28,00 224,00
NOTE:
Use for the example only NOME and ID not is null
strange query
-
- PlatinumLounger
- Posts: 4339
- Joined: 26 Apr 2010, 17:36
strange query
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: strange query
How about
SELECT ANNO, MESE, Min(GIORNO) AS DAL, Max(GIORNO) AS AL, AL-DAL+1 AS NRGG, IDNOME AS ID, NOME AS NOMINATIVO, FILA, NUMERO AS NUM, PREZZO, NRGG*PREZZO AS TOTALE FROM OMBRELLONI GROUP BY ANNO, MESE, IDNOME, NOME, FILA, NUMERO, PREZZO HAVING IDNOME Is Not Null
SELECT ANNO, MESE, Min(GIORNO) AS DAL, Max(GIORNO) AS AL, AL-DAL+1 AS NRGG, IDNOME AS ID, NOME AS NOMINATIVO, FILA, NUMERO AS NUM, PREZZO, NRGG*PREZZO AS TOTALE FROM OMBRELLONI GROUP BY ANNO, MESE, IDNOME, NOME, FILA, NUMERO, PREZZO HAVING IDNOME Is Not Null
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4339
- Joined: 26 Apr 2010, 17:36
Re: strange query
sorry bro, but i cant test now.HansV wrote: ↑09 Sep 2021, 17:56How about
SELECT ANNO, MESE, Min(GIORNO) AS DAL, Max(GIORNO) AS AL, AL-DAL+1 AS NRGG, IDNOME AS ID, NOME AS NOMINATIVO, FILA, NUMERO AS NUM, PREZZO, NRGG*PREZZO AS TOTALE FROM OMBRELLONI GROUP BY ANNO, MESE, IDNOME, NOME, FILA, NUMERO, PREZZO HAVING IDNOME Is Not Null
but sure work!