In this article, we will learn SQL Query how to extract data on monthly basis from a table or generate a report Month wise and we will implement it with the help of an example for better understanding. We will follow step by step process. Here we will discuss how to extract data on monthly basis from a table or generate a report Month wise with the help of an SQL query using the DATENAME( ) function. This function is a defined function of SQL. It is used to extract a specific part of data. This function extracts the results as a string type value. Syntax: DATENAME( required part of date,date)Here required parameters of the date will be:
For the purpose of implementing the query to find the month-wise reports. We will create a database “SAMPLE” and we will create a table “NEWJOINEE” in the database called “SAMPLE“. We will follow the below steps to implement this Query Step 1: Create DatabaseThe SQL server statement for creating a database called SAMPLE is as follows Query: Create database SAMPLE;Step 2: Use DatabaseSQL statement to switch the database context SAMPLE as follows: Query: Use SAMPLE;Step 3: Creation table in DatabaseFor creation a table in a database. We need to execute a query in Microsoft SQL Server. Like Mysql, Oracle, etc. We will use this query: Syntax: create table table_name( column1 type(size), column2 type(size), . . . columnN type(size) );Query: DECLARE @date DATE; DECLARE @start_date DATE; DECLARE @end_date DATE; DECLARE @loop_date DATE; -- declaring a table variable DECLARE @dates TABLE (date DATE); -- setting the first and the last date in the month given by date SET @date = '2021/08/1'; SET @start_date = DATEFROMPARTS(YEAR(@date ), MONTH(@date ), '01'); SET @end_date = EOMONTH(@date); -- check dates SELECT @date AS cur_date, @start_date AS first_date, @end_date AS last_date; -- populating a table (variable) with all dates in a given month SET @loop_date = @start_date; WHILE @loop_date <= @end_date BEGIN INSERT INTO @dates(date) VALUES (@loop_date); SET @loop_date = DATEADD(DAY, 1, @loop_date); END; CREATE TABLE NEWJOINEE ( EMPNAME VARCHAR(25) NOT NULL, EMPCONTACT BIGINT, DATEOFJOIN DATE NOT NULL, DESIGNATION VARCHAR(20) );Step 4: Insert Data into Table To insert data into a table there is the query we will use here in SQL Server. Syntax: insert into table_name( value1, value2, value3,...valueN);Query: INSERT INTO NEWJOINEE VALUES ('VISHAL',9193458625,'12-JAN-2021','SYSTEM ENGINEER'), ('RAM',7856958725,'22-JAN-2021','MANAGER'), ('VIPIN',91458458625,'25-JAN-2021','SYSTEM ENGINEER'), ('VINOD',9759554664,'28-JAN-2021','MANAGER'), ('AKASH',7500554664,'18-JAN-2021','EXECUTIVE'), ('RAJ',7856958625,'02-FEB-2021','MANAGER'), ('AJAY',9756644159,'20-FEB-2021','SYSTEM ENGINEER'), ('SHYAM',919347625,'12-FEB-2021','EXECUTIVE'), ('VIPIN',91458458625,'25-FEB-2021','SYSTEM ENGINEER'), ('VIJAY',7858458625,'25-FEB-2021','EXECUTIVE'), ('VIKASH',9759554664,'28-FEB-2021','SYSTEM ENGINEER'), ('VIVEK',9193458625,'12-MAR-2021','MANAGER'), ('ANUJ',91458458625,'25-MAR-2021','EXECUTIVE'), ('AKASH',7500554664,'18-MAR-2021','MANAGER'), ('RAKESH',7845758725,'22-MAR-2021','EXECUTIVE');Output: Step 5: Month-wise report query Query: SELECT MAX(DATENAME(MM,DATEOFJOIN)) AS JOININGMONTH, COUNT(1) AS "TOTALEMP. JOIN" FROM NEWJOINEE GROUP BY MONTH(DATEOFJOIN);Output: Query criteria help you zero in on specific items in an Access database. If an item matches all the criteria you enter, it appears in the query results. To add criteria to an Access query, open the query in Design view and identify the fields (columns) you want to specify criteria for. If the field is not in the design grid, double-click the field to add it to the design grid and then enter the criterion in the Criteria row for that field. If you’re not sure how to make this happen, see Introduction to queries. A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value. For example, = "Chicago" is an expression that Access can compare to values in a text field in a query. If the value for that field in a given record is "Chicago", Access includes the record in the query results. Here are some examples of commonly used criteria you can use as a starting point to create your criteria. The examples are grouped by data types. A criterion is similar to a formula — it is a string that may consist of field references, operators, and constants. Query criteria are also referred to as expressions in Access. The following tables shows some sample criteria and explains how they work.
As you can see, criteria can look very different from each other, depending on the data type of the field to which they apply and your specific requirements. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references. This topic lists several commonly used criteria by data type. If the examples given in this topic do not address your specific needs, you might need to write your own criteria. To do that, you must first familiarize yourself with the full list of functions, operators, special characters, and the syntax for expressions referring to fields and literals. Here, you will see where and how you add the criteria. To add a criteria to a query, you must open the query in Design view. You then identify the fields for which you want to specify criteria. If the field is not already in the design grid, you add it by either dragging it from the query design window to the field grid, or by double-clicking the field (Double-clicking the field automatically adds it to the next empty column in the field grid.). Finally, you type the criteria in the Criteria row Criteria that you specify for different fields in the Criteria row are combined by using the AND operator. In other words, the criteria specified in the City and BirthDate fields are interpreted like this: City = "Chicago" AND BirthDate < DateAdd (" yyyy ", -40, Date())
1. The City and BirthDate fields include criteria. 2. Only records where the value of the City field is Chicago will satisfy this criterion. 3. Only records of those who are at least 40 years old will satisfy this criterion. 4. Only records that meet both criteria will be included in the result. What if you want only one of these conditions to be met? In other words, if you have alternate criteria, how do you enter them? If you have alternate criteria, or two sets of independent criteria where it is sufficient to satisfy one set, you use both the Criteria and the or rows in the design grid.
1. The City criterion is specified in the Criteria row. 2. The BirthDate criterion is specified in the or row. Criteria specified in the Criteria and or rows are combined using the OR operator, as shown below: City = "Chicago" OR BirthDate < DateAdd (" yyyy ", -40, Date()) If you need to specify more alternatives, use the rows below the or row. Before you continue with the examples, note the following:
Note: Beginning in Access 2013, Text fields are now named Short Text and Memo fields are now named Long Text. The following examples are for the CountryRegion field in a query that is based on a table that stores contacts information. The criterion is specified in the Criteria row of the field in the design grid.
A criterion that you specify for a Hyperlink field is, by default, applied to the display text portion of the field value. To specify criteria for the destination Uniform Resource Locator (URL) portion of the value, use the HyperlinkPart expression. The syntax for this expression is as follows: HyperlinkPart([Table1].[Field1],1) = "http://www.microsoft.com/", where Table1 is the name of the table containing the hyperlink field, Field1 is the hyperlink field, and http://www.microsoft.com is the URL you want to match.
The following examples are for the UnitPrice field in a query that is based on a table that stores products information. The criterion is specified in the Criteria row of the field in the query design grid.
The following examples are for the OrderDate field in a query based on a table that stores Orders information. The criterion is specified in the Criteria row of the field in the query design grid.
As an example, your Customers table has a Yes/No field named Active, used to indicate whether a customer's account is currently active. The following table shows how values entered in the Criteria row for a Yes/No field are evaluated.
Attachments In the Criteria row, type Is Null to include records that do not contain any attachments. Type Is Not Null to include records that contain attachments. Lookup fields There are two types of Lookup fields: those that look up values in an existing data source (by using a foreign key), and those that are based on a list of values specified when the Lookup field is created. Lookup fields that are based on a list of specified values are of the Text data type, and valid criteria are the same as for other text fields. The criteria you can use in a Lookup field based on values from an existing datasource depend on the data type of the foreign key, rather than the data type of the data being looked up. For example, you may have a Lookup field that displays Employee Name, but uses a foreign key that is of the Number data type. Because the field stores a number instead of text, you use criteria that work for numbers; that is, >2. If you do not know the data type of the foreign key, you can inspect the source table in Design view to determine the data types of the field. To do this:
Multivalued fields Data in a multivalued field are stored as rows in a hidden table that Access creates and populates to represent the field. In query Design view, this is represented in the Field List by using an expandable field. To use criteria for a multivalued field, you supply criteria for a single row of the hidden table. To do this:
Introduction to queries Create a simple select query |