[SQL] Select waarde in select gebruiken

[Tazzios]

Ik wil de waarde van de SELECT(berijder_id) gebruiken in de WHERE statement van een SELECT die zich in de select bevindt. Onderstaande voorbeeld is waarschijnlijk duidelijker

Versimpelde situatie:
Syntax:

SQL: filename

SELECT berijder_id, startdatum.
    (SELECT     SUM(DATEDIFF(d,startdatum, einddatum)   AS 'aantal dagen'
    FROM         Verklaringen
    WHERE     (Verklaringen.Berijder_id = 2) 
FROM Reserveringen 

Op de plaats van 2 moet de Reserveringen.Berijder_id waarde van de desbetreffende record ingezet worden. om zo het aantal dagen uit verklaringen te kunnen tellen voor de desbetreffende berijder.

ik heb gekeken naar HAVING en IN SELECT maar ik daar rede ik het niet mee zag ik.

[RobIII]

Ik zou eens gaan kijken naar Hoe werken joins? of ALIASsen

[Tazzios]

ik ga me schamen als ik het met joins af kan. Zit ik weer te moeilijk te denken.

Update:

Met een JOIN werkt het inderdaad.

SQL: filename

 SELECT     ISNULL(CONVERT(VARCHAR, dbo.Berijders.Achternaam + ', ' + dbo.Berijders.Voornaam), 'Totaal: ') AS Berijder, CONVERT(VARCHAR, 
                      ISNULL(dbo.Autos.Kenteken, 'Totaal:')) AS Auto, SUM(CONVERT(decimal(10, 2), dbo.Autos.Fiscalewaarde / DAY(DATEADD(day, - 1, DATEADD(Month, 1, 
                      '2010-01-01'))) * DATEDIFF(d, CASE WHEN MONTH(Reserveringen.startdatum) = 01 AND Year(Reserveringen.startdatum) 
                      = 2010 THEN Reserveringen.startdatum ELSE '2010-01-01' END, ISNULL(CASE WHEN MONTH(Reserveringen.einddatum) = 01 AND 
                      Year(Reserveringen.einddatum) = 2010 THEN DATEADD(Day, 1, Reserveringen.einddatum) ELSE NULL END, DATEADD(Month, 1, '2010-01-01')))) + 0) 
                      AS catalogusprijsRato, SUM(DATEDIFF(d, CASE WHEN MONTH(Reserveringen.startdatum) = 01 AND Year(Reserveringen.startdatum) 
                      = 2010 THEN Reserveringen.startdatum ELSE '2010-01-01' END, ISNULL(CASE WHEN MONTH(Reserveringen.einddatum) = 01 AND 
                      Year(Reserveringen.einddatum) = 2010 THEN DATEADD(Day, 1, Reserveringen.einddatum) ELSE NULL END, DATEADD(Month, 1, '2010-01-01'))) + 0) 
                      AS aantaldagen, SUM(CONVERT(decimal(10, 2), dbo.Autos.Fiscalewaarde * (CONVERT(decimal(10, 2), dbo.Autos.Bijtelling) / 100) 
                      / 12 / DAY(DATEADD(day, - 1, DATEADD(Month, 1, '2010-01-01'))) * DATEDIFF(d, CASE WHEN MONTH(Reserveringen.startdatum) = 01 AND 
                      Year(Reserveringen.startdatum) = 2010 THEN Reserveringen.startdatum ELSE '2010-01-01' END, 
                      ISNULL(CASE WHEN MONTH(Reserveringen.einddatum) = 01 AND Year(Reserveringen.einddatum) = 2010 THEN DATEADD(Day, 1, 
                      Reserveringen.einddatum) ELSE NULL END, DATEADD(Month, 1, '2010-01-01')))) + 0) AS leaseprijs, SUM(DATEDIFF(d, 
                      CASE WHEN MONTH(verklaringen.startdatum) = 01 AND Year(verklaringen.startdatum) = 2010 THEN verklaringen.startdatum ELSE '2010-01-01' END, 
                      ISNULL(CASE WHEN MONTH(verklaringen.einddatum) = 01 AND Year(verklaringen.einddatum) = 2010 THEN DATEADD(Day, 1, verklaringen.einddatum) 
                      ELSE NULL END, DATEADD(Month, 1, '2010-01-01'))) + 0) AS verklaringsdagen
FROM         dbo.Reserveringen INNER JOIN
                      dbo.Autos ON dbo.Reserveringen.Auto_id = dbo.Autos.ID INNER JOIN
                      dbo.Berijders ON dbo.Reserveringen.Berijder_id = dbo.Berijders.ID LEFT OUTER JOIN
                      dbo.Verklaringen ON dbo.Berijders.ID = dbo.Verklaringen.Berijder_id
WHERE     (dbo.Reserveringen.Startdatum <= DATEADD(Month, 1, '2010-01-01')) AND (dbo.Reserveringen.Einddatum >= '2010-01-01' OR
                      dbo.Reserveringen.Einddatum IS NULL) AND (dbo.Verklaringen.Startdatum <= DATEADD(Month, 1, '2010-01-01')) AND 
                      (dbo.Verklaringen.Einddatum >= '2010-01-01') OR
                      (dbo.Reserveringen.Startdatum <= DATEADD(Month, 1, '2010-01-01')) AND (dbo.Reserveringen.Einddatum >= '2010-01-01' OR
                      dbo.Reserveringen.Einddatum IS NULL) AND (dbo.Verklaringen.Startdatum <= DATEADD(Month, 1, '2010-01-01')) AND 
                      (dbo.Verklaringen.Einddatum IS NULL)
GROUP BY dbo.Berijders.Achternaam + ', ' + dbo.Berijders.Voornaam, dbo.Autos.Kenteken WITH ROLLUP

Het totale resultaat is:
Berijdernaam, Kenteken, relatievecatalogusprijs(adv het aantal dagengereden), aantaldagen gereden, De te betalen leaseprijs per auto, aantal dagen dat er een verklaring wast in die week.
d.m.v. de ROLLUP het totaal per persoon

Hoeveel dagen een verklaring voor een bepaalde auto was mogen ze zelf uitgaan zoeken

Update2:
Werkt toch niet helemaal goed ik heb een persoon met 2 verklaringen bij deze persoon worden nu het aantal dagen dat hij de auto had verdubbelt. LEFT RIGHT JOIN bij verklaringen maakt niet uit
zodra ik de JOIN toevoeg kloppen het aantal dagen bereden niet meer.

[ Voor 105% gewijzigd door Tazzios op 28-01-2010 16:14 ]

[sopsop]

Wat een onleesbare brij met die case when. Ik heb geen zin om hier naar te kijken omdat ik eerst drie kwartier je selected columns aan het herindelen ben. En ik durf mijn handen in het vuur te steken als dat niet op een nettere manier kan.

[DamadmOO]

Tazzios schreef op donderdag 28 januari 2010 @ 10:29:
Update2:
Werkt toch niet helemaal goed ik heb een persoon met 2 verklaringen bij deze persoon worden nu het aantal dagen dat hij de auto had verdubbelt. LEFT RIGHT JOIN bij verklaringen maakt niet uit
zodra ik de JOIN toevoeg kloppen het aantal dagen bereden niet meer.

Dat kan je oplossen door een derived table te gebruiken die per berijder 1 row returned met het aantal dagen zodat je niet hoeft te summeren. Maar die query van je is dus totaal onleesbaar. Kan je niet een database overview geven met de 1:1, 1:N relaties. En wat je dan terug wilt krijgen.

Kijk verder ook eens naar je GROUP BY gedeelte. De gehele query is namelijk niet valid.

[MBV]

Nooit van layouten gehoord *zucht* Als er meer in een veld staat dan alleen een veldnaam verdient het z'n eigen regel, en zet je na elke komma dus een enter. CASE WHEN x THEN y ELSE z END: THEN en ELSE op een nieuwe regel.

Je wilt in je start-post iets doen met een select in een veld, wat daar echt niet voor bedoeld is. En dat kan echt prima met joins:

SQL:

SELECT R.berijder_id, R.startdatum, X.aantal_dagen AS 'aantal dagen'
FROM Reserveringen R
LEFT JOIN   (
    SELECT     SUM(DATEDIFF(d,startdatum, einddatum) AS aantal_dagen
    FROM         Verklaringen
  ) AS X
   ON r.berijder_id = X.berijder_id

Daarbij mag je dus alle logica in de subselects laten zitten, en de join-condities in de ON-clause, anders wordt het 100% onleesbaar. Door joins te gebruiken zou je alle condities kunnen groeperen, waardoor duidelijk is wat bij wat hoort, maar zoals je het nu hebt opgeschreven kan je de join weglaten en alles in de FROM neerzetten

@sopsop: sommige rapportagepakketten vereisen dat je alles in de query oplost. Dit kan veel overzichtelijker door een paar dingen door gewone code te laten doen, en misschien is er nu ook een handigere query te verzinnen, maar dit soort dingen groeien meestal

[ Voor 40% gewijzigd door MBV op 30-01-2010 12:27 ]

[Tazzios]

Vorige week kreeg ik te horen van ze dat een verklaring geld voor heel het jaar.
Het kan dus allemaal weer grondig verbouwd en versimpeld worden.

[YopY]

Even door een SQL formatter gehaald:

SQL:

SELECT   ISNULL(CONVERT(VARCHAR, dbo.Berijders.Achternaam + ', ' + dbo.Berijders.Voornaam), 'Totaal: ') AS Berijder,
         CONVERT(VARCHAR, ISNULL(dbo.Autos.Kenteken, 'Totaal:'))                                        AS Auto    ,
         SUM(CONVERT(DECIMAL(10, 2), dbo.Autos.Fiscalewaarde / DAY(DATEADD(DAY, - 1, DATEADD(MONTH, 1, '2010-01-01'))) * DATEDIFF(d,
         CASE
                  WHEN MONTH(Reserveringen.startdatum)    = 01
                  AND      YEAR(Reserveringen.startdatum) = 2010
                  THEN Reserveringen.startdatum
                  ELSE '2010-01-01'
         END, ISNULL(
                  CASE
                           WHEN MONTH(Reserveringen.einddatum)    = 01
                           AND      YEAR(Reserveringen.einddatum) = 2010
                           THEN DATEADD(DAY, 1, Reserveringen.einddatum)
                           ELSE NULL
                  END, DATEADD(MONTH, 1, '2010-01-01')))) + 0) AS catalogusprijsRato,
         SUM(DATEDIFF(d,
         CASE
                  WHEN MONTH(Reserveringen.startdatum)    = 01
                  AND      YEAR(Reserveringen.startdatum) = 2010
                  THEN Reserveringen.startdatum
                  ELSE '2010-01-01'
         END, ISNULL(
                  CASE
                           WHEN MONTH(Reserveringen.einddatum)    = 01
                           AND      YEAR(Reserveringen.einddatum) = 2010
                           THEN DATEADD(DAY, 1, Reserveringen.einddatum)
                           ELSE NULL
                  END, DATEADD(MONTH, 1, '2010-01-01')))     + 0) AS aantaldagen,
         SUM(CONVERT(DECIMAL(10, 2), dbo.Autos.Fiscalewaarde * (CONVERT(DECIMAL(10, 2), dbo.Autos.Bijtelling) / 100) / 12 / DAY(DATEADD(DAY, - 1, DATEADD(MONTH, 1, '2010-01-01'))) * DATEDIFF(d,
         CASE
                  WHEN MONTH(Reserveringen.startdatum)    = 01
                  AND      YEAR(Reserveringen.startdatum) = 2010
                  THEN Reserveringen.startdatum
                  ELSE '2010-01-01'
         END, ISNULL(
                  CASE
                           WHEN MONTH(Reserveringen.einddatum)    = 01
                           AND      YEAR(Reserveringen.einddatum) = 2010
                           THEN DATEADD(DAY, 1, Reserveringen.einddatum)
                           ELSE NULL
                  END, DATEADD(MONTH, 1, '2010-01-01')))) + 0) AS leaseprijs,
         SUM(DATEDIFF(d,
         CASE
                  WHEN MONTH(verklaringen.startdatum)    = 01
                  AND      YEAR(verklaringen.startdatum) = 2010
                  THEN verklaringen.startdatum
                  ELSE '2010-01-01'
         END, ISNULL(
                  CASE
                           WHEN MONTH(verklaringen.einddatum)    = 01
                           AND      YEAR(verklaringen.einddatum) = 2010
                           THEN DATEADD(DAY, 1, verklaringen.einddatum)
                           ELSE NULL
                  END, DATEADD(MONTH, 1, '2010-01-01'))) + 0) AS verklaringsdagen
FROM     dbo.Reserveringen
         INNER JOIN dbo.Autos
         ON       dbo.Reserveringen.Auto_id = dbo.Autos.ID
         INNER JOIN dbo.Berijders
         ON       dbo.Reserveringen.Berijder_id = dbo.Berijders.ID
         LEFT OUTER JOIN dbo.Verklaringen
         ON       dbo.Berijders.ID      = dbo.Verklaringen.Berijder_id
WHERE    (dbo.Reserveringen.Startdatum <= DATEADD(MONTH, 1, '2010-01-01')
         )
AND
         (
                  dbo.Reserveringen.Einddatum      >= '2010-01-01'
         OR       dbo.Reserveringen.Einddatum IS NULL
         )
AND
         (
                  dbo.Verklaringen.Startdatum <= DATEADD(MONTH, 1, '2010-01-01')
         )
AND
         (
                  dbo.Verklaringen.Einddatum >= '2010-01-01'
         )
OR
         (
                  dbo.Reserveringen.Startdatum <= DATEADD(MONTH, 1, '2010-01-01')
         )
AND
         (
                  dbo.Reserveringen.Einddatum      >= '2010-01-01'
         OR       dbo.Reserveringen.Einddatum IS NULL
         )
AND
         (
                  dbo.Verklaringen.Startdatum <= DATEADD(MONTH, 1, '2010-01-01')
         )
AND
         (
                  dbo.Verklaringen.Einddatum IS NULL
         )
GROUP BY dbo.Berijders.Achternaam + ', ' + dbo.Berijders.Voornaam,
         dbo.Autos.Kenteken WITH ROLLUP

en zelfs die kan er maar weinig van maken. Doe jezelf een plezier en refactor deze query in iets eenvoudigers, minder (formatterings)logica in de query, gebruik een view hier en daar (bijvoorbeeld voor de 'berijders' naam (een view met voornaam + ', ' + achternaam, evt. tussenvoegsel), bekijk je datumberekeningen (wat doen die precies?), etcetera.