sql query to get daily count

Introduction:

Here I will explain how to create SQL query to get daily count like daily Users, daily visitors, daily order or get count per day than use to SQL query. if you want to create a query to get count like daily order, daily visitors or get count per day something that time use following SQL query to get data as per your requirement.

So, Now I explaining how to create SQL query to get daily count like daily Users, daily visitors, daily order SQL server

Description: 

In previously post I explained to  
Three tier architecture in asp.net
Remove .aspx from url in asp.net
Temp table in sql
Change the column name or datatype or size in sql server
Stored procedure with paging sql server
Calling web service without adding web reference 





SQL query to get daily countSQL query for daily countsSQL query to get daily data
Calculate Number of Orders per Day
Calculate Number of visitors per Day
How to count number of records per day
Get daily sales order countsSQL query get count per day
SQL query to get how many user register daily on my website
SQL query to get how many order daily on my website
SQL query to get how many user visit my website daily

Example: 

Below SQL query will get result to how many user register daily on my website

SELECT CONVERT(DATE, CreatedDate) as AllDates, Count(*) as DailyUserRegisterd

FROM tblUser

GROUP BY CONVERT(DATE, CreatedDate)

ORDER BY AllDates DESC

Below SQL query will get result to how many order daily on my website

SELECT CONVERT(DATE, CreatedDate) as AllDates, Count(*) as DailyOrder

FROM tblOrder

GROUP BY CONVERT(DATE, CreatedDate)

ORDER BY AllDates DESC

Below SQL query will get result to how many user visit my website daily

SELECT CONVERT(DATE, CreatedDate) as AllDates, Count(*) as DailyVisitors

FROM tblVisitor

GROUP BY CONVERT(DATE, CreatedDate)

ORDER BY AllDates DESC

(In above SQL query change table name and field name as per your table name)

 

stored procedure with paging sql server

Introduction:

Here I will explain how to create stored procedure with paging in SQL server. In the database thousand of records and we fetch some record from database record that time required to create stored procedure with paging in SQL server. If we want to get record from table for paging then required to pass Page index and page site as input parameter in stored procedure.

So, Now I explaining how to create stored procedure with paging SQL server

Description: 

In previously post I explained to  
Three tier architecture in asp.net
Remove .aspx from url in asp.net
Temp table in sql
Change the column name or datatype or size in sql server
Connectionstring in asp.net
Calling web service without adding web reference 



stored procedure with paging SQL server
custom paging stored procedure SQL server 2008
implement paging in SQL stored procedure
implementing pagination in stored procedure
paging in stored procedure SQL server 2012
stored procedure with sorting, paging, and filteringSQL stored procedure paging row_number
custom paging in gridview using stored procedure
custom paging in SQL server 2008

how to create stored procedure with paging and sorting SQL server

 Example:

CREATE PROCEDURE GetUserList

(         

  @SortOrder VARCHAR(300)=NULL,     

  @PageIndex INT=NULL,                 

  @PageSize INT=NULL        

)         

AS         

BEGIN         

DECLARE @SQLQuery NVARCHAR(6000)         

IF (@SortOrder='' OR @SortOrder IS NULL)       

       SET @SortOrder='FirstName asc';

      

SET @SQLQuery = 'WITH PAGED AS

                           (SELECT

                           row_number() OVER( ORDER BY '+@SortOrder+') AS RowNumber ,

                           ID,

                           FirstName,

                           LastName

                           FROM tblUserDetails U

                           WHERE IsActive=1'

SET @SQLQuery = @SQLQuery + '

                           SELECT *, (SELECT COUNT(*) FROM PAGED) AS TotalRecordCount 

                           FROM PAGED               

                           WHERE 

                           PAGED.RowNumber BETWEEN (' + CONVERT(NVARCHAR(10), @PageIndex) + ' - 1) *

                           ' + CONVERT(NVARCHAR(10), @PageSize) + ' + 1 AND

                           ' + CONVERT(NVARCHAR(10), @PageIndex) + ' *

                           ' + CONVERT(NVARCHAR(10), @PageSize) + ' '                    

        

PRINT @SQLQuery     

EXECUTE(@SQLQuery)     

END

Now execute above stored procedure oin your SQL to create SP with paging and sorting in SQL server (change field name and table name which you want to paging).

And now execute this command 

EXEC GetUserList '',1,5

EXEC GetUserList '',10,10

EXEC GetUserList 'LastName DESC',20,5

 
Above command you will get result like 
first 5 records for first page
and second command will get 10 records of 10 th page
and so on.

You can use this stored procedure to bind gridview with custom paging. 

 

temp table in sql

Introduction:

Here I will explain how to use temp table in SQL. Sometimes required to create and save temporary data for some calculation in SQL server that time use to temp table in SQL.

So, Now I explaining how to use temporary table in SQL. 


Description: 

In previously post I explained to  
Three tier architecture in asp.net
Remove .aspx from url in asp.net
file type validation in jquery
Change the column name or datatype or size in sql server
How to set or get textbox value using Javascript or Jquery
Calling web service without adding web reference  






And now here I explain one by one operation and use of temp table in SQL.

Declare temp table in SQL
Insert record in temp table
Insert record from existing table in temp table
insert into temp table in SQLselect record from temp table
Fetch one by one record from temp able
Delete record from temp table 

temporary tables in sql


Example:

Declare temp table in SQL

DECLARE @tempTable TABLE (

                                  ID INT,

                                  FirstName Varchar(250),

                                  LastName Varchar(250)

                                  )

 

Insert record in temp table

INSERT INTO @tempTable  Values (101,‘Dhruvik’,’NK’)

Insert record from existing table in temp table

       INSERT INTO @tempTable

                     SELECT top 10 ID, FirstName,LastName

                                  FROM tblUserDetails

 

select record from temp table

SELECT * FROM @tempTable

Fetch one by one record from temp able

WHILE EXISTS (SELECT 1 FROM @tempTable)

       BEGIN

              SELECT TOP 1 @ID = ID,

                                  @FirstName = FirstName,

                                  @LastName = LastName

              FROM @tempTable

              DELETE FROM @tempTable WHERE ID=@ID

      

       END

 

Delete record from temp table 

DELETE FROM @tempTable WHERE ID=101

It means we can execute same query as we executing on physically table (like CREATE TABLE ).

calling web service without adding web reference

Introduction:

Here I will explain how to calling web service without adding web reference using C# code using. Sometimes required to call particular web service without adding web reference at that time we need to call that web service using WebRequest. Once we created and deployed web service that time client required to call webservice so how to do this.

So, Now I explaining how to call web service without adding web reference.


Description: 

In previously post I explained to  
file type validation in jquery
Change the column name or datatype or size in sql server
How to set or get textbox value using Javascript or Jquery
Server does not support secure connections  
Three tier architecture in asp.net
Remove .aspx from url in asp.net

And now here I explain to how to call web service without adding web reference using C# code.

Now copy the following code in your page.

this is the method in MyWebService.asmx wants to call without adding web reference
 

    [WebMethod]

    public string MyDemoWebService(string data1, string data2)

    {

       // Your code here

       return "";

    }

 Now copy the following code in code behind where you want to call web service.

private string CallWebService(string data1, string data2)

    {

    string url = "http://www.YOURLIVESITEURL/MyWebService.asmx/MyDemoWebService";

        if (Request.IsLocal)

        {

            url = "http://localhost:8945/MyWebService.asmx/MyDemoWebService";

        }

        string result = "";

        string strPost = "data1=" + data1+ "&data2=" + data2;

        StreamWriter myWriter = null;

        HttpWebRequest objRequest = (HttpWebRequest)WebRequest.Create(url);

        objRequest.Method = "POST";

        objRequest.Timeout = 200000; // this should be greater than IIS / web server's timeout

        objRequest.ContentLength = strPost.Length;

        objRequest.ContentType = "application/x-www-form-urlencoded";

        objRequest.KeepAlive = false;

        try

        {

            myWriter = new StreamWriter(objRequest.GetRequestStream());

            myWriter.Write(strPost);

        }

        catch (Exception e)

        {

            throw new Exception(e.ToString());

        }

        finally

        {

            myWriter.Close();

        }

        HttpWebResponse objResponse = (HttpWebResponse)objRequest.GetResponse();

        using (StreamReader sr = new StreamReader(objResponse.GetResponseStream)))

        {

            result = sr.ReadToEnd();

            sr.Close();

        }

        return result;

    }

 

  Now call above function CallWebService in your button click event with given parameter and your web service is called without adding web reference. you will get response in result string variavle.

Now run your project and check.