reg sql query

Question

Post reply

reg sql query

p.avinash689

SSC-Addicted

Points: 435
More actions
May 28, 2014 at 1:02 am

#286966

Hi All,
I have a data like below
code description qtyprice
GN0510 G0110 DISPO VAN SYRINGES19
GN0510 G0110 DISPO VAN SYRINGES19
GN0510 G0110 DISPO VAN SYRINGES19
GN0510 G0110 DISPO VAN SYRINGES19
GN0510 G0110 DISPO VAN SYRINGES19
but i need a out put like this below
code description qtyprice
GN0510 G0110 DISPO VAN SYRINGES545
How can i change my query please help me.
my query is
select OPPHARSALES2.ITEMCODE,MASTERITEM.ITEMNAME,OPPHARSALES2.QTY
FROM OPPHARSALES1 ,
OPPHARSALES2 , MASTERITEM
WHERE OPPHARSALES2.ITEMCODE=MASTERITEM.ITEMCODE AND OPPHARSALES1.BILLNO=OPPHARSALES2.BILLNO AND OPPHARSALES1.PATTYPE IN ('OP','PHARMACY')
AND (CONVERT(VARCHAR(10), BILLDATE, 21) BETWEEN '2013-05-01' AND '2014-05-27')ORDER BY OPPHARSALES1.BILLNO
Thanks in Advance,
Avinash P

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 22999 More actions · Answer 1

create table #OppHarSales1

(

BillNoint

,BillDatedate

,PatTypevarchar(8)

)

create table #OppHarSales2

(

ItemCodevarchar(6)

,Qtyint

,Priceint

,BillNoint

)

create table #MasterItem

(

ItemCodevarchar(6)

,Itemnamevarchar(25)

)

insert into #OppHarSales1

select 1,'2013-05-01', 'OP'union all

select 2,'2013-09-19', 'OP'union all

select 3,'2013-11-02', 'PHARMACY'union all

select 4,'2014-02-06', 'OP'union all

select 5,'2014-05-27', 'PHARMACY'

insert into #OppHarSales2

select 'GN0510',1,9,1 union all

select 'GN0510',1,9,2 union all

select 'GN0510',1,9,3 union all

select 'GN0510',1,9,4 union all

select 'GN0510',1,9,5

insert into #MasterItem

select 'GN0510','G01 10 DISPO VAN SYRINGES'

select

op2.Itemcode

,mi.Itemname

,UnitCount =sum(op2.qty)

,TotalPrice =sum(op2.Price)

From

#OppHarSales2 op2

join #MasterItemmion mi.ItemCode= op2.ItemCode

join #OppHarSales1 op1on op1.BillNo= op2.BillNo

where op1.BillDate>= '2013-05-01'

and op1.BillDate<= '2014-05-27'

and op1.PatTypein (

'OP'

,'PHARMACY'

)

group by mi.Itemname,op2.ItemCode

drop table #OppHarSales1,#OppHarSales2,#MasterItem

The code above will do what you want BUT I have made a couple of assumptions and it may not work in your environment. If you read this article

http://www.sqlservercentral.com/articles/Best+Practices/61537/

it will make it much easier to post code and get help in future. If you need anything clarifying please ask.

N

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

p.avinash689 SSC-Addicted Points: 435 More actions · Answer 2

I tried the above format but i didnt get what i want to be.

Neil Burton SSC-Insane Points: 22999 More actions · Answer 3

Ok, what did you get?

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

p.avinash689 (5/28/2014)
I tried the above format but i didnt get what i want to be.

This isn't helpful. As you can see, BWFC now has to ask you a bunch of questions in an attempt to figure out what's not worked. Maybe it has - but you don't like the results his statements generate. Only you know. Please provide as much information as you can so folks who are helping you don't have to guess what to do next.

A good understanding of aggregation is a basic, fundamental requirement for SQL developers. You can read about GROUP BY here. Skip down to the Examples section.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Shiva N Hall of Fame Points: 3125 More actions · Answer 5

SELECT DISTINCT a.itemcode

,b.itemname

,SUM(qty) AS Total_Qty

,SUM(Price) AS Total_Price

FROM oppharsales2 AS a

LEFT JOIN masteritem AS b ON a.itemcode = b.itemcode

INNER JOIN oppharsales1 c ON a.billno = c.billno

WHERE c.BillDate >= '2013-05-01'

AND c.BillDate <= '2014-05-27'

AND c.PatType IN (

'OP'

,'PHARMACY'

)

GROUP BY a.itemcode

,b.itemname

Thanks,
Shiva N
Database Consultant

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

shiva N (5/28/2014)
SELECT DISTINCT a.itemcode
,b.itemname
,SUM(qty) AS Total_Qty
,SUM(Price) AS Total_Price
FROM oppharsales2 AS a
LEFT JOIN masteritem AS b ON a.itemcode = b.itemcode
INNER JOIN oppharsales1 c ON a.billno = c.billno
GROUP BY a.itemcode
,b.itemname

Can you explain why you've used DISTINCT?

Where did the LEFT JOIN come from?

SELECT

= o2.ITEMCODE,

[description] = m.ITEMNAME,

[qty]= SUM(o2.QTY),

[price]= NULL -- source unspecified

FROM OPPHARSALES1 o1

INNER JOIN OPPHARSALES2 o2

ON o2.BILLNO = o1.BILLNO

INNER JOIN MASTERITEM m

ON m.ITEMCODE = o2.ITEMCODE

WHERE o1.PATTYPE IN ('OP','PHARMACY')

AND (CONVERT(VARCHAR(10), BILLDATE, 21) BETWEEN '2013-05-01' AND '2014-05-27')

GROUP BY o2.ITEMCODE, m.ITEMNAME

-----------------------------------------------------

(CONVERT(VARCHAR(10), BILLDATE, 21) BETWEEN '2013-05-01' AND '2014-05-27')

-- This will be a performance hog, it's not SARGable.

-- Cast the two date constants to the same datatype as BILLDATE.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

The following line in your original code will also make it impossible for your query to use an index effectively...

AND (CONVERT(VARCHAR(10), BILLDATE, 21) BETWEEN '2013-05-01' AND '2014-05-27')

It should be written as...

AND BillDate >= '2013-05-01'

AND BillDate < '2014-05-28' --<<----<<<< Notice that we've added "1" to the date here.

The other thing is that I know you're probably under-the-gun to get this done but you haven't given us much to help you with and comments like ...

I tried the above format but i didnt get what i want to be.

...simply give us no clue as to what's going on. If you want some really good help, you need to improve the quality of your question and the data you've provided. Please see the first link in my signature line below under "Helpful Links" for how to do that.

Also, since you've written this in all uppercase, I have to ask, are you using SQL Server or something else like Oracle?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

p.avinash689 SSC-Addicted Points: 435 More actions · Answer 8

p.avinash689

SSC-Addicted

Points: 435

May 29, 2014 at 11:15 pm

#1717660

i got it thanks to all...:)