Εξαγωγή των άρθρων με βάση την κατηγορία

Ρόλοι και δικαιώματα στο Wordpress. Πως προσθέτουμε ή αφαιρούμε δικαιώματα σε ρόλους του Wordpress. 
SELECT DISTINCT
post_title
, concat('https://www.thewebsite.gr/',post_name)
,(SELECT group_concat(wp_terms.name separator ', ') 
    FROM wp_terms
    INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id
    INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE taxonomy= 'category' and wp_posts.ID = wpr.object_id
) AS "Categories"
FROM wp_posts
WHERE post_type = 'post' 
AND post_status = 'publish' 
ORDER BY
id DESC

Μετατροπή όλων των πινάκων σε InnoDB από MyISAM

Το InnoDB είναι πιο γρήγορο για το wordpress. Εκτός αυτού είναι λιγότερο πιθανό να κρασάρουν πίνακες.

SET @DATABASE_NAME = 'name_of_your_db';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

Για stored procedure:

DROP PROCEDURE IF EXISTS convertToInnodb;
DELIMITER //
CREATE PROCEDURE convertToInnodb()
BEGIN
mainloop: LOOP
  SELECT TABLE_NAME INTO @convertTable FROM information_schema.TABLES
  WHERE `TABLE_SCHEMA` LIKE DATABASE()
  AND `ENGINE` LIKE 'MyISAM' ORDER BY TABLE_NAME LIMIT 1;
  IF @convertTable IS NULL THEN 
    LEAVE mainloop;
  END IF;
  SET @sqltext := CONCAT('ALTER TABLE `', DATABASE(), '`.`', @convertTable, '` ENGINE = INNODB');
  PREPARE convertTables FROM @sqltext;
  EXECUTE convertTables;
  DEALLOCATE PREPARE convertTables;
  SET @convertTable = NULL;
END LOOP mainloop;

END//
DELIMITER ;

CALL convertToInnodb();
DROP PROCEDURE IF EXISTS convertToInnodb;

Αποθήκευση των tabs σε διαφορετικό path με Macro

Microsoft Excel

Οι καρτέλες αποθηκεύονται ως αυτοτελή excel. Οι τιμές των κελιών τους αποθηκεύονται ως values.

 

Option Explicit

Public Sub MISCSV()

Dim wbkExport As Workbook
Dim shtToExport As Worksheet

Set shtToExport = ThisWorkbook.Worksheets("mis")
Set wbkExport = Application.Workbooks.Add
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False
Worksheets("mis").Cells.Copy
Worksheets("mis").Cells.PasteSpecial xlPasteValues
wbkExport.SaveAs Filename:="C:\MIS\mis.xlsx"
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False

Set shtToExport = ThisWorkbook.Worksheets("PORTFOLIO STATISTICS")
Set wbkExport = Application.Workbooks.Add
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False
Worksheets("Portfolio Statistics").Cells.Copy
Worksheets("Portfolio Statistics").Cells.PasteSpecial xlPasteValues
wbkExport.SaveAs Filename:="C:\mis\PORTFOLIO STATISTICS.xlsx"
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False

Set shtToExport = ThisWorkbook.Worksheets("DAILY REVAL")
Set wbkExport = Application.Workbooks.Add
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False
Worksheets("daily reval").Cells.Copy
Worksheets("daily reval").Cells.PasteSpecial xlPasteValues
wbkExport.SaveAs Filename:="C:\mis\DAILY REVAL.xlsx"
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False

Set shtToExport = ThisWorkbook.Worksheets("PORTFOLIO BENCHMARK")
Set wbkExport = Application.Workbooks.Add
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False
Worksheets("PORTFOLIO BENCHMARK").Cells.Copy
Worksheets("PORTFOLIO BENCHMARK").Cells.PasteSpecial xlPasteValues
wbkExport.SaveAs Filename:="C:\mis\PORTFOLIO BENCHMARK.xlsx"
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False

Set shtToExport = ThisWorkbook.Worksheets("SYNOLA")
Set wbkExport = Application.Workbooks.Add
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False
Worksheets("SYNOLA").Cells.Copy
Worksheets("SYNOLA").Cells.PasteSpecial xlPasteValues
wbkExport.SaveAs Filename:="C:\mis\SYNOLA.xlsx"
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False


MsgBox "ALL DONE"
End Sub

Date Dimension Power bi 

Dates = 

VAR CurrentDate = TODAY()
RETURN

ADDCOLUMNS (
CALENDAR( DATE( 1900; 1; 1); DATE( 2022; 12; 31 ) );

"Year"; YEAR([Date]);

"Month in Year"; MONTH([Date]);

"Month"; FORMAT([Date];"mmm");

"Quarter in Year"; QUARTER([Date]);

"Quarter"; CONCATENATE("Q"; QUARTER([Date]) );

"Month & Year Sort"; FORMAT( [Date]; "yyyymm" );

"Month & Year"; FORMAT([Date];"MMM yyyy");

"Month Offset"; DATEDIFF( CurrentDate; [Date]; MONTH );

"Date Offset"; DATEDIFF( CurrentDate; [Date]; DAY )

)


Max Selected Date (in context) = 

MAXX(

KEEPFILTERS(

VALUES( Dates[Date] )

);

CALCULATE(

MIN( Dates[Date] )

)

)


Min Selected Date (in context) = 

MINX(

KEEPFILTERS(

VALUES( Dates[Date] )

);

CALCULATE(

MIN( Dates[Date] )

)

)



BankCountNewCustomers = 
//COUNTROWS(FILTER(Customers, Customers[DateRegistered].[Year] >= [NewRegisteredMeasure])) + 0
var MINDATE = [Min Selected Date (in context)]
VAR MAXDATE  = [Max Selected Date (in context)]
var datasetd = CALCULATETABLE(
values(Customers[gid]); Customers[DateRegistered] >= MINDATE && Customers[DateRegistered] <= MAXDATE) 
return 
CALCULATE([BankCountAllCustomers]; datasetd)

Selected από φίλτρο Power BI 

CALCULATE(
    MIN(TransactionsEBanking[Ημερομηνία]);
    ALLSELECTED(TransactionsEBanking[Ημερομηνία])
    )


Change Date End = 
Var _endDate= Maxx(ALLSELECTED('Date'[Date Filer]),ENDOFMONTH('Date'[Date Filer]))
Var   _start_date= minx(ALLSELECTED('Date'[Date Filer]),'Date'[Date Filer]))


return
_endDate & " " & _start_date

Υπολογισμός χρόνου σε DAX 

--  DATEDIFF computes the delta between two dates, using different units of measure
--  YEAFRAC returns the delta as a fraction (in years) 
EVALUATE
VAR StartDate =  DATE ( 2011, 01, 01 )
VAR EndDate =    DATE ( 2012, 12, 15 )
RETURN
    {
        ( "DATEDIFF Year",     DATEDIFF ( StartDate, EndDate, YEAR ) ),
        ( "DATEDIFF Quarter",  DATEDIFF ( StartDate, EndDate, QUARTER ) ),
        ( "DATEDIFF Month",    DATEDIFF ( StartDate, EndDate, MONTH ) ),
        ( "DATEDIFF Day",      DATEDIFF ( StartDate, EndDate, DAY ) ),
        ( "Subtraction",       INT ( EndDate - StartDate ) ),
        ( "YEARFRAC",          YEARFRAC ( StartDate, EndDate ) )
    }

MeetingTimeMinsHours = 
VAR _all = SUMX( Committees_Meetings; Committees_Meetings[time_end] - Committees_Meetings[time_start] ) * 1
VAR _days  = INT( _all ) 
VAR _time = _all - _days 
RETURN  FORMAT( _time; "hh:mm")