frank123 Posted March 3, 2019 Report Posted March 3, 2019 Nenu entry level lo jobs chusukutunnanu...please SQL saahayam cheyandi. Problem As part of the Insurance policy quote process, agents will provide quotes for their clients. These quotes provide important insights into market and customer behavior and agents are required to enter every quote into the system as soon as it is received. To help enforce this policy, a report is needed to identify deals that did NOT have at least three quotes entered into the system within 7 days after the Application Signed Date for that deal. Tables Policy – contains all of the deals that made it to a status of Application Signed Quote – contains the quotes entered for each deal (joins on OpportunityID) Policy table structure : CREATE TABLE [dbo].[Policy]( [OpportunityID] [varchar](100) NOT NULL, [DealNumber] [int] NULL, [ProductionAmount] [int] NULL, [DealPurpose] [varchar](100) NULL, [DealStatus] [varchar](100) NULL, [LenderType] [varchar](100) NULL, [OppCreatedDate] [date] NULL, [ApplicationSignedDate] [date] NULL, [ExpectedCloseDate] [date] NULL, CONSTRAINT [PK_T_Opportunity] PRIMARY KEY CLUSTERED ) Quote table structure : CREATE TABLE [dbo].[T_Quote]( [QuoteID] [varchar](100) NOT NULL, [OpportunityID] [varchar](100) NOT NULL, [LenderType] [varchar](100) NULL, [CreatedDate] [date] NULL, [WinningQuoteInd] [int] NULL, CONSTRAINT [PK_QuoteID] PRIMARY KEY CLUSTERED ) Outputs 1)Summary showing number and percent of deals that were in compliance and number and percent of deals that were not in compliance 2)Show the specific deals that were OUT of compliance, with the following data points: Total number of quotes entered for the deal Date first quote was created Date last quote was created Date third quote was created Number of days it took to enter the 3rd quote after Application Signed Date Quote
Mitron Posted March 3, 2019 Report Posted March 3, 2019 9 minutes ago, frank123 said: Nenu entry level lo jobs chusukutunnanu...please SQL saahayam cheyandi. Problem As part of the Insurance policy quote process, agents will provide quotes for their clients. These quotes provide important insights into market and customer behavior and agents are required to enter every quote into the system as soon as it is received. To help enforce this policy, a report is needed to identify deals that did NOT have at least three quotes entered into the system within 7 days after the Application Signed Date for that deal. Tables Policy – contains all of the deals that made it to a status of Application Signed Quote – contains the quotes entered for each deal (joins on OpportunityID) Policy table structure : CREATE TABLE [dbo].[Policy]( [OpportunityID] [varchar](100) NOT NULL, [DealNumber] [int] NULL, [ProductionAmount] [int] NULL, [DealPurpose] [varchar](100) NULL, [DealStatus] [varchar](100) NULL, [LenderType] [varchar](100) NULL, [OppCreatedDate] [date] NULL, [ApplicationSignedDate] [date] NULL, [ExpectedCloseDate] [date] NULL, CONSTRAINT [PK_T_Opportunity] PRIMARY KEY CLUSTERED ) Quote table structure : CREATE TABLE [dbo].[T_Quote]( [QuoteID] [varchar](100) NOT NULL, [OpportunityID] [varchar](100) NOT NULL, [LenderType] [varchar](100) NULL, [CreatedDate] [date] NULL, [WinningQuoteInd] [int] NULL, CONSTRAINT [PK_QuoteID] PRIMARY KEY CLUSTERED ) Outputs 1)Summary showing number and percent of deals that were in compliance and number and percent of deals that were not in compliance 2)Show the specific deals that were OUT of compliance, with the following data points: Total number of quotes entered for the deal Date first quote was created Date last quote was created Date third quote was created Number of days it took to enter the 3rd quote after Application Signed Date K Quote
enduku_ayya Posted March 3, 2019 Report Posted March 3, 2019 14 minutes ago, frank123 said: Nenu entry level lo jobs chusukutunnanu...please SQL saahayam cheyandi. Problem As part of the Insurance policy quote process, agents will provide quotes for their clients. These quotes provide important insights into market and customer behavior and agents are required to enter every quote into the system as soon as it is received. To help enforce this policy, a report is needed to identify deals that did NOT have at least three quotes entered into the system within 7 days after the Application Signed Date for that deal. Tables Policy – contains all of the deals that made it to a status of Application Signed Quote – contains the quotes entered for each deal (joins on OpportunityID) Policy table structure : CREATE TABLE [dbo].[Policy]( [OpportunityID] [varchar](100) NOT NULL, [DealNumber] [int] NULL, [ProductionAmount] [int] NULL, [DealPurpose] [varchar](100) NULL, [DealStatus] [varchar](100) NULL, [LenderType] [varchar](100) NULL, [OppCreatedDate] [date] NULL, [ApplicationSignedDate] [date] NULL, [ExpectedCloseDate] [date] NULL, CONSTRAINT [PK_T_Opportunity] PRIMARY KEY CLUSTERED ) Quote table structure : CREATE TABLE [dbo].[T_Quote]( [QuoteID] [varchar](100) NOT NULL, [OpportunityID] [varchar](100) NOT NULL, [LenderType] [varchar](100) NULL, [CreatedDate] [date] NULL, [WinningQuoteInd] [int] NULL, CONSTRAINT [PK_QuoteID] PRIMARY KEY CLUSTERED ) Outputs 1)Summary showing number and percent of deals that were in compliance and number and percent of deals that were not in compliance 2)Show the specific deals that were OUT of compliance, with the following data points: Total number of quotes entered for the deal Date first quote was created Date last quote was created Date third quote was created Number of days it took to enter the 3rd quote after Application Signed Date seriously.. come on man are you asking some one to do your assignment lazu b**gg*er.. go fish.. Quote
soldier Posted March 3, 2019 Report Posted March 3, 2019 2 minutes ago, enduku_ayya said: seriously.. come on man are you asking some one to do your assignment lazu b**gg*er.. go fish.. Easy bro. Time unte help chey or suggest something where he can learn, this is 2019 peace bro. No anger. Quote
karthikn Posted March 4, 2019 Report Posted March 4, 2019 13 minutes ago, Firangin said: 2*2= 6 1+6-2=55 69 = 69 LHS = RHS hence proved Quote
futureofandhra Posted March 4, 2019 Report Posted March 4, 2019 1 hour ago, enduku_ayya said: seriously.. come on man are you asking some one to do your assignment lazu b**gg*er.. go fish.. Give him some direction instead of being harsh man 1 Quote
Quickgun_murugan Posted March 4, 2019 Report Posted March 4, 2019 1 hour ago, karthikn said: 69 = 69 LHS = RHS hence proved Quote
Quickgun_murugan Posted March 4, 2019 Report Posted March 4, 2019 3 hours ago, frank123 said: Nenu entry level lo jobs chusukutunnanu...please SQL saahayam cheyandi. Problem As part of the Insurance policy quote process, agents will provide quotes for their clients. These quotes provide important insights into market and customer behavior and agents are required to enter every quote into the system as soon as it is received. To help enforce this policy, a report is needed to identify deals that did NOT have at least three quotes entered into the system within 7 days after the Application Signed Date for that deal. Tables Policy – contains all of the deals that made it to a status of Application Signed Quote – contains the quotes entered for each deal (joins on OpportunityID) Policy table structure : CREATE TABLE [dbo].[Policy]( [OpportunityID] [varchar](100) NOT NULL, [DealNumber] [int] NULL, [ProductionAmount] [int] NULL, [DealPurpose] [varchar](100) NULL, [DealStatus] [varchar](100) NULL, [LenderType] [varchar](100) NULL, [OppCreatedDate] [date] NULL, [ApplicationSignedDate] [date] NULL, [ExpectedCloseDate] [date] NULL, CONSTRAINT [PK_T_Opportunity] PRIMARY KEY CLUSTERED ) Quote table structure : CREATE TABLE [dbo].[T_Quote]( [QuoteID] [varchar](100) NOT NULL, [OpportunityID] [varchar](100) NOT NULL, [LenderType] [varchar](100) NULL, [CreatedDate] [date] NULL, [WinningQuoteInd] [int] NULL, CONSTRAINT [PK_QuoteID] PRIMARY KEY CLUSTERED ) Outputs 1)Summary showing number and percent of deals that were in compliance and number and percent of deals that were not in compliance 2)Show the specific deals that were OUT of compliance, with the following data points: Total number of quotes entered for the deal Date first quote was created Date last quote was created Date third quote was created Number of days it took to enter the 3rd quote after Application Signed Date Opportunity ID meeda join chesi use Groupby and Having so that you can use aggregate functions in your SELECT statement Quote
ekunadam_enkanna Posted March 4, 2019 Report Posted March 4, 2019 You need to study aggregate queries and stored procedures for this. Here is my ghetto sql, which you can use to improve upon. select * from dbo.Policy P , dbo.T_Quote Q where P.OpportunityID = Q.OpportunityID and datediff( Q.CreatedDate, P.ApplicationSignedDate) < 7 Having COUNT(Q.QuoteID) < 3 Quote
libraguy86 Posted March 4, 2019 Report Posted March 4, 2019 4 hours ago, enduku_ayya said: seriously.. come on man are you asking some one to do your assignment lazu b**gg*er.. go fish.. Dude .. if u wanna help do it or just stay out of it .. you do not have the right to say someone wtevrr u want .. have some respect . Quote
kingcasanova Posted March 4, 2019 Report Posted March 4, 2019 4 hours ago, ekunadam_enkanna said: You need to study aggregate queries and stored procedures for this. Here is my ghetto sql, which you can use to improve upon. select * from dbo.Policy P , dbo.T_Quote Q where P.OpportunityID = Q.OpportunityID and datediff( Q.CreatedDate, P.ApplicationSignedDate) < 7 Having COUNT(Q.QuoteID) < 3 group by kooda esi dobbithe correct ga untundi anukunta Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.