How can I delete a USB’s partition table?

I have a 32Gb USB, and I flashed it with a bootable image. So, that process modified the partition table, and the USB now reports a size of 1Gb. Unfortunately, erasing or reformatting the USB doesn’t fix it. From what I’ve read, I need to completely nuke the partition table. Usually, this would be very easy to do on a Linux machine, but I must do it on a Mac right now. The disk utility doesn’t seem to have an option for this. Is there another tool or set of commands I could use?

r markdown – Hide chunkname in table header from Rmd output

I am running an Rmd script which renders to a word .docx file. I am outputting tables from from unnamed chunks using flextable() and for each table it prints, for example, “(#Table:unnamed-chunk-7)” next to the table header that I have created with set_caption(). I do not want this chunk name text to appear. Does anyone know how I can get rid of it?

data %>% 
  flextable() %>% 
  delete_part(part="header") %>%
  merge_v(j=1, part="body") %>% 
  bold(i=1, bold=T, part="body") %>%
  bg(i = 1,part = "body", bg = "darkgray")%>%
  theme_box() %>%
  #height(i = 7, 4, part = "body")%>%
  flextable::width(j=1:2,3.5)%>%
  set_caption("Table 1")%>%
  fontsize(size = 9, part = "all")

sql server – Create a json based on multiple queries on the same table when join is irrelevant

I need to construct a json based on the results of two different queries on the same table, where a join is irrelevant.

Consider the following example (full demo is here):

CREATE TABLE Trees
(
    (Id) INT,
    (Type) NVARCHAR(100),
    (Height) DECIMAL(2,1)
);

INSERT INTO Trees ((Id), (Type), (Height))
VALUES 
 (1, 'Palm', 5.5), 
 (2, 'Pine', 6.2), 
 (3, 'Apple', 2.5), 
 (4, 'Japanese Cedar', 0.5), 
 (5, 'Spanish Fir', 0.6);

I want to construct the following json:

(
  "highTrees": 
  (
     {
        "id": 1,
        "type": "Palm",
        "height": 5.5
     },
     {
       "id": 1,
        "type": "Pine",
        "height": 6.2
     }
  ),
  "lowTrees": 
  (
     {
        "id": 4,
        "type": "Japanese Cedar",
        "height": 0.5
     },
     {
       "id": 5,
        "type": "Spanish Fir",
        "height": 0.6
     }
  )
)
  

I tried this:

SELECT 
   Id as 'highTrees.id',
   Type as 'highTrees.type',
   Height as 'highTrees.height'
FROM Trees WHERE (Height) > 5
UNION ALL
SELECT 
   Id as 'lowTrees.id',
   Type as 'lowTrees.type',
   Height as 'lowTrees.height'
FROM Trees WHERE (Height) < 1
FOR JSON PATH;

But obviously this isn’t the way to go, since it gives this:

(
  {
    "highTrees": {
      "id": 1,
      "type": "Palm",
      "height": 5.5
    }
  },
  {
    "highTrees": {
      "id": 2,
      "type": "Pine",
      "height": 6.2
    }
  },
  {
    "highTrees": {
      "id": 4,
      "type": "Japanese Cedar",
      "height": 0.5
    }
  },
  {
    "highTrees": {
      "id": 5,
      "type": "Spanish Fir",
      "height": 0.6
    }
  }
)

How can I achieve the desired result?

Execution plan for slow-running query shows table spool operator generating millions of rows

I’m an aspiring Database Administrator and I’ve taken on the monumental task of performance tuning our decade-old sql server. After watching countless Brent Ozar videos, I now feel like I can competently navigate an execution plan.

I’ve been taking a look at the following query to try and improve performance:

WITH FDLOC (ParticipantID, FirstDayAfterLossOfCoverage) AS
 (
  select p.ID, Participants.GetFirstDayAfterLossOfCoverage(p.ID) AS FirstDayAfterLossOfCoverage from dbo.Participants p (nolock)
  inner join Participants.BenefitAssignments pba (nolock) on pba.ParticipantID = p.ID
  inner join dbo.BenefitPlans b (nolock) on b.ID = pba.BenefitPlanID
  where p.Deleted = 0 and 
        p.Archived = 0 and 
     p.TypeID IN (1) and
        pba.DELETED = 0 and
     b.DELETED = 0 and
     b.CarrierID = @CarrierID
 ),
 CoverageEnd (ParticipantID, CoverageEndDate) AS
 (
  select p.ID, (select CoverageEndDate from Participants.CalculateEndOfCoverageDate(p.ID,pqe.EventType)) AS CoverageEndDate 
  from dbo.Participants p (nolock)
  inner join Participants.BenefitAssignments pba (nolock) on pba.ParticipantID = p.ID
  inner join dbo.BenefitPlans b (nolock) on b.ID = pba.BenefitPlanID
  inner join Participants.QBEvents pqe on pqe.ParticipantID = p.PQBID
  where p.Deleted = 0 and 
        p.Archived = 0 and 
     p.TypeID IN (1) and
        pba.DELETED = 0 and
     b.DELETED = 0 and
     b.CarrierID = @CarrierID
 )

 select distinct
  case when @getAllParticipants = 0 then ept.ID else @emptyGuid end ProcessID, 
  p.ID as ParticipantID, p.PqbID, p.FirstName, p.LastName, p.MiddleInit, p.TitleID, p.DateOfBirth, isnull(p.Gender, 'Not Specified') (Gender),
  pqb.SocialSecurityNumber PrimarySSN, p.SocialSecurityNumber, p.RelationshipID, p.StatusID,
  case when p.ID = p.PqbID then 'Y' else 'N' end YesNoCondition, null MaintenanceCode,
  case when p.TypeID = 3 then pc.CoverageStartDate else fdloc.FirstDayAfterLossOfCoverage end FDLOC,
  case when p.TypeID = 3 then '12/31/9999' else eoc.CoverageEndDate end CobraEndDate,
  ph.Number PhoneNumber, pe.Email ParticipantEmail,
  case
   when p.ID = p.PqbID then
    case p.TypeID
     when 3 then 'RT'
     when 1 then
      case when c.ReceiverID in (@customCarrierReceiverID_DELTA, @customCarrierReceiverID_UHC) then 'FT' else 'AC' end
    end
   else null
  end EmpStatusCode,
  case p.TypeID when 3 then 'A' when 1 then 'C' end BenefitStatusCode,
  case 
   when ph.PhoneTypeID = 1 or ph.PhoneTypeID = 3 then 'WP'
   when ph.PhoneTypeID = 2 or ph.PhoneTypeID = 4 then 'HP'
   when ph.PhoneTypeID is null then 'EM'
  end CommNumberQualifier,
   pa.AddressLine1, pa.AddressLine2, pa.City, upper(pa.(State)) (State),
  case 
   when charindex('-', pa.PostalCode) = 0 then pa.PostalCode
   else substring(pa.PostalCode, 0, charindex('-', pa.PostalCode))
  end PostalCode,
  case when pa.Country in ('USA', 'US') then null else pa.Country end Country,
  e.SenderID EmployerSenderID, e.SenderCode EmployerSenderCode, e.Name EmployerName, e.Code EmployerCode, c.EDIPolicyNumber, 
  e.ReferenceID EmployerReferenceID, c.Name CarrierName, c.Code CarrierCode, c.ReceiverID CarrierReceiverID, c.ReceiversCode CarrierReceiverCode, 
  c.TaxIDCode CarrierTaxIDCode, e.TaxIDCode EmployerTaxIDCode, null TerminationDate, 
  case when p.TypeID = 3 then null else qe.EDIID end EventType
 from Participants p (nolock)
  inner join Participants pqb (nolock)
    inner join Employers e (nolock) on pqb.EmployerID = e.ID
   inner join Participants.Continuees pc (nolock)on pqb.ID = pc.ParticipantID
   left join Participants.CurrentEvent pq 
    left join QualifyingEvents qe (nolock) on pq.EventType = qe.ID
   on pqb.ID = pq.ParticipantID
   outer apply
   (
    select top 1 * from Participants.Addresses (nolock)where ParticipantID = pqb.ID and Active = 1 and DELETED = 0 order by UpdatedOn, (Order)
   ) pa
   outer apply
   (
    select top 1 * from Participants.Phones (nolock)where ParticipantID = pqb.ID and Active = 1 and isDeleted = 0 order by UpdatedOn, IsPrimary
   ) ph
   outer apply
   (
    select top 1 * from Participants.Emails (nolock)where ParticipantID = pqb.ID and isPrimary = 1 and isDeleted = 0 order by UpdatedOn, isPrimary
   ) pe
  on p.PqbID = pqb.ID
  inner joi
/*...<TRUNCATED>...*/
select * from (
 select distinct p.ID (ParticipantID), p.PqbID (PQBID), p.StatusID, c.ReceiverID,
  CASE when b.EDIPlanCode is null or b.EDIPlanCode = '' 
  then 
   b.Description 
  else 
   b.EDIPlanCode + 
   case when c.ReceiverID = @customCarrierReceiverID_UHC and p.TypeID = 1 
   then 
    'TY' 
   else 
    ''  
   end
  end
 as BenefitPlanName, 
 case when c.id = @tabenCarrierID_UHC  and c.ReceiverID  = @customCarrierReceiverID_UHC and  pb.EligibilityDate < '4/1/2017' then '4/1/2017' else pb.EligibilityDate end (PlanStartDate), 
 
 case when p.ID = p.PqbID then cl.EDICode else NULL end as CoverageLevelCode,
 isnull(case when p.StatusID not in (5, 13) then (assignEnd).(end)
 else 
  case when  (assignEnd).(end) < (term).EffectiveDate  -- when termed, and the termination occured after the plan was ended, use plan end, else use termination end
  then 
   (assignEnd).(end)
  else
   (term).EffectiveDate
  end
 end, '12/31/9999')
  as PlanEndDate,
  case b.PlanTypeID 
   when 1 then 'HLT'
   when 2 then 'DEN'
   when 3 then 'VIS'
   else 'UDF' -- what are other values?
  end
   as InsuranceLineCode, b.EDIPlanCode, b.ID (BenefitPlanID) , c.IncludeTermedInEDI
     from participants p (nolock)
  inner join edi.processtable ept (nolock) on
  case when @getAllParticipants = 0 then p.PqbID else @emptyGuid end = case when @getAllParticipants = 0 then ept.ParticipantID else @emptyGuid end
  inner join participants.BenefitAssignments pb (nolock)on pb.ParticipantID = p.ID
  inner join Benefits.CoverageLevels cl (nolock)on cl.ID = pb.CoverageLevelID
  inner join dbo.BenefitPlans b (nolock)on pb.BenefitPlanID = b.ID
  inner join dbo.Carriers c (nolock)on c.Id = b.CarrierID and c.id = ept.CarrierID
  cross apply (select max(ISNULL(pbm.enddate,'12/31/9999')) (end) from participants.BenefitAssignments pbm (nolock) where pbm.ParticipantID = p.ID
   and pbm.BenefitPlanID = pb.BenefitPlanID and pbm.CoverageLevelID = pb.CoverageLevelID and pbm.DELETED = 0 and pbm.BeginDate <= isnull(pbm.EndDate, '12/31/9999')) assignEnd
  outer apply (select top 1 pt.Effectivedate from participants.Terminations pt (nolock)where  pt.ParticipantID = p.PqbID and p.StatusID in (5, 13) and pt.ReinstateDate is null Order by pt.EffectiveDate desc) (term)
  where 
 ( (@getAllParticipants = 0 and 
  ept.StatusID is null and ept.RequestID is null)
  or (@getAllParticipants = 1)
   ) 
  and c.id = @carrierid
  and b.DELETED = 0 and b.Active = 1 and cl.Deleted = 0 and pb.DELETED = 0
  and p.DELETED = 0 and isnull(p.Archived, 0) = 0 
   and 
  (
  (p.StatusID IN (4, 8, 12, 18, 19)) --pqb/retiree enrolled/100% and terminated
  OR
  ( p.StatusID IN (5, 7, 13) AND ISNULL(c.IncludeTermedInEDI, 0) = 1) -- include termed when option is set
 )
  and cl.Deleted = 0
  and pb.BeginDate <= isnull(pb.EndDate, '12/31/9999')
  and isnull
  (
  (select top 1 pt.ReasonID from participants.Terminations pt (nolock)where pt.ParticipantID = p.PqbID 
  and p.StatusID in (5, 13) and pt.ReinstateDate is null order by pt.EffectiveDate desc), 
 -1) <> 3 --exclude qbs termed for non-commence
 and b.EDIPlanCode <> 'ignore'
 and not exists (select 1 from participants.Notes pn (nolock)where pn.ParticipantID = p.ID and pn.Note like '%EDI_IGNORE%' and pn.isDeleted = 0)
 
 ) x
 where
 ( 
  --19584
  (x.PlanEndDate >= @today and x.ReceiverID in (@customCarrierReceiverID_UHC,@customCarrierReceiverID_DELTA,@customCarrierReceiverID_EYEMED))
   or 
  (x.ReceiverID not in (@customCarrierReceiverID_UHC,@customCarrierReceiverID_DELTA,@customCarrierReceiverID_EYEMED))
    )

Here's an image of the execution plan

Looking at this execution plan, it’s easy to see that the final sort is taking up the bulk of the cost. My plan of attack is to try and minimize the amount of data being sorted in the sort operator.

You can see where the table spool operator after EDI.ProcessTable is creating 48,245,028 rows, even though the actual index scan only return 65,1087 rows. The operator shows that there’s 741 iterations. That explains why the spool contains so many rows. I don’t think it’s necessary to have this many rows in our temp table, especially when it’s all getting sorted in the final output. The worst part about this query is that sql is sorting 65,000,000 rows and is only spitting out 717 rows.

I think this query is terribly optimized but I’m not sure where to begin. I think a good start would be somehow getting rid of that table spool so that it’s creating a massive list of rows to sort through.

You can’t see it in the picture I’ve attached, but there are multiple table spool operators that are adding millions of rows to a temp table, even though the child operator only spits out 81.

Any helpful tips would be greatly appreciated.

sql – How to search to see if values of a column exist in another table as sub-string in BQ

I am familiar with the CONTAINS_SUBSTR function in BigQuery.
 Here’s an example from the official BigQuery documentation of how it works (with minor modification):



WITH Recipes AS
 (SELECT 'Blueberry pancakes' as Breakfast, 'Egg salad sandwich' as Lunch, 'Potato dumplings' as Dinner UNION ALL
  SELECT 'Potato pancakes', 'Toasted cheese sandwich', 'Beef stroganoff' UNION ALL
  SELECT 'Ham scramble', 'Steak avocado salad', 'Tomato pasta' UNION ALL
  SELECT 'Avocado toast', 'Tomato soup', 'Blueberry salmon' UNION ALL
  SELECT 'Corned beef hash', 'Lentil potato soup', 'Glazed ham')
SELECT * FROM Recipes;

Recipes:

Breakfast Lunch Dinner
Blueberry pancakes Egg salad sandwich Potato dumplings
Potato pancakes Toasted cheese sandwich Beef stroganoff
Ham scramble Steak avocado salad Tomato pasta
Avocado toast Tomato soup Blueberry salmon
Corned beef hash Lentil potato soup Glazed ham
SELECT * FROM Recipes WHERE CONTAINS_SUBSTR((Lunch), 'potato');

Result:

Breakfast Lunch Dinner
Corned beef hash Lentil potato soup Glazed ham

However, instead of a single STRING literal (potato in the above example), I want to be able to use a table with a single column that contains the keywords that I’m looking for:

Keywords AS
(SELECT 'potato' as ingredient UNION ALL
 SELECT 'cheese' UNION ALL
 SELECT 'egg')

Desired output:

Breakfast Lunch Dinner
Corned beef hash Lentil potato soup Glazed ham
Potato pancakes Toasted cheese sandwich Beef stroganoff
Blueberry pancakes Egg salad sandwich Potato dumplings

Is there a way to use CONTAINS_SUBSTR or any other function and pass the ingredient column of Keywords as input to it to get the desired output?


Example of a query that I’m looking for (this does not work):


SELECT * FROM Recipes WHERE CONTAINS_SUBSTR((Lunch), (SELECT ingredient from Keywords));

mysql – Procedure insert into another table using cursor

I’m creating a procedure, using cursor, which checks in different tables if the current quantity of 5 products are below their minimum quantity. If the current quantity is below I create a PURCHASE ORDER (table). I’m having difficulty capturing the information from the different tables in the course to create the PURCHASE ORDER.

CREATE TABLE IF NOT EXISTS `purchase_order`(
    `id_purchase` INT(11) NOT NULL AUTO_INCREMENT,
    `description` VARCHAR(200) NOT NULL,
    `total_consolidated` DECIMAL NOT NULL,
    `was_canceled` SET('Y','N'),
    `branch_number` INT(14) NOT NULL,
    `date_accomplished` DATE NOT NULL,
    PRIMARY KEY(`id_purchase`),
    FOREIGN KEY(`branch_number`)
    REFERENCES `branch` (`branch_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `item_product`(
    `lot_number` INT(11) NOT NULL AUTO_INCREMENT,
    `id_purchase` INT(11) NOT NULL,
    `quantity_requested` INT(11) NOT NULL,
    PRIMARY KEY(`lot_number`),
   FOREIGN KEY(`id_purchase`)
   REFERENCES `purchase_order` (`id_purchase`)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `product`(
    `id_product`INT(11) NOT NULL AUTO_INCREMENT,  
    `minimum_quantity` INT(11) NOT NULL,    
    `lot_number` INT(11) NOT NULL,     
    PRIMARY KEY(`id_product`),
    FOREIGN KEY(`lot_number`)
    REFERENCES `item_product` (`lot_number`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)   
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `stock_product`(
    `id_stock_product`INT(11) NOT NULL AUTO_INCREMENT,    
    `current_quantity`INT(11) NOT NULL,   
    `id_product`INT(11) NOT NULL,    
    PRIMARY KEY(`id_stock_product`),
    FOREIGN KEY(`id_product`)
    REFERENCES `product` (`id_product`)    
    ON UPDATE CASCADE)
ENGINE = InnoDB;

DELIMITER $$
CREATE PROCEDURE sp_generate_purchase_order()
BEGIN
    DECLARE countN INT DEFAULT 0;
    DECLARE minimumQuantity INT DEFAULT 0;
    DECLARE currentQuantity INT DEFAULT 0;
    DECLARE curs CURSOR FOR SELECT  P.minimum_quantity, SP.current_quantity  from produc P inner join stock_product as SP
on P.id_product = SP.id_product;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
    OPEN curs;    
    FETCH curs INTO minimumQuantity, currentQuantity;
    WHILE countN != 5
        FETCH curs INTO minimumQuantity, currentQuantity;
        IF currentQuantity < minimumQuantity
        THEN /*create the purchase order*/;
        END IF;
        set countN = countN + 1;
    END WHILE;
    CLOSE curs;
    
END$$
DELIMITER ;

plotting – How can I label a table of histograms with the names of the data symbols, not the data themselves?

How can I label a table of histograms with the names of the data symbols, not the data themselves?

If I want to make a table of plots with each plot labeled with the name of the function it plots, this is simple:

f[x_] := x; g[x_] := x^2;
Table[Plot[p[x], {x, -1, 1}, PlotLabel -> p], {p, {f, g}}]

However, if I want to do the same for Histogram, I have not figured out how. I have tried various combinations using ToString, Hold, HoldForm, and Unevaluated, as well as ChartLabels. For example,

l = RandomReal[{0, 1}, 20]; m = RandomReal[{0, 1}, 20];
Table[Histogram[p, PlotLabel -> p], {p, {l, m}}]

does not work, as the labels are the values of l and m, not the strings l and m.

The same question applies to the format analogous to

Plot[#[x], {x, -1, 1}, PlotLabel -> #] & /@ {f, g}

which works for Plot but not for Histogram.

Creating Tables with the same table names from other databases doesn’t have any bad effects?

I have a WordPress website and I cloned it to another website(domain) using a plugin called Duplicator. So in the backend, phpMyAdmin, I’ve two different databases but all the tables in both databases have the same names.

enter image description here

I just want to make sure that this does not have any kind of bad effects.

formulas – Google sheets – how can I use functions to resort my table in a way charts can read it well

I’m using app sheets to see what I do in a day. I select the task I am starting on the app and app sheets dumps the data into a table in google sheets.

I have made an example of what this looks like. Sheet1 is what it looks like At the moment and sheet2 is what I would like it to look like (or something else that will help charts read it).

https://docs.google.com/spreadsheets/d/1GF6cjlNMSTJov4G6ZMusXSWS-DMwnK4SgQqNs8d8uws/edit?usp=sharing

I have been pulling my hair out trying to do this so if you know how please tell me.

formulas – Google Sheets – How can i collect and add values related to a value next to it in a table and add them to another table

I’m using app sheets to see what I do in a day. I select the task i am starting on the app and app sheets dumps the data into a table in google sheets.

I have made an example of what this looks like. Sheet1 is what it looks like At the moment and sheet2 is what I would like it to look like.

https://docs.google.com/spreadsheets/d/1GF6cjlNMSTJov4G6ZMusXSWS-DMwnK4SgQqNs8d8uws/edit?usp=sharing

I can get the dates in just fine using the UNIQUE function but I’m struggling with the values.

If you have any questions or answers please tell me as I have been pulling my hair out trying to do this.