MS SQL Server
  1. MS SQL Server
  3. Comment partitionner une table complexe
Intereting Posts
Obtenir la valeur de SQL Var conforme sur le text varchar Requête SQL pour sélectionner égal ou inférieur ou supérieur à Le catalogue de search en text intégral de SQL Server n'est pas entièrement mis à jour Emplacement des données SQL Server 2008 slqcmd Quelle est l'approche la plus simple à la list d'amis dans EF? Nom de file dynamic pour les sauvegardes SQL Server? SQL: comparaison des dates avec uniquement les colonnes Mois et Année comment désactiver le numéro automatique en sql? Commande de valeurs à afficher dans une vue hiérarchique Requête XML DML (Xpath) pour interdire les colonnes dupliacte. Il devrait tester si une colonne existe avant de l'insert dans Jointure SQL sur deux tables non liées ou ayant des keys primaires Le triggersur de mise à jour SQL ne peut pas gérer plusieurs inputs Solution élégante d'parsing PHP pour les files sources de text délimités par de grands tuyaux Comment avoir un triggersur dans une database insert des données dans une autre database sur une connection de server lié Comment créer une colonne et copyr une autre colonne dans la colonne nouvellement créée

Comment partitionner une table complexe

J'ai une table appelée CHECKINOUT dans SQL Server, la table a les colonnes suivantes: 

 PASSID CHECKTIME CHECKTYPE UID -------------------------------------------------- PS3 2015-08-05 01:12:02.100 0 CAA0322 PS3 2015-08-06 02:17:02.310 1 CAA0322 PS4 2015-08-03 01:02:03.200 0 CAA0322 PS4 2015-08-03 11:11:01.233 1 CAA0322 PS3 2015-08-02 11:11:01.210 0 CAA0322 PS3 2015-08-02 12:02:04.147 1 CAA0322 PS1 2015-09-05 11:11:01.210 0 CAA0322 PS1 2015-09-05 01:12:09.010 1 CAA0322

PASSID est le laissez-passer donné chaque fois que quelqu'un accède aux locaux et au moment où ils sont autorisés, leur heure d'logging est enregistrée et le type de chèque devient 0 

  CHECKTYPE=0

Quand ils checkout des locaux, le time qu'ils checkout est également enregistré à nouveau dans le checktime mais cette fois le drapeau checktype tourne à 1 

  CHECKTYPE=0

maintenant à la fin de la journée, je voudrais voir un rapport de chaque passe indépendamment du nombre de fois qu'il est entré et sortir avec une sortie comme 

 PASSID CheckInTime CheckOutTime UID PS1 2015-08-05 01:12:02.100 2015-08-06 02:17:02.310 CAA0322 PS3 2015-08-05 01:12:02.100 2015-08-06 02:17:02.310 CAA0322 PS3 2015-08-02 11:11:01.210 2015-08-02 12:02:04.147 CAA0322 PS4 2015-08-03 01:02:03.200 22015-08-03 11:11:01.233 CAA0322

L'affichage des résultats ci-dessus suppose ce qui suit

  • CheckInTime est le premier checktime avec checktype = 0 de cet ID dans desc
  • CheckOutTime est le premier checktime avec checktype = 1 de cet ID dans desc

J'ai essayé d'utiliser des partitions mais j'ai échoué, je ne peux que returnner un logging, mais j'aimerais voir tous les loggings mon code était ci-dessous, mais il ne renvoie qu'un seul logging, j'aimerais returnner tout sans spécifier de passid 

 declare @PassID varchar(30)='PS3'; with LastEntryData(PASSID, CHECKTIME, CHECKTYPE, GateID, UID) as ( select top 2 c.PASSID, c.CHECKTIME, c.CHECKTYPE, c.GateID, c.UID from CHECKINOUT c where c.PASSID = @PassID order by c.CHECKTIME desc ), CheckInTime(CHECKTIME) as ( select i.CHECKTIME from LastEntryData i where i.CHECKTYPE = 0), CheckOutTime(CHECKTIME) as ( select i.CHECKTIME from LastEntryData i where i.CHECKTYPE = 1) select l.PASSID, CheckInTime = i.CHECKTIME, CheckOutTime = o.CHECKTIME, l.UID from LastEntryData l, CheckInTime i, CheckOutTime o group by l.PASSID, l.GateID, l.UID, i.CHECKTIME, o.CHECKTIME

Faisons une table. (Vous obtiendrez plus de réponses si vous faites cela.) 

 create table checkinout ( passid char(3) not null, checktime timestamp not null default current_timestamp, checktype integer not null check (checktype in (1, 0)), uid char(7) not null ); insert into checkinout values ('PS3', '2015-08-05 01:12:02.100', 0, 'CAA0322'), ('PS3', '2015-08-06 02:17:02.310', 1, 'CAA0322'), ('PS4', '2015-08-03 01:02:03.200', 0, 'CAA0322'), ('PS4', '2015-08-03 11:11:01.233', 1, 'CAA0322'), ('PS3', '2015-08-02 11:11:01.210', 0, 'CAA0322'), ('PS3', '2015-08-02 12:02:04.147', 1, 'CAA0322'), ('PS1', '2015-09-05 11:11:01.210', 0, 'CAA0322'), ('PS1', '2015-09-05 01:12:09.010', 1, 'CAA0322');

Je vais faire quelques suppositions éclairées, parce que vos données d'échantillon ne correspondent pas à votre production attendue.

Cette requête (dans PostgreSQL) returnne toutes les heures d'arrivée. 

 select uid, passid, checktime as time_in from checkinout where checktype = 0 order by uid, passid, checktime; 
 uid passid time_in
 -
 CAA0322 PS1 2015-09-05 11: 11: 01.21
 CAA0322 PS3 2015-08-02 11: 11: 01.21
 CAA0322 PS3 2015-08-05 01: 12: 02.1
 CAA0322 PS4 2015-08-03 01: 02: 03.2

Une sous-requête renverra les heures de paiement. Cela fonctionnera sur n'importe quel dbms SQL. 

 select t1.uid, t1.passid, t1.checktime, (select min(checktime) from checkinout where uid = t1.uid and passid = t1.passid and checktime >= t1.checktime and checktype = 1) as time_out from checkinout t1 where checktype = 0 order by t1.uid, t1.passid, t1.checktime; 
 uid passid time_in time_out
 -
 CAA0322 PS1 2015-09-05 11: 11: 01.21  
 CAA0322 PS3 2015-08-02 11: 11: 01.21 2015-08-02 12: 02: 04.147
 CAA0322 PS3 2015-08-05 01: 12: 02.1 2015-08-06 02: 17: 02.31
 CAA0322 PS4 2015-08-03 01: 02: 03.2 2015-08-03 11: 11: 01.233

Les mauvaises données expliquent la valeur NULL.

Si vos données et vos employés sont très bien élevés et si votre plate-forme prend en charge les fonctions analytiques, vous pouvez utiliser lead () ou lag () pour rassembler les pièces. (Je pense que SQL Server 2012+ supporte lead () et lag ().) 

 with checkinout_extended as ( select uid, passid, checktime as time_in, checktype, lead(checktime) over (partition by uid, passid order by checktime, checktype) as time_out from checkinout ) select uid, passid, time_in, time_out from checkinout_extended where checktype = 0 order by uid, passid, time_in;