SQLTeam.com | Weblogs | Forums

Get records for purchase tracking and categorize them

So, my SQL is not as good as what it used to be!

Basically I am trying to create a query that will pull back records for these fields:

OrderPlacedOn
CourseNumber
Title (Course)
Cost (Course)
AccessCodeID (User order history transaction)
AccessCode
CategoryName (Category)
State (User)

The purpose of the query is to view records that track what access codes was used to purchase what courses. If it is a paypal purchase then we also need to know that too (Paypal purchase is determined on if the AccessCodeID is NULL in the user order history transaction table)

Does this make sense?

I tried this query but I get back what appears to be duplicates or records that are associated with access codes that the individual did not use to purchase at all.

SELECT u.UserID, u.Firstname, u.Lastname, u.EmailAddress, uoh.OrderPlacedOn as [Purchase Date], /ucc.DateCreated AS [Date Passed],/ c.CourseNumber, c.Title, c.Cost AS [Course Price],
uoht.AccessCodeID AS [Purchase Type], ac.AccessCode, cat.CategoryName AS [License Type], u.[State]

FROM UserOrderHistory uoh
INNER JOIN UserOrderHistoryTransaction uoht ON uoht.UserOrderHistoryID = uoh.ID
INNER JOIN Users u ON u.UserID = uoh.UserID
INNER JOIN Category cat ON cat.CategoryID = u.ProfessionalLicenseType
--LEFT OUTER JOIN UserCourseCertificate ucc ON ucc.UserID = u.UserID
INNER JOIN UserRegisteredCourses urc ON urc.UserID = u.UserID
INNER JOIN Course c ON c.CourseID = urc.CourseID
LEFT OUTER JOIN CourseAccessCode ca ON ca.CourseID = c.CourseID
INNER JOIN AccessCodes ac on ac.ID = ca.AccessCodeID

GROUP BY u.UserID, u.Firstname, u.Lastname, u.EmailAddress, uoh.OrderPlacedOn , /ucc.DateCreated ,/ c.CourseNumber, c.Title, c.Cost ,
uoht.AccessCodeID, cat.CategoryName , u.[State], ac.AccessCode
ORDER BY u.UserID, c.CourseNumber

Here is my schema:

/****** Object: Table [dbo].[AccessCodes] Script Date: 22/08/2019 08:50:35 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AccessCodes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DateCreated] [datetime] NOT NULL,
[Active] [bit] NOT NULL,
[AccessCode] nvarchar NOT NULL,
[ActiveFrom] [datetime] NULL,
[ActiveTo] [datetime] NULL,
[HREmail] nvarchar NULL,
[PercentDiscount] [int] NOT NULL,
CONSTRAINT [PK_AccessCodes] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/
Object: Table [dbo].[Category] Script Date: 22/08/2019 08:50:36 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] nvarchar NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/
Object: Table [dbo].[Course] Script Date: 22/08/2019 08:50:36 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course](
[CourseID] [int] IDENTITY(1,1) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[IsActive] [bit] NOT NULL,
[CourseNumber] nvarchar NOT NULL,
[Title] nvarchar NOT NULL,
[CategoryID] [int] NOT NULL,
[Description] nvarchar NOT NULL,
[Cost] [money] NOT NULL,
[IsAARCApproved] [bit] NOT NULL,
[Status] [tinyint] NOT NULL,
[ProviderID] [int] NOT NULL,
[ContractHours] [decimal](16, 2) NOT NULL,
[CertificateFee] [money] NOT NULL,
[Disclaimer] nvarchar NULL,
[RequireSurveyCompletion] [bit] NOT NULL,
[RequireCertificateAccessCode] [bit] NOT NULL,
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CourseID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/
Object: Table [dbo].[CourseAccessCode] Script Date: 22/08/2019 08:50:36 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CourseAccessCode](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CourseID] [int] NOT NULL,
[AccessCodeID] [int] NOT NULL,
CONSTRAINT [PK_CourseAccessCode] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/
Object: Table [dbo].[UserOrderHistory] Script Date: 22/08/2019 08:50:36 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserOrderHistory](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DateAdded] [datetime] NOT NULL,
[UserID] [int] NOT NULL,
[OrderPlacedOn] [datetime] NOT NULL,
[CourseID] [int] NOT NULL,
[CourseTitle] nvarchar NOT NULL,
[CourseNumber] nvarchar NOT NULL,
[TotalCost] [money] NOT NULL,
[ProviderID] [int] NOT NULL,
[ProviderName] nvarchar NOT NULL,
[ProviderNumber] nvarchar NOT NULL,
[ProviderEmail] nvarchar NOT NULL,
[UserEmailAddress] nvarchar NOT NULL,
[CourseSubscriptionID] [int] NULL,
CONSTRAINT [PK_UserOrderHistory] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/
Object: Table [dbo].[UserOrderHistoryTransaction] Script Date: 22/08/2019 08:50:36 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserOrderHistoryTransaction](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[UserID] [int] NOT NULL,
[ProviderTransactionID] nvarchar NOT NULL,
[TotalCost] [money] NOT NULL,
[UserOrderHistoryID] [int] NOT NULL,
[AccessCEReference] nvarchar NOT NULL,
[AccessCodeID] [int] NULL,
[AuthCode] nvarchar NULL,
[ReasonCode] nvarchar NULL,
[Decision] nvarchar NULL,
[ResponseString] nvarchar NULL,
CONSTRAINT [PK_UserOrderHistoryTransaction] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/
Object: Table [dbo].[UserRegisteredCourses] Script Date: 22/08/2019 08:50:36 /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRegisteredCourses](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[CourseID] [int] NOT NULL,
[DateOfCompletion] [datetime] NULL,
[ConfirmationCode] nvarchar NULL,
[Status] [tinyint] NOT NULL,
[AffirmationSigned] [bit] NULL,
[PurchasedCertificate] [bit] NOT NULL,
[CourseSubscriptionExpiration] [datetime] NULL,
[CourseSubscriptionID] [int] NULL,
CONSTRAINT [PK_UserRegisteredCourses] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/
Object: Table [dbo].[Users] Script Date: 22/08/2019 08:50:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[DateOfRegistration] [datetime] NOT NULL,
[Firstname] nvarchar NOT NULL,
[Lastname] nvarchar NOT NULL,
[Password] nvarchar NOT NULL,
[IsActive] [bit] NOT NULL,
[EmailAddress] nvarchar NOT NULL,
[PhoneNumber] nvarchar NOT NULL,
[RegistrationKey] nvarchar NULL,
[ProfLicenseNumber] varchar NULL,
[AARC] varchar NULL,
[State] varchar NULL,
[SecurityQuestion] nvarchar NULL,
[SecurityQuestionAnswer] nvarchar NULL,
[NewsletterSub] [bit] NOT NULL,
[ProfessionalLicenseType] [int] NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AccessCodes] ADD CONSTRAINT [DF_AccessCodes_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO
ALTER TABLE [dbo].[AccessCodes] ADD CONSTRAINT [DF_AccessCodes_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[AccessCodes] ADD CONSTRAINT [DF_AccessCodes_PercentDiscount] DEFAULT ((0)) FOR [PercentDiscount]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_CreatedOn] DEFAULT (getdate()) FOR [CreatedOn]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_IsActive] DEFAULT ((0)) FOR [IsActive]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_Cost] DEFAULT ((0)) FOR [Cost]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_IsAARCApproved] DEFAULT ((0)) FOR [IsAARCApproved]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_ContactHours] DEFAULT ((0)) FOR [ContractHours]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_CertificateFee] DEFAULT ((0)) FOR [CertificateFee]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_RequireSurveyCompletion] DEFAULT ((0)) FOR [RequireSurveyCompletion]
GO
ALTER TABLE [dbo].[Course] ADD CONSTRAINT [DF_Course_RequireCertificateAccessCode] DEFAULT ((0)) FOR [RequireCertificateAccessCode]
GO
ALTER TABLE [dbo].[UserOrderHistory] ADD CONSTRAINT [DF_UserOrderHistory_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
GO
ALTER TABLE [dbo].[UserOrderHistory] ADD CONSTRAINT [DF_UserOrderHistory_TotalCost] DEFAULT ((0)) FOR [TotalCost]
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] ADD CONSTRAINT [DF_UserOrderHistoryTransaction_CreatedOn] DEFAULT (getdate()) FOR [CreatedOn]
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] ADD CONSTRAINT [DF_UserOrderHistoryTransaction_TotalCost] DEFAULT ((0)) FOR [TotalCost]
GO
ALTER TABLE [dbo].[UserRegisteredCourses] ADD CONSTRAINT [DF_UserRegisteredCourses_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[UserRegisteredCourses] ADD DEFAULT ((0)) FOR [AffirmationSigned]
GO
ALTER TABLE [dbo].[UserRegisteredCourses] ADD CONSTRAINT [DF_UserRegisteredCourses_PurchasedCertificate] DEFAULT ((0)) FOR [PurchasedCertificate]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_DateOfRegistration] DEFAULT (getdate()) FOR [DateOfRegistration]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_IsActive] DEFAULT ((0)) FOR [IsActive]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_NewsletterSub] DEFAULT ((1)) FOR [NewsletterSub]
GO
ALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [FK_Course_Category] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Category]
GO
ALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [FK_Course_Provider] FOREIGN KEY([ProviderID])
REFERENCES [dbo].[Provider] ([ID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Provider]
GO
ALTER TABLE [dbo].[CourseAccessCode] WITH CHECK ADD CONSTRAINT [FK_CourseAccessCode_AccessCodes] FOREIGN KEY([AccessCodeID])
REFERENCES [dbo].[AccessCodes] ([ID])
GO
ALTER TABLE [dbo].[CourseAccessCode] CHECK CONSTRAINT [FK_CourseAccessCode_AccessCodes]
GO
ALTER TABLE [dbo].[CourseAccessCode] WITH CHECK ADD CONSTRAINT [FK_CourseAccessCode_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[CourseAccessCode] CHECK CONSTRAINT [FK_CourseAccessCode_Course]
GO
ALTER TABLE [dbo].[UserOrderHistory] WITH CHECK ADD CONSTRAINT [FK_UserOrderHistory_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[UserOrderHistory] CHECK CONSTRAINT [FK_UserOrderHistory_Users]
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] WITH CHECK ADD CONSTRAINT [FK_UserOrderHistoryTransaction_AccessCodes] FOREIGN KEY([AccessCodeID])
REFERENCES [dbo].[AccessCodes] ([ID])
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] CHECK CONSTRAINT [FK_UserOrderHistoryTransaction_AccessCodes]
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] WITH CHECK ADD CONSTRAINT [FK_UserOrderHistoryTransaction_UserOrderHistory] FOREIGN KEY([UserOrderHistoryID])
REFERENCES [dbo].[UserOrderHistory] ([ID])
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] CHECK CONSTRAINT [FK_UserOrderHistoryTransaction_UserOrderHistory]
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] WITH CHECK ADD CONSTRAINT [FK_UserOrderHistoryTransaction_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[UserOrderHistoryTransaction] CHECK CONSTRAINT [FK_UserOrderHistoryTransaction_Users]
GO
ALTER TABLE [dbo].[UserRegisteredCourses] WITH CHECK ADD CONSTRAINT [FK_UserRegisteredCourses_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[UserRegisteredCourses] CHECK CONSTRAINT [FK_UserRegisteredCourses_Course]
GO
ALTER TABLE [dbo].[UserRegisteredCourses] WITH CHECK ADD CONSTRAINT [FK_UserRegisteredCourses_CourseSubscription] FOREIGN KEY([CourseSubscriptionID])
REFERENCES [dbo].[CourseSubscription] ([ID])
GO
ALTER TABLE [dbo].[UserRegisteredCourses] CHECK CONSTRAINT [FK_UserRegisteredCourses_CourseSubscription]
GO
ALTER TABLE [dbo].[UserRegisteredCourses] WITH CHECK ADD CONSTRAINT [FK_UserRegisteredCourses_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[UserRegisteredCourses] CHECK CONSTRAINT [FK_UserRegisteredCourses_Users]
GO
ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_Users] FOREIGN KEY([ProfessionalLicenseType])
REFERENCES [dbo].[Category] ([CategoryID])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Users]
GO

Would it be possible for you to give us access to your sql server?

eeek. Most likely not due to user data on the DB :frowning:

in that case, please provide sample data :wink:

I was afraid you were going to say that :wink:
To make it easier, how can I mask or random the data in the database already?

you can leverage sys.objects or sys.columns to randomize things. of course you will need to make sure the relationships between tables is not messed up

here you go:

http://192.211.60.26/ClientDBGen.zip

it is a bacpac.

No thanks

Not sure what you mean? Where would it be best to share?

Not sure I wanna click that url. Why dont you just post your sample data on forum

sigh. It just wouldn't be easy in that regard.
I would happily upload a SQL Script but there is no upload option. This link is hosted on my own webserver.

copy paste from your sql scripts here should work?

create table #Users(UserId int, [DateOfRegistration] [datetime] NOT NULL,
[Firstname] nvarchar NOT NULL,
[Lastname] nvarchar NOT NULL,
[Password] nvarchar NOT NULL)

insert into #Users
select 1,  'ahmed', 'a86', 'N33dSampleD@T@' union
select 2,  'Darth', 'Vader', 'D@rkS1d3@' 

etc

Hi

Another idea is
Create dummy tables ..

Put 3 to 4 lines sample data
Keeping pk fk relationships in mind

I could do this
But will leave it to you

:slightly_smiling_face::slightly_smiling_face:

Tried to make it easy for you guys, thats all.

this is becoming harder and more difficult than easy. I cant paste in the scripts, too large in one post. I'll try to see how best I can split them up.

I'll have to figure out the best way of getting data here since that is fundamental to the query i need.

Query tsql
Copy paste 3 or 4 records

And put everything in a block with arrow
Symbol here

When you click on arrow whole block shows

here is the SQL Script for data. It is a simple txt file. Here is the onedrive link share:

https://1drv.ms/t/s!Aqq_Eht9hMJKi5JMSFP_ADhXKhTFaw

Sorry, wont click that myself. Post dml and ddl here please

Are you guys kidding me? This is supposed to be simple. it seems things are being made more difficult here.
Since I posted the schema, how about you insert it into a database yourselves and add data to it?

Remember

You are asking for our help..

Creating dummy data is the most irritating part
And menial part

People "who post questions" are also expected to provide sample data ...in general