August 14, 2012 at 11:46 am
Hey guys,
I am having trouble with this OVER PARTITION BY...
Here is some sample code to test on:
CREATE TABLE [dbo].[tst_InvoiceHeader](
[invoice_id] [int] NULL,
[grouo_id] [int] NULL,
[subscriber_id] [int] NULL,
[createdate] [datetime] NULL,
[duedate] [datetime] NULL
) ON [PRIMARY]
INSERT INTO dbo.tst_Invoices
( invoice_id ,
grouo_id ,
subscriber_id ,
createdate ,
duedate
)
SELECT 100, 5, 1111, '7/24/2012', '08/01/2012' UNION ALL
SELECT 100, 5, 1111, '7/24/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012'
SELECT invoice_id,
ROW_NUMBER() OVER (PARTITION BY subscriber_id, grouo_id,createdate ORDER BY createdate DESC) AS 'Row Number',
grouo_id,
subscriber_id,
createdate,
duedate
FROM dbo.tst_Invoices
The way it partitions it is it takes the invoice id's of 100 and ranks them 1, 2 and takes the invoice ids of 200 and ranks them 1, 2, 3....
I need it rank all the invoice ids of 200 as 1 and all the invoice ids of 100 as 2?
The business behind this is if the invoices are created within the same month and have the same due date, i only need to the the higher invoice id...i could do with different invoices that have the same due date and was created within the same month but my problem is when i have a set of invoices that have multiple records...It ranks them individually and I need them to rank as a group...I hope I am explaining this sufficiently...If not, let me know and I will try to reword it!
Thanks guys
August 14, 2012 at 11:55 am
You mean something more like this?
CREATE TABLE [dbo].[tst_InvoiceHeader](
[invoice_id] [int] NULL,
[grouo_id] [int] NULL,
[subscriber_id] [int] NULL,
[createdate] [datetime] NULL,
[duedate] [datetime] NULL
) ON [PRIMARY]
INSERT INTO dbo.tst_InvoiceHeader
( invoice_id ,
grouo_id ,
subscriber_id ,
createdate ,
duedate
)
SELECT 100, 5, 1111, '7/24/2012', '08/01/2012' UNION ALL
SELECT 100, 5, 1111, '7/24/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012'
SELECT invoice_id,
DENSE_RANK() OVER (ORDER BY invoice_id DESC, createdate DESC) AS 'Rank Number',
grouo_id,
subscriber_id,
createdate,
duedate
FROM dbo.tst_InvoiceHeader;
go
DROP TABLE dbo.tst_InvoiceHeader;
go
August 14, 2012 at 12:00 pm
Yes that looks to be what I need...I am going to try it out on my original query...
Never used the DENSE function...Need to look that one up
Thank you so much!
August 14, 2012 at 12:12 pm
I was wrong...That does not work for me...
Try this code:
CREATE TABLE [dbo].[tst_Invoices](
[invoice_id] [int] NULL,
[grouo_id] [int] NULL,
[subscriber_id] [int] NULL,
[createdate] [datetime] NULL,
[duedate] [datetime] NULL
) ON [PRIMARY]
INSERT INTO dbo.tst_Invoices
( invoice_id ,
grouo_id ,
subscriber_id ,
createdate ,
duedate
)
SELECT 100, 5, 1111, '7/24/2012', '08/01/2012' UNION ALL
SELECT 100, 5, 1111, '7/24/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 300, 5, 1111, '7/25/2012', '09/01/2012' UNION ALL
SELECT 300, 5, 1111, '7/25/2012', '09/01/2012' UNION ALL
SELECT 400, 5, 1111, '7/28/2012', '09/01/2012' UNION ALL
SELECT 400, 5, 1111, '7/28/2012', '09/01/2012' UNION ALL
SELECT 400, 5, 1111, '7/28/2012', '09/01/2012'
SELECT invoice_id,
ROW_NUMBER() OVER (PARTITION BY subscriber_id, grouo_id,duedate ORDER BY createdate DESC) AS 'Row Number',
--DENSE_RANK() OVER (ORDER BY invoice_id DESC, createdate DESC) AS 'Rank Number',
grouo_id,
subscriber_id,
createdate,
duedate
FROM dbo.tst_Invoices;
This separates them by the due date, 5 for 8/1 and 5 for 9/1 but I need the same thing...I need the 100's as 2, 200's as 1 for the 8/1 due date, and then for 9/1 due date, I need 300 as 1, 400 as 2...
Is there a way to do this?
August 14, 2012 at 12:14 pm
asm1212 (8/14/2012)
I was wrong...That does not work for me...Try this code:
CREATE TABLE [dbo].[tst_Invoices](
[invoice_id] [int] NULL,
[grouo_id] [int] NULL,
[subscriber_id] [int] NULL,
[createdate] [datetime] NULL,
[duedate] [datetime] NULL
) ON [PRIMARY]
INSERT INTO dbo.tst_Invoices
( invoice_id ,
grouo_id ,
subscriber_id ,
createdate ,
duedate
)
SELECT 100, 5, 1111, '7/24/2012', '08/01/2012' UNION ALL
SELECT 100, 5, 1111, '7/24/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 300, 5, 1111, '7/25/2012', '09/01/2012' UNION ALL
SELECT 300, 5, 1111, '7/25/2012', '09/01/2012' UNION ALL
SELECT 400, 5, 1111, '7/28/2012', '09/01/2012' UNION ALL
SELECT 400, 5, 1111, '7/28/2012', '09/01/2012' UNION ALL
SELECT 400, 5, 1111, '7/28/2012', '09/01/2012'
SELECT invoice_id,
ROW_NUMBER() OVER (PARTITION BY subscriber_id, grouo_id,duedate ORDER BY createdate DESC) AS 'Row Number',
--DENSE_RANK() OVER (ORDER BY invoice_id DESC, createdate DESC) AS 'Rank Number',
grouo_id,
subscriber_id,
createdate,
duedate
FROM dbo.tst_Invoices;
This separates them by the due date, 5 for 8/1 and 5 for 9/1 but I need the same thing...I need the 100's as 2, 200's as 1 for the 8/1 due date, and then for 9/1 due date, I need 300 as 1, 400 as 2...
Is there a way to do this?
Take your sample data, and show us what the expected results should be based on that. This wil probably require you to manually create the expected results, but then we will know what you are trying to accomplish.
August 14, 2012 at 12:18 pm
This?
CREATE TABLE [dbo].[tst_Invoices](
[invoice_id] [int] NULL,
[grouo_id] [int] NULL,
[subscriber_id] [int] NULL,
[createdate] [datetime] NULL,
[duedate] [datetime] NULL
) ON [PRIMARY]
INSERT INTO dbo.tst_Invoices
( invoice_id ,
grouo_id ,
subscriber_id ,
createdate ,
duedate
)
SELECT 100, 5, 1111, '7/24/2012', '08/01/2012' UNION ALL
SELECT 100, 5, 1111, '7/24/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 200, 5, 1111, '7/31/2012', '08/01/2012' UNION ALL
SELECT 300, 5, 1111, '7/25/2012', '09/01/2012' UNION ALL
SELECT 300, 5, 1111, '7/25/2012', '09/01/2012' UNION ALL
SELECT 400, 5, 1111, '7/28/2012', '09/01/2012' UNION ALL
SELECT 400, 5, 1111, '7/28/2012', '09/01/2012' UNION ALL
SELECT 400, 5, 1111, '7/28/2012', '09/01/2012'
SELECT invoice_id,
--ROW_NUMBER() OVER (PARTITION BY subscriber_id, grouo_id,duedate ORDER BY createdate DESC) AS 'Row Number',
DENSE_RANK() OVER (PARTITION BY duedate ORDER BY invoice_id DESC, createdate DESC) AS 'Rank Number',
grouo_id,
subscriber_id,
createdate,
duedate
FROM dbo.tst_Invoices;
GO
DROP TABLE [dbo].[tst_Invoices]
GO
Never mind, it isn't. In the first set you want 200 as 1 and 100 as 2 and then in the next you want 300 as 1 and 400 as 2.
So that makes the sorting inconsistent.
August 14, 2012 at 12:50 pm
So here is what I want the finished result set to look like:
invoice_id rank subscriberid create date due date
100 2 1111 7/24 8/1
100 2 1111 7/24 8/1
200 1 1111 7/31 8/1
200 1 1111 7/31 8/1
200 1 1111 7/31 8/1
300 2 1111 7/25 9/1
300 2 1111 7/25 9/1
400 1 1111 7/28 9/1
400 1 1111 7/28 9/1
400 1 1111 7/28 9/1
the first 5 records need to be together b/c of the 8/1 due date...and the last 5 need to be together b/c of 9/1...
August 14, 2012 at 12:59 pm
asm1212 (8/14/2012)
So here is what I want the finished result set to look like:invoice_id rank subscriberid create date due date
100 2 1111 7/24 8/1
100 2 1111 7/24 8/1
200 1 1111 7/31 8/1
200 1 1111 7/31 8/1
200 1 1111 7/31 8/1
300 2 1111 7/25 9/1
300 2 1111 7/25 9/1
400 1 1111 7/28 9/1
400 1 1111 7/28 9/1
400 1 1111 7/28 9/1
the first 5 records need to be together b/c of the 8/1 due date...and the last 5 need to be together b/c of 9/1...
Looking at that, you have all the information you need in my striked out code above, you just have to figure out the what needs to change. In fact, all that you may need is an order by clause on the final select.
August 14, 2012 at 1:17 pm
Yep, just need an ORDER BY on the select statement.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply