September 18, 2014 at 7:40 pm
CREATE TABLE [dbo].[retail](
[subscriber] [int] NULL,
[wan] [int] NULL,
[activation_date] [datetime] NULL
) ON [PRIMARY]
insert into dbo.retail(subscriber,wan,activation_date)
select '1126','1099','2014-10-05 00:00:00.000' UNION ALL
select '1126','1100','2014-10-09 00:00:00.000' UNION ALL
select '1127','1121','2014-10-05 00:00:00.000' UNION ALL
select '1127','1122','2014-10-09 00:00:00.000' UNION ALL
select '1128','1123','2014-10-05 00:00:00.000' UNION ALL
select '1129','1124','2014-10-09 00:00:00.000'
Can anyone please help me to show data in below format according to date wise
Old dated Wan ,New dated Wan,subscriber
1099 1100 1126
1121 1122 1127
Condition
1)Only duplicate entries of subscriber should show this format
September 18, 2014 at 10:16 pm
There are few ways of doing this, here are two of them. The first uses a combination of GROUP BY and HAVING, the second uses ROW_NUMBER and MIN/MAX Window function.
😎
USE tempdb;
GO
SET NOCOUNT ON;
CREATE TABLE [dbo].[retail](
[subscriber] [int] NULL,
[wan] [int] NULL,
[activation_date] [datetime] NULL
) ON [PRIMARY];
insert into dbo.retail(subscriber,wan,activation_date)
select '1126','1099','2014-10-05 00:00:00.000' UNION ALL
select '1126','1100','2014-10-09 00:00:00.000' UNION ALL
select '1127','1121','2014-10-05 00:00:00.000' UNION ALL
select '1127','1122','2014-10-09 00:00:00.000' UNION ALL
select '1128','1123','2014-10-05 00:00:00.000' UNION ALL
select '1129','1124','2014-10-09 00:00:00.000'
/* GROUP BY and HAVING */
SELECT
MIN(RT.wan) AS [Old dated Wan]
,MAX(RT.wan) AS [New dated Wan]
,RT.subscriber
FROM dbo.retail RT
GROUP BY RT.subscriber
HAVING MIN(RT.wan) <> MAX(RT.wan)
/* ROW NUMBER and WINDOW function*/
;WITH BASE_SET AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY RT.subscriber
ORDER BY (SELECT NULL)
) AS BS_RID
,MIN(RT.wan) OVER
(
PARTITION BY RT.subscriber
) AS [Old dated Wan]
,MAX(RT.wan) OVER
(
PARTITION BY RT.subscriber
) AS [New dated Wan]
,RT.subscriber
FROM dbo.retail RT
)
SELECT
BS.[Old dated Wan]
,BS.[New dated Wan]
,BS.subscriber
FROM BASE_SET BS
WHERE BS.BS_RID = 2;
DROP TABLE dbo.retail;
Results
Old dated Wan New dated Wan subscriber
------------- ------------- -----------
1099 1100 1126
1121 1122 1127
September 19, 2014 at 4:45 am
Thanks for this solution.
Is this possible i get old dated wan and new dated wan according to activation_date column.?
Activation date of both wan compared then old dated wan set as old dated wan and latest one new dated wan.
September 21, 2014 at 4:45 am
selpoivre (9/19/2014)
Thanks for this solution.Is this possible i get old dated wan and new dated wan according to activation_date column.?
Activation date of both wan compared then old dated wan set as old dated wan and latest one new dated wan.
Here are the queries with the activation dates added
😎
USE tempdb;
GO
SET NOCOUNT ON;
CREATE TABLE [dbo].[retail](
[subscriber] [int] NULL,
[wan] [int] NULL,
[activation_date] [datetime] NULL
) ON [PRIMARY];
insert into dbo.retail(subscriber,wan,activation_date)
select '1126','1099','2014-10-05 00:00:00.000' UNION ALL
select '1126','1100','2014-10-09 00:00:00.000' UNION ALL
select '1127','1121','2014-10-05 00:00:00.000' UNION ALL
select '1127','1122','2014-10-09 00:00:00.000' UNION ALL
select '1128','1123','2014-10-05 00:00:00.000' UNION ALL
select '1129','1124','2014-10-09 00:00:00.000'
/* GROUP BY and HAVING */
SELECT
MIN(RT.wan) AS [Old dated Wan]
,MIN(RT.activation_date) AS [OLD activation_date]
,MAX(RT.wan) AS [New dated Wan]
,MAX(RT.activation_date) AS [NEW activation_date]
,RT.subscriber
FROM dbo.retail RT
GROUP BY RT.subscriber
HAVING MIN(RT.wan) <> MAX(RT.wan)
/* ROW NUMBER and WINDOW function*/
;WITH BASE_SET AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY RT.subscriber
ORDER BY (SELECT NULL)
) AS BS_RID
,MIN(RT.wan) OVER
(
PARTITION BY RT.subscriber
) AS [Old dated Wan]
,MIN(RT.activation_date) OVER
(
PARTITION BY RT.subscriber
) AS [Old activation_date]
,MAX(RT.wan) OVER
(
PARTITION BY RT.subscriber
) AS [New dated Wan]
,MAX(RT.activation_date) OVER
(
PARTITION BY RT.subscriber
) AS [New activation_date]
,RT.subscriber
FROM dbo.retail RT
)
SELECT
BS.[Old dated Wan]
,BS.[Old activation_date]
,BS.[New dated Wan]
,BS.[New activation_date]
,BS.subscriber
FROM BASE_SET BS
WHERE BS.BS_RID = 2;
DROP TABLE dbo.retail;
Results
Old dated Wan OLD activation_date New dated Wan NEW activation_date subscriber
------------- ----------------------- ------------- ----------------------- -----------
1099 2014-10-05 00:00:00.000 1100 2014-10-09 00:00:00.000 1126
1121 2014-10-05 00:00:00.000 1122 2014-10-09 00:00:00.000 1127
Adding the duration in days
SELECT
BS.[Old dated Wan]
,BS.[Old activation_date]
,BS.[New dated Wan]
,BS.[New activation_date]
,DATEDIFF(DAY,BS.[Old activation_date],BS.[New activation_date]) AS DURATION_DAY
,BS.subscriber
FROM BASE_SET BS
WHERE BS.BS_RID = 2;
Results
Old dated Wan Old activation_date New dated Wan New activation_date DURATION_DAY subscriber
------------- ----------------------- ------------- ----------------------- ------------ -----------
1099 2014-10-05 00:00:00.000 1100 2014-10-09 00:00:00.000 4 1126
1121 2014-10-05 00:00:00.000 1122 2014-10-09 00:00:00.000 4 1127
September 28, 2014 at 11:48 pm
@Eirikur Eiriksson
Just a quick question.
You used the MAX(wan)<>MIN(Wan) instead of COUNT(wan)>1
When I checked the estimated execution plan there is one less operator(compute scalar) in the first operation (MIN , MAX method). but both having same cost also the additional operator uses 0% and I just tested this in the sample data given. So is there any difference between these two in a performance aspect?
October 4, 2014 at 11:15 pm
desperadomar (9/28/2014)
@Eirikur EirikssonJust a quick question.
You used the MAX(wan)<>MIN(Wan) instead of COUNT(wan)>1
When I checked the estimated execution plan there is one less operator(compute scalar) in the first operation (MIN , MAX method). but both having same cost also the additional operator uses 0% and I just tested this in the sample data given. So is there any difference between these two in a performance aspect?
The quick answer is yes, there is a difference, COUNT is slightly more costly, few points below.
😎
1. The COUNT adds a row to the stream, additional 4 bytes, which has a cost and although neglectable when the set is this small, isn't needed for the logic.
2. Always use the actual plan if possible, the estimated plans normally aren't close enough.
3. The compute scalar operator doesn't always do as it says on the tin, it is more of a placeholder for an expression, in this case [Expr1002] = Scalar Operator(CONVERT_IMPLICIT(int,[Expr1013],0))
October 5, 2014 at 7:14 pm
insert into dbo.retail(subscriber,wan,activation_date)
select '1700', '1802','2014-10-05 00:00:00.000' UNION ALL
select '1700','1801','2014-10-09 00:00:00.000'
For above dataset query wont work because wan 1801 activation_date is latest so it should come in New Dated Wan column but with your query its showing in Old Dated Wan column.Any solution for this that wan should come in old dated wan and new dated column according to activation date.I hope you get my point.
October 5, 2014 at 10:10 pm
The assumption made in the code was that the WAN values would be in an increasing order, as that's correct according to the last sample, one has to add a directive for the order. The row_number functions partitions the set on the subscriber and assigns an order number to each subscribers entry. The case statement in the cross tab sections then handles the column assignment and finally the max aggregation is used to remove the nulls and merging each subscriber into one row.
😎
USE tempdb;
GO
SET NOCOUNT ON;
CREATE TABLE [dbo].[retail](
[subscriber] [int] NULL,
[wan] [int] NULL,
[activation_date] [datetime] NULL
) ON [PRIMARY];
insert into dbo.retail(subscriber,wan,activation_date)
select '1126','1099','2014-10-05 00:00:00.000' UNION ALL
select '1126','1100','2014-10-09 00:00:00.000' UNION ALL
select '1127','1121','2014-10-05 00:00:00.000' UNION ALL
select '1127','1122','2014-10-09 00:00:00.000' UNION ALL
select '1128','1123','2014-10-05 00:00:00.000' UNION ALL
select '1129','1124','2014-10-09 00:00:00.000' UNION ALL
select '1700' ,'1802' ,'2014-10-05 00:00:00.000' UNION ALL
select '1700','1801','2014-10-09 00:00:00.000'
/* GROUP BY and HAVING */
;WITH GROUPED_SET AS
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY RT.subscriber
ORDER BY RT.activation_date
) AS SAC_RID
,RT.subscriber
,RT.wan
,RT.activation_date
FROM dbo.retail RT
)
SELECT
MAX(CASE WHEN RT.SAC_RID = 1 THEN RT.wan END ) AS [Old dated Wan]
,MAX(CASE WHEN RT.SAC_RID = 1 THEN RT.activation_date END ) AS [OLD activation_date]
,MAX(CASE WHEN RT.SAC_RID = 2 THEN RT.wan END ) AS [New dated Wan]
,MAX(CASE WHEN RT.SAC_RID = 2 THEN RT.activation_date END ) AS [NEW activation_date]
,RT.subscriber
FROM GROUPED_SET RT
GROUP BY RT.subscriber
HAVING MIN(RT.wan) <> MAX(RT.wan);
DROP TABLE dbo.retail;
Results
Old dated Wan OLD activation_date New dated Wan NEW activation_date subscriber
------------- ----------------------- ------------- ----------------------- -----------
1099 2014-10-05 00:00:00.000 1100 2014-10-09 00:00:00.000 1126
1121 2014-10-05 00:00:00.000 1122 2014-10-09 00:00:00.000 1127
1802 2014-10-05 00:00:00.000 1801 2014-10-09 00:00:00.000 1700
October 5, 2014 at 11:08 pm
Hi
Use this
select min(wan),max(wan),subscriber from retail group by subscriber having count(*)>1
October 5, 2014 at 11:23 pm
punnaiah.pendyala (10/5/2014)
HiUse this
select min(wan),max(wan),subscriber from retail group by subscriber having count(*)>1
Will not work if the earlier wan is higher than the later wan as noted in the previous posts.
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply