Le Canada est sans doute l'un des pays ou les taxes de vente sur les produits et services sont les plus compliqués à appliquer. Et pour cause: Elle varie d'une province ou territoire à l'autre, elle est parfois composé ou séparé. Voici leur représentation en date du mois septembre de l'année 2006 :
| Province/Territoire | Taxe 1 | Taxe 2 | Taux |
|---|---|---|---|
| Alberta | TVH: 6% | Aucune | 6% |
| Colombie-Britannique | TVH: 6% | TVP: 7% | 13% |
| Ile-du-Prince-Édouard | TPS: 6% | TVP: 10% | 16.6% |
| Manitoba | TPS: 6% | TVP: 7% | 13% |
| Nouveau-Brunswick | TVH: 14% | Aucune | 14% |
| Nouvelle-Écosse | TVH: 14% | Aucune | 14% |
| Nunavut | TVH: 6% | Aucune | 6% |
| Ontario | TPS: 6% | TVP: 8% | 14% |
| Québec | TPS: 6% | TVQ: 7.5% | 13.95% |
| Saskatchewan | TPS: 6% | TVP: 7% | 13% |
| Terre-Neuve/Labrador | TVH: 14% | Aucune | 14% |
| Territoire du Nord Ouest | TVH: 6% | Aucune | 6% |
| Territoire du Yukon | TVH: 6% | Aucune | 6% |
Et que vous avez entrée les données suivantes:
CREATE TABLE cdmtax (
Cin CHAR(3) DEFAULT '000' not null,
TaxId BIGINT not null,
Compute CHAR(1),
Status CHAR(1),
CountryId BIGINT,
RegionId BIGINT,
TaxName1 VARCHAR(255),
TaxRate1 FLOAT,
TaxName2 VARCHAR(255),
TaxRate2 FLOAT,
TaxName3 VARCHAR(255),
TaxRate3 FLOAT,
Description VARCHAR(32000),
CreateDate CHAR(10),
CreateUserId CHAR(20),
LastUpdate CHAR(10),
UpdateUserId CHAR(20),
CONSTRAINT PKTAX PRIMARY KEY (Cin, TaxId)
);
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64256','C','A','10851','11066','TPS','6.0000','TVQ','7.5000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64257','C','A','10851','11062','TVH','14.0000','','0.0000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64258','C','A','10851','11060','TVH','14.0000','','0.0000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64259','C','A','10851','11061','TVH','14.0000','','0.0000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64260','S','A','10851','11058','TPS','6.0000','TVP','7.0000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64261','C','A','10851','11057','TVH','6.0000','','0.0000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64262','S','A','10851','11059','TPS','6.0000','TVP','7.0000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64263','S','A','10851','11068','TVH','6.0000','','0.0000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64264','S','A','10851','11063','TVH','6.0000','','0.0000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64265','S','A','10851','11064','TPS','6.0000','TVP','8.0000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64266','C','A','10851','11065','TPS','6.0000','TVP','10.0000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64267','S','A','10851','11067','TPS','6.0000','TVP','7.0000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64268','S','A','10851','11069','TVH','6.0000','','0.0000','','0.0000','','2006/06/14','GLADIR','2007/01/04','GLADIR');
Insert Into cdmtax(Cin,TaxId,Compute,Status,CountryId,RegionId,TaxName1,TaxRate1,TaxName2,TaxRate2,TaxName3,TaxRate3,Description,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('000','64585','S','A','10887','0','TVA','19.6000','','0.0000','','0.0000','','2006/06/19','GLADIR','2006/06/19','GLADIR');
CREATE TABLE cdmcountryregion(
CountryId BIGINT not null,
RegionId BIGINT not null,
MID CHAR(10),
Name_EN VARCHAR(255),
Name_FR VARCHAR(255),
Description VARCHAR(32000),
Status CHAR(1),
CreateDate CHAR(10),
CreateUserId CHAR(20),
LastUpdate CHAR(10),
UpdateUserId CHAR(20),
CONSTRAINT PKCOuNTRYREGION PRIMARY KEY (CountryId,RegionId)
);
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011057','AB','Alberta','Alberta','','A','2005/08/18','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011058','BC','British Columbia','Colombie-Britanique','','A','2005/08/18','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011059','MB','Manitoba','Manitoba','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011060','NB','New Brunswick','Nouveau-Brunswick','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011061','NF','Newfoundland','Terre-Neuve/Labrador','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011062','NS','Nova Scotia','Nouvelle-Écosse','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011063','NU','Nunavut','Nunavut','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011064','ON','Ontario','Ontario','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011065','PE','Prince Edward Island','Ile-du-Prince-Édouard','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011066','QC','Quebec','Québec','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011067','SK','Saskatchewan','Saskatchewan','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011068','NT','Northwest Territories','Territoire du Nord Ouest','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Insert Into cdmcountryregion(CountryId,RegionId,MID,Name_EN,Name_FR,Description,Status,CreateDate,CreateUserId,LastUpdate,UpdateUserId) Values ('0000010851','0000011069','YT','Yukon Territory','Territoire du Yukon','','A','2005/08/19','GLADIR','2005/08/22','GLADIR');
Tout d'abord, voici un premier exemple permet d'afficher des données complètes sur les taxes en Sybase:
SELECT
cdmcountryregion.Name_FR As RegionName,
cdmcountryregion.MID As MID,
cdmtax.TaxName1,cdmtax.TaxRate1,cdmtax.TaxRate2,
IF (Compute = 'S') THEN
Cast(cdmtax.TaxRate1 As Double)+Cast(cdmtax.TaxRate2 As Double)
ELSE
Cast(cdmtax.TaxRate1 As Double)+((Cast(cdmtax.TaxRate2 As Double)*(100.0+Cast(cdmtax.TaxRate1 As Double)))/100.0)
ENDIF
As Taux
FROM cdmtax
LEFT JOIN cdmcountryregion ON cdmcountryregion.CountryId=cdmtax.CountryId and cdmcountryregion.RegionId=cdmtax.RegionId
on obtiendra le résultat suivant:
| Nom de la province/territoire | MID | Taxe 1 | Taxe 2 | Taux |
| Québec | QC | TPS:6% | TVQ:7.5% | 13.95% |
| Nouvelle-Écosse | NS | TVH:14% | 14% | |
| Nouveau-Brunswick | NB | TVH:14% | 14% | |
| Terre-Neuve/Labrador | NF | TVH:14% | 14% | |
| Colombie-Britanique | BC | TPS:6% | TVP:7% | 13% |
| Alberta | AB | TVH:6% | 6% | |
| Manitoba | MB | TPS:6% | TVP:7% | 13% |
| Territoire du Nord Ouest | NT | TVH:6% | 6% | |
| Nunavut | NU | TVH:6% | 6% | |
| Ontario | ON | TPS:6% | TVP:8% | 14% |
| Ile-du-Prince-Édouard | PE | TPS:6% | TVP:10% | 16.6% |
| Saskatchewan | SK | TPS:6% | TVP:7% | 13% |
| Territoire du Yukon | YT | TVH:6% | 6% |
Dernière mise à jour : Dimanche, le 31 mai 2015