Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-08-09 : 16:04:52

Hi Guys

I have the following tables (trimmed down and with sample data) for a query that I am try to perform.

FraudLedger:
ReceiptID, FraudCode
111,1000
111,2000
111,3000
111,4000
112,1000
112,2000
113,1000
113,2000
113,3000
114,1000
114,2000

FraudCode:
ID, FraudName
1000, CustomerID Blacklisted
2000, CustomerEmail Blacklisted
3000, CustomerAddress Blacklisted
4000, CustomerTelephone Blacklisted

Receipt:
ReceiptID, CustomerID,DateEntered
105, 1, 2009-07-15
106 ,2, 2009-07-15
107 ,3, 2009-07-15
108, 4, 2009-07-15
109, 5, 2009-07-15
110, 6, 2009-07-15
111, 1, 2009-07-20
112 ,2, 2009-07-20
113 ,3, 2009-07-20
114, 4, 2009-07-20

ReceiptItem:
ReceiptItemID, ReceiptID, PriceIncTax
1111,111,100.00
1112,111, 50.00
1113,112, 50.00
1114,112, 50.00
1115,113, 75,00
1116,114, 50.00
1117,114, 50.00

Customer:
CustomerID
1, ted@jl.com
2, pete@jl.com
3, jane@jl.com
4, hannah@jl.com

With these tables I have produced a query to try and achieve the result below:

Ideal Result:
CustomerID, CustomerEmail, LastOrderDate, LastOrderValue, CustomerID Blacklisted, CustomerEmail Blacklisted, CustomerAddress BlacklistedCustomerTelephone Blacklisted

1, 2009-07-20, 150.00, Yes, Yes, Yes, Yes
2, 2009-07-20, 100.00, Yes, Yes, No, No
3, 2009-07-20, 75.00, Yes, Yes, Yes, No
4, 2009-07-20, 100.00, Yes, Yes, No, No

I am using the following query to try and obtain these results but running into some problems.

USE DB
GO

WITH BlackListedOptions AS
(
SELECT
c.CustomerID
,c.Email
,CASE fl.FraudCode WHEN 1000 THEN 1
ELSE 0
END AS 'CustomerID Blacklisted '
,CASE fl.FraudCode WHEN 2000 THEN 1
ELSE 0
END AS 'CustomerEmail Blacklisted'
,CASE fl.FraudCode WHEN 3000 THEN 1
ELSE 0
END AS 'CustomerAddress Blacklisted'
,CASE fl.FraudCode WHEN 4000 THEN 1
ELSE 0
END AS 'CustomerTelephone Blacklisted '

FROM dbo.FraudLedger AS fl WITH (NOLOCK)
JOIN dbo.Receipt AS r WITH (NOLOCK)
ON fl.ReceiptID = r.ReceiptID
AND fl.FraudCode IN (1000,2000,3000,4000)
JOIN dbo.Customer AS c WITH (NOLOCK)
ON r.CustomerID = c.CustomerID
JOIN dbo.FraudCode AS fc WITH (NOLOCK)
ON fl.FraudCodeID = fc.FraudCodeID

GROUP BY
c.CustomerID
,c.Email
,fl.FraudCode
)

,LastOrder AS
(
SELECT
c.CustomerID
,r.ReceiptID
,r.DateEntered
,SUM(ri.PriceIncTax) AS 'PriceIncTax'
,ROW_NUMBER() OVER (PARTITION BY C.CustomerID ORDER BY r.DateEntered DESC) AS 'RowNumber'

FROM dbo.Customer AS c WITH (NOLOCK)
JOIN BlackListedOptions AS blo WITH (NOLOCK)
ON c.CustomerID = blo.CustomerID
JOIN dbo.Receipt AS r WITH (NOLOCK)
ON c.CustomerID = r.CustomerID
JOIN dbo.ReceiptItem AS ri WITH (NOLOCK)
ON r.ReceiptID = ri.ReceiptID

GROUP BY
c.CustomerID
,r.ReceiptID
,r.DateEntered
)

,Results AS
(

SELECT DISTINCT
blo.Email
,MIN(lo.DateEntered) AS 'OrderDate'
,lo.ReceiptID
,lo.PriceIncTax

,CASE
SUM(blo. CustomerID Blacklisted) WHEN 1 THEN 'Yes'
ELSE'No'
END AS ' CustomerID Blacklisted'
,CASE
SUM(blo. CustomerEmail Blacklisted) WHEN 1 THEN 'Yes'
ELSE'No'
END AS ' CustomerEmail Blacklisted'
,CASE
SUM(blo.CustomerAddress Blacklisted') WHEN 1 THEN 'Yes'
ELSE'No'
END AS ' CustomerAddress Blacklisted '
,CASE
SUM(blo. CustomerTelephone Blacklisted) WHEN 1 THEN 'Yes'
ELSE'No'
END AS 'CustomerTelephone Blacklisted'

FROM BlackListedOptions AS blo
JOIN LastOrder AS lo
ON blo.CustomerID = lo.CustomeriD
AND lo.RowNumber = 1

GROUP BY
blo.Email
,lo.ReceiptID
,lo.PriceIncTax

)

SELECT * FROM Results

The results I am getting from this query are showing incorrect figures in some cases, such as the last order value being doubled or more, and there are also customer Ids being duplicated.

Does anyone know what I am doing wrong or have another solution to get the desired results?

Thanking you in advance!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 01:12:13

try this than using the above code


SELECT DISTINCT c.customerid,c.email,r.receiptid,
r.DateEntered,SUM(ri.PriceIncTax) AS 'PriceIncTax',
CASE WHEN MAX(CASE WHEN  FraudName = 'CustomerID Blacklisted'  THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS 'CustomerIDBlacklisted',
CASE WHEN MAX(CASE WHEN  FraudName = 'CustomerEmail Blacklisted'  THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS 'CustomerEmailBlacklisted',
CASE WHEN MAX(CASE WHEN  FraudName = 'CustomerAddress Blacklisted'  THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END  AS 'CustomerAddressBlacklisted',
CASE WHEN MAX(CASE WHEN  FraudName = 'CustomerTelephone Blacklisted'  THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END  AS 'CustomerTelephoneBlacklisted'
FROM @Receipt r
INNER JOIN @Customer c on c.customerid = r.customerid
INNER JOIN @FraudLedger fl on fl.receiptid = r.receiptid
INNER JOIN @FraudCode fc on fc.id = fl.fraudcode
INNER JOIN @ReceiptItem ri on ri.receiptid = r.receiptid
group by c.customerid,c.email,r.receiptid,
r.DateEntered

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-08-10 : 04:49:52

Hi

Thanks for that.

I've had to modify the script a bit as shown below just to speed up the process also some of the field values and names have been changed:

SELECT t.*
FROM(
SELECT DISTINCT
c.CustomerID
,c.Email
,r.ReceiptID
,r.DateEntered
,SUM(ri.PriceIncTax) AS 'PriceIncTax'
,CASE WHEN MAX(CASE WHEN fl.FraudCode = 16 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS 'Customer Id is blacklisted'
,CASE WHEN MAX(CASE WHEN fl.FraudCode = 17 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS 'Customer Email is blacklisted'
,CASE WHEN MAX(CASE WHEN fl.FraudCode = 18 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS 'Customer Credit Card is blacklisted'
,CASE WHEN MAX(CASE WHEN fl.FraudCode = 19 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS 'Delivery Address/Postcode combination is blacklisted'
,ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY r.DateEntered DESC) AS 'RowNumber'
FROM dbo.Receipt AS r WITH (NOLOCK)
INNER JOIN dbo.Customer AS c WITH (NOLOCK)
ON c.customerid = r.customerid
INNER JOIN dbo.FraudLedger AS fl WITH (NOLOCK)
ON fl.receiptid = r.receiptid
AND fl.FraudCode IN (16,17,18,19)
INNER JOIN dbo.FraudCode AS fc WITH (NOLOCK)
ON fl.FraudCode = fc.FraudCodeID
INNER JOIN dbo.ReceiptItem AS ri WITH (NOLOCK)
ON ri.receiptid = r.receiptid

GROUP BY
c.CustomerID
,c.Email
,r.ReceiptID
,r.DateEntered
) t
WHERE t.RowNumber =1

The results are no longer showing duplicates! However in some records the PriceIncTax field is still showing a grater value that what it should be, ie value being doubled or tripled.

Any ideas on what I could do?

Thanks

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-08-10 : 05:27:34

Hi

This script seems to be working now:

SELECT
t.CustomerID
,t.Email
,t.ReceiptID
,t.DateEntered
,t.PriceIncTax
,t.CustomerIdIsBlacklisted
,t.CustomerEmailIsBlacklisted
,t.CustomerCreditCardIsBlacklisted
,t.DeliveryAddressPostcodeCombinationIsBlacklisted

FROM(

SELECT DISTINCT
c.CustomerID
,c.Email
,r.ReceiptID
,r.DateEntered
,(SELECT SUM(PriceIncTax) FROM dbo.ReceiptItem WHERE receiptid = r.receiptID) AS 'PriceIncTax'
,CASE WHEN MAX(CASE WHEN fl.FraudCode = 16 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS 'CustomerIdIsBlacklisted'
,CASE WHEN MAX(CASE WHEN fl.FraudCode = 17 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS 'CustomerEmailIsBlacklisted'
,CASE WHEN MAX(CASE WHEN fl.FraudCode = 18 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS 'CustomerCreditCardIsBlacklisted'
,CASE WHEN MAX(CASE WHEN fl.FraudCode = 19 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS 'DeliveryAddressPostcodeCombinationIsBlacklisted'
,ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY r.DateEntered DESC) AS 'RowNumber'
FROM dbo.Receipt AS r WITH (NOLOCK)
INNER JOIN dbo.Customer AS c WITH (NOLOCK)
ON c.customerid = r.customerid
INNER JOIN dbo.FraudLedger AS fl WITH (NOLOCK)
ON fl.receiptid = r.receiptid
AND fl.FraudCode IN (16,17,18,19)
INNER JOIN dbo.FraudCode AS fc WITH (NOLOCK)
ON fl.FraudCode = fc.FraudCodeID
INNER JOIN dbo.ReceiptItem AS ri WITH (NOLOCK)
ON ri.receiptid = r.receiptid

GROUP BY
c.CustomerID
,c.Email
,r.ReceiptID
,r.DateEntered

) t
WHERE t.RowNumber =1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 07:28:46

Welcome
k fine
why r u using subquery for the PriceIncTax u can have a inner join as in my qurey
if u want group by on receiptid use partition clause

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-08-10 : 07:43:04

Hi

The only way I could find for the PriceIncTax field to show the correct value was to use a subquery.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 08:09:49

k then and no need of join with ReceiptItem in the select list

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-08-10 : 08:22:36

Will do!

Thanks for all your help.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 08:28:37

quote:
Originally posted by rcr69er

Will do!

Thanks for all your help.

Welcome

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-08-10 : 08:38:50

Just a quick quetsion regarding your original query.

Why do you use MAX(CASE...? I've seen it used before but never been totally sure why it is used.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-10 : 08:51:47

to get the single record
and u can use like this (wtih single case statement)


SELECT DISTINCT c.customerid,c.email,r.receiptid,
r.DateEntered,(SELECT SUM(PriceIncTax) FROM @ReceiptItem WHERE receiptid = r.receiptID) AS 'PriceIncTax',
MAX(CASE WHEN  FraudName = 'CustomerID Blacklisted'  THEN 'YES' ELSE 'No' END ) AS 'CustomerIDBlacklisted',
MAX(CASE WHEN  FraudName = 'CustomerEmail Blacklisted'  THEN 'YES' ELSE 'No'  END)  AS 'CustomerEmailBlacklisted',
MAX(CASE WHEN  FraudName = 'CustomerAddress Blacklisted'  THEN 'YES' ELSE 'No'  END)   AS 'CustomerAddressBlacklisted',
MAX(CASE WHEN  FraudName = 'CustomerTelephone Blacklisted'  THEN 'YES' ELSE 'No'  END)  AS 'CustomerTelephoneBlacklisted'
FROM @Receipt r 
INNER JOIN @Customer c on c.customerid = r.customerid
INNER JOIN @FraudLedger fl on fl.receiptid = r.receiptid
INNER JOIN @FraudCode fc on fc.id = fl.fraudcode
group by c.customerid,c.email,r.receiptid,
r.DateEntered

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources