I've created an if statement to control if a customer discount is right. I take the sales volume of its previous year, control if it is in the exact range, then if it is not I need to write the right discount to apply. My problem is that this suggested discount is not the right one. I'll show you the code. Before I give a sample customer data.
Discount Applied is 60 % Sales Volume 2016--->€ 13.895.90 Sales Volume 2015 ---> € 25.686.92
This is my query:
DECLARE all variables that i need......
SET @Anno1 = YEAR(GETDATE());
SET @Anno2 = YEAR(DATEADD(year,-1,GETDATE()));
SET @Anno3 = YEAR(DATEADD(year,-2,GETDATE()));
SET @datada = DATEADD(DAY, -15, GETDATE());
SET @dataa = GETDATE();
----- set discount sales volume ---
SET @40 = '€ '+ REPLACE(CONVERT(varchar, CAST('1500.0000' AS money), 105),',','.');
SET @50 = '€ '+ REPLACE(CONVERT(varchar, CAST('15000.0000' AS money), 105),',','.');
SET @60 = '€ '+ REPLACE(CONVERT(varchar, CAST('150000.0000' AS money), 105),',','.');
SET @70 = '€ '+ REPLACE(CONVERT(varchar, CAST('200000.0000' AS money), 105),',','.');
SET @80 = '€ '+ REPLACE(CONVERT(varchar, CAST('500000.0000' AS money), 105),',','.');
---create cursor---
DECLARE c CURSOR FOR
SELECT DISTINCT
CODCONTO,
DSCCONTO1
FROM .dbo.TESTEDOCUMENTI
WHERE D (.dbo.TESTEDOCUMENTI.TIPODOC = 'PCL' OR .dbo.TESTEDOCUMENTI.TIPODOC = 'ORC' ) AND .dbo.TESTEDOCUMENTI.DATADOC BETWEEN @datada AND @dataa
----take each customer----
OPEN c
FETCH NEXT FROM c INTO @CodiceCliente,@Cliente
--IF @CodiceCliente IS NULL goto finescript;
WHILE @@FETCH_STATUS = 0
BEGIN
-------------------------------------------------------------------
----------------------set sales volumes to variables---
-------------------------------Current year -----
SET @FattAnnoCorrente =
(SELECT '€ '+ REPLACE(CONVERT(varchar, CAST(SUM(TOTIMPONIBILE) AS money), 105),',','.') FROM .dbo.TESTEDOCUMENTI
WHERE CODCLIFOR = @CodiceCliente ANDAND .dbo.TESTEDOCUMENTI.TIPODOC = 'FVC' AND .dbo.TESTEDOCUMENTI.ESERCIZIO = YEAR(GETDATE()));
-------------------------------Previous Year -----
SET @FattAnnoPrecedente =
(SELECT '€ '+ REPLACE(CONVERT(varchar, CAST(SUM(TOTIMPONIBILE) AS money), 105),',','.') FROM .dbo.TESTEDOCUMENTI
WHERE CODCLIFOR = @CodiceCliente AND .dbo.TESTEDOCUMENTI.TIPODOC = 'FVC' AND .dbo.TESTEDOCUMENTI.ESERCIZIO = YEAR(DATEADD(year,-1,GETDATE())));
------------------------------2 Previous years -----
SET @Fatt2AnniPrecedenti =
(SELECT '€ '+ REPLACE(CONVERT(varchar, CAST(SUM(TOTIMPONIBILE) AS money), 105),',','.') FROM .dbo.TESTEDOCUMENTI
WHERE CODCLIFOR = @CodiceCliente AND .dbo.TESTEDOCUMENTI.TIPODOC = 'FVC' AND .dbo.TESTEDOCUMENTI.ESERCIZIO = YEAR(DATEADD(year,-2,GETDATE())));
----------- Take the last document discount and set to variable -----
SET @Sconto =
(SELECT DISTINCT MAX(SCONTORIGA)
FROM .dbo.TESTEDOCUMENTI
WHERE SCONTORIGA IS NOT NULL AND CODCLIFOR = @CodiceCliente AND (.dbo.TESTEDOCUMENTI.TIPODOC = 'PCL' OR .dbo.TESTEDOCUMENTI.TIPODOC = 'ORC' ) AND .dbo.TESTEDOCUMENTI.DATADOC BETWEEN @datada AND @dataa);
--------------------------verify condition---THERE IS THE TRUOBLES----
---------------------------PREVIOUS YEAR SALES VOLUME----
IF @FattAnnoCorrente IS NULL SET @FattAnnoCorrente = '0'
IF @FattAnnoPrecedente IS NULL SET @FattAnnoPrecedente = '0'
IF @Fatt2AnniPrecedenti IS NULL SET @Fatt2AnniPrecedenti = '0'
IF @FattAnnoPrecedente = '0' goto fatturatocorrente;
IF (@FattAnnoPrecedente > '0' and @FattAnnoPrecedente < @40) and @Sconto < '40' goto finescript;
IF (@FattAnnoPrecedente > @40 and @FattAnnoPrecedente < @50 ) and (@Sconto > '40' and @Sconto < '50') goto finescript;
IF (@FattAnnoPrecedente > @50 and @FattAnnoPrecedente < @60 ) and (@Sconto > '50' and @Sconto < '60') goto finescript;
IF (@FattAnnoPrecedente > @60 and @FattAnnoPrecedente < @70 ) and (@Sconto > '60' and @Sconto < '70') goto finescript;
IF (@FattAnnoPrecedente > @70 and @FattAnnoPrecedente < @80 ) and (@Sconto > '70' and @Sconto < '80') goto finescript;
IF (@FattAnnoPrecedente > @80 and @FattAnnoPrecedente < '999999999999999999' ) and @Sconto > '80' goto finescript;
------------------------------------FIND THE SUGESTED DISCOUNT ------ THIS IS WRONG
IF ((@FattAnnoPrecedente > '0' and @FattAnnoPrecedente < @40 ))
SET @ScontoPrevisto = 'inferiore al 40%';
IF ((@FattAnnoPrecedente > @40 and @FattAnnoPrecedente < @50 ) )
SET @ScontoPrevisto = 'compreso tra 40% e 50%';
IF ((@FattAnnoPrecedente > @50 and @FattAnnoPrecedente < @60 ) )
SET @ScontoPrevisto = 'compreso tra 50% e 60%';
IF ((@FattAnnoPrecedente > @60 and @FattAnnoPrecedente < @70 ) )
SET @ScontoPrevisto = 'compreso tra 60% e 70%';
IF ((@FattAnnoPrecedente > @70 and @FattAnnoPrecedente < @80 ) )
SET @ScontoPrevisto = 'compreso tra 70% e 80%';
IF ((@FattAnnoPrecedente > @80 and @FattAnnoPrecedente < '999999999999999999' ) )
SET @ScontoPrevisto = 'superiore all"80%';
SET @AnnoConsiderato = 'ANNO PRECEDENTE';
fatturatocorrente:
------------USE CURRENT YEAR IF PREVIOUS SALES VOLUME IS 0---------
IF @FattAnnoPrecedente NOT LIKE '0' goto fatturatoesistente;
IF (@FattAnnoCorrente > '0' and @FattAnnoCorrente < @40 ) and @Sconto < '40' goto finescript;
IF (@FattAnnoCorrente > @40 and @FattAnnoCorrente < @50 ) and (@Sconto > '40' and @Sconto < '50') goto finescript;
IF (@FattAnnoCorrente > @50 and @FattAnnoCorrente < @60 ) and (@Sconto > '50' and @Sconto < '60') goto finescript;
IF (@FattAnnoCorrente > @60 and @FattAnnoCorrente < @70 ) and (@Sconto > '60' and @Sconto < '70') goto finescript;
IF (@FattAnnoCorrente > @70 and @FattAnnoCorrente < @80 ) and (@Sconto > '70' and @Sconto < '80') goto finescript;
IF (@FattAnnoCorrente > @80 and @FattAnnoCorrente < '999999999999999999' ) and @Sconto > '80' goto finescript;
------------------------------------FIND SUGGESTED DISCOUNT ------
--SET @FattAnnoCorrente = '1';
IF ((@FattAnnoCorrente > '0' and @FattAnnoCorrente < @40 ))
SET @ScontoPrevisto = 'inferiore al 40%';
IF ((@FattAnnoCorrente > @40 and @FattAnnoCorrente < @50 ))
SET @ScontoPrevisto = 'compreso tra 40% e 50%';
IF ((@FattAnnoCorrente > @50 and @FattAnnoCorrente < @60 ))
SET @ScontoPrevisto = 'compreso tra 50% e 60%';
IF ((@FattAnnoCorrente > @60 and @FattAnnoCorrente < @70 ))
SET @ScontoPrevisto = 'compreso tra 60% e 70%';
IF ((@FattAnnoCorrente > @70 and @FattAnnoCorrente < @80 ))
SET @ScontoPrevisto = 'compreso tra 70% e 80%';
IF ((@FattAnnoCorrente > @80 and @FattAnnoCorrente < '999999999999999999'))
SET @ScontoPrevisto = 'superiore all"80%';
SET @AnnoConsiderato = 'ANNO CORRRENTE';
IF @Sconto LIKE '0.0%' SET @ScontoPrevisto = 'da stabilire in base alla merce ordinata'
fatturatoesistente:
-----------
--- HERE THERE WAS SOME TABLES CALLED BELOW BUT THEY WORK FINE, SO I REMOVED THEM ---
---------------------------------
---HTML EMAIL BODY SET WITH ALL VARIABLES, ALL WORKING FINE BUT THE @SCONTOPREVISTO is the wrong one----
SET @Email =
N'......HTML CODE....' + @ScontoPrevisto + '..HTML CODE...';
SET @oggettomail = 'ERRATA SCONTISTICA PER ' + @Cliente;
IF @Emailis null goto finescript;
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@gmail.com',
@subject = @oggettomail,
@body = @Email,
@body_format = 'HTML' ;
finescript:
--take the next customerE---
FETCH NEXT FROM c INTO @CodiceCliente,@Cliente
END
--clean---
CLOSE c
DEALLOCATE c
The result of this query for @ScontoPrevisto, suggested discount is wrong, it is between 70% and 80 % but as you see the previous year sales volume is about 25000 so the right discount must be 50-60%. I dont' understand why. Instead for some customer, reslt is good. Another customer has
Sales Volume 2016--->0 Sales Volume 2015 ---> 0 Discount 60%
Result is greater then 80 % instead to be smaller than 40 %. I wait for your answer. Thank you guys!
Aucun commentaire:
Enregistrer un commentaire