November 5, 2014 at 4:53 pm
Hello,
SQL is not my strong point, Im more of a UI developer, unfortunately the DBA is away for the next two weeks and I have been given the job to finish what he said he started....
I have the following tables in the DB as shown below
First one is called Dealer
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Dealer](
[DealerId] [int] NOT NULL,
[Name] [varchar](100) NOT NULL,
CONSTRAINT [PK_Dealer] PRIMARY KEY CLUSTERED
(
[DealerId] 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
Second one is called Commission
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Commission](
[CommissionId] [int] NOT NULL,
[CustomerId] [int] NOT NULL,
[DealerId] [int] NOT NULL,
[ProductId] [int] NOT NULL,
[CommissionAmount] [money] NOT NULL,
[PaymentStatusId] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NULL,
CONSTRAINT [PK_Commission] PRIMARY KEY CLUSTERED
(
[CommissionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Third one is called CommissionHistory, So if anything changes in the commission table an entry is made here.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CommissionHistory](
[CommissionHistoryId] [int] NOT NULL,
[CommissionId] [int] NOT NULL,
[ExistingCustomerId] [int] NOT NULL,
[NewCustomerId] [int] NOT NULL,
[ExistingDealerId] [int] NOT NULL,
[NewDealerId] [int] NOT NULL,
[ExistingProductId] [int] NOT NULL,
[NewProductId] [int] NOT NULL,
[ExistingCommissionAmount] [money] NOT NULL,
[NewCommissionAmount] [money] NOT NULL,
[ExistingPaymentStatusId] [int] NOT NULL,
[NewPaymentStatusId] [int] NOT NULL,
[ActionDate] [datetime] NOT NULL,
CONSTRAINT [PK_CommissionHistory] PRIMARY KEY CLUSTERED
(
[CommissionHistoryId] 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
Now I have the following queries I need to write and run and this is where I seek your help.
1. Write a query that retrieves the total amount of paid commission for a specific dealer over a custom
time period.
I have done the following
Select d.Name, Sum(c.CommissionAmount) from Dealer d
Inner join Commission c on c.DealerId = d.DealerId
Where c.CreatedDate between (@FromDate, @ToDate) and c.PaymentStatusId = 5
Order by d.Name
2. Write a query to calculate the average period (in days) all commissions have taken to transition from
‘Verified’ to ‘Paid’ for a single dealer.
Statuses.
1. Created
2. Verified
3. Rejected
4. Awaiting Payment
5. Paid
6. Refunded
Select Avg(DateDiff(day,col1,col2)) as difference
Where status in(2,3,4,5)
I need to some how cater for the history table as im unaware of how to do this......
4. Improve the DB structure and/or performance of the above queries, assuming any change can be
made to the DB schema.
I was going to recommend foreign keys, as I don't see any relationships between the tables.
Can someone help me with the second query please.
November 6, 2014 at 1:53 am
Can you post up some consumable sample data for the tables please?
In order to write the second query, you will need to know how to track a single commission amount through the payment process. How do you identify one of these? It might be CommissionID, then again CommissionID could be the PK for the table...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 6, 2014 at 2:08 am
This was removed by the editor as SPAM
November 6, 2014 at 4:57 am
Hi guys sorry for keeping you waiting. I created a fiddle for this but forgot to update the post, please feel free to test using it.
sqlfiddle.com/#!2/dce89d
I haven't tested your queries yet, ill test them in the morning
November 6, 2014 at 9:37 am
Those queries themselves are relatively trivial. You'll sort that out quickly.
But the clustering index is the most critical overall factor in performance and two tables need changed. Without more details on the actual data, and the index stats from SQL Server, I can't give any more specific keys, but the indexes below will be vastly better than what you have now for overall performance. You can tweak them later if needed.
--doesn't change the key, just makes sure the table is packed tight
ALTER INDEX PK_Dealer ON dbo.Dealer REBUILD WITH ( FILLFACTOR = 99 )
--no [n]varchar, can be packed very tightly
ALTER TABLE dbo.Commission DROP CONSTRAINT [PK_Commission]
CREATE UNIQUE CLUSTERED INDEX CL_Commission ON dbo.Commission ( CreatedDate, CommissionId ) WITH ( FILLFACTOR = 99 )
ALTER TABLE dbo.Commission ADD CONSTRAINT PK_Commission PRIMARY KEY NONCLUSTERED ( CommissionId ) WITH ( FILLFACTOR = 99 )
--a history table presumably won't change at all, so pack it 100% full
ALTER TABLE dbo.CommissionHistory DROP CONSTRAINT PK_CommissionHistory
CREATE UNIQUE CLUSTERED INDEX CL_CommissionHistory ON dbo.CommissionHistory ( ActionDate, CommissionHistoryId ) WITH ( FILLFACTOR = 100 )
ALTER TABLE dbo.CommissionHistory ADD CONSTRAINT PK_CommissionHistory PRIMARY KEY NONCLUSTERED ( CommissionHistoryId )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply