sql server 2008 query

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
LVL 1
maqskywalkerAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
LowfatspreadCommented:
try using specifying it as   max(status)
0
 
appariConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.