Solved

sql server 2008 query

Posted on 2013-07-01
5
292 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

Title # Comments Views Activity
How do I figure out how much database is expected to grow? 15 74
How come this XML node is not read? 3 27
SQL Syntax 6 39
SQL Distinct Question 3 13
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

735 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