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)

 

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 ).

Change the column name or datatype or size in sql server

How to change the column name of the table or datatype of the column or size in sql server


1)
How to change column name of the table in SQL

ALTER TABLE TABLENAME
             ALTER COLUMN [NEWCOLUMNNAME] varchar(200)


Example
ALTER TABLE tblUser
             ALTER COLUMN [Name] varchar(200)


2)
How to change datatype of the column in SQL

ALTER TABLE tblUser
             ALTER COLUMN [UserName] varchar(500)


3)
How to change size of the column in SQL

ALTER TABLE tblUser
             ALTER COLUMN [UserName] varchar(500)