Solved

How to DELETE multiple rows of data for a SINGLE RECORD based on coded DATE field that is found within ONE of the Rows?

Posted on 2016-09-11
9
43 Views
Last Modified: 2016-09-12
My data looks like this:

rowid,detaildata
1,0BATCH NEXTGEN         NEXTGENEMR 20160829CPM         651
2,1P0007.63579.0000                           1609168541          899972053           1230                                  CN25
3,2P201608232016082399348            1   0         1
4,51 000.00
6,71609168541
7,1P0007.63512.0000                           1093765679          100458751           1230                                  CN25
8,2P201508262015082699347            1   0         1
9,51 000.00
10,71093765679
11,1P0014.236945.0000                          1174807291          2254912701          1230                                  GW23
12,2P201608252016082599341            1   0         1
13,51 000.00
20,71174807291
21,1P0003.316952.0000                          1962840793          A00199273           3230                                  MM05
22,2P201608262016082699307            1   0         1
23,51 000.00
25,71962840793
26,1P0003.316921.0000                          1821384934          A00188972           3230                                  MM05
27,2P201608282016082899308            1   0         1
28,51 000.00
30,71821384934

I need a SQL Script that will delete multiple rows of data that pertain to a SINGLE RECORD. A single record consists of rows where the "DetailData" field starts with a the numbers (1-7).

In my sample data RowIDs 2-6 are for one record. RowIDs 7-10 are for the next record. RowIDs  11-20 are for the next record..and so on..etc.

The "DetailData" field for all rows that start with a "2" contain a coded date like this "20150826" in characters 3-10.

I need to find all ROWIDs that contain a coded Date that is LESS THAN < '10/01/2015' and then DELETE all of the Rows that pertain to this RECORD. So in my example data above, I need to remove rows 7-10 out of this table since they belong to a RECORD that has a date that is LESS THAN < '10/01/2015'.

Please let me know how I can delete multiple rows of data based on this type of coded date that is found in ONE of the records ROWS.

Thanks
0
Comment
Question by:MIKE
  • 4
  • 4
9 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 41793787
your data is stored in a very weird format. I am pretty sure there is an academic term for this data structure, and even an article describing pros and cons.

I think, if you have control for this database you could improve it like this: add two columns: recordId and DetailType. Then use the same RecordID for the rows corresponding to each logical record. The detailType is something that will let you identify what the detail row means. E.g you know that the detail with 1 means and order date, detail 2 means status, detail 3 means invoice amount, etc. Therefore, your table can be re-written like this:
rowid  detaildata  (truncated for display purposes)                                   recordID                      detailType
------------------------------------------------------------------------------------------------------------------------------
2      1P0007.63579.0000                           1609168541                         1234                          status
3      2P201608232016082399348            1   0         1                             1234                          orderDate
4      51 000.00                                                                      1234                          invoiceAmount
6      71609168541                                                                    1234                          tax
7      1P0007.63512.0000                           1093765679                         1235                          status
8      2P201508262015082699347            1   0         1                             1235                          orderDate

Open in new window

This way it will be more EAV table then yours. It will still be EAV though with all the problems EAV presents, but it will be more manageable this way
0
 
LVL 17

Author Comment

by:MIKE
ID: 41794069
Experts,

PLEASE. I don't need ideas on how to change my current table. It is what it is and I have NO CONTROL over the format of the source table. It is a 3rd party software that can't be changed at all. The final table that I provide back to the Source MUST be in the EXACT SAME FORMAT as the original source data...

I need to know how to accomplish my task as mentioned in my question....
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41794085
Got the "is what it is" part. Nobody would query data in this form if they had a choice! Give me a few...I'm trying to think of the implicit relationship that will turn this relational, and I am still working on my morning coffee. Thank you for the morning wake-up exercise.
0
 
LVL 13

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 41794158
The coffee is still soaking in (i.e. test carefully), but here's a SELECT for the rows associated with the date range < 10/1/2015. Adjust the date as needed and switch to the commented-out DELETE to actually do the job. All the usual disclaimers apply.

If you are using an earlier version of SQL Server, you might need to code CAST in place of TRY_CAST.

I didn't attempt any optimization. Given the 'schema' of this data, what's the point? If you have a huge number of rows, however, then there might be ways to speed it up, especially since two of the CTEs are referenced twice. Let me know if you need me to look at that.

WITH RowIndex_CTE AS		--Extract row index of each row
(
	SELECT       
		rowid
	  , rowindex = left(detaildata, 1)
	  , detaildata
	FROM SomeData1
),
MakeID_CTE AS				--Assign a 'set' ID to each set of rows
(
SELECT
    rowid
  , setid = 
	(
		select max(rowid) 
		from RowIndex_CTE
		where rowid <= ri.rowid
			and rowindex = 1
	)
  , rowindex
  , detaildata
FROM            RowIndex_CTE ri
where rowindex > 0
),
AssignDate_CTE AS			--Assign a date to each row of each set
(
	select
		rowid
	  , setid
	  , rowindex
	  , setdate = 
		(
			select
				try_cast(substring(detaildata, 3, 8) as date)
			from MakeID_CTE
			where setid = mi.setid
				and rowindex = 2
		)
	  , detaildata
	from MakeID_CTE mi
)
select *						--Comment out, and uncomment DELETE below to actually remove the rows
from AssignDate_CTE ad
where setdate < '10/01/2015'	--Comment out to see all rows

--DELETE							--Change to DELETE to remove the selected rows
--from AssignDate_CTE
--where setdate < '10/01/2015'	--Comment out to see all rows (but not when DELETEing!)

;

Open in new window

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 17

Author Comment

by:MIKE
ID: 41794239
MEGAN...!!

GREAT WORK!... This appears to be exactly what I'm needing.

Thanks for your help it is MUCH appreciated!!

Thanks
M
0
 
LVL 17

Author Closing Comment

by:MIKE
ID: 41794241
GREAT WORK Megan!!
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41794408
You are welcome.

Having had breakfast, I am scratching my head over what I wrote for the DELETE clause, but I did test it before posting and it did do what it was supposed to do. But a delete against a CTE? I would normally delete against the base table and join to the CTE.
0
 
LVL 17

Author Comment

by:MIKE
ID: 41794454
Don't worry about that. I fully realize that you don't fully understand the exact requirement. What you provided is exactly what I needed...and I'll make the needed tweaks to make if fit my overall solution.

Thanks!
M
0
 
LVL 13

Expert Comment

by:Megan Brooks
ID: 41794664
Great, that's what I was hoping for.

For queries like this I often write a series of CTEs, expecting that SQL Server will combine them and create a execution pipeline. I find the CTE syntax easier to read than subqueries, and it is rather easy to debug as well. Off the top of my head, without looking, I don't know what it does with the correlated subqueries, though. In my ideal world it would spool some of the extracted data to tempdb and use it for lookups. One can dream.

CROSS APPLY might be useful here, but I didn't think of it at the time and it wouldn't necessarily be more readable or produce a better plan. One way or another the query needs to do correlated subqueries to establish the relationships.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now