Reputation: 370
How to use WHERE clause with a PIVOT
Why, do my where clauses here get ignored, it's summing invoices regardless of the valued entered as parameters, spent hours on this and going mad!
ALTER PROCEDURE [dbo].[Usp_custom_dash_metric_invoicespend]
@CompanyGUID UNIQUEIDENTIFIER,
@UserGUID UNIQUEIDENTIFIER,
@CompanyUserGUID UNIQUEIDENTIFIER,
@InvoiceYearFrom CHAR(4),
@InvoiceYearTo CHAR(4),
@CompanySupplierGUID UNIQUEIDENTIFIER
AS
BEGIN
WITH _d
AS (SELECT a.totalgrossvaluehome1 AS tgvh1,
invoicedate AS ind,
c.guid AS cug,
a.companysupplierguid AS cus,
c.userguid AS ug,
b.guid AS cg
FROM invoices a (nolock)
JOIN companies b (nolock)
ON ( a.companyguid = b.guid )
JOIN companyusers c (nolock)
ON ( c.userguid = a.createdbyguid )
WHERE a.invoicedate BETWEEN Cast(Isnull(NULL, Cast(Datepart(year
,
Dateadd(
year, -1,
Getdate()))AS NVARCHAR(
max
)))
+ '-01-01' AS DATETIME) AND
Cast(
Isnull(NULL, Cast(Datepart(year,
Getdate()
)AS
NVARCHAR(max)))
+ '-12-31' AS DATETIME)
AND c.guid = @CompanyUserGUID
OR @CompanyUserGUID IS NULL
AND a.companysupplierguid = @CompanySupplierGUID
OR @CompanySupplierGUID IS NULL
AND c.userguid = @UserGUID
OR @UserGUID IS NULL
AND a.companyguid = @CompanyGUID
OR @CompanyGUID IS NULL)
SELECT *
FROM (SELECT Year(ind) AS [ayear],
LEFT(Datename(month, ind), 3)AS [amonth],
Isnull(tgvh1, 0) AS Amount
FROM _d
WHERE cug = @CompanyUserGUID
OR @CompanyUserGUID IS NULL
AND cus = @CompanySupplierGUID
OR @CompanySupplierGUID IS NULL
AND ug = @UserGUID
OR @UserGUID IS NULL
AND cg = @CompanyGUID
OR @CompanyGUID IS NULL)s
PIVOT ( Sum(amount)
FOR [amonth] IN (jan,
feb,
mar,
apr,
may,
jun,
jul,
aug,
sep,
oct,
nov,
dec) )AS pivotal
WHERE pivotal.ayear >= Isnull(@InvoiceYearFrom, Datepart(year,
Dateadd(year, -1,
Getdate()
)))
AND pivotal.ayear <= Isnull(@InvoiceYearTo, Datepart(year, Getdate(
))
)
ORDER BY [ayear]
END
It's this bit specifically:
WHERE a.invoicedate BETWEEN Cast(Isnull(@InvoiceDateFrom, Cast(Datepart(year
,
Dateadd(
year, -1,
Getdate()))AS NVARCHAR(
max
)))
+ '-01-01' AS DATETIME) AND
Cast(
Isnull(@InvoiceDateTo, Cast(Datepart(year,
Getdate()
)AS
NVARCHAR(max)))
+ '-12-31' AS DATETIME)
AND c.guid = @CompanyUserGUID
OR @CompanyUserGUID IS NULL
AND a.companysupplierguid = @CompanySupplierGUID
OR @CompanySupplierGUID IS NULL
AND c.userguid = @UserGUID
OR @UserGUID IS NULL
AND a.companyguid = @CompanyGUID
OR @CompanyGUID IS NULL)
eg, the data returned in the pivot is summing all data (I assumed because it's taking the data from a CTX that it would include the filtered data, so I would expect if I passed in @Userguid = (a real user id) it would restrict the pivoted counts to that of invoices just for the user... it does not :'(
Upvotes: 1
Views: 2682
Answers (2)
Reputation: 8047
Looking at your WHERE clause, I see OR being used on ungrouped statements. I think you want to replace your last chunk of your WHERE clause with the following (note that you'll want an extra closing paren afterward to end your CTE):
AND (c.guid = @CompanyUserGUID -- Added opening paren (required)
OR (@CompanyUserGUID IS NULL -- Added parens around group (optional)
AND a.companysupplierguid = @CompanySupplierGUID)
OR (@CompanySupplierGUID IS NULL -- Added parens around group (optional)
AND c.userguid = @UserGUID)
OR (@UserGUID IS NULL -- Added parens around group (optional)
AND a.companyguid = @CompanyGUID)
OR @CompanyGUID IS NULL) -- Added closing paren
Update
Looking at the contents of your WHERE clause a bit more closely, I think you may want to add a bunch of additional groupings. From what I can tell, I think your intent is to only checks a GUID parameter if all previous GUID parameters were NULL. Your current WHERE clause checks all GUID parameters where the previous one (but not necessarily the one before that) was NULL. These groupings should solve that issue.
AND (c.guid = @CompanyUserGUID
OR ( @CompanyUserGUID IS NULL
AND (a.companysupplierguid = @CompanySupplierGUID
OR ( @CompanySupplierGUID IS NULL
AND (c.userguid = @UserGUID
OR ( @UserGUID IS NULL
AND ( a.companyguid = @CompanyGUID
OR @CompanyGUID IS NULL))))))))
End Update
As far as I understand order of operations in SQL-Server, the parenthesis around the individual @param IS NULL OR column = @param clauses is not necessary (aside from making your intent clearer). The opening and closing parenthesis around the grouping as a whole, on the other hand, is necessary. Without it, your SQL is checking (a.invoicedate BETWEEN ... AND c.guid = @CompanyUserGUID) OR [GUID clauses...]
In addition, I strongly recommend cleaning up your SQL that gets your time ranges. You can simplify them quite a bit (and make your code easier to read and diagnose) by using the following:
-- "Start of last year":
-- Get the number of years from 0 (1900-01-01) to this year from 0
-- Then add add that many years (minus 1 for last year) back to 0
DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE())-1, 0)
-- "End of this year": Get "the start of next year" and subtract one day
DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE())+1, 0))
Note that unless your invoicedate column truncates the time, your current logic actually excludes records on 2014-12-31, due to the time-stamp in your filter being 00:00:00. If this is the case, I recommend just going with the start of next year.
Upvotes: 1
Reputation: 680
I think you should add proper braces here, grouping the OR & AND conditions are important
AND c.guid = @CompanyUserGUID
OR @CompanyUserGUID IS NULL
AND a.companysupplierguid = @CompanySupplierGUID
OR @CompanySupplierGUID IS NULL
AND c.userguid = @UserGUID
OR @UserGUID IS NULL
AND a.companyguid = @CompanyGUID
OR @CompanyGUID IS NULL
Upvotes: 1