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

    Unanswered: How to Create Time Series

    Hi,

    I have created a series of 4 SQL statments that contain 1-2 temporary tables each that are in one Query. I created it this way because I could logically step through the data. Because it contains ##tables I know it cant be used in either a named query in analysis services, or as a VIEW.

    Can you make any reccomenmodifications can i make to the below statement to allow it to be more flexible?

    Can you direct me towards some newb/simple reading that will help me decide how to begin creating the time series?
    1. Time Series by compID
    2. Time Series by Group of compID



    I expect the time series will reflect the following scenario.
    As a general rule: Residents move-in to the community and stay UNTIL their needs can no longer be met by this type of community. When someone moves-in they must have a CS. CS1 is the lowest, CS10 is the highest. As a residents CS increases the quantity and length of AWAY should increase. Residents needs will fluctuate during their stay in the community but there should be a general upward trend in services. We would like to ultimately project the needs of our residents, which residents will leave and what is the NEED of the next incoming resident.

    Types of Transactions that occur in the Time Series "MAJOR EVENTS"

    1. WL: (Customer will be moving-in to the community soon)
    2. MSF: (Customer has moved-in to the community)
    3. CS1-10: (Level of services provided IE: how much product/service bought daily)
    4. AWAY: (Customer is absent from community for a period of time)
    5. TERM: (Customer will be moving-out of the community soon)
    6. OUT: (Customer has moved-out of the community


    Code:
    /* Check for existance of YourChoice temporary table and if it exists drop it*/
    
    if OBJECT_ID('tempdb..##yctemp') is not null begin	drop table ##yctemp end
    if OBJECT_ID('tempdb..##yc') is not null begin	drop table ##yc end
    /* SELECT from communications transactions that are not deleted, have a company, and are care steps.  
    	Standardize column names in temporary tables.
    	Insert these transactions into YC temporary table created above. */
    	
    SELECT     SUBSTRING(comm_trantype,CHARINDEX('YC',comm_trantype,0)+2,LEN(comm_trantype)) as TRANS, comm_trandate as transDATE, CmLi_Comm_CompanyID as compID, Comm_CommunicationId AS commID
    		   ,(ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
    			ORDER BY CmLi_Comm_CompanyID,comm_trandate)) AS seq
    			, (CASE WHEN comm_trantype LIKE 'NO%' then 'OFF'
    			   ELSE 'ON' END) as transTYPE
    INTO ##yctemp
    FROM         dbo.vCommunication
    WHERE (Comm_Deleted IS NULL) AND (CmLi_Comm_CompanyID IS NOT NULL) AND (comm_trantype LIKE '%YC%')
    
    
    /*
    Organize YC transactions into start & end dates for each transaction. 
    Sort by compID & transDATE
    Insert into Temporary Table ##YC
    */
    
    SELECT TOP 100 PERCENT [on].compID, [on].commID, [on].TRANS, [on].transDATE, [off].transDATE AS endDATE
    INTO ##YC
    FROM
    (SELECT *
    	FROM ##yctemp) AS [on] LEFT JOIN
            (SELECT *
    			FROM ##yctemp) AS [off] ON [off].CompID = [on].CompID AND [off].seq >= [on].seq AND [on].TRANS = [off].TRANS and [on].transTYPE <> [off].transTYPE
    WHERE [on].transTYPE = 'ON'
    ORDER BY [on].compID, [on].transDATE DESC
    
    
    /* End YourChoice Temporary Table statements */
    
    /* Check for existance of AWAY temporary table and if it exists drop it*/
    if OBJECT_ID('tempdb..##awaytemp') is not null begin	drop table ##awaytemp end
    if OBJECT_ID('tempdb..##away') is not null begin	drop table ##away end
    
    /* 
    SELECT from communications transactions that are not deleted, have a company, and are care steps.  
    	Standardize column names in temporary tables 
    	Insert these transactions into YC temporary table created above.
    */
    
    SELECT     comm_trantype as TRANS, comm_trandate as transDATE, CmLi_Comm_CompanyID as compID, Comm_CommunicationId AS commID
    		   ,(ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
    			ORDER BY CmLi_Comm_CompanyID,comm_trandate)) AS seq
    			, (CASE WHEN comm_trantype LIKE 'RETURN%' then 'OFF'
    			   ELSE 'ON' END) as transTYPE
    INTO ##awaytemp
    FROM         dbo.vCommunication
    WHERE (Comm_Deleted IS NULL) AND (CmLi_Comm_CompanyID IS NOT NULL) AND (comm_trantype LIKE '%AWAY%') OR (comm_trantype LIKE '%RETURN%')
    ORDER BY compID
    
    
    /*
    Organize AWAY transactions into start & end dates for each transaction. 
    Sort by compID & transDATE
    Insert into Temporary Table ##AWAY
    */
    
    SELECT TOP 100 PERCENT [on].compID, [on].commID,[on].TRANS,[on].transDATE, [off].transDATE as endDATE
    INTO ##AWAY
    FROM
    (SELECT *, (ROW_NUMBER() OVER (PARTITION BY compID ORDER BY compID,transDATE)) as onSEQ
    	FROM ##awaytemp
    	WHERE TRANS LIKE 'AWAY') AS [on] LEFT JOIN
            (SELECT  *, (ROW_NUMBER() OVER (PARTITION BY compID ORDER BY compID,transDATE)) as offSEQ
    			FROM ##awaytemp
    			WHERE TRANS LIKE 'RETURN') AS [off] ON [off].compID = [on].CompID 
    						AND [on].transTYPE <> [off].transTYPE 
    						AND [on].commID <> [off].commID 
    						AND [on].onSEQ = [off].offSEQ 
    						AND [on].transDATE <= [off].transDATE 
    						AND [on] .seq < [off].seq 
    						AND [on].compID = [off].compID
    WHERE [on].transTYPE = 'ON'  
    ORDER BY [on].compID, [on].transDATE DESC
    
    
    /* End Away Temporary Table statements */
    
    /* Check for existance of CARE STEP temporary table and if it exists drop it*/
    if OBJECT_ID('tempdb..##cstemp') is not null begin	drop table ##cstemp end
    if OBJECT_ID('tempdb..##cs') is not null begin	drop table ##cs end
    /* 
    SELECT from communications transactions that are not deleted, have a company, and are care steps.  
    	Standardize column names in temporary tables. 
    	Insert these transactions into YC temporary table created above.
    */
    	
    SELECT     comm_trantype as TRANS, comm_trandate as transDATE, CmLi_Comm_CompanyID as compID, Comm_CommunicationId AS commID
    		   ,(ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
    			ORDER BY CmLi_Comm_CompanyID,comm_trandate)) AS seq
    INTO ##cstemp
    FROM         dbo.vCommunication
    WHERE (Comm_Deleted IS NULL) AND (CmLi_Comm_CompanyID IS NOT NULL) AND (comm_trantype LIKE '%CS%')
    
    
    
    /*
    Organize CS transactions into start & end dates for each transaction. 
    Sort by compID & transDATE
    Insert into Temporary Table ##CS
    */
    
    SELECT TOP 100 PERCENT [on].compID, [on].commID, [on].TRANS, [on].transDATE,[off].transDATE as endDATE
    INTO ##CS
    FROM
    (SELECT DISTINCT *, (ROW_NUMBER() OVER (PARTITION BY compID ORDER BY compID,transDATE)) as onSEQ
    	FROM ##cstemp) AS [on] LEFT JOIN
            (SELECT *, (ROW_NUMBER() OVER (PARTITION BY compID ORDER BY compID,transDATE)) as offSEQ
    			FROM ##cstemp) AS [off] ON [off].CompID = [on].CompID 
    										AND [off].seq >= [on].seq
    										AND [on].transDATE <= [off].transDATE 
    										AND [on] .seq < [off].seq 
    										AND [on].commID <> [off].commID
    										AND [on].onSEQ + 1 = [off].offSEQ
    						
    ORDER BY [on].compID, [on].transDATE DESC
    
    
    /* End CareStep Temporary Table statements */
    
    /*create Occupancy transactions using temporary table named ##occtemp. */
    
    if OBJECT_ID('tempdb..##occtemp') is not null begin	drop table ##occtemp end
    if OBJECT_ID('tempdb..##occ') is not null begin	drop table ##occ end
    
    SELECT     comm_trantype as TRANS, comm_trandate as transDATE, CmLi_Comm_CompanyID as compID, Comm_CommunicationId AS commID
    		   ,(ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
    			ORDER BY CmLi_Comm_CompanyID,comm_trandate)) AS seq
    			, (CASE WHEN comm_trantype LIKE 'MSF%' then '2'
    					WHEN comm_trantype LIKE 'WL%' then '1'
    					WHEN comm_trantype LIKE 'TERM%' then '3'
    					WHEN comm_trantype LIKE 'OUT' then '4'
    			   ELSE NULL END) as transTYPE
    INTO ##occtemp
    FROM         dbo.vCommunication
    WHERE (Comm_Deleted IS NULL) AND (CmLi_Comm_CompanyID IS NOT NULL) AND (comm_trantype IN ('MSF','WL','TERM','OUT'))
    ORDER BY compID
    
    /*
    Organize OCC transactions into start & end dates for each transaction. 
    Sort by compID & transDATE
    Insert into Temporary Table ##OCC
    */
    
    SELECT TOP 100 PERCENT [on].compID, [on].commID,[on].TRANS,[on].transDATE, [off].transDATE as endDATE
    INTO ##OCC
    FROM
    (SELECT *, (ROW_NUMBER() OVER (PARTITION BY compID ORDER BY compID,transDATE)) as onSEQ
    	FROM ##occtemp) AS [on] LEFT JOIN
            (SELECT  *, (ROW_NUMBER() OVER (PARTITION BY compID ORDER BY compID,transDATE)) as offSEQ
    			FROM ##occtemp) AS [off] ON [off].compID = [on].CompID 
    						AND [on].transTYPE < [off].transTYPE 
    						AND [on].commID <> [off].commID 
    						AND [on].transDATE <= [off].transDATE 
    						AND [on] .seq < [off].seq 
    						AND [on].compID = [off].compID
    						AND [on].onSEQ + 1 = [off].offSEQ
    
    ORDER BY [on].compID, [on].transDATE DESC
    
    SELECT *
    FROM ##YC
    UNION
    SELECT *
    FROM ##AWAY
    UNION
    SELECT *
    FROM ##CS
    UNION
    SELECT *
    FROM ##OCC
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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