Insert record in a table with only an IDENTITY column? SQL Server

 I was making an image gallery with images only. So I have created a table with a single column named ID with identity. I want to create a row with the auto number and create an image with a created auto.  number.

The problem is How to insert a table with only an IDENTITY column. 

This problem is solved by the query given below.

INSERT dbo.TABLE DEFAULT VALUES;

Next Example

Success Pending order chart

 

Welcome, This post will help you to make a chart for Success Pending order count datewise. This Sales chart shows the business summary of a month date-wise in just a glimpse. It takes a few seconds the get an idea of the whole month's business, It is a better choice to written  business summary. 

Developer Information

Language        : C#

Framework     : MVC ASP.Net 

Database         : SQL Server

Inline              : HTML,CSS,Java script

Chart               : Google Chart

Application     : Show Success-Pending order datewise for a current month

Database Table

 Let's get Start

At the beginning I thought it was a simple task but when I dived into dept, I found it a complicated task because success and pedning order is detected by the status field from the database. 1 indicates success, 0 indicates pending. The main problem was counting separately success,pending on the same date

Google Chart

I have used google chart for render chart. Chart data is three column format given below. First parameter is date,second , third for show chart lines for show pending or success order .

data.addRows([
 [1, 0, 0],
 [2, 2, 0],
 [3, 0, 3],
 [4, 18, 10]
]);

Requirement

As you see in the above chart data format, it is divided into three parts. The main challenge is to get pending and success counts datewise. I will show you the query step by step because to explanation of the entire query will be difficult to understand. Each sub-query will render output for the next query.

1. Get the pending and success order list of the current month datewise. Pending-Success valie save in status filed. 1 for success and 0 for pending.

with str1 as
(

select day(orderdate) as day,case when status=1  then  sum(status) end as success,case when status=0 then count(status) end as pending   from [Order]

group  by (OrderDate),status having  month(OrderDate)=month(getdate())

 ) select * from str1

I have saved query in   CTE to output in str1 variable, which will be used output for next query.

Now we have the list of success and pending order list datewise. We have to combine this in a single row for datewise.

2. Now it is time to sum the pending and success order datewise by combining the above query output to other CTE variable str2

 with str1 as

(

select day(orderdate) as day,case when status=1  then  sum(status) end as success,case when status=0 then count(status) end as pending   from [Order]
group  by (OrderDate),status
having  month(OrderDate)=month(getdate())
 ),
 str2 as
 (

   select day,sum(success) as success,count(pending) as pending from str1 group by day,success,pending
) select * from str2

 

3. We got a list of success-pending order sum in above list. Now, it's time to remove NULL column and merge row into a single row by the query given below.

with str1 as
(
select day(orderdate) as day,case when status=1  then  sum(status) end as success,case when status=0 then count(status) end as pending   from [Order]

group  by (OrderDate),status
 having  month(OrderDate)=month(getdate())
 ),
 str2 as
 (
   select day,sum(success) as success,count(pending) as pending from str1 group by day,success,pending

  )
 select day,max(success) s,max(pending) as p from (
  select day,isnull(success,'') as success,isnull(pending,'') as pending from str2 group by day,success,pending) tbl
  group by day

Next Topic

How to insert into a table with just one IDENTITY column? SQL Server

 I have a table named Slider, Which stores my website's homepage slider image. So I need one column auto-increment identity field for the slider image record and the uploaded image will be renamed as the identity field value.

So I had to insert a row in a single-column auto increment field. I have achieved this by the query given below.

INSERT INTO [dbo].[Slider]  DEFAULT VALUES 

Next Topic

Page wise record in SQL Server 10 records per page

 Get Page wise record in SQL Server 10 records each page

;with pagewise as

(

SELECT        ROW_NUMBER() OVER(ORDER BY id) AS rowno, ID, roww, name, city

FROM            Student ) select * from pagewise where rowno>9 and rowno<21 order by rowno

 

Next Topic

Execute Store procedure with command for SQL server

 Execute Store procedure with the command for SQL server

Syntax : 

exec procedure_name 'arg1','arg2','ar3',...'argN'

Column to row transform in SQL Server

 Q :  In an organization,  staff tasks had been assigned through CRM and task status (New, Complete, Pending) submitted to CRM back. Now Admin want to see the every staff task status New, Compare and pending order with staff name

Table Structure

Process N record from each group | SQL Server

 

ROW_NUMBER() function in SQL Server is very useful special in case when you want to process N records from a specific group. This function has been around since SQL Server 2005 and at its core, provides a way to provide sequential numbering for rows returned by a query.

As well as sequential numbering, ROW_NUMBER() provides a way to partition the results returned so this suited me just fine

Table Structure