Skip to main content
Code Review

Return to Question

Tweeted twitter.com/#!/StackCodeReview/status/479528249366564864
added 28 characters in body
Source Link
Phrancis
Phrancis
  • 20.5k
  • 6
  • 70
  • 155
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="DataSource1">
      <DataSourceReference>/Data Sources/Reporting</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID> REMOVED </rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@startdate">
            <Value>=Parameters!startdate.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@enddate">
            <Value>=Parameters!enddate.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>

USE Reporting;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--DECLARE @StartDate DATETIME;
--DECLARE @EndDate DATETIME;

-- Extract the list of serials for the desired accounts into a temp table
IF OBJECT_ID('tempdb..#ContractDupSerial') IS NOT NULL DROP TABLE #ContractDupSerial;

SELECT
    cdo.Serial_Number,
    cdo.Model_Number,
    cmo.Contract_Number,
    cmo.Dealer_ID,
    cmo.Status,
    cmo.Status_Date,
    cmo.CSR_ID
INTO #ContractDupSerial
FROM
Servicer_Reporting.dbo.Contract_Master_Original AS cmo
INNER JOIN Servicer_Reporting.dbo.contract_detail_original AS cdo
    ON cdo.Contract_Number = cmo.Contract_Number
    AND cdo.Data_Source = cdo.Data_Source
INNER JOIN Servicer_Reporting.dbo.Dealer_Handling AS dh
    on dh.Dealer_ID = cmo.Dealer_ID
WHERE
dh.Account_ID IN (102,108,103,107,138,139,142,147,148,162,163,169,170)
AND cmo.Creation_Date &gt;= @startdate
AND cmo.Creation_Date &lt; @enddate

-- Filter to get only records where duplicate serials exist

SELECT     
    c.Serial_Number,
    c.Model_Number,
    c.Contract_Number,
    c.Dealer_ID,
    c.Status,
    c.Status_Date,
    c.CSR_ID
FROM #ContractDupSerial AS c
-- Clean up bogus data
WHERE c.Serial_Number IS NOT NULL
AND c.Serial_Number NOT LIKE 'N/A%'
AND c.Serial_Number NOT LIKE 'NA%'
AND c.Serial_Number NOT LIKE 'X%'
AND c.Serial_Number NOT LIKE '0%'
AND c.Serial_Number NOT LIKE ''
AND c.Serial_Number NOT LIKE '.'
-- Filter out manual cancellations and entries
AND c.Serial_Number NOT IN
    (
    SELECT c.Serial_Number
    FROM #ContractDupSerial AS c
    WHERE c.CSR_ID IS NOT NULL
    )
-- Filter in duplicate serial numbers
AND c.Serial_Number IN
    (
    SELECT c.Serial_Number
    FROM #ContractDupSerial AS c
    GROUP BY c.Serial_Number
    HAVING COUNT(c.Serial_Number) > 1
    )
ORDER BY c.Serial_Number ASC, c.Contract_Number ASC;


</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="Serial_Number">
          <DataField>Serial_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Model_Number">
          <DataField>Model_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Contract_Number">
          <DataField>Contract_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Dealer_ID">
          <DataField>Dealer_ID</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Status">
          <DataField>Status</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Status_Date">
          <DataField>Status_Date</DataField>
          <rd:TypeName>System.Decimal</rd:TypeName>
        </Field>
        <Field Name="CSR_ID">
          <DataField>CSR_ID</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportSections>
    <ReportSection>
      <Body>
        <ReportItems>
          <Tablix Name="Tablix1">
            <TablixBody>
              <TablixColumns>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
              </TablixColumns>
              <TablixRows>
                <TablixRow>
                  <Height>0.25in</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox3">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>Dealer ID</Value>
                                  <Style>
                                    <FontSize>11pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                    <Color>White</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox3</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>#4e648a</Color>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>#384c70</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox4">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>Contract Number</Value>
                                  <Style>
                                    <FontSize>11pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                    <Color>White</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox4</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>#4e648a</Color>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>#384c70</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>

<!-- CONTINUE FOR HUNDREDS OF LINES -->

      </Page>

    </ReportSection>
  </ReportSections>
  <ReportParameters>
    <ReportParameter Name="startdate">
      <DataType>DateTime</DataType>
      <Prompt>Contract Create Start Date:</Prompt>
    </ReportParameter>
    <ReportParameter Name="enddate">
      <DataType>DateTime</DataType>
      <Prompt>Contract Create End Date:</Prompt>
    </ReportParameter>
  </ReportParameters>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportServerUrl> REMOVED </rd:ReportServerUrl>
  <rd:ReportID> REMOVED </rd:ReportID>
</Report>

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="DataSource1">
      <DataSourceReference>/Data Sources/Reporting</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID> REMOVED </rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@startdate">
            <Value>=Parameters!startdate.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@enddate">
            <Value>=Parameters!enddate.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>

USE Reporting;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--DECLARE @StartDate DATETIME;
--DECLARE @EndDate DATETIME;

-- Extract the list of serials for the desired accounts into a temp table
IF OBJECT_ID('tempdb..#ContractDupSerial') IS NOT NULL DROP TABLE #ContractDupSerial;

SELECT
    cdo.Serial_Number,
    cdo.Model_Number,
    cmo.Contract_Number,
    cmo.Dealer_ID,
    cmo.Status,
    cmo.Status_Date,
    cmo.CSR_ID
INTO #ContractDupSerial
FROM
Servicer_Reporting.dbo.Contract_Master_Original AS cmo
INNER JOIN Servicer_Reporting.dbo.contract_detail_original AS cdo
    ON cdo.Contract_Number = cmo.Contract_Number
    AND cdo.Data_Source = cdo.Data_Source
INNER JOIN Servicer_Reporting.dbo.Dealer_Handling AS dh
    on dh.Dealer_ID = cmo.Dealer_ID
WHERE
dh.Account_ID IN (102,108,103,107,138,139,142,147,148,162,163,169,170)
AND cmo.Creation_Date &gt;= @startdate
AND cmo.Creation_Date &lt; @enddate

-- Filter to get only records where duplicate serials exist

SELECT     
    c.Serial_Number,
    c.Model_Number,
    c.Contract_Number,
    c.Dealer_ID,
    c.Status,
    c.Status_Date,
    c.CSR_ID
FROM #ContractDupSerial AS c
WHERE c.Serial_Number IS NOT NULL
AND c.Serial_Number NOT LIKE 'N/A%'
AND c.Serial_Number NOT LIKE 'NA%'
AND c.Serial_Number NOT LIKE 'X%'
AND c.Serial_Number NOT LIKE '0%'
AND c.Serial_Number NOT LIKE ''
AND c.Serial_Number NOT LIKE '.'
-- Filter out manual cancellations and entries
AND c.Serial_Number NOT IN
    (
    SELECT c.Serial_Number
    FROM #ContractDupSerial AS c
    WHERE c.CSR_ID IS NOT NULL
    )
-- Filter in duplicate serial numbers
AND c.Serial_Number IN
    (
    SELECT c.Serial_Number
    FROM #ContractDupSerial AS c
    GROUP BY c.Serial_Number
    HAVING COUNT(c.Serial_Number) > 1
    )
ORDER BY c.Serial_Number ASC, c.Contract_Number ASC;


</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="Serial_Number">
          <DataField>Serial_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Model_Number">
          <DataField>Model_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Contract_Number">
          <DataField>Contract_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Dealer_ID">
          <DataField>Dealer_ID</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Status">
          <DataField>Status</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Status_Date">
          <DataField>Status_Date</DataField>
          <rd:TypeName>System.Decimal</rd:TypeName>
        </Field>
        <Field Name="CSR_ID">
          <DataField>CSR_ID</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportSections>
    <ReportSection>
      <Body>
        <ReportItems>
          <Tablix Name="Tablix1">
            <TablixBody>
              <TablixColumns>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
              </TablixColumns>
              <TablixRows>
                <TablixRow>
                  <Height>0.25in</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox3">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>Dealer ID</Value>
                                  <Style>
                                    <FontSize>11pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                    <Color>White</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox3</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>#4e648a</Color>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>#384c70</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox4">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>Contract Number</Value>
                                  <Style>
                                    <FontSize>11pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                    <Color>White</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox4</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>#4e648a</Color>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>#384c70</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>

<!-- CONTINUE FOR HUNDREDS OF LINES -->

      </Page>

    </ReportSection>
  </ReportSections>
  <ReportParameters>
    <ReportParameter Name="startdate">
      <DataType>DateTime</DataType>
      <Prompt>Contract Create Start Date:</Prompt>
    </ReportParameter>
    <ReportParameter Name="enddate">
      <DataType>DateTime</DataType>
      <Prompt>Contract Create End Date:</Prompt>
    </ReportParameter>
  </ReportParameters>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportServerUrl> REMOVED </rd:ReportServerUrl>
  <rd:ReportID> REMOVED </rd:ReportID>
</Report>

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="DataSource1">
      <DataSourceReference>/Data Sources/Reporting</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID> REMOVED </rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@startdate">
            <Value>=Parameters!startdate.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@enddate">
            <Value>=Parameters!enddate.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>

USE Reporting;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--DECLARE @StartDate DATETIME;
--DECLARE @EndDate DATETIME;

-- Extract the list of serials for the desired accounts into a temp table
IF OBJECT_ID('tempdb..#ContractDupSerial') IS NOT NULL DROP TABLE #ContractDupSerial;

SELECT
    cdo.Serial_Number,
    cdo.Model_Number,
    cmo.Contract_Number,
    cmo.Dealer_ID,
    cmo.Status,
    cmo.Status_Date,
    cmo.CSR_ID
INTO #ContractDupSerial
FROM
Servicer_Reporting.dbo.Contract_Master_Original AS cmo
INNER JOIN Servicer_Reporting.dbo.contract_detail_original AS cdo
    ON cdo.Contract_Number = cmo.Contract_Number
    AND cdo.Data_Source = cdo.Data_Source
INNER JOIN Servicer_Reporting.dbo.Dealer_Handling AS dh
    on dh.Dealer_ID = cmo.Dealer_ID
WHERE
dh.Account_ID IN (102,108,103,107,138,139,142,147,148,162,163,169,170)
AND cmo.Creation_Date &gt;= @startdate
AND cmo.Creation_Date &lt; @enddate

-- Filter to get only records where duplicate serials exist

SELECT     
    c.Serial_Number,
    c.Model_Number,
    c.Contract_Number,
    c.Dealer_ID,
    c.Status,
    c.Status_Date,
    c.CSR_ID
FROM #ContractDupSerial AS c
-- Clean up bogus data
WHERE c.Serial_Number IS NOT NULL
AND c.Serial_Number NOT LIKE 'N/A%'
AND c.Serial_Number NOT LIKE 'NA%'
AND c.Serial_Number NOT LIKE 'X%'
AND c.Serial_Number NOT LIKE '0%'
AND c.Serial_Number NOT LIKE ''
AND c.Serial_Number NOT LIKE '.'
-- Filter out manual cancellations and entries
AND c.Serial_Number NOT IN
    (
    SELECT c.Serial_Number
    FROM #ContractDupSerial AS c
    WHERE c.CSR_ID IS NOT NULL
    )
-- Filter in duplicate serial numbers
AND c.Serial_Number IN
    (
    SELECT c.Serial_Number
    FROM #ContractDupSerial AS c
    GROUP BY c.Serial_Number
    HAVING COUNT(c.Serial_Number) > 1
    )
ORDER BY c.Serial_Number ASC, c.Contract_Number ASC;


</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="Serial_Number">
          <DataField>Serial_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Model_Number">
          <DataField>Model_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Contract_Number">
          <DataField>Contract_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Dealer_ID">
          <DataField>Dealer_ID</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Status">
          <DataField>Status</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Status_Date">
          <DataField>Status_Date</DataField>
          <rd:TypeName>System.Decimal</rd:TypeName>
        </Field>
        <Field Name="CSR_ID">
          <DataField>CSR_ID</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportSections>
    <ReportSection>
      <Body>
        <ReportItems>
          <Tablix Name="Tablix1">
            <TablixBody>
              <TablixColumns>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
              </TablixColumns>
              <TablixRows>
                <TablixRow>
                  <Height>0.25in</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox3">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>Dealer ID</Value>
                                  <Style>
                                    <FontSize>11pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                    <Color>White</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox3</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>#4e648a</Color>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>#384c70</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox4">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>Contract Number</Value>
                                  <Style>
                                    <FontSize>11pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                    <Color>White</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox4</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>#4e648a</Color>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>#384c70</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>

<!-- CONTINUE FOR HUNDREDS OF LINES -->

      </Page>

    </ReportSection>
  </ReportSections>
  <ReportParameters>
    <ReportParameter Name="startdate">
      <DataType>DateTime</DataType>
      <Prompt>Contract Create Start Date:</Prompt>
    </ReportParameter>
    <ReportParameter Name="enddate">
      <DataType>DateTime</DataType>
      <Prompt>Contract Create End Date:</Prompt>
    </ReportParameter>
  </ReportParameters>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportServerUrl> REMOVED </rd:ReportServerUrl>
  <rd:ReportID> REMOVED </rd:ReportID>
</Report>
Source Link
Phrancis
Phrancis
  • 20.5k
  • 6
  • 70
  • 155

Find records with duplicate serial number

I was tasked today with cleaning up data from a SSRS report put together by a DB programmer. The original report was to find duplicate serial number sales within a user-defined period of time. I was given an Excel file with the result data set and it smelled really funny, so I decided to go into SSRS and find out what the DBA did.

Turns out the person who requested the report did not explain it very well, and our data services team was overworked, so the result set was bogus and the report took over 30 minutes to execute. It looked like a desperate attempt to make a self-join-ish operation and had SELECT DISTINCT * which threw a big red flag.

So I decided to rewrite it.

Background

  1. A certain number of orders were processed as duplicates resulting in multiple coverage sales for the same unit, and invoicing problems etc.

  2. We wanted to sort out manually cancelled orders (Status = 'CAN') from deleted orders (Status = 'DEL'), with the deleted ones being what we were looking for, as deleted orders don't show up in any of the invoicing, service, etc. applications so they are more difficult to track down (by design).

  3. The report below runs in about 3 minutes. I know nothing about XML (so please don't crucify me, and I didn't write that code either) but I suspect the hundreds and hundreds of lines of formatting after the SQL query might bog it down. Unfortunately I have no way to benchmark it against SSMS performance with just the SQL, as I only have access to SSRS. Any advice on cleaning up redundant/unnecessary XML is welcome.

So here goes. Any tips on performance, best practices, etc. appreciated!

Original report: approx 550 rows returned
My updated version: 120 rows returned (for May '14)

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="DataSource1">
      <DataSourceReference>/Data Sources/Reporting</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID> REMOVED </rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet1">
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@startdate">
            <Value>=Parameters!startdate.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@enddate">
            <Value>=Parameters!enddate.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>

USE Reporting;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

--DECLARE @StartDate DATETIME;
--DECLARE @EndDate DATETIME;

-- Extract the list of serials for the desired accounts into a temp table
IF OBJECT_ID('tempdb..#ContractDupSerial') IS NOT NULL DROP TABLE #ContractDupSerial;

SELECT
    cdo.Serial_Number,
    cdo.Model_Number,
    cmo.Contract_Number,
    cmo.Dealer_ID,
    cmo.Status,
    cmo.Status_Date,
    cmo.CSR_ID
INTO #ContractDupSerial
FROM
Servicer_Reporting.dbo.Contract_Master_Original AS cmo
INNER JOIN Servicer_Reporting.dbo.contract_detail_original AS cdo
    ON cdo.Contract_Number = cmo.Contract_Number
    AND cdo.Data_Source = cdo.Data_Source
INNER JOIN Servicer_Reporting.dbo.Dealer_Handling AS dh
    on dh.Dealer_ID = cmo.Dealer_ID
WHERE
dh.Account_ID IN (102,108,103,107,138,139,142,147,148,162,163,169,170)
AND cmo.Creation_Date &gt;= @startdate
AND cmo.Creation_Date &lt; @enddate

-- Filter to get only records where duplicate serials exist

SELECT     
    c.Serial_Number,
    c.Model_Number,
    c.Contract_Number,
    c.Dealer_ID,
    c.Status,
    c.Status_Date,
    c.CSR_ID
FROM #ContractDupSerial AS c
WHERE c.Serial_Number IS NOT NULL
AND c.Serial_Number NOT LIKE 'N/A%'
AND c.Serial_Number NOT LIKE 'NA%'
AND c.Serial_Number NOT LIKE 'X%'
AND c.Serial_Number NOT LIKE '0%'
AND c.Serial_Number NOT LIKE ''
AND c.Serial_Number NOT LIKE '.'
-- Filter out manual cancellations and entries
AND c.Serial_Number NOT IN
    (
    SELECT c.Serial_Number
    FROM #ContractDupSerial AS c
    WHERE c.CSR_ID IS NOT NULL
    )
-- Filter in duplicate serial numbers
AND c.Serial_Number IN
    (
    SELECT c.Serial_Number
    FROM #ContractDupSerial AS c
    GROUP BY c.Serial_Number
    HAVING COUNT(c.Serial_Number) > 1
    )
ORDER BY c.Serial_Number ASC, c.Contract_Number ASC;


</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="Serial_Number">
          <DataField>Serial_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Model_Number">
          <DataField>Model_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Contract_Number">
          <DataField>Contract_Number</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Dealer_ID">
          <DataField>Dealer_ID</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Status">
          <DataField>Status</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Status_Date">
          <DataField>Status_Date</DataField>
          <rd:TypeName>System.Decimal</rd:TypeName>
        </Field>
        <Field Name="CSR_ID">
          <DataField>CSR_ID</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportSections>
    <ReportSection>
      <Body>
        <ReportItems>
          <Tablix Name="Tablix1">
            <TablixBody>
              <TablixColumns>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
                <TablixColumn>
                  <Width>1in</Width>
                </TablixColumn>
              </TablixColumns>
              <TablixRows>
                <TablixRow>
                  <Height>0.25in</Height>
                  <TablixCells>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox3">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>Dealer ID</Value>
                                  <Style>
                                    <FontSize>11pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                    <Color>White</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox3</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>#4e648a</Color>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>#384c70</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>
                    <TablixCell>
                      <CellContents>
                        <Textbox Name="Textbox4">
                          <CanGrow>true</CanGrow>
                          <KeepTogether>true</KeepTogether>
                          <Paragraphs>
                            <Paragraph>
                              <TextRuns>
                                <TextRun>
                                  <Value>Contract Number</Value>
                                  <Style>
                                    <FontSize>11pt</FontSize>
                                    <FontWeight>Bold</FontWeight>
                                    <Color>White</Color>
                                  </Style>
                                </TextRun>
                              </TextRuns>
                              <Style />
                            </Paragraph>
                          </Paragraphs>
                          <rd:DefaultName>Textbox4</rd:DefaultName>
                          <Style>
                            <Border>
                              <Color>#4e648a</Color>
                              <Style>Solid</Style>
                            </Border>
                            <BackgroundColor>#384c70</BackgroundColor>
                            <PaddingLeft>2pt</PaddingLeft>
                            <PaddingRight>2pt</PaddingRight>
                            <PaddingTop>2pt</PaddingTop>
                            <PaddingBottom>2pt</PaddingBottom>
                          </Style>
                        </Textbox>
                      </CellContents>
                    </TablixCell>

<!-- CONTINUE FOR HUNDREDS OF LINES -->

      </Page>

    </ReportSection>
  </ReportSections>
  <ReportParameters>
    <ReportParameter Name="startdate">
      <DataType>DateTime</DataType>
      <Prompt>Contract Create Start Date:</Prompt>
    </ReportParameter>
    <ReportParameter Name="enddate">
      <DataType>DateTime</DataType>
      <Prompt>Contract Create End Date:</Prompt>
    </ReportParameter>
  </ReportParameters>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportServerUrl> REMOVED </rd:ReportServerUrl>
  <rd:ReportID> REMOVED </rd:ReportID>
</Report>