RSS

Monthly Archives: February 2015

Deleting older files after a specified number of days using VB.NET


If You want to Temp or your specific Export file History maintain folder to delete file after some days then this function resolve your problem.

Calling Way:

First Way : 

Dim DeleteInfo As Long()

DeleteInfo = DeleteFilesXDaysOld("c:\temp\", "*.*", 60)

Console.Writeline(DeleteInfo(0) & " file(s) deleted.")
Console.Writeline("Freeing " & DeleteInfo(1) & " bytes.")


Second Way:

Imports System.IO

 Dim DeleteInfo As Long()
 If My.Computer.FileSystem.DirectoryExists(Path.GetTempPath & "\Export\Invoice") = True Then
 DeleteInfo = DeleteFilesXDaysOld(Path.GetTempPath & "\Export\Invoice", "*.*", 2)
 End If

Check Directory Exist or Not in Vb.Net else create

Dim GLFilePath as string = Path.GetTempPath & "\Export\Invoice"

 If My.Computer.FileSystem.DirectoryExists(Path.GetTempPath & "\Export\Invoice") = False Then
 My.Computer.FileSystem.CreateDirectory(Path.GetTempPath & "\Export\Invoice")
 End If

Function Delete Files:

Private Function DeleteFilesXDaysOld( _ ByVal Path As String, _ Optional ByVal Pattern As String = "*.*", _ Optional ByVal Days As Integer = 365) As Long() ' ReturnValues is an array which holds 2 values ' The first being the count of files deleted ' The second being the amount of space freed Dim ReturnValues = New Long() {0,0} Dim File As String Dim FileInfo As System.IO.FileInfo Dim Files() As String = _ System.IO.Directory.GetFiles(Path, Pattern) Try For Each File In Files FileInfo = New System.IO.FileInfo(File) If FileInfo.LastWriteTime < _ Today.AddDays(0 - Days) Then ReturnValues(0) += 1 ReturnValues(1) += FileInfo.Length() FileInfo.Delete() End If Next Return ReturnValues Catch exn As Exception MsgBox(exn.ToString, _ MsgBoxStyle.Critical Or _ MsgBoxStyle.OkOnly Or _ MsgBoxStyle.SystemModal, _ "Error") Return ReturnValues End Try End Function

Advertisements
 
1 Comment

Posted by on February 15, 2015 in Vb.Net

 

Tags: , , , , ,

group by sequence number MySQL


Before you use below sample code i prefer you use this URL code which better for you.

http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

 

SAMPLE DataTable

CREATE TABLE CountryCity
 (
 city VARCHAR(100),
 country VARCHAR(100),
 population int
 );

INSERT INTO CountryCity VALUES ('Lahore', 'Pakistan',8175133); 
 INSERT INTO CountryCity VALUES ('Multan', 'Pakistan',3792621); 
 INSERT INTO CountryCity VALUES ('Liquat Pur', 'Pakistan',2695598); 
 
 INSERT INTO CountryCity VALUES ('Kalkata', 'India',2181000);
 INSERT INTO CountryCity VALUES ('Dehli', 'India',808000);
 INSERT INTO CountryCity VALUES ('Goya', 'India',422000);
 
 INSERT INTO CountryCity VALUES ('Riyadh', 'KSA',7825300);
 INSERT INTO CountryCity VALUES ('Madina', 'KSA',1016800);
 INSERT INTO CountryCity VALUES ('Makkah', 'KSA',770800);

MySQL Query to Get Top 2

To get the 2 largest Country cities for each country, you can use the following query in MySQL:
SELECT city, country, population
FROM
(SELECT city, country, population,
@country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
@current_country := country
FROM CountryCity
ORDER BY country, population DESC
) ranked
WHERE country_rank <= 2;

 

The query returns:

 


city
country population
Kalkata India 2181000
Dehli India 808000
Riyadh KSA 7825300
Madina KSA 1016800
Lahore Pakistan 8175133
Multan Pakistan 3792621

 

How It Works

Explanation of the MySQL query:

  • Session Variables

Currently MySQL does not support ROW_NUMBER() function that can assign a sequence number within a group, but as a workaround we can use MySQL session variables.

These variables do not require declaration, and can be used in a query to do calculations and to store intermediate results.

@current_country := country

This code is executed for each row and stores the value of country column to @current_country variable.

@country_rank := IF(@current_country = country, @country_rank + 1, 1)

In this code, if @current_country is the same we increment rank, otherwise set it to 1. For the first row@current_country is NULL, so rank is also set to 1.

For correct ranking, we need to have ORDER BY country, population DESC

So if we just execute the subquery:

   SELECT city, country, population, 
       @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
       @current_country := country 
  FROM Countrycities
  ORDER BY country, population DESC

We get the list of cities ranked by population within the country:

city country population country_rank current_country
Kalkata India 2181000 1 India
Dehli India 808000 2 India
Goya India 422000 3 India
riyadh KSA 7825300 1 KSA
Madina KSA 1016800 2 KSA
Makkah KSA 770800 3 KSA
Lahore Pakistan 8175133 1 Pakistan
Multan Pakistan 3792621 2 Pakistan
Liquat Pur Pakistan 2695598 3 Pakistan
  • Selecting Range

When we have a rank assigned to each city within its country, we can retrieve the required range:

   -- Get top 2 for each country
   SELECT city, country, population
   FROM (/*subquery above*/) ranked
   WHERE country_rank <= 2;
 
   -- Get the city with 3rd population for each country
   SELECT city, country, population
   FROM (/*subquery above*/) ranked
   WHERE country_rank = 3;

ROW_NUMBER() – Oracle, SQL Server and PostgreSQL

In Oracle, SQL Server and PostgreSQL you can achieve the same functionality using ROW_NUMBER function:

   SELECT city, country, population
   FROM
    (SELECT city, country, population, 
                  ROW_NUMBER() OVER (PARTITION BY country ORDER BY population DESC) as country_rank
      FROM cities) ranked
   WHERE country_rank <= 2;

This query works in Oracle, SQL Server and PostgreSQL without any changes and returns:

city country population
Kalkata India 2181000
Dehli India 808000
Riyadh KSA 7825300
Madina KSA 1016800
Lahore Pakistan 8175133
Multan Pakistan 3792621
 
Leave a comment

Posted by on February 12, 2015 in My SQL

 

Tags: , , ,