Question

sql query

Asked by: romeiovasu

hi i have to write a sql query
i have sku aaa
sku bbb
sku ccc
sku abc
sku acc

i have to write a query.when on invoice only if it is only sku aaa then it is cost + 10. if it is with other products then cost for aaa is 0 dollars but for other products it is cost + 5.  how can i write that one

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2008-03-14 at 14:17:46ID23243139
Topics

SQL Server 2005

,

SQL Query Syntax

Participating Experts
4
Points
500
Comments
19

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Collecting Top Selling Skus
    I have a task here, but I'm not quite sure how to approach it, I just know what the output should look like. Hopefully I can get some help in getting started. I have all the skus we sell. We'd like to get the top 3 skus sold with each sku. So sku a, what are the top thre...
  2. Need to return a count of Vendors by SKU as a field in a re…
    Table contains multiple Vendors with multplie SKUs. I currently have a query that pulls on a single vendor listing only those skus which are also listed by other vendors (See attached sql statement.) Now in addition to what gets returned I want to add a field to the result ...
  3. What is the correct CONVERT statement in MS SQL to chan…
    What is the correct CONVERT syntax in MS SQL to change the exported CSV data from an exponent to an integer? We have created a SQL statement for use with a third-party e-commerce application, but the SKU field seems to be formatted as an exponent and displaying wrong SKU data...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: RQuadlingPosted on 2008-03-14 at 14:21:12ID: 21129677

So, are these statements correct.

1 SKU only on invoice. SKU is AAA. Cost is +10
More than 1 SKU on invoice. Cost is +5 except when SKU is AAA

 

by: RQuadlingPosted on 2008-03-14 at 14:22:33ID: 21129688

It would be helpful to have some table and column names to work off and a few examples.

 

by: angelIIIPosted on 2008-03-14 at 14:22:59ID: 21129696

can you clarify that with data samples, please?

 

by: romeiovasuPosted on 2008-03-14 at 14:27:37ID: 21129727

here is the data

itemname     sku    cost
sim               aaa     0
item1            bbb     5
item2            ccc      7
item3            abc     8

if on the invoice only item is aaa then cost should be cost + 10

if on the invoice if there are other items then it all other items should be cost + 5 but for sku aaa should be 0 only.

 

by: BriCrowePosted on 2008-03-14 at 14:37:40ID: 21129805

Could you supply some real data or at least the real schema?  Obviously you are leaving out important fields like the invoicekey.

 

by: romeiovasuPosted on 2008-03-14 at 14:45:53ID: 21129877

here is the data

itemname     sku    cost   invoiceno
sim               aaa     0       a1234
item1            bbb     5       a1235
item2            ccc      7      a1235
item3            abc     8       a1235
sim               aaa     0       a1235
sim               aaa     0       a1236
sim               aaa     0       a1237
sim               bbb     5       a1237

 

by: romeiovasuPosted on 2008-03-14 at 14:47:31ID: 21129890

like on onvoice a1234 and a1236 you see the sku only aaa then price should be cost + 10 that means it should be $10 and on other invoices there are different products in that case the price of other products should be cost + 5 but for sku aaa it should be $0 only.

 

by: angelIIIPosted on 2008-03-14 at 14:53:08ID: 21129935

here we go:

select t.itemname, t.sku    t.cost  , t.invoiceno
, case when t.sku = 'aaa'
       then case when exists ( select null
                               from yourtable i
                               where i.invoiceno= t.invoiceno
                                 and i.sku <> 'aaa'
                              )
                 then 0 else 10 end
       else case when exists ( select null
                               from yourtable i
                               where i.invoiceno= t.invoiceno
                                 and i.sku = 'aaa'
                              )
                 then t.cost + 5 else t.cost end
  end new_cost
from yourtable t

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

Select allOpen in new window

 

by: romeiovasuPosted on 2008-03-14 at 15:33:48ID: 21130178

angellll in the second case statement does the sku aaa takes cost +5 or $0 for sku aaa

 

by: angelIIIPosted on 2008-03-14 at 15:37:10ID: 21130205

>angellll in the second case statement does the sku aaa takes cost +5 or $0 for sku aaa
did you try the code? any problems? might be just a misunderstanding of the requirements...

 

by: romeiovasuPosted on 2008-03-14 at 16:06:45ID: 21130406

angellll this is the result i got it didnt add the cost + 5 for other products but it added cost + 10 for the sku 2861 but if it is with other products on the same invoice then also it added as cost + 10 if it is on the same invoice with other products it should be $0 for that sku2861. this is the query that i have return

select tp.globalproductid,tp.serialnumber,tp.quantity, tp.unitcost,t.storeidto,tp.transferid,t.transferidbystore,
case when tp.globalproductid = 2861
 then case when exists(select null
from arjayiqm.arjaytelecom.dbo.iqclerk_transfersandproductsarchive tpp
where tpp.transferid = tp.transferid and tp.globalproductid <> 2861)
then 0 else 10 end
else
case when exists(select null
from arjayiqm.arjaytelecom.dbo.iqclerk_transfersandproductsarchive tpp
where tpp.transferid = tp.transferid and tp.globalproductid = 2861
) then tp.unitcost + 5 else tp.unitcost end
end newcost
from arjayiqm.arjaytelecom.dbo.iqclerk_transfersandproductsarchive tp inner join
arjayiqm.arjaytelecom.dbo.iqclerk_transfersarchive t on tp.transferid = t.transferid
where t.storeidto= 46

174	355195018075399	1	115.0000	46	9083	TMBSWTR1	115.0000
174	355195018075407	1	115.0000	46	9083	TMBSWTR1	115.0000
174	355195018075415	1	115.0000	46	9083	TMBSWTR1	115.0000
174	355195018075423	1	115.0000	46	9083	TMBSWTR1	115.0000
179	357771013708644	1	65.0000	46	9083	TMBSWTR1	65.0000
179	359463010080601	1	65.0000	46	9083	TMBSWTR1	65.0000
180	359464010233034	1	65.0000	46	9083	TMBSWTR1	65.0000
180	359464010235369	1	65.0000	46	9083	TMBSWTR1	65.0000
193	354580016774799	1	275.0000	46	9083	TMBSWTR1	275.0000
193	354580016775093	1	275.0000	46	9083	TMBSWTR1	275.0000
203	355828013370049	1	210.0000	46	9083	TMBSWTR1	210.0000
203	355828013370841	1	210.0000	46	9083	TMBSWTR1	210.0000
207	355054016246033	1	210.0000	46	9083	TMBSWTR1	210.0000
207	355054016246058	1	210.0000	46	9083	TMBSWTR1	210.0000
207	355054016246132	1	210.0000	46	9083	TMBSWTR1	210.0000
213	358304014509776	1	125.0000	46	9083	TMBSWTR1	125.0000
222	352862014097197	1	410.0000	46	9083	TMBSWTR1	410.0000
222	352862014098278	1	410.0000	46	9083	TMBSWTR1	410.0000
222	352862014175837	1	410.0000	46	9083	TMBSWTR1	410.0000
2861	8901260100020389296F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260210046108206F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260360019827751F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260390030392477F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260410045635866F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260410045635965F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260420019109632F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260440006888113F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260440006888121F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260440006888139F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260440012376293F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260490031197881F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260490031197899F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260490034565522F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260530003274885F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260530003274919F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260550010301554F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260550010304251F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260550010304269F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260550010304285F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260600050638470F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260600050638488F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260620014952965F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260620014953229F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260620016956782F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260620016958192F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017561040F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017561057F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017561123F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017561131F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017562592F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017570926F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017570934F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001403536F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001403544F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001533860F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001533878F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001586116F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001601477F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001601485F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001601493F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001601501F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260930019784176F	1	.0000	46	9083	TMBSWTR1	10.0000
3396	358264015370364	1	375.0000	46	9083	TMBSWTR1	375.0000
3396	358264015371941	1	375.0000	46	9083	TMBSWTR1	375.0000
3396	358264015371974	1	375.0000	46	9083	TMBSWTR1	375.0000
3417	355812011298030	1	180.0000	46	9083	TMBSWTR1	180.0000
3417	355812011298048	1	180.0000	46	9083	TMBSWTR1	180.0000
3472	358777011675080	1	345.0000	46	9083	TMBSWTR1	345.0000
3489	353691012845664	1	360.0000	46	9083	TMBSWTR1	360.0000
3489	353691012845672	1	360.0000	46	9083	TMBSWTR1	360.0000
3489	353691012901210	1	360.0000	46	9083	TMBSWTR1	360.0000
3489	353691012901228	1	360.0000	46	9083	TMBSWTR1	360.0000
3489	353691012901236	1	360.0000	46	9083	TMBSWTR1	360.0000
3489	353691012901244	1	360.0000	46	9083	TMBSWTR1	360.0000
3489	353691016026014	1	360.0000	46	9083	TMBSWTR1	360.0000
3490	353692010516810	1	360.0000	46	9083	TMBSWTR1	360.0000
3490	353692010516828	1	360.0000	46	9083	TMBSWTR1	360.0000
3595	355564014199497	1	280.0000	46	9083	TMBSWTR1	280.0000
3595	355564014199703	1	280.0000	46	9083	TMBSWTR1	280.0000
3595	355564014203737	1	280.0000	46	9083	TMBSWTR1	280.0000
3595	355564014204008	1	280.0000	46	9083	TMBSWTR1	280.0000
3595	355564014204057	1	280.0000	46	9083	TMBSWTR1	280.0000
3595	355564014231787	1	280.0000	46	9083	TMBSWTR1	280.0000
3595	355564014232678	1	280.0000	46	9083	TMBSWTR1	280.0000
3602	358784011867535	1	140.0000	46	9083	TMBSWTR1	140.0000
3680	358780012611807	1	140.0000	46	9083	TMBSWTR1	140.0000
2861	8901260390031256663F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031256705F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031256846F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031259634F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031259659F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031259709F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031260012F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031260046F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031260079F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031260095F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031260129F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031393763F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031393839F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031393870F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031394704F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031394977F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031395206F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031395214F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031395222F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031395230F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031690820F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031690903F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031905681F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031906101F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031906119F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031906184F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031906267F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031906283F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031906697F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031907174F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031907737F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031907851F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032190697F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032190879F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032190903F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032190945F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032191497F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032191612F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032192230F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032192297F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032192396F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032192578F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032192651F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032192776F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032193246F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032193477F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032193998F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032194194F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032194202F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032194434F	1	.0000	46	9089	TMBSWTR2	10.0000
196	358968010827893	1	140.0000	46	9093	TMBSWTR3	140.0000
196	358968010830509	1	140.0000	46	9093	TMBSWTR3	140.0000
196	358968010832158	1	140.0000	46	9093	TMBSWTR3	140.0000
196	358968010832778	1	140.0000	46	9093	TMBSWTR3	140.0000
196	358968010832794	1	140.0000	46	9093	TMBSWTR3	140.0000
196	358968010833198	1	140.0000	46	9093	TMBSWTR3	140.0000
2861	8901260390031256168f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031256242f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031256259f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031256325f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031256341f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031256713f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031267983f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031280754f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031280945f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031280960f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031281133f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031393748f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031393912f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031393961f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031394654f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031395131f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031395479f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031690994f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390032041312f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390032191653f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260440009044888f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260440009044904f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260440009044920f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260440009045174f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260440009045240f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260440009658398f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260480006122774f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260480006123368f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260480006123590f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260480006128524f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260480006128532f	1	.0000	46	9093	TMBSWTR3	10.0000
3667	359270010656292	1	180.0000	46	9093	TMBSWTR3	180.0000
3667	359270010656300	1	180.0000	46	9093	TMBSWTR3	180.0000
3667	359270010656482	1	180.0000	46	9093	TMBSWTR3	180.0000
3667	359270010662134	1	180.0000	46	9093	TMBSWTR3	180.0000
3667	359270010662407	1	180.0000	46	9093	TMBSWTR3	180.0000
                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:

Select allOpen in new window

 

by: angelIIIPosted on 2008-03-14 at 16:14:23ID: 21130444

your query did not join with invoiceid (serialnumber) ?

select tp.globalproductid,tp.serialnumber,tp.quantity, tp.unitcost,t.storeidto,tp.transferid,t.transferidbystore,
case when tp.globalproductid = 2861
 then case when exists(select null
from arjayiqm.arjaytelecom.dbo.iqclerk_transfersandproductsarchive tpp
where tpp.serialnumber= tp.serialnumberand tp.globalproductid <> 2861)
then 0 else 10 end
else
case when exists(select null
from arjayiqm.arjaytelecom.dbo.iqclerk_transfersandproductsarchive tpp
where tpp.serialnumber = tp.serialnumber and tp.globalproductid = 2861
) then tp.unitcost + 5 else tp.unitcost end
end newcost
from arjayiqm.arjaytelecom.dbo.iqclerk_transfersandproductsarchive tp inner join
arjayiqm.arjaytelecom.dbo.iqclerk_transfersarchive t on tp.transferid = t.transferid
where t.storeidto= 46

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Select allOpen in new window

 

by: romeiovasuPosted on 2008-03-14 at 16:23:20ID: 21130474

angellll then also i got the same result. it didnt add cost + 5 for other products and if sku 2861 and other products are there then it should be cost + 5 for other product and for 2861 it should be $0. if on invoice if only 2861sku is available then it is cost + 10.

222	352862014097197	1	410.0000	46	9083	TMBSWTR1	410.0000
222	352862014098278	1	410.0000	46	9083	TMBSWTR1	410.0000
222	352862014175837	1	410.0000	46	9083	TMBSWTR1	410.0000
3489	353691012845664	1	360.0000	46	9083	TMBSWTR1	360.0000
3489	353691012845672	1	360.0000	46	9083	TMBSWTR1	360.0000
3489	353691012901210	1	360.0000	46	9083	TMBSWTR1	360.0000
3489	353691012901228	1	360.0000	46	9083	TMBSWTR1	360.0000
3489	353691012901236	1	360.0000	46	9083	TMBSWTR1	360.0000
3489	353691012901244	1	360.0000	46	9083	TMBSWTR1	360.0000
3489	353691016026014	1	360.0000	46	9083	TMBSWTR1	360.0000
3490	353692010516810	1	360.0000	46	9083	TMBSWTR1	360.0000
3490	353692010516828	1	360.0000	46	9083	TMBSWTR1	360.0000
193	354580016774799	1	275.0000	46	9083	TMBSWTR1	275.0000
193	354580016775093	1	275.0000	46	9083	TMBSWTR1	275.0000
207	355054016246033	1	210.0000	46	9083	TMBSWTR1	210.0000
207	355054016246058	1	210.0000	46	9083	TMBSWTR1	210.0000
207	355054016246132	1	210.0000	46	9083	TMBSWTR1	210.0000
174	355195018075399	1	115.0000	46	9083	TMBSWTR1	115.0000
174	355195018075407	1	115.0000	46	9083	TMBSWTR1	115.0000
174	355195018075415	1	115.0000	46	9083	TMBSWTR1	115.0000
174	355195018075423	1	115.0000	46	9083	TMBSWTR1	115.0000
3595	355564014199497	1	280.0000	46	9083	TMBSWTR1	280.0000
3595	355564014199703	1	280.0000	46	9083	TMBSWTR1	280.0000
3595	355564014203737	1	280.0000	46	9083	TMBSWTR1	280.0000
3595	355564014204008	1	280.0000	46	9083	TMBSWTR1	280.0000
3595	355564014204057	1	280.0000	46	9083	TMBSWTR1	280.0000
3595	355564014231787	1	280.0000	46	9083	TMBSWTR1	280.0000
3595	355564014232678	1	280.0000	46	9083	TMBSWTR1	280.0000
3417	355812011298030	1	180.0000	46	9083	TMBSWTR1	180.0000
3417	355812011298048	1	180.0000	46	9083	TMBSWTR1	180.0000
203	355828013370049	1	210.0000	46	9083	TMBSWTR1	210.0000
203	355828013370841	1	210.0000	46	9083	TMBSWTR1	210.0000
179	357771013708644	1	65.0000	46	9083	TMBSWTR1	65.0000
3396	358264015370364	1	375.0000	46	9083	TMBSWTR1	375.0000
3396	358264015371941	1	375.0000	46	9083	TMBSWTR1	375.0000
3396	358264015371974	1	375.0000	46	9083	TMBSWTR1	375.0000
213	358304014509776	1	125.0000	46	9083	TMBSWTR1	125.0000
3472	358777011675080	1	345.0000	46	9083	TMBSWTR1	345.0000
3680	358780012611807	1	140.0000	46	9083	TMBSWTR1	140.0000
3602	358784011867535	1	140.0000	46	9083	TMBSWTR1	140.0000
196	358968010827893	1	140.0000	46	9093	TMBSWTR3	140.0000
196	358968010830509	1	140.0000	46	9093	TMBSWTR3	140.0000
196	358968010832158	1	140.0000	46	9093	TMBSWTR3	140.0000
196	358968010832778	1	140.0000	46	9093	TMBSWTR3	140.0000
196	358968010832794	1	140.0000	46	9093	TMBSWTR3	140.0000
196	358968010833198	1	140.0000	46	9093	TMBSWTR3	140.0000
3667	359270010656292	1	180.0000	46	9093	TMBSWTR3	180.0000
3667	359270010656300	1	180.0000	46	9093	TMBSWTR3	180.0000
3667	359270010656482	1	180.0000	46	9093	TMBSWTR3	180.0000
3667	359270010662134	1	180.0000	46	9093	TMBSWTR3	180.0000
3667	359270010662407	1	180.0000	46	9093	TMBSWTR3	180.0000
179	359463010080601	1	65.0000	46	9083	TMBSWTR1	65.0000
180	359464010233034	1	65.0000	46	9083	TMBSWTR1	65.0000
180	359464010235369	1	65.0000	46	9083	TMBSWTR1	65.0000
2861	8901260100020389296F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260210046108206F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260360019827751F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260390030392477F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260390031256168f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031256242f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031256259f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031256325f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031256341f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031256663F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031256705F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031256713f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031256846F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031259634F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031259659F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031259709F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031260012F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031260046F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031260079F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031260095F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031260129F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031267983f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031280754f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031280945f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031280960f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031281133f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031393748f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031393763F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031393839F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031393870F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031393912f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031393961f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031394654f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031394704F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031394977F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031395131f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031395206F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031395214F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031395222F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031395230F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031395479f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031690820F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031690903F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031690994f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390031905681F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031906101F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031906119F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031906184F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031906267F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031906283F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031906697F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031907174F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031907737F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390031907851F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032041312f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390032190697F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032190879F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032190903F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032190945F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032191497F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032191612F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032191653f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260390032192230F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032192297F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032192396F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032192578F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032192651F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032192776F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032193246F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032193477F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032193998F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032194194F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032194202F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260390032194434F	1	.0000	46	9089	TMBSWTR2	10.0000
2861	8901260410045635866F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260410045635965F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260420019109632F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260440006888113F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260440006888121F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260440006888139F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260440009044888f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260440009044904f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260440009044920f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260440009045174f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260440009045240f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260440009658398f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260440012376293F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260480006122774f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260480006123368f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260480006123590f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260480006128524f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260480006128532f	1	.0000	46	9093	TMBSWTR3	10.0000
2861	8901260490031197881F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260490031197899F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260490034565522F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260530003274885F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260530003274919F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260550010301554F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260550010304251F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260550010304269F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260550010304285F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260600050638470F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260600050638488F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260620014952965F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260620014953229F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260620016956782F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260620016958192F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017561040F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017561057F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017561123F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017561131F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017562592F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017570926F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260750017570934F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001403536F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001403544F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001533860F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001533878F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001586116F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001601477F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001601485F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001601493F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260870001601501F	1	.0000	46	9083	TMBSWTR1	10.0000
2861	8901260930019784176F	1	.0000	46	9083	TMBSWTR1	10.0000
                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:

Select allOpen in new window

 

by: angelIIIPosted on 2008-03-14 at 16:26:36ID: 21130486

please clarify which field is the "invoice id" ?

 

by: romeiovasuPosted on 2008-03-14 at 16:30:07ID: 21130496

where u see 9083,9089,9093 that is invoice number field.

 

by: romeiovasuPosted on 2008-03-14 at 17:13:08ID: 21130673

any suggestions angellll

 

by: ee_rleePosted on 2008-03-14 at 21:37:03ID: 21131472

hi

I changed

tpp.serialnumber= tp.serialnumber

to

tpp.globalproductid = tp.globalproductid

from angelIII's code. pls try

select tp.globalproductid,tp.serialnumber,tp.quantity, tp.unitcost,t.storeidto,tp.transferid,t.transferidbystore,
case when tp.globalproductid = 2861
 then case when exists(select null
from arjayiqm.arjaytelecom.dbo.iqclerk_transfersandproductsarchive tpp
where tpp.globalproductidr= tp.globalproductid and tp.globalproductid <> 2861)
then 0 else 10 end
else
case when exists(select null
from arjayiqm.arjaytelecom.dbo.iqclerk_transfersandproductsarchive tpp
where tpp.globalproductid = tp.globalproductid and tp.globalproductid = 2861
) then tp.unitcost + 5 else tp.unitcost end
end newcost
from arjayiqm.arjaytelecom.dbo.iqclerk_transfersandproductsarchive tp inner join
arjayiqm.arjaytelecom.dbo.iqclerk_transfersarchive t on tp.transferid = t.transferid
where t.storeidto= 46

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Select allOpen in new window

 

by: romeiovasuPosted on 2008-03-15 at 14:15:31ID: 31439910

thank you

 

by: romeiovasuPosted on 2008-03-15 at 14:15:42ID: 21134312

thank you

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...