locked
Unique Occurences with conditions... RRS feed

  • Question

  • Hi all,

     

    i have discuused various issues I have been facing Here are the steps i used until now.

    1. Took all of the BUG details and case details created a table that also has a column "call_rec - which contains a string of CaseIDs taht are associated with one particular BUGID.

    2.So I created a new column Bug_Count  in a new table that will be set to 1 on a condition..

    SELECT [BugNo], [Fiscal Month], MIN(CaseId) AS CaseID, 1 AS BugCount

    INTO

     

    [BugCountFrom CEB]

    FROM

     

    [CUstomerEncountered BURTS-Case_and_BU_mappings]

    GROUP

     

    BY [BugNo],[Fiscal Month]

    This will give me the BUGcount =1 for every encountered CASEID by fiscal month

    3. then I took this table and joined with my maintable (which has all of the other BUGinfo and Caseinfo) and added BUGCOunt ( by fiscal month)to this view.

    4. then I created another Table UNique_BugCountFrom_CEB- irrespective of fiscal MONTH..

    SELECT

     

    [BugNo], MIN(CaseId) AS MinCaseID, 1 AS UniqueBugCount

    INTO

     

    [UNique_BugCountFrom_CEB]

    FROM

     

    [CUstomerEncountered BURTS-Case_and_BU_mappings]

    GROUP

     

    BY [BugNo]

    5. then I joined this again with the above view that has the BUGCount as well as all of the maintable information and added UNIQUE bugcount from the above step in order to get the UNique count irrestctive of the occurence of the cases.So some times the UniqueBugCount will be Null  or 1

    6. the issue I have is when I try to pivot this view into excel, i am always encountering duplicates. So I took the approach that you suggested that seem to only give the unique BURTs count which I am already getting from UNique_BugCountFrom_CEB

    here is the query- which is pretty much giving me the results I get from step 2.

     

    WITH

     

    OrderData AS (

     

    Select dbo.[BugCountFrom CEB].BugCount, dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].BugNo,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].DateModified, dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].call_rec,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].[Encountered Date], dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].state,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].[public], dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].sev,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].pri, dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].impact,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].panicstr, dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].type,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].subtype,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].customer_case_score,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].escal_status, dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].date_create,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].CaseId, dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].title,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].[Fiscal Month], dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].Category,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].SYMPTOMTEXT,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].PRIORITY,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].NA_RECEIVEDVIA,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].STATUSTEXT,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].CREATEDATE,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].NA_CAT1NAME,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].NA_CAT2NAME,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].NA_CAT3NAME,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].NA_SYMPTOMNAME,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].NA_PROBLEMCAUSE,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].NA_RESOLUTIONDESC,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].CASE_AGE, dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].OSVERSION,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].ACCOUNT_CLASSIFICATION,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].CUSTOMERTYPE,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].CUSTOMERNAME,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].ACCTYPE,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].ACCTYPEDESC, dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].CAP,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].FEDERAL, dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].TEA,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].subteam, dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].target_release,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].keywords,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].[BU from Engineering],

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].BU2,

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].[NGS-Identified Main BU],

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings].Product

     

    ,

     

    ROW_NUMBER() OVER (PARTITION BY [CUstomerEncountered BURTS-Case_and_BU_mappings].BugNo ORDER BY [CUstomerEncountered BURTS-Case_and_BU_mappings].[Fiscal Month])AS Selector

     

    FROM dbo.[BugCountFrom CEB] RIGHT OUTER JOIN

    dbo

    .[CUstomerEncountered BURTS-Case_and_BU_mappings] ON

    dbo

    .[BugCountFrom CEB].CaseID = dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].CaseId AND

    dbo

    .[BugCountFrom CEB].BugNo = dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].BugNo AND

    dbo

    .[BugCountFrom CEB].[Fiscal Month] = dbo.[CUstomerEncountered BURTS-Case_and_BU_mappings].[Fiscal Month]

    )

    SELECT

     

    *

    FROM

     

    OrderData

    WHERE

     

    Selector = 1

    7. I guess what need is a final view with the BugCount by fiscal month and Unique BUG_Count- I am able to run both of those views with no issues- however I end up with duplicates when I do teh join.

    How can I get rid of duplicates and still be able to have all of the columns I need.

    Help appreciated.

    Thursday, November 18, 2010 9:43 PM

Answers

  • Can you restate your question?

     

    TO ALL: Use INSERT CODE BLOCK for posting code.


    Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by Kalman Toth Monday, November 29, 2010 11:19 PM
    Wednesday, November 24, 2010 5:37 PM