background preloader

Relational database

Facebook Twitter

Computing First and Last Day of Current and Previous Month (SQL Server) - CodeProject. Download source code - 902 B Introduction This tip is about getting first and last day of current and previous month, a highly desired requirement in many database and reporting solutions.

Computing First and Last Day of Current and Previous Month (SQL Server) - CodeProject

There are many examples available but it is always good to explore them further. SQL2008R2 and previous versions require work around to calculate first and last day of the month. It is very important to keep a consistent approach to calculate the first and last day of the current and previous month. Despite the fact that SQL2012 and upward versions provide built in EOMONTH() function to calculate first day and last day of month, it is worth considering to understand how to get there without the need to use EOMONTH() because the code can be modified further for related computations. Logic Behind Code If you know the logic behind the code, it is relatively easier to understand and write the code.

Get First Day of Current Month Subtract all days of the current month Add one day to get first day Coding Example. Handling NULL in T-SQL - CodeProject. Table of Contents Foreword Displaying error message is always better than generating wrong output.

Handling NULL in T-SQL - CodeProject

When you deal with NULL, there might be a chance of getting output even though the code has some logical errors. It plays a vital role to maintain the quality and accuracy on database output. Five Common SQL Mistakes. As you learn SQL, watch out for these common coding mistakes You’ve written some SQL code and you’re ready to query your database.

Five Common SQL Mistakes

You input the code and …. no data is returned. Instead, you get an error message. Don’t despair! Using Conditional Logic in SQL with CASE Expression - CodeProject. The main purpose of a CASE expression is to return a value based on one or more conditional tests.

Using Conditional Logic in SQL with CASE Expression - CodeProject

CASE expressions can be used anywhere in a SQL statement an expression is allowed. Though a truly an expression, some people refer to them as “CASE statements.” This most likely stems from that use of the phrase in programming languages. The CASE expression is extremely versatile. How to manipulate hierarchical information in flat relational database tables (optionally industrial strength) - CodeProject. Warning Just thought about looking into this algorithm I invented back in 2002.

How to manipulate hierarchical information in flat relational database tables (optionally industrial strength) - CodeProject

Its 29/Nov/2016 when I am thinking about this. Upon cursory, couple of minutes, examination of the article and the algorithm it has serious flaws. I will put up a new algorithm that serves the same purpose in another article. This article will remain up to keep a history of the gensis of the idea for this algorithm I had. Monitoring SQL Server Database. What is Monitoring?

Monitoring SQL Server Database

While working or managing a database, we may need various types of information regarding the database such as settings like owner's name, file names, backup schedules and status of the database, etc. So, we can say, monitoring database is checking or verifying various properties like settings and status of the database. Finding Items in a Collection that Match one of... Introduction Recently, I have answered a couple of Quick Answers where the user wanted a list of items that match a condition from another collection (similar to SQL's IN operator.

Finding Items in a Collection that Match one of...

The trick to solving this is reversing the order of the operands in the Where Linq extension from the norm. Using the Code The normal structue of a Where predicate follows the pattern: Build Your Own Movie Database with SQL Server. Introduction In the last month, I have been dealing with the problem of calling a Web Service from SQL Server.

Build Your Own Movie Database with SQL Server

Why would I? Fairness, mainly. When all your applications are passing through the same Web Service that manages concurrency and audit, if a stored procedure writes directly onto the database, it is not really fair! Background SQL Server gives a big help with that, with the OLE Automation processes. How to Format Repeating Values in SQL Result Set. Download source code - 514 B Introduction Overall, formatting the output data from the database server should never be done in SQL.

How to Format Repeating Values in SQL Result Set

There are a lot of better tools and languages to do the formatting. DROP IF EXISTS in SQL Server 2016 (DIE) Prior to SQL Server 2016, when we need to drop a SQL Object, it's the best practice to check whether the respective object exists or not.

DROP IF EXISTS in SQL Server 2016 (DIE)

Otherwise, the operation will return in an error. Hide Copy Code DROP TABLE [SomeTable] If the object is not found, it will return the following error: Msg 3701, Level 11, State 5, Line 11 Cannot drop the table 'SomeTable', because it does not exist or you do not have permission. Hence, we need to change the syntax as: IF EXISTS(SELECT 'x' FROM sys.objects AS O WHERE = 'SomeTable' AND O. Masking sensitive data with SQL Server 2016. Introduction At the time of writing this article, it’s been more than 2 months since the most secure and powerful SQL Server yet released for public. It has many features which you can use to make your data and application more secure, more maintained and easily retrieved as per your requirements. It has introduced both client side security such as Always Encrypted as well as server side security such as Data masking etc.

SQL Table Hierarchy. Download source - 2 KB Introduction To get an idea about a new database, or to insert records into a database, or to delete records from a database, it is necessary to know the relationships between the tables. An SQL script is provided, which retrieves and shows in an intuitive way the table hierarchy of a database. 50 Important Queries In SQL Server. In this article I will explain some general purpose queries. I think each developer should have knowledge of these queries. These queries are not related to any specific topic of SQL. But knowledge of such queries can solve some complex tasks and may be used in many scenarios, so I decided to write an article on these queries. Query 1: Retrieve List of All Database. SQL Server Stored Procedure explained with Examples. 1. Introduction A "Stored Procedure" in Sqlserver is an executable program unit that can be stored in the database as objects.

These stored procedures can be executed from client application programs to get good performance. The stored procedure can be called from other scripting or even from some other stored procedures. The communication between SqlServer Stored procedure and client application happens through parameters and return values. Client applications optionally sends data to the stored procedure and stored procedures perform processing based on the data sent by the client application. From SQL-Server to Web Service.

Introduction One of my daily tasks is to integrate SAP's Business One ERP inside our customer's IT system. Integration is an important part of the job, since the companies for which we implement the ERP have more often than never, other systems to do complementary tasks and all these systems have to communicate together. For one of the last projects I worked on, the need was to "deeply" integrate the ERP in the IT environment of the customer. For reasons which belong to this customer, this bi-directional integration (send modification done in the ERP, integrate modifications done in other systems) was done thru web services calls...

So it means SOAP calls with xml data transfers. Easily said not so easy to implement (at least the first time. Lead and Lag Functions in SQL Server. How to import data into MS SQL server from CSV and XML. SQL Server Structure Error Handling. Convert SQL Server database to PostgreSQL. A simple and another way to find rows on n’th maximum number from a table. A highly experienced leader with successful track record of software development, product innovations, brand management and corporate communication etc.

SQL Server Table and Column Naming Conventions. Simple Way To Use Pivot In SQL Query. SQL Server export to CSV. Using Conditional Logic in SQL with CASE Expression. The main purpose of a CASE expression is to return a value based on one or more conditional tests. Get monthly attendance report by Store Procedure in Sql. Introduction. JSON for SQL Server. Part 1. A Case Study on How to use Joins, Subqueries, and Unions to Combine Data in SQL. Renaming a Database in Mysql. Present and Future of DBMSs.


How to Convert an SQL Server Data into JSON Object. E-Surfer's Weblog: Thinking,feeling in JAVA, J2EE, XML, Web Services development. Using a Database With Your Android* App. SQL Server 2012 Metadata. A generic Collection to DataTable Mapper. SQL Server Dapper. Graph Database. Data and Goliath: The Hidden Battles to Collect Your Data and Control Your World: Bruce Schneier: 9780393244816: Books. Everything about SQL- Get Started. Using DataSets to work with (relational) in-memory data. Using C# to connect to and query from a SQL database. Table Generation Finder For Rotational Database In SQL Server. Open source solutions, PostgreSQL, Nginx, MongoDB, cloud database. Order by in SQL by Multiple Drop Down List in One Single Logic.

Opencsv - Frequently Asked Questions. XML Database and XQuery to the Rescue. Mapkon/oc-conversion-tools. Printing list of ODBC data sources with pyodbc module. Data with Structure Migration from MySQL to SQL Server 2012. Algorithms, 4th Edition by Robert Sedgewick and Kevin Wayne. Caribbean Open DRI Community: Setting up your o... German Association for Medical Informatics, Biometry and Epidemiology (GMDS)

Back Issues Methods of Information in Medicine. Technical Symposium on Computer Science Education. Big data. HEALTH DATA COLLECTION. Mobile. Designing robust web API. API Design. Web API Design Cookbook. How to design an API: best practises, concepts, technical aspects. TOOLS. APIs.


Comparing STRIDE-RDM, REDCap and Medrio - Services - SCCI - Stanford Medicine. System Architecture. MTD Data Capture - Data Capture. Electronic Data Capture Software Features. Project Proposal - Aneesha Arora. International Journal of Medical Informatics. Accessing MYSQL Database Using Java in Eclipse IDE. Basic SQL Queries for Beginners. Aggregate functions - SQL Server: How to handle divide by 0 cases in query.

Binary Replication Tutorial - PostgreSQL wiki. Rubyrep: Replicating two databases. POSTGRESQL REPLICATION USING RUBYREP by Carmen Cuacos on Prezi. PostgreSQL Replication Tools. PL/pgSQL (en) – PostgreSQL. Index. Learn NoSQL Database Design From Scratch by Eduonix Learning Solutions. PostgreSQL. Conditional Statements in PHP code Between HTML Code. Mysql - How to assign data only to users who have a specific role? File:RBAC.jpg. Database - How to design Users, Roles and permission schema? Relational Database Design - (Third Edition) - ScienceDirect.

Database - What is the difference between 3NF and BCNF? PHP: Securing database connection credentials. Restrict Access to Tables and Fields. Nbviewer.ipython. Chapter%209%20-%20Loading%20data%20from%20SQL%20databases. Creating and using Stata plugins. FAQ: Using a plugin to connect to a database. Free CSS Templates - Download free CSS templates. Gehrke. Sql2alg_eng. SQL Relational Algebra Examples. Relational Algebra Translator. 1NF, 2NF, 3NF and BCNF in Database Normalization. 1NF, 2NF, 3NF and BCNF in Database Normalization.