SQL Query For Chart Diagram

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have a Following Output but not What i desire For... please help me to get desire output

With Following Query

SQL

SELECT SUM(Column Name) AS Total ,Status
DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate))
DeleiveryDate , Total
FROM Abc
WHERE Column Name = Fieldname
AND (DelivaryDate BETWEEN @FromDate AND @ToDate)
GROUP BY DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate)) , Status
ORDER BY DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate)) , Status

HTML

What I have                                                    

DelivaryDate          Status       Total            
26-08-2014            DND          50           
26-08-2014           Failed        20            
26-08-2014           Delivered     100
27-08-2014              DND         5
27-08-2014           Delivered      10

HTML

What i Want

DelivaryDate    DND   Failed   Delivered    Total
26-08-2014      50      20        100        170
27-08-2014      5      -         10          15

Thanks in Advance...

Posted 28-Aug-14 21:50pm

Rahul JR

Add a Solution

Comments

syed shanu 29-Aug-14 3:56am

Try using Pivot Query

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

syed shanu · Accepted Answer · 2014-08-28T22:53:00

Check this Query hope this will help you.

SQL

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Status) 
                     FROM Abc
             WHERE Column Name = Fieldname
             AND (DelivaryDate BETWEEN @FromDate AND @ToDate)
             GROUP BY DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate)) , Status
             ORDER BY DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate)) , Status
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        
	
set @query = 'SELECT DeleiveryDate,' + @cols + ' from 
             (
               Select  SUM(Column Name) AS Total ,Status
             DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate))
             DeleiveryDate 
    FROM Abc
             WHERE Column Name = Fieldname
             AND (DelivaryDate BETWEEN @FromDate AND @ToDate)
             GROUP BY DATEADD(DAY,0,DATEDIFF(DAY,0,DelivaryDate)) , Status          
            ) x
            pivot 
            (
                SUM(Total)
                for Status in (' + @cols + ')
            ) p '

execute(@query)