Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2012
    Posts
    9

    Unanswered: NEWBIE: QUERY to create VIEW/REPORT

    Problem Background:

    ALL, I am new to SQL and have a complicated request that i hope is not really as complicated as i have made it. I am running MSFT SQL 2008 with a CRM system. In that CRM system we have defined "COMMUNICATIONS" that have a number of different "TYPES" These communications are tied to Companies, which in our business is a resident of our community. The resident has the ability to request a 1 time service (TRAVEL) that would be recorded with one "COMMUNICATION" , or a reoccuring service (DAILY CARE) that would be recorded with two "COMMUNICATIONS" (Start/Stop). THe communication ID does appear to be sequential, but may not be entered sequentially.

    Ideally, we would like to be able to create a timeline for a specific resident around 3 specific types of transactions. (AWAY, RETURN, CS) There should be some strong predictive value to these data points. I have created two VIEWS for each of the below situation. Further down in this post I will post specific goals & problems encountered thus far.


    Thank you in advance for your help and im sorry for posing soo much information but it seems the more i browse the forums, that people are always asking for more detail to help solve problems.


    ABSENCES
    AWAY = Start of an absence from the building
    RETURN = End of an absence from the building
    A person during their reisdency will have multiple Absences
    An absence could start on the same day a previous absence ends
    Absences vary in length
    CS
    CS0-CS10 (each is a different TYPE) of communication
    CS0 indicates that the person has not moved in yet, or has moved out
    CS1-10 has a value attached to it for daily time to assist that individual
    CS may go both up and down over time

    VIEW 1) AWAYS
    Length of Absence
    Time between absences ** (no progress so far)
    Frequency of Absence ** (no progress so far)
    Does frequency increase when CS increases?** (no progress so far)
    Relate CS to specific AWAY to ( IE: CS at time of absence is the CS triggered before away)

    Query in DESIGN View

    SELECT TOP 100 PERCENT comm_trantype AS Type, comm_trandate AS Date, CmLi_Comm_CompanyID AS CompID, Comm_CommunicationId AS CommID, ROW_NUMBER()
    OVER (PARTITION BY CmLi_Comm_CompanyID
    ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
    FROM CRM_CSLDB.dbo.vCommunication
    WHERE (comm_trantype LIKE N'AWAY%') OR
    (comm_trantype LIKE N'RETURN%')
    GROUP BY CmLi_Comm_CompanyID, comm_trantype, comm_trandate, Comm_CommunicationId, Comm_Deleted
    HAVING (Comm_Deleted IS NULL)
    SELECT TOP 100 PERCENT t1.*, DATEDIFF(day, t1.date, t2.date) AS AwayDays, t2.Date AS ReturnDate
    FROM [AwayTemp] AS t1 INNER JOIN
    [AwayTemp] AS t2 ON [t1].[Compid] = [t2].[Compid] AND (t1.Seq + 1 = t2.Seq)
    WHERE t2.Type = 'Return'
    ORDER BY t1.CompID, t1.CommID, t1.[Date]

    Results from QUERY in DESIGN VIEW

    TYPE DATE COMPID COMMID SEQ
    AWAY 2011-02-24 00:00:00.000 51 74753 1
    RETURN 2011-03-31 00:00:00.000 51 74754 2
    AWAY 2011-03-28 00:00:00.000 64 74068 1
    RETURN 2011-04-30 00:00:00.000 64 75297 2
    AWAY 2011-05-01 00:00:00.000 64 75298 3
    RETURN 2011-05-31 00:00:00.000 64 76332 4
    AWAY 2011-06-01 00:00:00.000 64 76336 5
    RETURN 2011-06-30 00:00:00.000 64 76337 6
    AWAY 2011-12-13 00:00:00.000 72 85805 1
    RETURN 2012-01-20 00:00:00.000 72 86814 2
    AWAY 2012-03-20 00:00:00.000 78 89964 1
    RETURN 2012-03-30 00:00:00.000 78 89965 2
    AWAY 2012-04-19 00:00:00.000 78 90898 3
    AWAY 2011-10-02 00:00:00.000 152 83383 1
    RETURN 2011-10-11 00:00:00.000 152 83818 2
    AWAY 2011-10-31 00:00:00.000 152 84248 3
    RETURN 2011-11-21 00:00:00.000 152 87347 4
    AWAY 2012-02-24 00:00:00.000 152 88614 5
    RETURN 2012-03-20 00:00:00.000 152 89866 6
    AWAY 2012-02-23 00:00:00.000 161 89254 1
    AWAY 2011-06-20 00:00:00.000 169 77564 1
    RETURN 2011-06-28 00:00:00.000 169 77566 2
    AWAY 2011-08-02 00:00:00.000 172 78323 1

    RESULTS FROM QUERY in SELECT TOP 11

    Type Date CompID CommID Seq AwayDays ReturnDate
    AWAY 2011-06-20 00:00:00.000 169 77564 1 8 2011-06-28 00:00:00.000
    AWAY 2011-12-23 00:00:00.000 1505 90504 4 112 2012-04-13 00:00:00.000
    AWAY 2011-10-09 00:00:00.000 3534 83946 1 16 2011-10-25 00:00:00.000
    AWAY 2012-01-27 00:00:00.000 3983 89036 3 94 2012-04-30 00:00:00.000
    AWAY 2012-03-09 00:00:00.000 4064 90061 5 20 2012-03-29 00:00:00.000
    AWAY 2011-10-29 00:00:00.000 4620 84278 1 32 2011-11-30 00:00:00.000
    AWAY 2012-02-09 00:00:00.000 4675 87780 5 4 2012-02-13 00:00:00.000
    AWAY 2011-06-06 00:00:00.000 4713 77205 1 24 2011-06-30 00:00:00.000
    AWAY 2011-12-15 00:00:00.000 4938 87776 1 19 2012-01-03 00:00:00.000
    AWAY 2012-04-01 00:00:00.000 5424 90194 1 29 2012-04-30 00:00:00.000
    AWAY 2012-04-14 00:00:00.000 5649 90637 1 6 2012-04-20 00:00:00.000

    VIEW 2) CARE STEP HISTORY **(No progress)
    LENGTH OF TIME IN EACH CS
    PREVIOUS and/or NEXT CS
    FREQUENCY OF CHANGE
    The analysis goal of this table is to predict CS over time.
    Relate CS to specific AWAY to (CS is the most immediate preciding CS

    QUERY in DESIGN VIEW

    SELECT TOP (100) PERCENT dbo.vCommunication.comm_trantype AS csTrans, dbo.vCommunication.comm_trandate AS csTranDate,
    dbo.vCommunication.CmLi_Comm_CompanyID AS CompanyID, dbo.vCommunication.Comm_CommunicationId AS Comm_ID, dbo.CSL_resident.Name,
    Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
    ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
    FROM dbo.vCommunication INNER JOIN
    dbo.CSL_resident ON dbo.vCommunication.CmLi_Comm_CompanyID = dbo.CSL_resident.Compid
    WHERE (dbo.vCommunication.comm_trantype LIKE N'CS%')
    GROUP BY dbo.vCommunication.CmLi_Comm_CompanyID, dbo.vCommunication.comm_trantype, dbo.vCommunication.comm_trandate,
    dbo.vCommunication.Comm_CommunicationId, dbo.CSL_resident.Name
    ORDER BY CompanyID, csTranDate, Comm_ID, csTrans

    QUERY RESULTS IN SELECT

    csTrans csTranDate CompanyID Comm_ID Name CommID Seq
    CS1 2009-07-27 00:00:00.000 11 76147 Harrison Bailey 76147 1
    CS3 2007-08-27 00:00:00.000 26 73777 Dorothy Wheeler 73777 1
    CS4 2011-12-02 00:00:00.000 26 85087 Dorothy Wheeler 85087 2
    CS5 2012-01-01 00:00:00.000 26 85446 Dorothy Wheeler 85446 3
    CS3 2011-03-31 00:00:00.000 51 74759 Bonnie Clawson 74759 1
    CS3 2008-04-19 00:00:00.000 62 73352 Tressie Desmond 73352 1
    CS4 2009-04-01 00:00:00.000 64 70436 Mary Jane Hamm 70436 1
    CS4 2009-02-22 00:00:00.000 72 73854 Cleveland Hester 73854 1
    CS2 2010-10-20 00:00:00.000 78 73040 Lola Hamilton 73040 1
    CS1 2011-04-04 00:00:00.000 130 73794 Irra Dell Pinkard 73794 1

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You haven't asked a question.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2012
    Posts
    9

    questions in red

    i have highlighted the "QUESTIONS" IN RED thank you for pointing out that i am truly a newb

    Quote Originally Posted by cwwhite View Post
    Problem Background:

    ALL, I am new to SQL and have a complicated request that i hope is not really as complicated as i have made it. I am running MSFT SQL 2008 with a CRM system. In that CRM system we have defined "COMMUNICATIONS" that have a number of different "TYPES" These communications are tied to Companies, which in our business is a resident of our community. The resident has the ability to request a 1 time service (TRAVEL) that would be recorded with one "COMMUNICATION" , or a reoccuring service (DAILY CARE) that would be recorded with two "COMMUNICATIONS" (Start/Stop). THe communication ID does appear to be sequential, but may not be entered sequentially.

    Ideally, we would like to be able to create a timeline for a specific resident around 3 specific types of transactions. (AWAY, RETURN, CS) There should be some strong predictive value to these data points. I have created two VIEWS for each of the below situation. Further down in this post I will post specific goals & problems encountered thus far.


    Thank you in advance for your help and im sorry for posing soo much information but it seems the more i browse the forums, that people are always asking for more detail to help solve problems.


    ABSENCES
    AWAY = Start of an absence from the building
    RETURN = End of an absence from the building
    A person during their reisdency will have multiple Absences
    An absence could start on the same day a previous absence ends
    Absences vary in length
    CS
    CS0-CS10 (each is a different TYPE) of communication
    CS0 indicates that the person has not moved in yet, or has moved out
    CS1-10 has a value attached to it for daily time to assist that individual
    CS may go both up and down over time

    VIEW 1) AWAYS
    Length of Absence Can you please help me calculate
    Time between absences ** (no progress so far) Can you please help me calculate
    Frequency of Absence ** (no progress so far)
    Does frequency increase when CS increases?** (no progress so far)
    Relate CS to specific AWAY to ( IE: CS at time of absence is the CS triggered before away)

    Query in DESIGN View

    SELECT TOP 100 PERCENT comm_trantype AS Type, comm_trandate AS Date, CmLi_Comm_CompanyID AS CompID, Comm_CommunicationId AS CommID, ROW_NUMBER()
    OVER (PARTITION BY CmLi_Comm_CompanyID
    ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
    FROM CRM_CSLDB.dbo.vCommunication
    WHERE (comm_trantype LIKE N'AWAY%') OR
    (comm_trantype LIKE N'RETURN%')
    GROUP BY CmLi_Comm_CompanyID, comm_trantype, comm_trandate, Comm_CommunicationId, Comm_Deleted
    HAVING (Comm_Deleted IS NULL)
    SELECT TOP 100 PERCENT t1.*, DATEDIFF(day, t1.date, t2.date) AS AwayDays, t2.Date AS ReturnDate
    FROM [AwayTemp] AS t1 INNER JOIN
    [AwayTemp] AS t2 ON [t1].[Compid] = [t2].[Compid] AND (t1.Seq + 1 = t2.Seq)
    WHERE t2.Type = 'Return'
    ORDER BY t1.CompID, t1.CommID, t1.[Date]

    Results from QUERY in DESIGN VIEW

    TYPE DATE COMPID COMMID SEQ
    AWAY 2011-02-24 00:00:00.000 51 74753 1
    RETURN 2011-03-31 00:00:00.000 51 74754 2
    AWAY 2011-03-28 00:00:00.000 64 74068 1
    RETURN 2011-04-30 00:00:00.000 64 75297 2
    AWAY 2011-05-01 00:00:00.000 64 75298 3
    RETURN 2011-05-31 00:00:00.000 64 76332 4
    AWAY 2011-06-01 00:00:00.000 64 76336 5
    RETURN 2011-06-30 00:00:00.000 64 76337 6
    AWAY 2011-12-13 00:00:00.000 72 85805 1
    RETURN 2012-01-20 00:00:00.000 72 86814 2
    AWAY 2012-03-20 00:00:00.000 78 89964 1
    RETURN 2012-03-30 00:00:00.000 78 89965 2
    AWAY 2012-04-19 00:00:00.000 78 90898 3
    AWAY 2011-10-02 00:00:00.000 152 83383 1
    RETURN 2011-10-11 00:00:00.000 152 83818 2
    AWAY 2011-10-31 00:00:00.000 152 84248 3
    RETURN 2011-11-21 00:00:00.000 152 87347 4
    AWAY 2012-02-24 00:00:00.000 152 88614 5
    RETURN 2012-03-20 00:00:00.000 152 89866 6
    AWAY 2012-02-23 00:00:00.000 161 89254 1
    AWAY 2011-06-20 00:00:00.000 169 77564 1
    RETURN 2011-06-28 00:00:00.000 169 77566 2
    AWAY 2011-08-02 00:00:00.000 172 78323 1

    RESULTS FROM QUERY in SELECT TOP 11

    Type Date CompID CommID Seq AwayDays ReturnDate
    AWAY 2011-06-20 00:00:00.000 169 77564 1 8 2011-06-28 00:00:00.000
    AWAY 2011-12-23 00:00:00.000 1505 90504 4 112 2012-04-13 00:00:00.000
    AWAY 2011-10-09 00:00:00.000 3534 83946 1 16 2011-10-25 00:00:00.000
    AWAY 2012-01-27 00:00:00.000 3983 89036 3 94 2012-04-30 00:00:00.000
    AWAY 2012-03-09 00:00:00.000 4064 90061 5 20 2012-03-29 00:00:00.000
    AWAY 2011-10-29 00:00:00.000 4620 84278 1 32 2011-11-30 00:00:00.000
    AWAY 2012-02-09 00:00:00.000 4675 87780 5 4 2012-02-13 00:00:00.000
    AWAY 2011-06-06 00:00:00.000 4713 77205 1 24 2011-06-30 00:00:00.000
    AWAY 2011-12-15 00:00:00.000 4938 87776 1 19 2012-01-03 00:00:00.000
    AWAY 2012-04-01 00:00:00.000 5424 90194 1 29 2012-04-30 00:00:00.000
    AWAY 2012-04-14 00:00:00.000 5649 90637 1 6 2012-04-20 00:00:00.000

    VIEW 2) CARE STEP HISTORY **(No progress)
    LENGTH OF TIME IN EACH CS - can you please help me calculate
    PREVIOUS and/or NEXT CS - can you please help me calculate
    FREQUENCY OF CHANGE
    The analysis goal of this table is to predict CS over time.
    Relate CS to specific AWAY to (CS is the most immediate preciding CS
    QUERY in DESIGN VIEW

    SELECT TOP (100) PERCENT dbo.vCommunication.comm_trantype AS csTrans, dbo.vCommunication.comm_trandate AS csTranDate,
    dbo.vCommunication.CmLi_Comm_CompanyID AS CompanyID, dbo.vCommunication.Comm_CommunicationId AS Comm_ID, dbo.CSL_resident.Name,
    Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
    ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
    FROM dbo.vCommunication INNER JOIN
    dbo.CSL_resident ON dbo.vCommunication.CmLi_Comm_CompanyID = dbo.CSL_resident.Compid
    WHERE (dbo.vCommunication.comm_trantype LIKE N'CS%')
    GROUP BY dbo.vCommunication.CmLi_Comm_CompanyID, dbo.vCommunication.comm_trantype, dbo.vCommunication.comm_trandate,
    dbo.vCommunication.Comm_CommunicationId, dbo.CSL_resident.Name
    ORDER BY CompanyID, csTranDate, Comm_ID, csTrans

    QUERY RESULTS IN SELECT

    csTrans csTranDate CompanyID Comm_ID Name CommID Seq
    CS1 2009-07-27 00:00:00.000 11 76147 Harrison Bailey 76147 1
    CS3 2007-08-27 00:00:00.000 26 73777 Dorothy Wheeler 73777 1
    CS4 2011-12-02 00:00:00.000 26 85087 Dorothy Wheeler 85087 2
    CS5 2012-01-01 00:00:00.000 26 85446 Dorothy Wheeler 85446 3
    CS3 2011-03-31 00:00:00.000 51 74759 Bonnie Clawson 74759 1
    CS3 2008-04-19 00:00:00.000 62 73352 Tressie Desmond 73352 1
    CS4 2009-04-01 00:00:00.000 64 70436 Mary Jane Hamm 70436 1
    CS4 2009-02-22 00:00:00.000 72 73854 Cleveland Hester 73854 1
    CS2 2010-10-20 00:00:00.000 78 73040 Lola Hamilton 73040 1
    CS1 2011-04-04 00:00:00.000 130 73794 Irra Dell Pinkard 73794 1

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, now how about the table definition for dbo.vCommunication, and a dozen or so rows of sample data?

    blindman
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2012
    Posts
    9

    vCommunication

    QUERY and results from vCommunication filtered for the column that includes only these types of transactions "ACTIONS" SEE RED BELOW. Results are pasted but just in case the formatting is worthless i have attached the txt file to this post.



    /****** Script for SelectTopNRows command from SSMS ******/
    SELECT TOP (1000) Comm_CommunicationId, Comm_OpportunityId, Comm_CaseId, Comm_ChannelId, Comm_Type, Comm_Action, Comm_Status, Comm_Priority,
    Comm_DateTime, Comm_ToDateTime, Comm_Private, Comm_OutCome, Comm_Note, Comm_Email, Comm_CreatedBy, Comm_CreatedDate, Comm_UpdatedBy,
    Comm_UpdatedDate, Comm_TimeStamp, Comm_Deleted, Comm_DocDir, Comm_DocName, Comm_TargetListId, Comm_NotifyTime, Comm_NotifyDelta,
    Comm_Description, Comm_SMSMessageSent, Comm_SMSNotification, Comm_WaveItemId, Comm_RecurrenceId, Comm_SegmentID, Comm_LeadID, Comm_SecTerr,
    Comm_WorkflowId, comm_messageid, Comm_From, Comm_TO, Comm_CC, Comm_BCC, Comm_ReplyTo, Comm_IsReplyToMsgId, Comm_SolutionId, Comm_IsHtml,
    Comm_HasAttachments, Comm_EmailLinksCreated, comm_completedtime, comm_percentcomplete, comm_taskreminder, Comm_CRMOnly, Comm_OriginalDateTime,
    Comm_OriginalToDateTime, Comm_Exception, Comm_Organizer, Comm_OrderId, Comm_QuoteId, Comm_OutlookID, Comm_MeetingID, comm_salesaction,
    comm_billrate, comm_billdate, comm_trantype, comm_trandate, comm_unit, comm_billrate_CID, comm_movereason, CmLi_Comm_UserID, CmLi_Comm_PersonID,
    CmLi_Comm_CompanyID, CmLi_Comm_AccountId, CmLi_CommLinkId, CmLi_SMSMessageSent, CmLi_Comm_NotifyTime, CmLi_Comm_InitialWave,
    CmLi_Comm_WaveResponse, CmlI_Comm_LeadID, Cmli_Status, cmli_recipient, Recu_RecurrenceId, Recu_Type, Recu_Interval, Recu_DayOfWeek,
    Recu_DayOfMonth, Recu_Instance, Recu_SubType, Recu_SubInterval, Recu_StartDateTime, Recu_EndDateTime
    FROM CRM_CSLDB.dbo.vCommunication
    WHERE (Comm_Action = N'bc')


    Comm_CommunicationId Comm_OpportunityId Comm_CaseId Comm_ChannelId Comm_Type Comm_Action Comm_Status Comm_Priority Comm_DateTime Comm_ToDateTime Comm_Private Comm_OutCome Comm_Note Comm_Email Comm_CreatedBy Comm_CreatedDate Comm_UpdatedBy Comm_UpdatedDate Comm_TimeStamp Comm_Deleted Comm_DocDir Comm_DocName Comm_TargetListId Comm_NotifyTime Comm_NotifyDelta Comm_Description Comm_SMSMessageSent Comm_SMSNotification Comm_WaveItemId Comm_RecurrenceId Comm_SegmentID Comm_LeadID Comm_SecTerr Comm_WorkflowId comm_messageid Comm_From Comm_TO Comm_CC Comm_BCC Comm_ReplyTo Comm_IsReplyToMsgId Comm_SolutionId Comm_IsHtml Comm_HasAttachments Comm_EmailLinksCreated comm_completedtime comm_percentcomplete comm_taskreminder Comm_CRMOnly Comm_OriginalDateTime Comm_OriginalToDateTime Comm_Exception Comm_Organizer Comm_OrderId Comm_QuoteId Comm_OutlookID Comm_MeetingID comm_salesaction comm_billrate comm_billdate comm_trantype comm_trandate comm_unit comm_billrate_CID comm_movereason CmLi_Comm_UserID CmLi_Comm_PersonID CmLi_Comm_CompanyID CmLi_Comm_AccountId CmLi_CommLinkId CmLi_SMSMessageSent CmLi_Comm_NotifyTime CmLi_Comm_InitialWave CmLi_Comm_WaveResponse CmlI_Comm_LeadID Cmli_Status cmli_recipient Recu_RecurrenceId Recu_Type Recu_Interval Recu_DayOfWeek Recu_DayOfMonth Recu_Instance Recu_SubType Recu_SubInterval Recu_StartDateTime Recu_EndDateTime
    69185 NULL NULL NULL Task bc Complete Normal 2010-10-22 16:25:00.000 NULL NULL NULL Monthly Service Fee 06/01/2010 NULL 70 2010-10-22 16:00:00.000 122 2011-11-17 15:25:22.000 2011-11-17 15:25:22.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -2147483639 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 100 NULL NULL NULL NULL Y 70 NULL NULL NULL NULL NULL 1930.000000 NULL MSF 2010-06-01 00:00:00.000 4 1 NULL 70 8406 4220 NULL 78607 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    69186 NULL NULL NULL Task bc Complete Normal 2010-10-22 16:30:00.000 NULL NULL NULL Cable TV 06/01/2010 NULL 70 2010-10-22 16:02:00.000 122 2011-11-17 15:25:12.000 2011-11-17 15:25:12.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -2147483639 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 100 NULL NULL NULL NULL Y 70 NULL NULL NULL NULL NULL 38.000000 NULL CABLE 2010-06-01 00:00:00.000 4 1 NULL 70 8406 4220 NULL 78608 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    69187 NULL NULL NULL Task bc Complete Normal 2010-10-22 16:30:00.000 NULL NULL NULL Carestep Level 2 06/01/2010 NULL 70 2010-10-22 16:03:00.000 122 2011-11-17 15:25:01.000 2011-11-17 15:25:01.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -2147483639 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 100 NULL NULL NULL NULL Y 70 NULL NULL NULL NULL NULL 215.000000 NULL CS2 2010-06-01 00:00:00.000 4 1 NULL 70 8406 4220 NULL 78609 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    69395 NULL NULL NULL Task bc Complete Normal 2010-10-28 20:00:00.000 NULL NULL NULL Activity 05/01/2010 NULL 79 2010-10-28 19:42:00.000 122 2011-11-17 15:50:38.000 2011-11-17 15:50:38.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -2147483639 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 100 NULL NULL NULL NULL Y 79 NULL NULL NULL NULL NULL 36.450000 NULL RTRAN 2010-05-01 00:00:00.000 NULL 1 NULL 122 8408 4222 NULL 111637 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    69400 NULL NULL NULL Task bc Complete Normal 2010-10-28 20:30:00.000 NULL NULL NULL Activity 05/01/2010 NULL 79 2010-10-28 20:02:00.000 122 2011-11-17 15:11:52.000 2011-11-17 15:11:52.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -2147483639 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2010-11-09 21:46:24.000 100 NULL NULL NULL NULL Y 79 NULL NULL NULL NULL NULL 20.500000 NULL RMAIN 2010-05-01 00:00:00.000 26 1 NULL 122 8427 4242 NULL 111630 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    69404 NULL NULL NULL Task bc Complete Normal 2010-10-28 20:40:00.000 NULL NULL NULL Activity 06/01/2010 NULL 79 2010-10-28 20:09:00.000 122 2011-11-17 15:11:01.000 2011-11-17 15:11:01.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -2147483639 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2010-11-09 21:47:25.000 100 NULL NULL NULL NULL Y 79 NULL NULL NULL NULL NULL 25.340000 NULL RMAIN 2010-06-01 00:00:00.000 26 1 NULL 122 8427 4242 NULL 111629 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
    69493 NULL NULL 20 Task bc Complete Normal 2010-11-02 17:05:00.000 NULL NULL NULL CARESTEP LEVEL 6 NULL 78 2010-11-02 16:34:00.000 43 2011-09-07 10:44:18.000 2011-09-07 10:44:18.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL -2147483639 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2010-11-09 21:53:43.000 100 NULL NULL NULL NULL Y NULL NULL NULL NULL NULL NULL 1461.940000 NULL CS6 2010-08-01 00:00:00.000 26 1 NULL 126
    Attached Files Attached Files

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In SQL Server Management Studio, navigate to your vCommunication table.
    Right click it, and select "Script table as...", then "Create to..." and then "New Query Window".

    Copy the output, and paste it into this thread.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2012
    Posts
    9
    SELECT [Comm_CommunicationId]
    ,[Comm_OpportunityId]
    ,[Comm_CaseId]
    ,[Comm_ChannelId]
    ,[Comm_Type]
    ,[Comm_Action]
    ,[Comm_Status]
    ,[Comm_Priority]
    ,[Comm_DateTime]
    ,[Comm_ToDateTime]
    ,[Comm_Private]
    ,[Comm_OutCome]
    ,[Comm_Note]
    ,[Comm_Email]
    ,[Comm_CreatedBy]
    ,[Comm_CreatedDate]
    ,[Comm_UpdatedBy]
    ,[Comm_UpdatedDate]
    ,[Comm_TimeStamp]
    ,[Comm_Deleted]
    ,[Comm_DocDir]
    ,[Comm_DocName]
    ,[Comm_TargetListId]
    ,[Comm_NotifyTime]
    ,[Comm_NotifyDelta]
    ,[Comm_Description]
    ,[Comm_SMSMessageSent]
    ,[Comm_SMSNotification]
    ,[Comm_WaveItemId]
    ,[Comm_RecurrenceId]
    ,[Comm_SegmentID]
    ,[Comm_LeadID]
    ,[Comm_SecTerr]
    ,[Comm_WorkflowId]
    ,[comm_messageid]
    ,[Comm_From]
    ,[Comm_TO]
    ,[Comm_CC]
    ,[Comm_BCC]
    ,[Comm_ReplyTo]
    ,[Comm_IsReplyToMsgId]
    ,[Comm_SolutionId]
    ,[Comm_IsHtml]
    ,[Comm_HasAttachments]
    ,[Comm_EmailLinksCreated]
    ,[comm_completedtime]
    ,[comm_percentcomplete]
    ,[comm_taskreminder]
    ,[Comm_CRMOnly]
    ,[Comm_OriginalDateTime]
    ,[Comm_OriginalToDateTime]
    ,[Comm_Exception]
    ,[Comm_Organizer]
    ,[Comm_OrderId]
    ,[Comm_QuoteId]
    ,[Comm_OutlookID]
    ,[Comm_MeetingID]
    ,[comm_salesaction]
    ,[comm_billrate]
    ,[comm_billdate]
    ,[comm_trantype]
    ,[comm_trandate]
    ,[comm_unit]
    ,[comm_billrate_CID]
    ,[comm_movereason]
    ,[comm_noticeflag]
    ,[CmLi_Comm_UserID]
    ,[CmLi_Comm_PersonID]
    ,[CmLi_Comm_CompanyID]
    ,[CmLi_Comm_AccountId]
    ,[CmLi_CommLinkId]
    ,[CmLi_SMSMessageSent]
    ,[CmLi_Comm_NotifyTime]
    ,[CmLi_Comm_InitialWave]
    ,[CmLi_Comm_WaveResponse]
    ,[CmlI_Comm_LeadID]
    ,[Cmli_Status]
    ,[cmli_recipient]
    ,[Recu_RecurrenceId]
    ,[Recu_Type]
    ,[Recu_Interval]
    ,[Recu_DayOfWeek]
    ,[Recu_DayOfMonth]
    ,[Recu_Instance]
    ,[Recu_SubType]
    ,[Recu_SubInterval]
    ,[Recu_StartDateTime]
    ,[Recu_EndDateTime]
    FROM [CRM_CSLDB].[dbo].[vCommunication]
    GO

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ah, No

    But HOLY COW...

    Did you create this table?

    Do you know what Normalization is?

    In any case, in order for us to help you, let's get simple

    State the Problem: I'd like to see how many ...yada yada yada

    The Table has these columns I need to work with

    CREATE TABLE <tablename> (Col1 int, Col2 char...etc)

    Som Sample Data I have would look like

    INSERT INTO <tablename> (Column list)
    SELECT...data...UNION ALL
    SELECT...data...UNION ALL
    ...ect


    And my final result should look like, based on the sample data supplied

    Column Headings
    Data
    ...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's not what I asked for.
    I gave specific steps to generate DDL for the table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2012
    Posts
    9

    create to

    sorry blindman


    USE [CRM_CSLDB]
    GO

    /****** Object: View [dbo].[vCommunication] Script Date: 05/01/2012 14:23:38 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE VIEW [dbo].[vCommunication] AS SELECT Communication.*, CmLi_Comm_UserID, CmLi_Comm_PersonID, CmLi_Comm_CompanyID, CmLi_Comm_AccountId, CmLi_CommLinkId, CmLi_SMSMessageSent, CmLi_Comm_NotifyTime, CmLi_Comm_InitialWave, CmLi_Comm_WaveResponse, CmlI_Comm_LeadID, Cmli_Status ,cmli_recipient, Recu_RecurrenceId, Recu_Type, Recu_Interval, Recu_DayOfWeek, Recu_DayOfMonth, Recu_Instance, Recu_SubType, Recu_SubInterval, Recu_StartDateTime, Recu_EndDateTime FROM Communication LEFT JOIN Comm_Link ON Comm_CommunicationID = CmLi_Comm_CommunicationID LEFT JOIN Recurrence ON Comm_RecurrenceID = Recu_RecurrenceID WHERE Comm_Deleted IS NULL AND CmLi_Deleted IS NULL

    GO

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's a LITTLE more helpful but (no surprise), you are creating a query from a view rather than from the base tables. That makes it a bit more difficult to understand the nature of your data.

    What data is stored in each of these tables:
    -Communication
    -Comm_Link
    -Recurrence

    Let's deal with your absence-related questions first.
    What defines an "absence"?
    Specifically, what are the date fields we should be looking at?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Apr 2012
    Posts
    9

    more info

    ,[CmLi_Comm_CompanyID] = CUSTOMER
    ,[comm_trantype] = 'AWAY'
    ,[comm_trandate] = '1/1/2012'


    ,[CmLi_Comm_CompanyID] = CUSTOMER
    ,[comm_trantype] = 'RETURN'
    ,[comm_trandate] = '1/30/2012'



    Apartment numbers at a given location vary so the communication field[Comm_SecTerr] = Territory/Location This field is not a required field in the individual communication transaction so the grouping of Apartments has to be done first by relating the COMPANY ID to the Company Table.


    The major tables in this database are:

    dbo.Company
    dbo.Person
    dbo.Communication

    dbo.Comm_Link as a primary key of CmLi_CommLinkId relates the below tables:
    Company via CmLi_Comm_CompanyId
    Person via CmLi_Comm_PersonId
    Communication via CmLi_Comm_CommunicationId




    The below QUERY is an example of ALL similar transactions already filtered and related to the COMPANY table to get a Territory Description


    SELECT dbo.Company.Comp_CompanyId AS compid, dbo.Company.Comp_Name AS company, dbo.Territories.Terr_Caption AS Cottage,
    dbo.Communication.comm_trandate AS trandate, dbo.Communication.comm_trantype AS Trantype, dbo.Communication.comm_unit AS unit,
    dbo.Communication.comm_movereason AS movereason, dbo.Communication.comm_billdate AS billdate, dbo.Communication.comm_billrate AS billrate,
    dbo.Communication.comm_salesaction AS salesaction, dbo.Communication.Comm_CommunicationId AS communicationid,
    dbo.Communication.Comm_Note AS billnote, dbo.Communication.Comm_Status AS Status, dbo.Company.comp_mas_customerno AS CustnoMAS,
    dbo.Company.comp_mas_ardivisionno AS DivnoMAS, dbo.Communication.comm_noticeflag AS noticeflag
    FROM dbo.Communication INNER JOIN
    dbo.Comm_Link ON dbo.Communication.Comm_CommunicationId = dbo.Comm_Link.CmLi_Comm_CommunicationId INNER JOIN
    dbo.Company ON dbo.Comm_Link.CmLi_Comm_CompanyId = dbo.Company.Comp_CompanyId INNER JOIN
    dbo.Territories ON dbo.Company.Comp_SecTerr = dbo.Territories.Terr_TerritoryID
    WHERE (dbo.Communication.comm_trantype IS NOT NULL) AND (dbo.Communication.Comm_Deleted IS NULL) AND (dbo.Comm_Link.CmLi_Deleted IS NULL) AND
    (dbo.Company.Comp_Deleted IS NULL) AND (dbo.Territories.Terr_Deleted IS NULL)

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK. So I gather that your system creates a record when someone exits ("AWAY"), and then another when they come back ("RETURN").

    But the data you posted seems to indicated that you can have multiple consecutive AWAY records without matching RETURN records:

    Quote Originally Posted by cwwhite View Post
    TYPE DATE COMPID COMMID SEQ
    AWAY 2011-02-24 00:00:00.000 51 74753 1
    RETURN 2011-03-31 00:00:00.000 51 74754 2
    AWAY 2011-03-28 00:00:00.000 64 74068 1
    RETURN 2011-04-30 00:00:00.000 64 75297 2
    AWAY 2011-05-01 00:00:00.000 64 75298 3
    RETURN 2011-05-31 00:00:00.000 64 76332 4
    AWAY 2011-06-01 00:00:00.000 64 76336 5
    RETURN 2011-06-30 00:00:00.000 64 76337 6
    AWAY 2011-12-13 00:00:00.000 72 85805 1
    RETURN 2012-01-20 00:00:00.000 72 86814 2
    AWAY 2012-03-20 00:00:00.000 78 89964 1
    RETURN 2012-03-30 00:00:00.000 78 89965 2
    AWAY 2012-04-19 00:00:00.000 78 90898 3
    AWAY 2011-10-02 00:00:00.000 152 83383 1
    RETURN 2011-10-11 00:00:00.000 152 83818 2
    AWAY 2011-10-31 00:00:00.000 152 84248 3
    RETURN 2011-11-21 00:00:00.000 152 87347 4
    AWAY 2012-02-24 00:00:00.000 152 88614 5
    RETURN 2012-03-20 00:00:00.000 152 89866 6
    AWAY 2012-02-23 00:00:00.000 161 89254 1
    AWAY 2011-06-20 00:00:00.000 169 77564 1
    RETURN 2011-06-28 00:00:00.000 169 77566 2
    AWAY 2011-08-02 00:00:00.000 172 78323 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •