Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CTE and Group By Expand / Collapse
Author
Message
Posted Friday, August 23, 2013 8:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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')
Post #1487842
Posted Friday, August 23, 2013 8:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 2,152, Visits: 1,744
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.
Post #1487844
Posted Friday, August 23, 2013 8:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 12,994, Visits: 12,409
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1487845
Posted Friday, August 23, 2013 8:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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.
Post #1487855
Posted Friday, August 23, 2013 8:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
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
Post #1487863
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse