February 17, 2012 at 5:20 am
Projects Table
ProjA
ProjB
ProjC
Customers Table
CustomerA
CustomerB
CustomerC
Link & Data Table (link between Projects table and Customers table)
Project NameCustomer Financial YearDeliveredCash
ProjA CustomerA 2011/12£300
ProjB CustomerB 2011/12£0
I want to display all customers that are not involved in projects A, B and C etc and those involved but DeliveredCash equals £0. i.e. potential tabke should look like the one below.
Project NameCustomer Financial YearDeliveredCash
ProjACustomerB2011/12£0 | customer A not included DeliveredCash £300
ProjACustomerC2011/12£0
ProjBCustomerA2011/12£0
ProjBCustomerB2011/12£0 | Customer B included DeliveredCash was £0
ProjBCustomerC2011/12£0
ProjCCustomerA2011/12£0
ProjCCustomerB2011/12£0
ProjCCustomerC2011/12£0
ProjDCustomerA2011/12£0
ProjDCustomerB2011/12£0
ProjDCustomerC2011/12£0
February 17, 2012 at 8:11 am
BEGIN TRAN
--Sample Data
CREATE TABLE Projects (projectID INT IDENTITY, projectName CHAR(5))
INSERT INTO Projects
SELECT projectName
FROM (VALUES('ProjA'),('ProjB'),('ProjC'),('ProjD')) a(projectName)
--Sample Data
CREATE TABLE Customers (customersID INT IDENTITY, customerName CHAR(9))
INSERT INTO Customers
SELECT customerName
FROM (VALUES('CustomerA'),('CustomerB'),('CustomerC'))a(customerName)
--Sample Data
CREATE TABLE Data (dataID INT IDENTITY, customersID INT, projectID INT, financialYear CHAR(7), DeliveredCash MONEY)
INSERT INTO Data
SELECT customersID, projectID, financialYear, DeliveredCash
FROM (VALUES(1, 1, '2011/12', £300),(2, 2, '2011/12', £0)) a(customersID, projectID, financialYear, DeliveredCash)
--Solution
SELECT d.projectName, c.customerName, d.financialYear, d.DeliveredCash
FROM (SELECT p.projectID, p.projectName, d.customersID, d.financialYear, ISNULL(d.DeliveredCash,0.00) AS DeliveredCash
FROM Projects p
LEFT OUTER JOIN Data d ON p.projectID = d.projectID) d
CROSS JOIN Customers c
WHERE d.DeliveredCash = 0.00 OR d.customersID <> c.customersID
ROLLBACK
Returns:
projectName customerName financialYear DeliveredCash
----------- ------------ ------------- ---------------------
ProjA CustomerB 2011/12 300.00
ProjA CustomerC 2011/12 300.00
ProjB CustomerA 2011/12 0.00
ProjB CustomerB 2011/12 0.00
ProjB CustomerC 2011/12 0.00
ProjC CustomerA NULL 0.00
ProjC CustomerB NULL 0.00
ProjC CustomerC NULL 0.00
ProjD CustomerA NULL 0.00
ProjD CustomerB NULL 0.00
ProjD CustomerC NULL 0.00
The reason you get NULL for ProjC and ProjD in "Financial Year" is because you're storing it in "Data". This means there is no "Financial Year" for those "Proj"
April 2, 2012 at 2:22 am
I am not sure why Cadavre's solution returns this record, because I don't think there's record in Data for Customer C and ProjA:
ProjA CustomerC 2011/12 300.00
If you still need this, I think this will work, however not sure about it from a performance perspective:
--Sample Data
DECLARE @Projects TABLE (projectID INT IDENTITY, projectName CHAR(5))
INSERT INTO @Projects
SELECT projectName
FROM (SELECT 'ProjA' as ProjectName UNION ALL SELECT 'ProjB' UNION ALL SELECT 'ProjC' UNION ALL SELECT 'ProjD') a
--Sample Data
DECLARE @Customers TABLE (customersID INT IDENTITY, customerName CHAR(9))
INSERT INTO @Customers
SELECT customerName
FROM (SELECT 'CustomerA' as customerName UNION ALL SELECT 'CustomerB' UNION ALL SELECT 'CustomerC')a
--Sample Data
DECLARE @data TABLE (dataID INT IDENTITY, customersID INT, projectID INT, financialYear CHAR(7), DeliveredCash MONEY)
INSERT INTO @data
SELECT customersID, projectID, financialYear, DeliveredCash
FROM (
SELECT 1 AS customersID, 1 as projectID, '2011/12' as financialYear, 300 AS DeliveredCash
UNION ALL SELECT 2, 2, '2011/12', 0) a
SELECT ProjectName, CustomerName, FinancialYear, DeliveredCash
FROM (
SELECT ProjectName, CustomerName, FinancialYear
,ISNULL((SELECT DeliveredCash FROM @data d2
WHERE d.FinancialYear = d2.FinancialYear and p.projectID = d2.projectID
and c.customersID = d2.customersID)
,0) As DeliveredCash
FROM @Customers c
CROSS APPLY @Projects p
CROSS APPLY (SELECT DISTINCT FinancialYear FROM @data) d) x
WHERE DeliveredCash = 0
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply