Link to home
Start Free TrialLog in
Avatar of maqskywalker
maqskywalker

asked on

sql server 2008 query

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:

User generated image

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


This is the logic that I need for the query:
User generated image
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

User generated image
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?
Avatar of COANetwork
COANetwork

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

Avatar of maqskywalker

ASKER

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:

User generated image
But I should be getting this:

User generated image
Avatar of PortletPaul
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

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)
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial