June 25, 2009 at 5:53 am
First, let me describe the three tables I am working with.
HardCardType: Contains two records that describe a type of 'hard card' for a game license.
hardcardtypeid name
1 Fishing
2 Hunting
LifetimePackage: Contains descriptions of lifetime game license packages that a customer can purchase. There is a hard card designation for each package:
LifetimePackageid hardcardtypeid name
1 1 Sport Fishing License
2 1 Additional Fishing Privileges
3 2 Hunting License
4 2 Additional Big Game Privileges
5 2 Additional Game Bird Privileges
LifetimePurchase: Contains customer purchase records of liftime packages:
customerid Lifetimepackageid
163 3
163 1
163 2
215 3
217 3
220 3
223 3
224 3
With this data, I need to show a record for each customerid and whether or not (true or false) they have a purchase record for any of the lifetime packages, per the hardcard types. Given the example of a single CustomerID (163), here is what my results currently look like (I will also furnish the query):
Customerid HardCardName Sport Fishing License Additional Fishing Privleges Hunting License Additional Big Game Privileges Additional Game Bird Privleges
163 Fishing TRUE TRUE TRUE FALSE FALSE
163 Hunting TRUE TRUE TRUE FALSE FALSE
I have the data grouped by customerid and hardcardname. This customer has purchased Lifetime packages 1,2, and 3. If you look at the first record, which contains the fishing group, notice that 'Hunting License' returns true. This is not correct since the HardcardTypeID for this package = 2 (Hunting), so this value should be False. Here is what the results should look like:
Customerid HardCardName Sport Fishing License Additional Fishing Privleges Hunting License Additional Big Game Privileges Additional Game Bird Privleges
163 Fishing TRUE TRUE FALSE FALSE FALSE
163 Hunting FALSE FALSE TRUE FALSE FALSE
The query that I am using incorporates a UDF. First, the query:
SELECT
cu.customerid,
ht.[Name] AS HardCardName,
dbo.udf_PendingFulfillmentLifetimePackages(cu.CustomerID, 1) AS [Sport Fishing License],
dbo.udf_PendingFulfillmentLifetimePackages(cu.CustomerID, 2) AS [Additional Fishing Privleges],
dbo.udf_PendingFulfillmentLifetimePackages(cu.CustomerID, 3) AS [Hunting License],
dbo.udf_PendingFulfillmentLifetimePackages(cu.CustomerID, 4) AS [Additional Big Game Privileges],
dbo.udf_PendingFulfillmentLifetimePackages(cu.CustomerID, 5) AS [Additional Game Bird Privleges]
FROM
dbo.Customer cu
INNER JOIN dbo.CustomerIndividual ci
ON cu.CustomerID = ci.CustomerID
LEFT OUTER JOIN dbo.CustomerIdentity ct
ON cu.CustomerID = ct.CustomerID
AND ct.Status = 1
INNER JOIN dbo.License li
ON li.CustomerID = cu.CustomerID
INNER JOIN dbo.Item it
ON li.ItemID = it.ItemID
INNER JOIN dbo.ItemCategory ic
ON it.ItemCategoryID = ic.ItemCategoryID
INNER JOIN dbo.LifetimePurchase lf
ON cu.CustomerID = lf.CustomerID
LEFT OUTER JOIN dbo.LifetimePackage lp
ON lp.LifetimePackageID = lf.LifetimePackageID
LEFT OUTER JOIN dbo.HardCardType ht
ON ht.HardCardTypeID = lp.HardCardTypeID
LEFT OUTER JOIN dbo.CustomerIndividualNamePrefix cn
ON ci.CustomerIndividualNamePrefixID = cn.PrefixID
LEFT OUTER JOIN dbo.CustomerIndividualNameSuffix cs
ON ci.CustomerIndividualNameSuffixID = cs.SuffixID
WHERE
lf.IsPendingHardCardFulfillment = 1
AND
cu.CustomerID = 163
GROUP BY
cu.CustomerID, ht.[name]
ORDER BY cu.customerid
***********************
The UDF is designed to take in the CustomerID and the LiftimePackageID and return True or False to reflect whether or not they have purchased a lifetime package:
CREATE FUNCTION [dbo].[udf_PendingFulfillmentLifetimePackages]
(
@CustomerID Int,
@LifetimePackageID Int
)
RETURNS VARCHAR(5)
AS
BEGIN
DECLARE @LifetimePackageResult VARCHAR(5)
SELECT
@LifetimePackageResult =
CASE
WHEN NOT MAX(LifetimePackageID) = 0 THEN 'TRUE'
ELSE 'FALSE'
END
FROM
(
SELECT
LifetimePackageID
FROM
dbo.LifeTimePurchase lp
WHERE
CustomerID = @CustomerID
AND
LifetimePackageID = @LifetimePackageID
AND
IsPendingHardCardFulfillment = 1
UNION ALL
SELECT 0 AS LifetimePackageID
) AS IQ
RETURN @LifetimePackageResult
END
**********************
What do I need in order for the results to come out as depicted in the second result set? Do I need a cross join of the lifetime package table to itself, and then resolve what combinations of lifetime package and hardcardtypeid are true and which ones are false? Is there a simpler approach to crosstab this?
Thank you for your help!
CSDunn
June 25, 2009 at 7:09 am
First, Why the tiny font??? I have old eyes and it is difficult to read your post. Please use a normal size font.
Second, As I struggled through your post, I noticed in your query tables and columns that you didn't define in your problem statement.
Third, please read the first article I reference below in my signature block regarding asking for assistance. It will show you the proper way to post code and sample data to get the best responses.
This includes providing the DDL for your tables, sample data in a format that can be cut/paste/run in SSMS to populate the tables. Also, be sure to test all your code before you post it. We shouldn't have to debug your code before we start working on your problem.
Please help us help you. If you can't do the work to setup the test environment for us to help you, why do you expect us to do that work? And for a more detailed reasoning behind this, read The Flip Side.
June 26, 2009 at 1:32 pm
I was able to fix this problem yesterday. Thanks.
June 26, 2009 at 1:43 pm
It must have been the magnifying glass that did the trick 😀
Do you mind sharing the solution you came up with ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 26, 2009 at 4:58 pm
cdun2 (6/26/2009)
I was able to fix this problem yesterday. Thanks.
I have to agree, you came here and asked for help. Proper forum etiquette, even if you solved the problem yourself, is to show everyone how you did it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply