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
74 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 25

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 16

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 16

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
 
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 16

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 16

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

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

630 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