Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql server 2008 query

Posted on 2013-02-05
3
Medium Priority
?
198 Views
Last Modified: 2013-02-07
Hi experts,

I have a sql server 2008 table called MyTestTable

It looks like this:

my table
Here is the scrip to create this table:

CREATE TABLE [dbo].[MyTestTable](
	[Region] [int] NULL,
	[Year] [int] NULL,
	[OrderNo] [int] NULL,
	[ItemCost] [smallmoney] NULL,
	[Status] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[MyTestTable] ([Region], [Year], [OrderNo], [ItemCost], [Status]) VALUES (1, 2013, 1001, 5.0000, N'Closed')
INSERT [dbo].[MyTestTable] ([Region], [Year], [OrderNo], [ItemCost], [Status]) VALUES (1, 2013, 1001, 10.0000, N'Closed')
INSERT [dbo].[MyTestTable] ([Region], [Year], [OrderNo], [ItemCost], [Status]) VALUES (1, 2013, 1001, 6.0000, N'Open')
INSERT [dbo].[MyTestTable] ([Region], [Year], [OrderNo], [ItemCost], [Status]) VALUES (1, 2013, 1001, 4.0000, N'Open')

Open in new window


I'm trying to group this table by Region, Year, OrderNo

I then want to Sum the ItemCost column.

For the Status column, this is what I need.

If all 4 items have a value of Closed then show this column when it's collapsed as Closed.

So if at least one the the items for this OrderNo has a value of Open then this column should show as Open when it's collapsed.

For example currently there are two items with a status of Closed and two items with a value of Open. So then the value in this column should show Open and my resulting query should show this:

my desired query result
Right now this is the query I have but it's not giving me the result I want shown above:

SELECT [Region]
      ,[Year]
      ,[OrderNo]
      ,SUM ([ItemCost]) As Cost
      ,[Status]
FROM [Test].[dbo].[MyTestTable]
GROUP BY [Region]
        ,[Year]
        ,[OrderNo]
        ,[Status]

How can I fix my query? Can anyone help? thanks.

So to summarize you can look at this image of sample values.
The only time the Status should say Closed is when all items are closed as shown in the 4th item in the picture below.

example values for my table
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 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38857653
try using specifying it as   max(status)
0
 
LVL 39

Assisted Solution

by:appari
appari earned 1000 total points
ID: 38857715
if possible values in status is only open and closed then you can try

SELECT [Region]
      ,[Year]
      ,[OrderNo]
      ,SUM ([ItemCost]) As Cost
      , max([Status]) [Status]
FROM [Test].[dbo].[MyTestTable]
GROUP BY [Region]
        ,[Year]
        ,[OrderNo]

Open in new window


in case other status values exists then try
SELECT [Region]
      ,[Year]
      ,[OrderNo]
      ,SUM ([ItemCost]) As Cost
      , case when max([Status]) = min([Status]) then min([Status]) else 'Open' end [Status]
FROM [Test].[dbo].[MyTestTable]
GROUP BY [Region]
        ,[Year]
        ,[OrderNo]

Open in new window

0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 38860233
SELECT
       [Region]
      ,[Year]
      ,[OrderNo]
      ,SUM ([ItemCost]) As Cost
      ,CASE WHEN SUM(CASE WHEN [Status] <> 'Closed' THEN 1 ELSE 0 END) = 0 THEN 'Closed' ELSE 'Open' END AS Status
FROM [Test].[dbo].[MyTestTable]
GROUP BY [Region]
        ,[Year]
        ,[OrderNo]
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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.
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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.

688 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