Section courante

A propos

Section administrative du site

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/territoireMIDTaxe 1Taxe 2Taux
QuébecQCTPS:6%TVQ:7.5%13.95%
Nouvelle-ÉcosseNSTVH:14% 14%
Nouveau-BrunswickNBTVH:14% 14%
Terre-Neuve/LabradorNFTVH:14% 14%
Colombie-BritaniqueBCTPS:6%TVP:7%13%
AlbertaABTVH:6% 6%
ManitobaMBTPS:6%TVP:7%13%
Territoire du Nord OuestNTTVH:6% 6%
NunavutNUTVH:6% 6%
OntarioONTPS:6%TVP:8%14%
Ile-du-Prince-ÉdouardPETPS:6%TVP:10%16.6%
SaskatchewanSKTPS:6%TVP:7%13%
Territoire du YukonYTTVH:6% 6%


Dernière mise à jour : Dimanche, le 31 mai 2015