3

I may just be setting up my query completely incorrect but my expected result set that I need returned is Nuestra B & Nosotros B since they fall into the date range respectively. I.E. Nuestra B feeduedate >= '20160301' AND fj.feeduedate <='20160330' and for Nosotros B fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606' they fall into place.

However when I run this query attempting to return the result I get 0 returned in my result set.

What is incorrect with my query? Below is sample data & DDL

    CREATE TABLE [dbo].[fei](
    [violatorsName] [varchar](500) NOT NULL,
    [violationNumber] [varchar](100) NOT NULL,
    [violationDate] [date] NOT NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[FJI](
    [violatorsName] [varchar](500) NOT NULL,
    [VIN] [varchar](100) NOT NULL,
    [vfees1] [float] NOT NULL,
    [vfees2] [float] NOT NULL,
    [vfees3] [float] NOT NULL,
    [vfees4] [float] NOT NULL,
    [vfees5] [float] NOT NULL,
    [vfees6] [int] NULL,
    [feeduedate] [date] NULL,
    [totalvfees] [float] NOT NULL,
    [totalvfeespaid] [float] NOT NULL,
    [vfeesremaining] [float] NOT NULL,
    [vfee7] [float] NOT NULL,
    [vfee8] [float] NOT NULL,
    [vfee9] [float] NOT NULL,
    [vfee10] [float] NOT NULL,
    [vfee11] [float] NOT NULL,
    [vfee12] [float] NULL,
    [vfee13] [float] NULL,
    [vfee14] [float] NULL,
    [vfee15] [float] NULL,
    [vfee16] [float] NULL
) ON [PRIMARY]

    INSERT [dbo].[fei] ([violatorsName], [violationNumber], [violationDate]) VALUES (N'Nostra A', N'3244', CAST(0xE63C0B00 AS Date))
INSERT [dbo].[fei] ([violatorsName], [violationNumber],  [violationDate]) VALUES (N'Nuestra B', N'408', CAST(0xE53C0B00 AS Date))
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros A', N'41', 1917.71, 0, 898.3, 10.870000, 0, 1906, CAST(0x353B0B00 AS Date), 9948, 9773, 7867, 1429.5, 9296.5, 401.53999999999996, 9371, 2826.88, 20782, 28875.31, 222059.4, 0, 0)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'211', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, CAST(0x2D3B0B00 AS Date), 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'211', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, CAST(0x2D3B0B00 AS Date), 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'311', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170101', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'811', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170103', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'11111', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170301', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)
INSERT [dbo].[FJI] ([violatorsName], [VIN], [vfees1], [vfees2], [vfees3], [vfees4], [vfees5], [vfees6], [feeduedate], [totalvfees], [totalvfeespaid], [vfeesremaining], [vfee7], [vfee8], [vfee9], [vfee10], [vfee11], [vfee12], [vfee13], [vfee14], vfee15, vfee16) VALUES (N'Nosotros B', N'77711', 2339.45, 0, 1545.14, 2.2, 69.13, NULL, '20170301', 6192, 6192, 6192, 0, 6192, 3404, 2788, 3955.9299999999994, 0, 4856.81, 0, 0, 15421.550000000001)



  SELECT ISNULL(fe.violatorsName,fj.violatorsName)
     , TotalSW  = COUNT(fe.violationNumber)
     , TotalSNT = COUNT(fj.VIN)
     , totalvfees = SUM(totalvfees)
     , calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
     , AdminFee = SUM(COALESCE(vfee12,0))
     , SecFee = SUM(COALESCE(vfee13,0))
FROM fei fe
FULL OUTER JOIN fji fj ON fj.violatorsName =fe.violatorsName
WHERE fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606'
AND fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName)


<strong>EDIT</strong><br>
    SELECT ISNULL(fe.violatorsName,fj.violatorsName)
     , TotalSW  = COUNT(fe.violationNumber)
     , TotalSNT = COUNT(fj.VIN)
     , totalvfees = SUM(totalvfees)
     , calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
     , AdminFee = SUM(COALESCE(vfee12,0))
     , SecFee = SUM(COALESCE(vfee13,0))
FROM fei fe
FULL OUTER JOIN fji fj ON fj.violatorsName =fe.violatorsName
WHERE ((fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606')
OR (fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'))
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName)
3
  • 2
    Change WHERE to AND. Or change AND fj. to OR fj. Jun 22, 2017 at 15:58
  • 1
    Looks like changing it to an OR worked. Testing with larger set of data. Jun 22, 2017 at 16:04
  • What is the expected output? Jun 22, 2017 at 17:00

2 Answers 2

9

When you have an OUTER JOIN, any WHERE clause against any outer table turns that into an inner table. Meaning only rows where that predicate can be evaluated will make it through. Effectively a WHERE clause in an OUTER JOIN makes that an INNER JOIN.

I would try:

AND ((fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606')
  OR  (fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'))

Or:

WHERE (fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606')
  OR  (fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330')
6
  • Do the parenthesis matter? Jun 22, 2017 at 16:10
  • 2
    @user2676140 It can in some cases. I always try to use them for the reader, not necessarily for SQL Server. Jun 22, 2017 at 16:15
  • Testing this on my production data it seems to be returning a count of all rows in the table regardless of date criteria for TotalSW & TotalSWT- for that specific violatername Jun 22, 2017 at 16:17
  • @user2676140 So on data different from that in the question? Can you put enough sample data into the question to reproduce the problem here? (I mean, this sample data of 4 rows doesn't have a single match, if that's similar to production data then maybe you should consider a union all instead of a full outer join. Or including sample data that includes matches. In any case, please clearly indicate your desired output, including all column values, not just which names you expect in the result.) Jun 22, 2017 at 16:21
  • Take a look at updated DDL. It returns 2 for Nosotros B even tho only 1 date for feeduedate falls between 20160301 and 20160330 Jun 22, 2017 at 16:38
4

Another way to have the same results - but probably with a different execution plan - is by getting the rows wanted from each table separately and then FULL JOIN them:

FROM 
    ( SELECT * FROM fei 
      WHERE ViolationDate >= '20170601'
        AND ViolationDate <= '20170606'
    ) AS fe
  FULL OUTER JOIN 
    ( SELECT * FROM fji 
      WHERE feeduedate >= '20160301' 
        AND feeduedate <= '20160330'
    ) AS fj 
  ON 
    fj.violatorsName = fe.violatorsName
-- no WHERE 

Tested at dbfiddle.uk.

SELECT ISNULL(fe.violatorsName,fj.violatorsName)
     , TotalSW  = COUNT(fe.violationNumber)
     , TotalSNT = COUNT(fj.VIN)
     , totalvfees = SUM(totalvfees)
     , calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
     , AdminFee = SUM(COALESCE(vfee12,0))
     , SecFee = SUM(COALESCE(vfee13,0))
FROM fei fe
FULL OUTER JOIN fji fj ON fj.violatorsName =fe.violatorsName
WHERE (fe.ViolationDate >= '20170601' AND fe.ViolationDate <= '20170606'
 OR fj.feeduedate >= '20160301' AND fj.feeduedate <='20160330'
) 
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName);
GO
(No column name) | TotalSW | TotalSNT | totalvfees | calculatedvfee | AdminFee |  SecFee
:--------------- | ------: | -------: | ---------: | -------------: | -------: | ------:
Nosotros B       |       0 |        2 |      12384 |           5576 |        0 | 9713.62
Nuestra B        |       1 |        0 |       null |              0 |        0 |       0

Warning: Null value is eliminated by an aggregate or other SET operation.
SELECT ISNULL(fe.violatorsName,fj.violatorsName)
     , TotalSW  = COUNT(fe.violationNumber)
     , TotalSNT = COUNT(fj.VIN)
     , totalvfees = SUM(totalvfees)
     , calculatedvfee = SUM(COALESCE(totalvfees,0)-COALESCE(vfee9,0))
     , AdminFee = SUM(COALESCE(vfee12,0))
     , SecFee = SUM(COALESCE(vfee13,0))
FROM 
    (select * from fei WHERE ViolationDate >= '20170601' AND ViolationDate <= '20170606') fe
FULL OUTER JOIN 
    (select * from fji WHERE feeduedate >= '20160301' AND feeduedate <='20160330') fj 
  ON fj.violatorsName =fe.violatorsName
GROUP BY ISNULL(fe.violatorsName, fj.violatorsName);
GO
(No column name) | TotalSW | TotalSNT | totalvfees | calculatedvfee | AdminFee |  SecFee
:--------------- | ------: | -------: | ---------: | -------------: | -------: | ------:
Nosotros B       |       0 |        2 |      12384 |           5576 |        0 | 9713.62
Nuestra B        |       1 |        0 |       null |              0 |        0 |       0

Warning: Null value is eliminated by an aggregate or other SET operation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.