Solved

sql server 2008 query

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

707 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now