Solved

sql server 2008 query

Posted on 2013-07-01
5
296 Views
Last Modified: 2013-07-02
Hi experts,

I'm using sql server 2008.

I have a table called TestCompanyOrders

This is the script to create this table:

CREATE TABLE [dbo].[TestCompanyOrders](
	[OrderID] [int] NOT NULL,
	[CompanyID] [int] NOT NULL,
	[CompanyName] [varchar](255) NULL,
	[ItemNumber] [int] NULL,
	[Item Description] [varchar](255) NULL,
	[ItemCost] [money] NULL,
	[POStatus] [varchar](255) NULL,
	[PayStatus] [varchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13409, 4614, N'AT & T', 1, N'Phone Service', 1560.0000, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13410, 4617, N'Walmart', 1, N'25 pack of ballpoint pens', 24.2500, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13411, 4623, N'Target', 1, N'20 ceiling fans', 660.0000, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13412, 4701, N'Office Depot', 1, N'10 boxes of paper', 224.0600, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13413, 4701, N'Office Depot', 2, N'paper clips', 54.4700, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13413, 4701, N'Office Depot', 1, N'Envelopes Letter Size', 54.4800, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13414, 4701, N'Office Depot', 2, N'20 pack of black pens', 34.0400, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13414, 4701, N'Office Depot', 1, N'20 pack of green pens', 34.0500, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13415, 4705, N'Dunder Miflin', 1, N'10 reams of white paper', 49.9200, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13415, 4705, N'Dunder Miflin', 3, N'10 reams of blue paper', 49.9000, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13415, 4705, N'Dunder Miflin', 2, N'10 reams of pink paper', 49.9200, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13416, 4723, N'Acme Inc.', 1, N'1 rocket launcher', 5851.6000, N'N', N'P')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13417, 4723, N'Acme Inc.', 1, N'1 howitzer', 7853.4900, N'N', N'P')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13418, 4733, N'Bear Data Inc.', 1, N'1 external 2TB hard drive', 106.9000, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13419, 4705, N'Dunder Miflin', 1, N'10 reams of purple paper', 0.0000, N'N', N' ')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13419, 4705, N'Dunder Miflin', 2, N'10 reams of yellow paper', 0.0000, N'N', N' ')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13419, 4705, N'Dunder Miflin', 3, N'10 reams of orange paper', 0.0000, N'N', N' ')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13420, 4789, N'Microsoft', 1, N'10 Microsoft keyboards', 188.8400, N'N', N'P')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13421, 4789, N'Microsoft', 1, N'10 licenses for SQL Server 2012', 135384.8100, N'N', N'P')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13422, 4789, N'Microsoft', 1, N'10 licenses for Visual Studio 2010 Ultimate', 13577.6500, N'N', N'P')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13423, 4792, N'Ace Hardware Inc.', 1, N'1 Air Conditioning Unit', 5000.0000, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13424, 4796, N'Dell Computers', 1, N'1 dell tablet', 240.4500, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13425, 4805, N'Office Max', 1, N'box of 50 pencils', 148.3200, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13425, 4805, N'Office Max', 2, N'box of 50 markers', 148.3300, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13426, 4912, N'Staples Inc.', 1, N'1 hp pavilion laptop', 412.7600, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13427, 4617, N'Walmart', 1, N'5 lawn chairs', 300.0000, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13428, 4623, N'Target', 1, N'two lawn chairs', 172.7600, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13429, 4914, N'PG & E', 1, N'Electricity Bill for July', 13286.5300, N'N', N'P')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13430, 4916, N'Home Depot', 1, N'5 oak trees', 52.2300, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13430, 4916, N'Home Depot', 2, N'5 pine trees', 52.2300, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13430, 4916, N'Home Depot', 3, N'5 redwood trees', 52.2500, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13431, 4623, N'Target', 1, N'1 patio set', 949.8400, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13432, 4918, N'Sears', 1, N'10 refrigerator units', 40000.0000, N'N', N'P')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13433, 4919, N'Osh Hardware', 1, N'4 sets of pliers', 88.5400, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13434, 4723, N'Acme Inc.', 2, N'1 bow and arrow set', 0.0000, N'Y', N' ')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13434, 4723, N'Acme Inc.', 1, N'1 box of 1000 bbs', 0.0000, N'Y', N' ')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13434, 4723, N'Acme Inc.', 3, N'1 Anvil', 0.0000, N'Y', N' ')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13434, 4723, N'Acme Inc.', 4, N'1 Crossbow', 0.0000, N'Y', N' ')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13435, 4924, N'Kmart', 1, N'10 packs of bubble gum', 25.1000, N'N', N'F')
INSERT [dbo].[TestCompanyOrders] ([OrderID], [CompanyID], [CompanyName], [ItemNumber], [Item Description], [ItemCost], [POStatus], [PayStatus]) VALUES (13436, 4928, N'Lowe''s', 1, N'1 lawn mower', 379.6000, N'N', N'F')

Open in new window


The table looks like this:

the TestCompanyOrders table

I'm trying to write a query that queries this table.


This is the logic that I need for the query:
the logic for my desired query
So I want to add a column called test to the TestCompanyOrders table.

My desired result of my query should look like this drawing that I drew in excel.

Notice the different OrderIDs are highlighted in blue and white.

The spreasheet used for my drawing: http://bit.ly/13mnUcI

my desired result set
So for example if you look at OrderID 1345 this order has three items.
ItemNumber 1 which is 10 reams of white paper, ItemNumber 2 which is 10 reams of pink paper and ItemNumber 3 which is 10 reams of blue paper.
Since the POStatus for all the items of this OrderID equal N than I have to look at the PayStatus.
So by the logic mentioned above, since all thes items have a PayStatus of F then in my test column these items should be "Closed"

So far I have this:

SELECT [OrderID]
      ,[CompanyID]
      ,[CompanyName]
      ,[ItemNumber]
      ,[Item Description]
      ,[ItemCost]
      ,[POStatus]
      ,[PayStatus]
      ,CASE [POStatus]
          WHEN 'Y' THEN 'Void'
          WHEN 'N' THEN
             CASE WHEN           THEN 'Closed' 
             
             
             
                  ELSE 'Open' 
             END
       END AS test
FROM [TestDatabase].[dbo].[TestCompanyOrders]

Open in new window


I can handle it when POStatus is Y but I'm having trouble when POStatus is N.


Can anyone help with this query?
0
Comment
Question by:maqskywalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 9

Expert Comment

by:COANetwork
ID: 39291643
CASE WHEN EXISTS(SELECT TCO.OrderID FROM TestCompanyOrders TCO WITH(NOLOCK) WHERE TCO.PayStatus <> 'F' AND TCO.OrderID = OrderID) THEN 'Open' ELSE 'Closed' END

Open in new window

0
 
LVL 1

Author Comment

by:maqskywalker
ID: 39291805
COANetwork,
So where would I insert that CASE statement in my query?

I placed it in like this:

SELECT [OrderID]
      ,[CompanyID]
      ,[CompanyName]
      ,[ItemNumber]
      ,[Item Description]
      ,[ItemCost]
      ,[POStatus]
      ,[PayStatus]
      ,CASE [POStatus]
          WHEN 'Y' THEN 'Void'
          WHEN 'N' THEN
             CASE WHEN EXISTS(SELECT TCO.OrderID FROM [TestDatabase].[dbo].[TestCompanyOrders] TCO WITH(NOLOCK) WHERE TCO.PayStatus <> 'F' AND TCO.OrderID = OrderID) THEN 'Closed' 
             ELSE 'Open'
         END
       END AS test
FROM [TestDatabase].[dbo].[TestCompanyOrders]

Open in new window


When I run it I get this:

query result
But I should be getting this:

desired result
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39292199
Seems to me the required case expression involves considering more than one field
SELECT
       [OrderID]
      ,[CompanyID]
      ,[CompanyName]
      ,[ItemNumber]
      ,[Item Description]
      ,[ItemCost]
      ,[POStatus]
      ,[PayStatus]
      ,CASE 
          WHEN [POStatus]  = 'Y' THEN 'Void'
          WHEN [PayStatus] = 'F' THEN 'Closed'
          ELSE                        'Open'
         END AS test
FROM [TestDatabase].[dbo].[TestCompanyOrders]

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39292209
btw: you can inspect/run the prior sql at: http://sqlfiddle.com/#!3/a5f16/4

mmm, this the given logic paraphrased:

if [PO Status] = 'Y' then 'Void'
if [PO Status] = 'N' AND [PayStatus] of ALL ITEMS for [OrderID] = 'F' then 'Closed'
else 'Open'

not sure I've got the 'all items' covered (yet)
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39292219
mmmm, again.
seems that testing for 'all items' may not be needed (well in the sample data it isn't needed)
try this:
SELECT
       [OrderID]
      ,[CompanyID]
      ,[CompanyName]
      ,[ItemNumber]
      ,[Item Description]
      ,[ItemCost]
      ,[POStatus]
      ,[PayStatus]
      ,CASE
          WHEN [POStatus]  = 'Y' THEN 'Void'
          WHEN [PayStatus] = 'F' THEN 'Closed'
          ELSE                        'Open'
         END
        AS test

      , count(*) over (partition BY OrderID)
      - count(CASE WHEN [PayStatus] = 'F' THEN 1 ELSE NULL END)
                 over (partition BY OrderID)
        AS unpaid_items

FROM [TestDatabase].[dbo].[TestCompanyOrders]

Open in new window

see: http://sqlfiddle.com/#!3/a5f16/9
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question