Solved

sql server 2008 query

Posted on 2013-07-01
5
290 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
  • 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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, show how to extract information from SQL Server on Database, Connection and Server properties

791 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