SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 [SOLVED]Query adjustment
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 09/04/2013 :  06:43:45  Show Profile  Reply with Quote
Hi,

I currently have a querry that gathers data for a report to be printed out.

Basicly what it dous is it checks wether the truck number assinged to a shipment is a "Bakwagen" and if the type of chauffeur/driver is a dag/day type.

SELECT Planlijst.Plan_ID, Planlijst.Datum, Planlijst.Nummer, Planlijst.Naam, Planlijst.Vertrek, Planlijst.Vertrek_Tikklok, Planlijst.Rit_1, Planlijst.Rit_2, Planlijst.Rit_3,
Planlijst.TrailerNo1, Planlijst.TrailerNo2, Planlijst.TrailerNo3, Planlijst.smsstatus, Planlijst.Incidentieel
FROM Planlijst LEFT OUTER JOIN
Vrachtwagens ON Planlijst.Nummer = Vrachtwagens.Nummer INNER JOIN
Chauffeurs ON Planlijst.Naam = Chauffeurs.Naam
WHERE (Planlijst.Datum = @Datum) AND (Chauffeurs.Type = 'Dag') AND (Vrachtwagens.Type = 'Bakwagen')

Now i want to also include shipments where the incidential field is set to 1, instead of the chauffeur beying a day type. So i adjusted my querry to the following:

SELECT Planlijst.Plan_ID, Planlijst.Datum, Planlijst.Nummer, Planlijst.Naam, Planlijst.Vertrek, Planlijst.Vertrek_Tikklok, Planlijst.Rit_1, Planlijst.Rit_2, Planlijst.Rit_3,
Planlijst.TrailerNo1, Planlijst.TrailerNo2, Planlijst.TrailerNo3, Planlijst.smsstatus, Planlijst.Incidentieel
FROM Planlijst LEFT OUTER JOIN
Vrachtwagens ON Planlijst.Nummer = Vrachtwagens.Nummer INNER JOIN
Chauffeurs ON Planlijst.Naam = Chauffeurs.Naam
WHERE (Planlijst.Datum = @Datum) AND (Planlijst.Incidentieel = 1 OR Chauffeurs.Type = 'Dag') AND (Vrachtwagens.Type = 'Bakwagen')

This however dous not work, it just shows all the results like before but not any shipments that are marked incidential.

Anyone knows why this is?

grts,
Necht

Edited by - nechtmarrie on 09/04/2013 09:10:06

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 09/04/2013 :  06:51:45  Show Profile  Reply with Quote
--try this
WHERE (Planlijst.Datum = @Datum) AND (Planlijst.Incidentieel = 1 OR Chauffeurs.Type = 'Dag') AND (Vrachtwagens.Type = 'Bakwagen')


--
Chandu
Go to Top of Page

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 09/04/2013 :  06:59:56  Show Profile  Reply with Quote
If i would use that all the orders where incidential = 0 would not be displayed anymore.
Go to Top of Page

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 09/04/2013 :  07:37:58  Show Profile  Reply with Quote
I tried running the querry with that adjustment just now and it returns no results at all
Go to Top of Page

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 09/04/2013 :  09:09:56  Show Profile  Reply with Quote
I solved it using an UNION, combining 2 querrys
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 09/04/2013 :  09:13:50  Show Profile  Reply with Quote
ok... try this too
SELECT Planlijst.Plan_ID, Planlijst.Datum, Planlijst.Nummer, Planlijst.Naam, Planlijst.Vertrek, Planlijst.Vertrek_Tikklok, Planlijst.Rit_1, Planlijst.Rit_2, Planlijst.Rit_3,
Planlijst.TrailerNo1, Planlijst.TrailerNo2, Planlijst.TrailerNo3, Planlijst.smsstatus, Planlijst.Incidentieel
FROM Planlijst LEFT OUTER JOIN
Vrachtwagens ON Planlijst.Nummer = Vrachtwagens.Nummer INNER JOIN
Chauffeurs ON Planlijst.Naam = Chauffeurs.Naam
WHERE (Planlijst.Datum = @Datum) AND (Planlijst.Incidentieel = 1 AND Chauffeurs.Type = 'Dag') AND (Vrachtwagens.Type = 'Bakwagen')


--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000