• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

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

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
MIKE
Asked:
MIKE
  • 4
  • 4
1 Solution
 
chaauCommented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
 
Megan BrooksSQL Server ConsultantCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Megan BrooksSQL Server ConsultantCommented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
MEGAN...!!

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

Thanks for your help it is MUCH appreciated!!

Thanks
M
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
GREAT WORK Megan!!
0
 
Megan BrooksSQL Server ConsultantCommented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
 
Megan BrooksSQL Server ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now