Utilisez l'algèbre booleanne dans tsql pour éviter une instruction CASE ou pour traiter des conditions WHERE complexes

Je suis tombé sur un scénario, je vais l'expliquer avec des données factices. Voir le tableau ci-dessous

Select * from LUEmployee empId name joiningDate 1049 Jithin 3/9/2009 1017 Surya 1/2/2008 1089 Bineesh 8/24/2009 1090 Bless 7/15/2009 1014 Dennis 1/5/2008 1086 Sus 9/10/2009 

J'ai besoin d'incrémenter la colonne de l'année de 1, seulement si les mois sont Jan, Mar, Juillet ou Déc.

 empId name joiningDate derived Year 1049 Jithin 3/9/2009 2010 1017 Surya 1/2/2008 2009 1089 Bineesh 8/24/2009 2009 1090 Bless 7/15/2009 2010 1014 Dennis 1/5/2008 2009 1086 Sus 9/10/2009 2009 

L'année dérivée est la colonne requirejse

Nous avons été en mesure de réaliser cela facilement avec une déclaration de cas comme ci-dessous

 Select *, YEAR(joiningDate) + CASE WHEN MONTH(joiningDate) in (1,3,7,12) THEN 1 ELSE 0 END from LUEmployee 

Mais il y a une condition supplémentaire de PM sur place, ne pas utiliser l'instruction CASE, CASE est inefficace. Insearch d'une soultion, Nous avons abouti à une solution suivante, une solution utilisant K-map binary, Comme suit


Si le nombre 1 à 12 représente les mois de janvier à décembre, voir le résultat binary entrez la description de l'image ici

La façon d'exprimer Karnaugh Map est donnée ci-dessous. entrez la description de l'image ici

le résultat sera

entrez la description de l'image ici

Nous devons réaliser l'expression avec les opérations binarys du server sql

 eg: binary of 12 = 1100 in the k-map, a = 1, b = 1, c = 0, d = 0 Similarly, binary of 7 = 0111 in the k-map, a = 0, b = 1, c = 1, d = 1 

pour get le bit le plus à gauche (d), nous devrons déplacer le bit vers la droite de 3 positions et le masque tous les bits sauf LSB.

 eg: ((MONTH(joiningDate)/8)&1) 

De même, deuxième bit de gauche (c), nous devons déplacer le bit vers la droite de 2 positions, puis masquer tous les bits sauf LSB

 eg: ((MONTH(joiningDate)/4)&1) 

Enfin, chaque bit peut être représenté comme

 so a = ((MONTH(joiningDate)/8)&1) b = ((MONTH(joiningDate)/4)&1) c = ((MONTH(joiningDate)/2)&1) d = (MONTH(joiningDate)&1) a inverse = (((MONTH(joiningDate)/8)&1)^1) b inverse = (((MONTH(joiningDate)/4)&1)^1) c inverse = (((MONTH(joiningDate)/2)&1)^1) d inverse = ((MONTH(joiningDate)&1)^1) 

Le code final sera

 SELECT *, YEAR(joiningDate) + CAST( ((MONTH(joiningDate)/8)&1)*((MONTH(joiningDate)/4)&1)*(((MONTH(joiningDate)/2)&1)^1)*((MONTH(joiningDate)&1)^1) | (((MONTH(joiningDate)/8)&1)^1)*(((MONTH(joiningDate)/4)&1)^1)*(MONTH(joiningDate)&1) | (((MONTH(joiningDate)/8)&1)^1)*((MONTH(joiningDate)/2)&1)*(MONTH(joiningDate)&1) AS INT) [derivedYear] FROM LUEmployee 

Le résultat sera

entrez la description de l'image ici


Question: Il peut y avoir des idées simples et less complexes, n'hésitez pas à le partager.

J'aime find un plus simple, ainsi que partager l'idée.Voici les conditions possibles sont 12 (12 mois). Nous pouvons utiliser k-map pour un nombre encore plus grand de conditions. Feutre comme k-map est pratique pour jusqu'à 64 conditions.

Ma première réaction serait de défendre l'utilisation de la clause dans cette affaire. Mais si vous n'êtes absolument pas autorisé à l'utiliser, vous pouvez simplement append une table avec les valeurs du mois et de l' incrément :

 LUMonthIncrement Month Increment 1 1 2 0 3 1 4 0 5 0 6 0 7 1 8 0 9 0 10 0 11 0 12 1 

Ensuite, vous pouvez vous joindre à cette table et append simplement l'incrément:

 Select LUEmployee.*, YEAR(joiningDate) + LUMonthIncrement.Increment as derivedYear from LUEmployee join LUMonthIncrement on MONTH(LUEmployee.joiningDate) = LUMonthIncrement.Month 

Cependant, il est peu probable que cela soit beaucoup plus performant, car pour joindre à LUMonthIncrement l' LUMonthIncrement MONTH(LUEmployee.joiningDate) doit être évaluée pour chaque ligne de la table LUEmployee .

Dans ce cas précis, vous pourriez faire une UNION car vous avez deux sous-sets distincts de votre jeu d'input qui ne dépendent pas l'un de l'autre et les critères de partage sont bien définis. Donc, vous pourriez faire quelque chose comme:

 Select *, YEAR(joiningDate) + 1 as derived_year from LUEmployee WHERE MONTH(joiningDate) = 1 OR MONTH(joiningDate) = 3 OR MONTH(joiningDate) = 7 OR MONTH(joiningDate) = 12 UNION Select *, YEAR(joiningDate) as derived_year from LUEmployee WHERE NOT (MONTH(joiningDate) = 1 OR MONTH(joiningDate) = 3 OR MONTH(joiningDate) = 7 OR MONTH(joiningDate) = 12) 

Prenant le concept de @ Month1429080 d'une table Month un pas plus loin, et le transforme en une table de gamme ; cela permettra l'élimination de l'appel à MONTH() dans la jointure. En supposant que vous avez une table de calendar (qui sont stupides utiles), vous pouvez build la requête comme ceci:

 WITH LUMonthIncrement AS (SELECT month, increment FROM (VALUES (1, 1), (2, 0), (3, 1), (4, 0), (5, 0), (6, 0), (7, 1), (8, 0), (9, 0), (10, 0), (11, 0), (12, 1)) m(month, increment)) SELECT LUEmployee.empId, LUEmployee.name, LUEmployee.joiningDate, IncrementRange.year FROM LUEmployee JOIN (SELECT Calendar.calendarDate AS rangeStart, DATEADD(month, 1, Calendar.calendarDate) AS rangeEnd, Calendar.year + LUMonthIncrement.increment AS year FROM Calendar JOIN LUMonthIncrement ON LUMonthIncrement.month = Calender.month WHERE Calendar.dayOfMonth = 1) IncrementRange ON LUEmployee.joiningDate >= IncrementRange.rangeStart AND LUEmployee.joiningDate < IncrementRange.rangeEnd 

(Non testé pour le moment)

Oui, j'utilise toujours une fonction ignorant l'index (en particulier, DATEADD(...) ). Cependant, la reference de la sous-requête est susceptible de s'exécuter en premier , et returnnera 12 lignes par an, et la jointure à LUEmployee est gratuite utiliser n'importe quel index sur cette table (qui est susceptible d'être beaucoup plus grand que le résultat de la sous-sélection). En supposant que Calendar a un index commençant par dayOfMonth (c'est une table de dimension, il devrait …), IncrementRange devrait être construit instantanément.

(Notez que j'utilise un formulaire de fourchette général ici, ce qui sera utile pour les types avec une portion de time attachée.Ceci est utile pour des choses comme l'agrégation des ventes par mois … Si vous utilisez 2012 avec une date ssortingcte tapez, vous pourriez potentiellement simplement join directement la table Calendar directement sur la valeur de la date, et passer la main sur la plage.)

Si vous voulez utiliser la logique de bits, voici un moyen

 SELECT [empId], [name], [joiningDate] , [derived Year] = YEAR(joiningDate) + (1 - cast(MONTH(joiningDate) / 8 as bit)) * (MONTH(joiningDate) % 2) - (cast(MONTH(joiningDate) / 5 as bit)) * (1 - cast(MONTH(joiningDate) / 6 as bit)) + (cast(MONTH(joiningDate) / 12 as bit)) FROM LUEmployee 

Démo SQLFiddle avec des données étendues pour avoir chaque mois disponible

Expliquer les bits

  • (1 - cast(MONTH(joiningDate) / 8 as bit)) * (MONTH(joiningDate) % 2) la première partie returnne 1 pour mois (nombre) less de 8, la deuxième partie vérifie la parité où 1 est impair, set ils ajoutent 1 pour 1,3, 5, 7; pour enlever les 5 dont nous avons besoin
  • (cast(MONTH(joiningDate) / 5 as bit)) * (1 - cast(MONTH(joiningDate) / 6 as bit)) la première partie renvoie 1 pour chaque valeur supérieure ou égale à 5, la seconde partie renvoie 1 pour chaque valeur less de 6, la seule intersection est 5
  • (cast(MONTH(joiningDate) / 12 as bit) return 1 seulement pour décembre

Avec toute l'option ici, si j'étais dans votre position, je les vérifierais tous pour la performance et rendrais count à mon PM avec datatables, je suis tout à fait sûr qu'il y a une leçon à apprendre.