August 23, 2013 at 8:28 am
I have a table where 'X' = Delivered and '' is not Delivered. I am trying to group the deliveries by PO Item, so that if all rows have been Delivered then we get an X.. otherwise ''.
So a SQL would return.. for the data below
4900239800, 0000,
4900239825, 0010, X
So the table.. stripped down.. would look like this:
/****** Object: Table [dbo].[PO_Delivery_Hist] Script Date: 08/23/2013 10:16:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PO_Delivery_Hist](
[Purchasing_Doc_Num] [varchar](50) NOT NULL,
[Purchasing_Req_Item_Num] [varchar](5) NOT NULL,
[Sequential_Num] [tinyint] NOT NULL,
[Delivery_Ind] [char](1) NULL,
CONSTRAINT [PK_PO_Delivery_Hist] PRIMARY KEY CLUSTERED
(
[Purchasing_Doc_Num] ASC,
[Purchasing_Req_Item_Num] ASC,
[Sequential_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Data:
insert into PO_Delivery_Hist
values ('4900239825', '0010', '1', 'X')
insert into PO_Delivery_Hist
values ('4900239825', '0010', '2', 'X')
insert into PO_Delivery_Hist
values ('4900239825', '0010', '3', 'X')
insert into PO_Delivery_Hist
values ('4900239800', '0000', '1', 'X')
insert into PO_Delivery_Hist
values ('4900239800', '0000', '2', '')
insert into PO_Delivery_Hist
values ('4900239800', '0000', '3', 'X')
August 23, 2013 at 8:35 am
It's the type of problem that's often easier if you look at it the other way round. If you can find one line of a PO that's not delivered then mark it as ''. If not, then you can assume all lines are delivered.
August 23, 2013 at 8:38 am
Excellent job posting ddl and sample data. This produces the output as stated.
select Purchasing_Doc_Num, Purchasing_Req_Item_Num, min(Delivery_Ind) as Delivered
from PO_Delivery_Hist
group by Purchasing_Doc_Num, Purchasing_Req_Item_Num
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2013 at 8:50 am
Ok.. I guess the error I got was not due to the way I was doing it.. the test table I just created to post this worked.. so the data type (on the OLAP) server must be the issue...
Msg 8117, Level 16, State 1, Line 5
Operand data type nvarchar is invalid for sum operator.
August 23, 2013 at 8:58 am
I used a CTE to cast the data... and it works, thanks for your help.... I was chasing the wrong rabbit.
;with Del_Ind as
(
SELECT Row_Number() Over(Partition by Purchasing_Doc_Num, Purchasing_Req_Item_Num order by Purchasing_Doc_Num, Purchasing_Req_Item_Num) as Row
,Purchasing_Doc_Num, Purchasing_Req_Item_Num, CAST(Delivery_Ind as CHAR(1)) as Delivery_Ind
From [p04].[data].[dbo].[vw_purcordstohist_raw]
)
SELECT Purchasing_Doc_Num, Purchasing_Req_Item_Num, Min(Delivery_Ind) as 'Delivery_ind'
From Del_Ind
group by Purchasing_Doc_Num, Purchasing_Req_Item_Num
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply