How to get Data in this way

Question

0.00/5 (No votes)

See more:

I have table MLD. I want that Invoiceno,sumof InvoicAmt of same orderno,CreatedDate and first invoice which have same createddate and orderno.

OrderNo       InvoiceNo     CreatedDate     InvoiceAmt      Qty
--------------------------------------------------------------------------------------
101           5012         11-Apr-2014      5200.00         31
101           5013         11-Apr-2014      1800.00         12
105           5083         14-Apr-2014      1800.00         12
105           3013         14-Apr-2014      4800.00         17
108           2304         18-Apr-2014      2612.00         41

I want result like this. How it is possible?

OrderNo       InvoiceNo      CreatedDate      InvoiceAmt      Qty
--------------------------------------------------------------------------------------
101            5012          11-Apr-2014       7000.00        43 
105            5083          14-Apr-2014       6600.00        12
108            2304          18-Apr-2014       2612.00        41

Please give me solution ....

Posted 12-Apr-14 18:57pm

Amit Vasi

Updated 12-Apr-14 21:05pm

Aarti Meswania

v3

Add a Solution

3 solutions

Solution 1

Use "Group by" Clause in your query

Posted 12-Apr-14 19:33pm

anilpal

Comments

Amit Vasi 13-Apr-14 1:37am

but how to get First InvoiceNum and Sum of All Invoices of same order and same date ?

Amit Vasi 13-Apr-14 1:37am

Please give me query

Solution 3

This is a simple way...

SQL

select 
     min(OrderNo) OrderNo,
     min(InvoiceNo) InvoiceNo,
     CreatedDate,
     sum(InvoiceAmt) InvoiceAmt,
     sum(Qty) Qty
From
     tblName
Group by 
     CreatedDate

Happy Coding!
:)

Posted 12-Apr-14 21:09pm

Aarti Meswania

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)

Peter Leow · Accepted Answer · 2014-04-12T20:59:00

Solution 2

Try this:

SQL

with cte  (orderno, createddate, invoiceamt, qty)
as
(
select orderno, createddate, sum(invoiceamt), sum(qty)
from mld group by orderno, createddate
) select orderno,
(select top 1 invoiceno from mld where mld.orderno = cte.orderno),
createddate, invoiceamt, qty
from cte

Posted 12-Apr-14 20:59pm

Peter Leow

v2

Comments

Amit Vasi 13-Apr-14 3:04am

Thanks alot...It has solved my problem...thanks

Peter Leow 13-Apr-14 3:28am

You are welcome.