SQL experts sahaayam kaavali

[frank123]

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

 

 

 

[Mitron]

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

[enduku_ayya]

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..

[soldier]

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.

[Firangin]

2*2= 6

1+6-2=55

[karthikn]

13 minutes ago, Firangin said:

2*2= 6

1+6-2=55

69 = 69 LHS = RHS hence proved 

[futureofandhra]

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 

[Quickgun_murugan]

1 hour ago, karthikn said:

69 = 69 LHS = RHS hence proved 

[Quickgun_murugan]

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

[ekunadam_enkanna]

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

 

[Desi_guy]

Idi kuda adagala...try cheyi first

[libraguy86]

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 .

 

[kingcasanova]

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

[TriStateBabu]

bump for sql experts