Removing redundant information

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  August 28, 2011 at 5:23 pm

  #1374350

  Isn't there also supposed to be some "deleted" data to go along with that "added" data?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions

• doug 40899

  SSCommitted

  Points: 1656

  More actions

  August 29, 2011 at 10:41 am

  #1374575

  jeff,

  Sorry. Here is the delete data:

  --===== If the test table already exists, drop it

  IF OBJECT_ID('TempDB..#moncalldelete','U') IS NOT NULL

  DROP TABLE #moncalldelete

  --===== Create the test table with

  CREATE TABLE #moncalldelete

  (

  FIRSTLISTING NVARCHAR,

  StartOncallDate INT,

  StartOncallTime INT,

  Duration DECIMAL,

  Schedname NVARCHAR,

  AddTime INT,

  AddDate INT

  )

  --===== All Inserts into the IDENTITY column

  SET IDENTITY_INSERT #moncalldelete ON

  --===== Insert the test data into the test table

  INSERT INTO #mytable

  (ID, DateValue, Value, YearValue, Monthvalue)

  SELECT 'BUTLER','38490','1020','840','BITTAR&ESKEW','1150','38481' UNION ALL

  SELECT 'CHOUTEAU, MICHELLE','38498','1020','900','ZZOB02','250','38491' UNION ALL

  SELECT 'WINN, MELISSA','38501','1020','900','ZZOB02','252','38491' UNION ALL

  SELECT '**FOR EMERGENCY ROOM CALLS GO TO ACCT #','38487','420','6360','PLASTICS','381','38478' UNION ALL

  SELECT 'PIRWITZ, MARK','38512','420','1440','AUSTINCARDIO','870','38468' UNION ALL

  SELECT 'C-1 DOCTOR - KLEPPER, MARK','38501','480','10080','PCCCA','557','38468' UNION ALL

  SELECT 'C-2 DOCTOR - CLARK, MARK','38501','480','10080','PCCCA','601','38468' UNION ALL

  SELECT 'C-2 ONCALL - ZIEBERT, CAROL','38557','480','10080','PCCCA','577','38468' UNION ALL

  SELECT 'C-2 ONCALL - ZIEBERT, CAROL','38564','480','10080','PCCCA','577','38468' UNION ALL

  SELECT 'C-2 ONCALL - ZIEBERT, CAROL','38571','480','10080','PCCCA','577','38468' UNION ALL

  SELECT 'C-2 ONCALL - ZIEBERT, CAROL','38578','480','10080','PCCCA','577','38468' UNION ALL

  SELECT 'C-2 ONCALL - ZIEBERT, CAROL','38585','480','10080','PCCCA','577','38468' UNION ALL

  SELECT 'C-2 ONCALL - ZIEBERT, CAROL','38592','480','10080','PCCCA','577','38468' UNION ALL

  SELECT 'C-2 ONCALL - ZIEBERT, CAROL','38503','480','1440','PCCCA','506','38475' UNION ALL

  SELECT 'C-1 ONCALL - PERRETT, KENNETH','38502','480','1440','PCCCA','559','38475' UNION ALL

  SELECT 'C-2 ONCALL - ZIEBERT, CAROL','38510','480','1440','PCCCA','506','38475' UNION ALL

  SELECT 'C-2 ONCALL - ZIEBERT, CAROL','38524','480','1440','PCCCA','506','38475' UNION ALL

  SELECT 'OWN TIL OUT','38524','480','2100','PCCCA','420','38491' UNION ALL

  SELECT 'OWN TIL OUT','38523','480','2100','PCCCA','420','38491' UNION ALL

  SELECT 'C-2 ONCALL - ZIEBERT, CAROL','38531','480','1440','PCCCA','506','38475' UNION ALL

  SELECT 'OWN TIL OUT','38530','480','2100','PCCCA','420','38491' UNION ALL

  SELECT 'OWN TIL OUT','38531','480','2100','PCCCA','420','38491' UNION ALL

  SELECT 'OWN TIL OUT','38503','480','2100','PCCCA','420','38491' UNION ALL

  SELECT 'OWN TIL OUT','38504','480','2100','PCCCA','420','38491' UNION ALL

  SELECT 'OWN TIL OUT','38505','480','2100','PCCCA','420','38491' UNION ALL

  SELECT 'OWN TIL OUT','38508','480','2100','PCCCA','420','38491' UNION ALL

  SELECT 'OWN TIL OUT','38509','480','2100','PCCCA','420','38491' UNION ALL

  SELECT 'OWN TIL OUT','38515','480','2100','PCCCA','420','38491' UNION ALL

  SELECT 'OWN TIL OUT','38516','480','2100','PCCCA','420','38491'

  --===== Set the identity insert back to normal

  SET IDENTITY_INSERT #moncalldelete OFF

• doug 40899

  SSCommitted

  Points: 1656

  More actions

  September 2, 2011 at 12:05 pm

  #1376615

  Jeff, (or anyone)

  Any word on this query?

  here's what I have now:

  SELECT FirstListing,OnCallStart,OnCallEnd

  FROM

  (

  SELECT

  moncallAdd.FirstListing,

  DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,

  DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,

  DATEADD(MINUTE, mOnCallAdd.duration,

  DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,

  DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,

  'Added' as Activity

  FROM

  mdr.dbo.mOnCallAdd

  WHERE DATEADD(MINUTE, mOnCalladd.StartOnCallTime,

  DATEADD(DAY, mOnCalladd.StartOnCallDate, '12/31/1899')) < GETDATE()

  AND

  DATEADD(MINUTE, mOnCalladd.duration,

  DATEADD(MINUTE, mOnCalladd.StartOnCallTime,

  DATEADD(DAY, mOnCalladd.StartOnCallDate, '12/31/1899'))) > GETDATE() and

  mOnCallAdd.SchedName = 'capital neph'

  UNION

  SELECT

  moncallDelete.FirstListing,

  DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,

  DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,

  DATEADD(MINUTE, mOnCallDelete.duration,

  DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,

  DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,

  'Deleted' as Activity

  FROM

  mdr.dbo.mOnCallDelete

  WHERE DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,

  DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) < GETDATE()

  AND

  DATEADD(MINUTE, mOnCallDelete.duration,

  DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,

  DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) > GETDATE() and

  mOnCallDelete.SchedName ='capital neph'

  )t

  GROUP BY FirstListing,OnCallStart,OnCallEnd

  HAVING SUM(CASE WHEN Activity='Added' THEN 1 ELSE 0 END) >0

  AND SUM(CASE WHEN Activity='Deleted' THEN 1 ELSE 0 END) =0

  and when I run that .. I get the following dataset:

  HEART HOSP - MOORE 2011-09-01 07:00:00.0002011-09-01 18:00:00.000

  NAMC - LYSON 2011-09-01 07:00:00.0002011-09-01 18:00:00.000

  SAMC, WESTLAKE, SETON SW - SIMPSON 2011-09-01 07:00:00.0002011-09-01 18:00:00.000

  RRMC/SUMMIT/RELIANT/GT-LYSON 2011-09-01 07:00:00.0002011-09-01 18:00:00.000

  SETON & CORNERST MAIN- MOORE 2011-09-01 07:00:00.0002011-09-01 18:00:00.000

  SETON HAYS-KYLE - PEREZ 2011-09-01 07:00:00.0002011-09-01 18:00:00.000

  ST DAVIDS - ROSEN 2011-09-01 13:00:00.0002011-09-01 18:00:00.000

  and when I run this query:

  SELECT

  moncallAdd.FirstListing,

  (Dateadd(MINUTE, moncalladd.addtime,

  DateAdd(Day,moncalladd.adddate,'12/31/1899'))) as AddStart,

  DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,

  DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,

  DATEADD(MINUTE, mOnCallAdd.duration,

  DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,

  DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,

  'Added' as Activity

  FROM

  mdr.dbo.mOnCallAdd

  WHERE DATEADD(MINUTE, mOnCalladd.StartOnCallTime,

  DATEADD(DAY, mOnCalladd.StartOnCallDate, '12/31/1899')) < GETDATE()

  AND

  DATEADD(MINUTE, mOnCalladd.duration,

  DATEADD(MINUTE, mOnCalladd.StartOnCallTime,

  DATEADD(DAY, mOnCalladd.StartOnCallDate, '12/31/1899'))) > GETDATE() and

  mOnCallAdd.SchedName = 'capital neph'

  UNION

  SELECT

  moncallDelete.FirstListing,

  (Dateadd(MINUTE, moncalldelete.addtime,

  DateAdd(Day,moncalldelete.adddate,'12/31/1899'))) as AddStart,

  DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,

  DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) AS OnCallStart,

  DATEADD(MINUTE, mOnCallDelete.duration,

  DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,

  DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) AS OnCallEnd,

  'Deleted' as Activity

  FROM

  mdr.dbo.mOnCallDelete

  WHERE DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,

  DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899')) < GETDATE()

  AND

  DATEADD(MINUTE, mOnCallDelete.duration,

  DATEADD(MINUTE, mOnCallDelete.StartOnCallTime,

  DATEADD(DAY, mOnCallDelete.StartOnCallDate, '12/31/1899'))) > GETDATE() and

  mOnCallDelete.SchedName ='capital neph'

  and after I remove the duplicates and the deletes from my table, I am left with this dataset:

  BRACK & HEALTH S. - ROSEN 2011-07-22 10:22:00.0002011-09-01 13:00:00.0002011-09-01 18:00:00.000Added

  CALL THERESA AT 320-0963 BEFORE CHANGING 2011-07-22 10:01:00.0002011-09-01 08:00:00.0002011-09-01 17:00:00.000Added

  HEART HOSP - MOORE 2011-07-19 11:26:00.0002011-09-01 07:00:00.0002011-09-01 18:00:00.000Added

  RRMC/SUMMIT/RELIANT/GT-LYSON 2011-07-22 10:25:00.0002011-09-01 07:00:00.0002011-09-01 18:00:00.000Added

  SETON HAYS-KYLE - PEREZ 2011-07-19 11:38:00.0002011-09-01 07:00:00.0002011-09-01 18:00:00.000Added

  NAMC - LYSON 2011-09-01 07:48:00.0002011-09-01 07:00:00.0002011-09-01 18:00:00.000Added

  SAMC, WESTLAKE, SETON SW - SIMPSON 2011-07-22 10:26:00.0002011-09-01 07:00:00.0002011-09-01 18:00:00.000Added

  SETON & CORNERST MAIN- MOORE 2011-07-19 09:46:00.0002011-09-01 07:00:00.0002011-09-01 18:00:00.000Added

  ST DAVIDS - ROSEN 2011-07-22 10:24:00.0002011-09-01 13:00:00.0002011-09-01 18:00:00.000Added

  The second set of data has the "complete" set of data but that's been filtered.You can see the difference between the two queries that I ran so the second query gives me ALL data, both adds and deletes. The other query only gives me the "adds" that don't have a matching delete, but obviously it's not catching all of that information. As you can see, the second dataset's first two entries aren't in the first dataset.

  Can anyone help me figure out why?