Discussion :

[Piwecel]

Bonjour,

Je suis débutant en SQL j'ai l'exercice suivant:

Je dois calculer le taux d'adresse IP disponible et afficher un pourcentage entre 0 et 60 / 60.01 et 90 et plus de 90.

table:

connexions (ref_mat, ref_mat_co,num_port,vlan)
correspondance(ref,code)
matos (ref,model,mac,version,type,total_ports)
reseaux(code,id_reseau,mask,status)

j'ai essayé la commande suivante mais cela ne donne pas du tout ce que je recherche:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
select r.reseaux,r.mask,r.status, 
round((Select count(*) 
from [network].[dbo].[Connexions] 
where r.code=vlan)/convert(float,status) *100,2) 
'taux d''occuppation', 
case 
when (round((Select count(*) 
from [network].[dbo].[Connexions] 
where r.code=vlan)/convert(float,status) *100,2)) 
between 0 and 60 then 'Libre au moins à 60%' 
when (round((Select count(*) 
from [network].[dbo].[Connexions] 
where r.code=vlan)/convert(float,status) *100,2))

[sabotage]

C'est une sacrée pagaille ce que tu as écris quand même.
- Tu utilises un alias "r" qui n'est pas défini.
- Cet alias "r" est utilisé avec la colonne "code" et la colonne "reseaux" qui sont dans deux tables différentes et qui ne sont pas citées dans la requête.

Bref une requête se construit morceau par morceau, comme tout code.
N'écris pas d'un bloc une grosse purée de pois.

C'est difficile de t'aider sans en savoir plus sur le contenu des colonnes.
Tu essaies visiblement de faire le ratio entre le nombre de ligne dans "connexions" et la colonne status dans "reseaux".
Il va falloir déjà nous expliquer ça.

[Piwecel]

Bonjour ,

Merci pour la modification de mon message.

J'avais déclaré la table r. (Réseaux ) dans une précédente commande .
Pour trouver le taux d' adressage je pense qu'il faut calculer le nombre de bit à 0 dans le masque et c'est la que je bloque je n'ai trouvé aucune commande qui effectue ce calcul .

[aieeeuuuuu]

Bonjour,

Quel est le type de vos colonnes ?

pour votre problème sur la masque, les opérateur bit à bit pourraient sans doute vous être utiles.

Donnez une exemple de données, on pourra mieux vous aider.

[ch3lte]

Voila le sujet complet de notre TP : (bien sur la première est déjà faites ^^)

1- Ajouter une colonne "VLan" dans la table "Connexions"

2- Ajouter une colonne "Status" dans la table "Reseaux"
Le status devra correspondre aux conditions suivantes :
• Si le Taux d'adressage entre 0 et 60% alors : "Espace d'adresse dispo au moins à 60%"
• Si le Taux d'adressage entre 60.01 et 90% alors: "Espace d'adressage chargé entre + 60 et 90%"
• Sinon "Attention!! Espace d'adressage occupéà + de 90%"

3- Ecrire une requête qui met à jour tous les status de tous les réseaux.
4- Ecrire une procédure qui met à jour le status d'un réseau dont l'id est passé en paramètre.

Petit rappel des différente table :

connexions (ref_mat, ref_mat_co,num_port,vlan)
correspondance(ref,code)
matos (ref,model,mac,version,type,total_ports)
reseaux(code,id_reseau,mask,status)

Nous avons le meme TP à faire, mais pour la 2 j'ai aucune idée par quoi commencer car pour savoir combien il y a d'hôte sur un réseau il faut que dans la table connexion on sache dans quelle réseau les equipements sont connecté, alors faut il se referer en fonction du VLAN dans connexions ? est-ce que VLAN dans connexions correspond au code dans réseaux ? (sachant que cette colonne est vide car on la creer a la question 1) Pour plus de detail je vous communique le script de création de la database (fonctionne que sur sql management studio) en piece jointe !

En éspérant qu'on puisse nous aider

[elsuket]

Montrez ce que vous avez fait, expliquez où vous êtes bloqués : on ne va pas faire vos exercices !

@++

[ch3lte]

bloquer à la question 1, il ne dise pas explicitement de remplir VLan de la table connexions en fonction du code dans la table reseau mais pour la suite c'est plus simple (si vous pensez qu'on peux faire autrement sa serait cool

question 1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
alter table Connexions
add  VLan varchar(4)

pour récupérer les code des réseaux en fonction des ref, il récupère bien les bons codes, cependant certaines connexions ont accès à 2 réseaux, sauf que ma commande récupère ligne par ligne chaque code de chaque connexion et ne combine pas quand une connexion qui a 2 VLAN et donc dans connexion on a 8 lignes mais la commande en récupère 12 car dans les 8 connexions, 4 ont accès à 2 réseaux... je sais qu'il faut faire un group by mais je suis perdu..

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT Reseaux.code
	  FROM Reseaux
	  LEFT JOIN Correspondance ON Correspondance.code=Reseaux.code
	  INNER JOIN Matos ON Matos.Ref=Correspondance.ref
	  INNER JOIN Connexions ON Connexions.Ref_mat=matos.Ref

question 2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
alter table reseaux
add  status varchar(100)

une fois que la colonne VLan est rempli, le code suivant est fonctionnel.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
select distinct R.code,R.Id_reseau,R.mask,
case 
	when (round((Select count(*)
from Connexions
where VLan=code) /(select dbo.calcul_CIDR(R.mask)) *100,2))
between 0 and 60 then 'Libre au moins à 60%'
when (round((Select count(*)
from Connexions
where VLan=code)/(select dbo.calcul_CIDR(R.mask)) *100,2))
between 61 and 90 then 'Occupation entre +60% et 90%'
else 'Attention !! occupation à + de 90%'
end
From Reseaux AS R, Correspondance AS C, Matos AS M

Question 3

idem que pour la 2

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
update Reseaux 
set status = case 
	when (round((Select count(*)
from Connexions
where VLan=code) /(select dbo.calcul_CIDR(R.mask)) *100,2))
between 0 and 60 then 'Libre au moins à 60%'
when (round((Select count(*)
from Connexions
where VLan=code)/(select dbo.calcul_CIDR(R.mask)) *100,2))
between 61 and 90 then 'Occupation entre +60% et 90%'
else 'Attention !! occupation à + de 90%'
end
From Reseaux AS R, Correspondance AS C, Matos AS M

question 4 :

idem pour la 2 et 3

create procedure maj_reseaux(@Id_reseau varchar(50))
as
begin
update Reseaux
set status = case
when (round((Select count(*)
from Connexions
where VLan=code) /(select dbo.calcul_CIDR(R.mask)) *100,2))
between 0 and 60 then 'Libre au moins à 60%'
when (round((Select count(*)
from Connexions
where VLan=code)/(select dbo.calcul_CIDR(R.mask)) *100,2))
between 61 and 90 then 'Occupation entre +60% et 90%'
else 'Attention !! occupation à + de 90%'
end
From Reseaux AS R, Correspondance AS C, Matos AS M
where ref=@Id_reseau;

end


En gros ce qui bloque c'est juste la 1 ^^

Ce qui est possible aussi c'est qu'on remplis pas la colonne VLan mais par contre il faut trouver la requete pour calculer le nombre de connexion par code reseau pour que je puisse modifier la partie "Select count(*)
from Connexions
where VLan=code"
qui (si la question 1 fonctionne) recupere le nombre d'utilisateur pour ensuite le diviser par le nombre d'ip possible (recuperer grace a une fonction qui check en fonction du mask le nombre d'ip) pour diviser par 100 et avoir notre pourcentage..

Si vous avez besoin de plus d'info faites le moi savoir ! ça fait deja un moment que je lutte ^^

elsuket je suis d'accord avec toi ! Tkt on ne chaume pas lol