How to join two CSV files for SSRS Reporting

How to join two CSV files for SSRS Reporting - Part 2

SSRS report with Inner joining two CSV files

   My recent article has covered “How to Use CSV file as DataSource for SSRS reports”, however one of the reader posted a question on that article, how to join two CSV files, i found it interesting hence i came up with this new article.

Joining two CSV files are as similar as joining two SQL tables, but there is a small syntax difference, consider this article as second part of previous article “How to Use CSV file as DataSource for SSRS reports” for better understanding on CSV as data source.

• First we will see the source data, i have created 2 CSV files with sample data of Mutual Funds and Asset Value,  with common ID for Joining 2 data source.
• Fund_groups.csv 

ID,Fund_Family,Fund_Family_Sub_Group,Line_Item,SubScript

1,SBI Blue Chip,SBI Blue Chip G,Received from General Partner,1

2,SBI Blue Chip,SBI Blue Chip N,Withholdings taxes,2

3,SBI Blue Chip,SBI Blue Chip O,Received from Distributed General Partner,3

4,DSP BR Micro Cap,DSP BR Micro Cap G,Received from General Partner,1

5,DSP BR Micro Cap,DSP BR Micro Cap N,Withholdings taxes,2

6,Birla Sun Life,Birla Sun Life G,Received from General Partner,1

7,Birla Sun Life,Birla Sun Life N,Withholdings taxes,2 

• Fund_Assets.csv 

ID,Total_Fund_Asset

1,10000000

2,1500000

3,8000000

4,40000000

5,41000000

6,4000000

7,4500000 

• Next querying those 2 data sources, and using it in SSRS reports, for this we need to use OLEDB Driver to establish connection.  (Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\CSV Source";Extended Properties="text;HDR=YES;FMT=Delimited")

• Write an ANSI SQL to join 2 CSV files, use complete file name as Table, we can use any joins Inner, Left, Right or Full Outer join, below is a sample query.

SELECT   fg.ID, fg.Fund_Family, fg.Fund_Family_Sub_Group, fg.Line_Item, fg.SubScript, 

fa.Total_Fund_Asset  

FROM Fund_groups.csv fg

INNER JOIN Fund_Assets.csv fa ON fg.ID = fa.ID

•      Now we got report data set, use it for report development, we can use any csv file no matter how large it is, joins will work. 
  

  

This is how we can join 2 different csv files using ANSI SQL. please leave your comments, messages, feedback, thank you

Microsoft SQL Server Database and DB log File Shrinking in one shot

Microsoft SQL Server Database and DB log File Shrinking in one shot

Most of the time  our database server use to go out of memory, whenever we restore Prod copy on Dev, then we use to do shrinking of log files and Database, doing this one by one is big task. Then I thought of doing this in one shot, here I am sharing a SQL code for the same.

I have used [sysdatabases] and [master_files] tables to get required metadata, and “while loop” for repeating DBCC Shrink for all databases, we can even do this using Courser but I prefer While loop in simple cases.

----------------------------------------------------------------------------

--#1 Get all Database names, Log file names and metadata by this SQL

----------------------------------------------------------------------------

select db.name as DBName,db.[dbid],df.name as DBFileName,df.type_Desc,df.State_desc

into #DBNames from [master].[sys].[sysdatabases] db inner join

[master].[sys].[master_files] df on df.database_id = db.[dbid]

and db.name not in ('master','tempdb','model','msdb') and df.State_desc='ONLINE'

-- Sample Result

/*

DBName        dbid   DBFileName           type_Desc    State_desc

-----------------------------------------------------------

AccountsDW    5             AccountsDW_Data      ROWS         ONLINE

AccountsDW    5             AccountsDW_log       LOG          ONLINE

SalesMaster   6             SalesMaster_Data ROWS             ONLINE

SalesMaster   6             SalesMaster_Log      LOG          ONLINE

*/

----------------------------------------------------------------------------

--#2 Declare required variables

----------------------------------------------------------------------------

Declare @DBID int =0, @MaxDBID int =0,@MinDBID int =0

Declare @SQL varchar(max)='',@DBName varchar(200),@Filename varchar(100)=''

----------------------------------------------------------------------------

--#3 Take Min Max DB id for further looping logic

----------------------------------------------------------------------------

Select @MinDBID=Min([dbid]),@MaxDBID=Max([dbid]) from #DBNames

-- Setting Starting point from Minimun DBID

Set @DBID = @MinDBID

----------------------------------------------------------------------------

--#4 Take DB name, log file name for shrinking in loop.

----------------------------------------------------------------------------

---- Here i have used 'LOG' in where condition because we need Log file name in ShringFile() function .

---- and Databse name for ShringDatabase() Function.

Select @DBName = DBName, @Filename=DBFileName from #DBNames where [dbid] = @MinDBID and type_Desc = 'LOG'

----------------------------------------------------------------------------

--#5 Loop for all databases

----------------------------------------------------------------------------

while @DBID<=@MaxDBID

begin

  -- Used Dynamic SQL for all databases.

  Set @SQL ='Use '+@DBName+ ' '+Char(10)

  Set @SQL += 'DBCC SHRINKFILE('+@Filename+',5)' +Char(10)

  Set @SQL += 'DBCC SHRINKDATABASE('+@DBName+')'+Char(10)

 

  --#6 Increment DBid for looping over all databases

  Set @DBID = @DBID+1

  Select @DBName = DBName, @Filename=DBFileName from #DBNames where [dbid] = @DBID and type_Desc = 'LOG'

  Print (@SQL)

  Exec (@SQL)

end

----------------------------------------------------------------------------

--#7 Final Output Print, wheich will execute one by one.

/*

Use AccountsDW

DBCC SHRINKFILE(AccountsDW_log,5)

DBCC SHRINKDATABASE(AccountsDW)

Use SalesMaster

DBCC SHRINKFILE(SalesMaster_Log,5)

DBCC SHRINKDATABASE(SalesMaster)

*/

----------------------------------------------------------------------------

Sys table help

https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysdatabases-transact-sql?view=sql-server-2017

Thank you,
please leave your feedback and suggestions, your new ideas could help many more developers. 

Mobile Internet Issue On ASUS Zenfone Max Pro M1, ASUS_X00TD

Mobile Internet Issue On ASUS Zenfone Max Pro M1

  ASUS Zenphone max Pro M1 (ASUS_X00TD), recently i faced data connection issue in my phone 4G Volt, i tried all options, Installing new internet settings, Updating APN

but it did not worked, after doing lot of search and study i fixed this, here is how.

go to your setting -->

1)    Network & Internet --> Mobile Network --> Advanced --> APN --> Here Delete all APN's

2) Then restart phone without APN

3) go to again same APN Setting  Network & Internet --> Mobile Network --> Advanced --> APN --> Options on right top --> Click Reset Default.

after this your internet will start as usual and fast.

Thank you.

   

Get First Business day of Month, in MS SQL Server

Get First Business day of the Month

In this article, we will discuss how to get First day of the month and First Business day of the month (Excludes Saturday and Sunday if it comes on 1^st day of month), SQL server does not have direct function to get all this we need to write some workaround code.

First, get First day of the Month

--This part gets First date of the month.

Declare @YourDate Datetime='2019-1-15', @FirstDateofMonth Datetime

Set @FirstDateofMonth=DATEADD(M, DATEDIFF(M, 0, @YourDate), 0)

Select(@FirstDateofMonth) 'First Date'

Now, get First Business day of the Month

--This part gets First date of the month.

Declare @YourDate Datetime='2019-1-5', @FirstDateofMonth Datetime

Set @FirstDateofMonth=DATEADD(M, DATEDIFF(M, 0, @YourDate), 0)

Select(@FirstDateofMonth) 'First Date'

Select @FirstDateofMonth=

case 

       When DATEPART(DW,@FirstDateofMonth) = 1 Then DATEADd(day,1,@FirstDateofMonth) -- Gets Sunday

       When DATEPART(DW,@FirstDateofMonth) = 7 then DATEADd(day,2,@FirstDateofMonth) -- Gets Saturday

       else @FirstDateofMonth end --'Business Date of month'

       Return @FirstDateofMonth

Let’s write Function to get First Business day of the Month

Create  Function [dbo].[ufnGetFirstBusinessDay](@YourDate Datetime)

Returns Datetime as

Begin

--This part gets First date of the month.

Declare @FirstDateofMonth Datetime

Set @FirstDateofMonth=DATEADD(m, DATEDIFF(m, 0, @YourDate), 0)

--This part gets First business day of the month,

--excludes Saturday and Sunday if it comes on 1st day of month

Select @FirstDateofMonth=

case 

       When DATEPART(DW,@FirstDateofMonth) = 1 Then DATEADd(day,1,@FirstDateofMonth)

       When DATEPART(DW,@FirstDateofMonth) = 7 then DATEADd(day,2,@FirstDateofMonth)

       else @FirstDateofMonth end --'Business Date of month'

       Return @FirstDateofMonth

End

--Select dbo.GetFirstBusinessDay('2019-6-15')