SQL - date variable isn't being parsed correctly?
- by Bill Sambrone
I am pulling a list of invoices filtered by a starting and ending date, and further filtered by type of invoice from a SQL table. When I specify a range of 2013-07-01 through 2013-09-30 I am receiving 2 invoices per company when I expect 3. When I use the built in select top 1000 query in SSMS and add my date filters, all the expected invoices appear.
Here is my fancy query that I'm using that utilizing variables that are fed in:
DECLARE @ReportStart datetime
DECLARE @ReportStop datetime
SET @ReportStart = '2013-07-01'
SET @ReportStop = '2013-09-30'
SELECT Entity_Company.CompanyName,
Reporting_AgreementTypes.Description,
Reporting_Invoices.InvoiceAmount,
ISNULL(Reporting_ProductCost.ProductCost,0),
(Reporting_Invoices.InvoiceAmount - ISNULL(Reporting_ProductCost.ProductCost,0)),
(Reporting_AgreementTypes.Description + Entity_Company.CompanyName),
Reporting_Invoices.InvoiceDate
FROM Reporting_Invoices
JOIN Entity_Company ON Entity_Company.ClientID = Reporting_Invoices.ClientID
LEFT JOIN Reporting_ProductCost ON Reporting_ProductCost.InvoiceNumber =Reporting_Invoices.InvoiceNumber
JOIN Reporting_AgreementTypes ON Reporting_AgreementTypes.AgreementTypeID = Reporting_Invoices.AgreementTypeID
WHERE Reporting_Invoices.AgreementTypeID = (SELECT AgreementTypeID FROM Reporting_AgreementTypes WHERE Description = 'Resold Services')
AND Reporting_Invoices.InvoiceDate >= @ReportStart AND Reporting_Invoices.InvoiceDate <= @ReportStop
ORDER BY CompanyName,InvoiceDate
The above only returns 2 invoices per company. When I run a much more basic query through SSMS I get 3 as expected, which looks like:
SELECT TOP 1000 [InvoiceID]
,[AgreementID]
,[AgreementTypeID]
,[InvoiceDate]
,[Comment]
,[InvoiceAmount]
,[InvoiceNumber]
,[TicketID]
,Entity_Company.CompanyName
FROM Reporting_Invoices
JOIN Entity_Company ON Entity_Company.ClientID = Reporting_Invoices.ClientID
WHERE Entity_Company.ClientID = '9' AND
AgreementTypeID = (SELECT AgreementTypeID FROM Reporting_AgreementTypes WHERE Description = 'Resold Services')
AND Reporting_Invoices.InvoiceDate >= '2013-07-01' AND Reporting_Invoices.InvoiceDate <= '2013-09-30'
ORDER BY InvoiceDate DESC
I've tried stripping down the 1st query to include only a client ID on the original invoice table, the invoice date, and nothing else. Still only get 2 invoices instead of the expected 3. I've also tried manually entering the dates instead of the @ variables, same result. I confirmed that InvoiceDate is defined as a datetime in the table. I've tried making all JOIN's a FULL JOIN to see if anything is hiding, but no change. Here is how I stripped down the original query to keep all other tables out of the mix and yet I'm still getting only 2 invoices per client ID instead of 3 (I manually entered the ID for the type filter):
--DECLARE @ReportStart datetime
--DECLARE @ReportStop datetime
--SET @ReportStart = '2013-07-01'
--SET @ReportStop = '2013-09-30'
SELECT --Entity_Company.CompanyName,
--Reporting_AgreementTypes.Description,
Reporting_Invoices.ClientID,
Reporting_Invoices.InvoiceAmount,
--ISNULL(Reporting_ProductCost.ProductCost,0),
--(Reporting_Invoices.InvoiceAmount - ISNULL(Reporting_ProductCost.ProductCost,0)),
--(Reporting_AgreementTypes.Description + Entity_Company.CompanyName),
Reporting_Invoices.InvoiceDate
FROM Reporting_Invoices
--JOIN Entity_Company ON Entity_Company.ClientID = Reporting_Invoices.ClientID
--LEFT JOIN Reporting_ProductCost ON Reporting_ProductCost.InvoiceNumber = Reporting_Invoices.InvoiceNumber
--JOIN Reporting_AgreementTypes ON Reporting_AgreementTypes.AgreementTypeID = Reporting_Invoices.AgreementTypeID
WHERE Reporting_Invoices.AgreementTypeID = '22'-- (SELECT AgreementTypeID FROM Reporting_AgreementTypes WHERE Description = 'Resold Services')
AND Reporting_Invoices.InvoiceDate >= '2013-07-01' AND Reporting_Invoices.InvoiceDate <= '2013-09-30'
ORDER BY ClientID,InvoiceDate
This strikes me as really weird as it is pretty much the same query as the SSMS generated one that returns correct results. What am I overlooking?
UPDATE
I've further refined my "test query" that is returning only 2 invoices per company to help troubleshoot this. Below is the query and a relevant subset of data for 1 company from the appropriate tables:
SELECT Reporting_Invoices.ClientID,
Reporting_AgreementTypes.Description,
Reporting_Invoices.InvoiceAmount,
Reporting_Invoices.InvoiceDate
FROM Reporting_Invoices
JOIN Reporting_AgreementTypes ON Reporting_AgreementTypes.AgreementTypeID = Reporting_Invoices.AgreementTypeID
WHERE Reporting_Invoices.AgreementTypeID = (SELECT AgreementTypeID FROM Reporting_AgreementTypes WHERE Description = 'Resold Services')
AND Reporting_Invoices.InvoiceDate >= '2013-07-01T00:00:00' AND Reporting_Invoices.InvoiceDate <= '2013-09-30T00:00:00'
ORDER BY Reporting_Invoices.ClientID,InvoiceDate
The above only returns 2 invoices. Here is the relevant table data:
Relevant data from Reporting_AgreementTypes
AgreementTypeID Description
22 Resold Services
Relevant data from Reporting_Invoices
InvoiceID ClientID AgreementID AgreementTypeID InvoiceDate
16111 9 757 22 2013-09-30 00:00:00.000
15790 9 757 22 2013-08-30 00:00:00.000
15517 9 757 22 2013-07-31 00:00:00.000
Actual results from my new modified query
ClientID Description InvoiceAmount InvoiceDate
9 Resold Services 3513.79 7/31/13 00:00:00
9 Resold Services 3570.49 8/30/13 00:00:00
