SQLTeam.com | Weblogs | Forums

Identifying accounts over multiple months


#1

Hello Everyone,
I'm currently using code to identify accounts that have had backdating done to them more than once within a given month. I would like to modify my query to only identify accounts that have been truly backdated more than once within a given month by using the Create Date for the order. I believe by adding

where B.CreateDate <> B.CreateDate

I will get the result I'm looking for. However, I would now like to also add the criteria to identify if an account has been backdated within consecutive months. This is the code I'm currently working with, I'm stuck as to how I can add the parameter to add activity within the past 90 days. Any and all help is greatly appreciated!

SELECT B.CSG_OpID, B.OP_NAME, B.SubAcctNo, count(B.SubAcctNo) as Multiple_Act, B.[DIFFERENCE] FROM #TMP11O AS B

WHERE B.[DIFFERENCE] > 0 AND B.[DIFFERENCE] < 41000
--and B.CreateDate <> B.CreateDate

group by B.CSG_OpID, B.OP_NAME, B.SubAcctNo, B.SubAcctNo, B.CreateDate, B.[DIFFERENCE]

Having count(B.SubAcctNo) > 1

Order By B.SubAcctNo, B.OP_NAME


Thank you!
Damian

#2

How can that ever be (except when B.CreateDate is null that is)? Are those not the same column?

Have you left something out of your post?


#3

Hi gbritton,
You're correct, and I realized this after I posted my question. There won't ever be a time where the create dates will not match, so I've removed that line from my code. I'm currently receiving results which indicate accounts that have been backdated multiple times within the same month, however, I'm currently stuck trying to identify whether an account has been backdated by the same user within consecutive months. Any help is very much appreciated!

Damian


#4

Would you please post a CREATE TABLE statement for the table and INSERT INTO statement(s) to populate it showing the exact situation you have and what you need to see from the query?


#5

I'm not creating any tables. I'm using pre-existing tables from the DB. The code I posted in my original question is the temp table I'm using in order to identify accounts that have been backdated multiple times within the same month. However, I would like to know if there is a calculation, or process in which I can also calculate if an account has been backdated by the same rep over a period of 3 months.


#6

I know that, but we want to create your scenario, the simplest way is if you post the CREATE TABLE statement and INSERT INTO statements

See: Posting Guidelines


#7

Ok. I understand. The query is a bit long, but here goes.

IF OBJECT_ID('TEMPDB..#TMP9O') IS NOT NULL DROP TABLE #TMP9O IF OBJECT_ID('TEMPDB..#TMP10O') IS NOT NULL DROP TABLE #TMP10O IF OBJECT_ID('TEMPDB..#TMP11O') IS NOT NULL DROP TABLE #TMP11O

--COMPLETES SERVICE ORDERS AND SRO'S--
SELECT DISTINCT
'OCR' AS TABLEIND
,C.[PRIN_OCR] AS PRIN
,C.[AGNT_OCR] AS AGENT
,C.[COMPL_DTE_OCR] AS COMPLETE_DTE
,C.[CREATE_DTE_OCR] AS CREATE_DTE
,C.[CREATE_OP_ID_OCR] AS OPID
,C.[CREATE_TERM_ID_OCR] AS TERMID
,C.[HSE_KEY_OCR] AS HSEKEY
,C.[CUST_ACCT_NO_OCR] AS CUSTACCTNO
,C.[ORDER_NO_OCR] AS ORDNO
,CASE WHEN C.[ORD_CLASS_OCR]='M' THEN 'SRO'
WHEN C.[ORD_CLASS_OCR]='S' THEN 'SERVICEORD'
WHEN C.[ORD_CLASS_OCR]='T' THEN 'TC'
ELSE ' ' END AS ORDCLASS
,CASE WHEN C.[ORD_STAT_OCR]='C' THEN 'CLOSED'
WHEN C.[ORD_STAT_OCR]='I' THEN 'INPROGRESS'
WHEN C.[ORD_STAT_OCR]='O' THEN 'OPEN'
WHEN C.[ORD_STAT_OCR]='X' THEN 'CANCELLED'
ELSE ' ' END AS ORDSTAT
,C.[SALESREP_OCR] AS SALESREP
,ISNULL(CC2.DESCR_CTD,' ') AS OrderRsn1
,ISNULL(CC3.DESCR_CTD,' ') AS OrderRsn2
,C.BILL_STOP_DTE_OCR AS BillStopDate

INTO #TMP9O

FROM [Vantage].[dbo].[OCR_ORDER_COMP] C WITH(NOLOCK) LEFT JOIN VANTAGE.DBO.CTD_DISPLAY CC2 (NOLOCK)
ON CC2.PRIN_CTD = C.PRIN_OCR AND
CC2.CDE_VALUE_CTD = SUBSTRING(C.ORD_RSN_OCR,1,2) AND
CC2.SPA_FLG_CTD = 'P' AND
CC2.CDE_TBL_NO_CTD = '03'

	LEFT JOIN VANTAGE.DBO.CTD_DISPLAY CC3 (NOLOCK) 

ON CC3.PRIN_CTD = C.PRIN_OCR AND
CC3.CDE_VALUE_CTD = SUBSTRING(C.ORD_RSN_OCR,3,2) AND
CC3.SPA_FLG_CTD = 'P' AND
CC3.CDE_TBL_NO_CTD = '03'

WHERE C.PRIN_OCR IN (7500,7400,6000) AND
C.[CREATE_DTE_OCR] BETWEEN @STARTDATE AND @ENDDATE
SELECT DISTINCT A.*,O.SUB_ACCT_NO_OHI AS SubAcctNo
,SBB.RES_NAME_SBB AS CustomerName
,SBB.VIP_FLG_SBB AS VipFlag
,SBB.HOME_PHONE_SBB AS ResPhone
,SBB.BUS_PHONE_SBB AS BusPhone

INTO #TMP10O

FROM #TMP9O A WITH(NOLOCK) INNER JOIN VANTAGE.DBO.OHI_HIST_ITEM O (NOLOCK)
ON A.PRIN = O.PRIN_OHI AND
A.AGENT = O.AGNT_OHI AND
A.ORDNO = O.ORDER_NO_OHI

		LEFT JOIN VANTAGE.DBO.SBB_BASE SBB (NOLOCK)

ON O.PRIN_OHI = SBB.PRIN_SBB AND
O.AGNT_OHI = SBB.AGNT_SBB AND
O.SUB_ACCT_NO_OHI = SBB.SUB_ACCT_NO_SBB

SELECT DISTINCT CONVERT(VARCHAR(8), GETDATE(), 1) AS ReportDate,
A.Prin,
A.Agent,
A.SubAcctNo,
A.CUSTACCTNO AS CustAcctNo,
A.ORDNO AS OrderNo,
A.HSEKEY AS HseKey,
A.CustomerName,
A.VipFlag,
CT2.DESCR_CTD AS VipFlagDesc,
A.ResPhone,
A.BusPhone,
H.ADDR1_HSE AS ServiceAddress,
H.RES_ADDR_2_HSE AS ServiceAptAndNumber,H.RES_CITY_HSE AS ServiceCity,
H.RES_STATE_HSE AS ServiceState,H.POSTAL_CDE_HSE AS ServiceZipAndZip4,
ISNULL(CUS.[E_MAIL_ADDR_CUS],' ') AS EmailAddr,
ISNULL(CONVERT(VARCHAR(10),A.CREATE_DTE,120),' ') as CreateDate,
ISNULL(CONVERT(VARCHAR(10),J.SCHED_DTE_OJB,120),' ') as ScheduleDate,
ISNULL(CONVERT(VARCHAR(10),A.COMPLETE_DTE,120),' ') as CompleteDate,
ISNULL(CONVERT(VARCHAR(10),A.BillStopDate,120),' ') as BillStopDate,
DATEDIFF(d, A.BillStopDate, A.COMPLETE_DTE) AS [DIFFERENCE],
ISNULL(J.JOB_NO_OJB,' ') AS JobNumber,
ISNULL(J.IR_TECH_OJB,' ') AS AssignedTech,
ISNULL(J.JOB_TYP_OJB,' ') AS JobType,
ISNULL(CC.DESCR_CTD,' ') AS JobTypeDesc,
A.ORDCLASS AS OrderClass,
A.ORDSTAT AS OrderStatus,
A.OrderRsn1,
A.OrderRsn2,
A.TERMID AS TermID,
A.SALESREP AS SalesRepID,
OP.PerNr,
A.OPID AS CSG_OpID,
ISNULL(OP.[FullName],' ') AS OP_NAME,
ISNULL(OP.[Title],' ') AS OP_TITLE,
OP.DEPARTMENT,
ISNULL(SP.[SUPERVISOR_NAME],' ') AS SUP_NAME,
ISNULL(OP.[Manager],' ') AS MGR_NAME,
ISNULL(OP.[Director],' ') AS DIR_NAME,
ISNULL(NULLIF(ISNULL(J.[LS_CHG_OP_ID_OJB],'ZZO'),J.[LS_CHG_OP_ID_OJB]),'ZZQ') AS LSCHG

INTO #TMP11O

FROM #TMP10O A WITH(NOLOCK) LEFT JOIN Vantage.dbo.OJB_JOBS J (NOLOCK)
ON A.Prin = J.PRIN_OJB AND
A.Agent = J.AGNT_OJB AND
A.ORDNO = J.ORDER_NO_OJB

LEFT JOIN VANTAGE.DBO.CTD_DISPLAY CC (NOLOCK)
ON CC.PRIN_CTD = J.PRIN_OJB AND
CC.CDE_VALUE_CTD = J.JOB_TYP_OJB AND
CC.SPA_FLG_CTD = 'P' AND
CC.CDE_TBL_NO_CTD = '32'

INNER JOIN Vantage.dbo.HSE_BASE H (NOLOCK)
ON A.Prin = H.PRIN_HSE AND
A.Agent = H.AGNT_HSE AND
A.HSEKEY = H.HSE_KEY_HSE

LEFT JOIN Vantage.DBO.SBB_BASE SBB (NOLOCK)
ON A.PRIN = SBB.PRIN_SBB AND
A.AGENT = SBB.AGNT_SBB AND
A.CUSTACCTNO = SBB.CUST_ACCT_NO_SBB AND
A.HSEKEY = SBB.HSE_KEY_SBB

LEFT JOIN VANTAGE.DBO.CTD_DISPLAY CT2 (NOLOCK)
ON SBB.PRIN_SBB = CT2.PRIN_CTD AND
CT2.CDE_TBL_NO_CTD = '24' AND CT2.CDE_VALUE_CTD = SBB.VIP_FLG_SBB AND CT2.SPA_FLG_CTD = 'P'

LEFT JOIN [Vantage].[dbo].[CUS_BASE] CUS (NOLOCK)
ON A.PRIN = CUS.[PRIN_CUS] AND
A.CustAcctNo = CUS.[CUST_ACCT_NO_CUS]

LEFT JOIN [Reporting].[dbo].[UserBaseOpIds] OP (NOLOCK)
ON A.OpID = OP.[CsgOpId]

LEFT JOIN CCBIS.adm.SapPersonnel SP (NOLOCK)
ON OP.PerNr = SP.PERNR

WHERE OP.Manager <> 'Doe, John'
AND OP.Director like 'General%'
AND SBB.EXT_STAT_SBB = ' '
AND A.ORDSTAT LIKE ('CLOSE%')

SELECT B.CSG_OpID, B.OP_NAME, B.SubAcctNo, count(B.SubAcctNo) as Multiple_Act FROM #TMP11O AS B

WHERE B.[DIFFERENCE] > 0 AND B.[DIFFERENCE] < 41000

group by B.CSG_OpID, B.OP_NAME, B.SubAcctNo, B.SubAcctNo, B.CreateDate

Having count(B.SubAcctNo) > 1

Order By B.SubAcctNo, B.OP_NAME


Please let me know if you need anything else. I appreciate you taking the time to help me with this. Thank you.
Damian

#8

Could you also please post some data to populate #TMP11O? That way we can set up and test a solution on our own. we need data that shows the problem. Please also post the desired results, using that data.


#9

Here's examples of #TMP11O results. Column D shows the results I'm currently receiving with my query. Column E would be the results I would like to get. Please let me know if you need anything else.


#10

Damian39

please create a sample data not by pasting an image but doing the following. this will help answer your question quicker
ie

create table #TMP11O(CSG_OpID int, OP_NAME varchar(50), SubAccountNo int, multiple_act int, Consecutive_BD_act int)

insert into #TMP11O
select 'ABC', Doe, John,, 8495600211111, 2, 1
union
select 'ABC', Doe, John,, 8495600211111, 3, 1

etc


#11

I'm confused. I provided the code I'm currently using, and the output I'm currently receiving. I don't understand how posting code that creates temp tables, and the columns that I'm currently getting back from my code will assist in answering the question quicker. As you can see by the last temp table #TMP11O, I'm currently doing a count on SubAcctNo's in order to identify which accounts have been backdated by the same rep more than once, however, I'm stuck on identifying accounts that have been backdated by same rep over a period of 3 months.


#12

that was just a sample on how you can provide us the data needed in order to help you. without sample data its just a shot in the dark. we do not have your data so if you post code such as

LEFT JOIN VANTAGE.DBO.CTD_DISPLAY CC (NOLOCK) ....
INNER JOIN Vantage.dbo.HSE_BASE H (NOLOCK) it does not help us since we do not have access to your database.

Also if you post a picture we cannot copy the data from that picture to try it out locally.


#13

I understand, however, I'm unable to upload anything but a picture. I tried to upload the spreadsheet, but was given an error message I am only allowed to upload JPEG. I will modify the code to create the tables and columns in order for you to be able to test.


#14

IN column F (I think, can't see the columns or rows) type in:
="INSERT INTO #TMP110 VALUES ('" & A2 & "', '" & B2 & "', " & C2 & ", " & D2 & ", " & E2 & ");"
Then drag the + in the right lower corner of F2 down to the last row with data. Then copy the F2 column and paste it into SSMS or a text editor. That will generate insert statement for each row in the spread sheet.


#15

Hello everyone,
I think I may have over-complicated the initial question within this thread. I'm essentially looking for the best code to use in order to identify values that repeat over a period of 90 days. In the above scenario that would be account numbers and user id's.The code that I'm using is working and bringing me back the data I need, however, I would like to further drill down over a period of 90 days. Does that simplify my question, or does everyone still require me to upload generic code in order for you to test?
Damian