SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQl help...


T-SQl help...

Author
Message
etirem
etirem
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 517
Hi, I have the sql sample code and the below image has the ouput needed.. thanks in advance...
I`m working with RANKING functions but need some expertise...
IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(10))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(111, '2012-10-05 00:00:00.000','A'),
(111, '2012-10-18 00:00:00.000','C'),
(111, '2012-11-01 00:00:00.000','B'),
(111, '2012-11-01 00:00:00.000','C'),
(111, '2012-11-20 00:00:00.000','C'),
(111, '2012-12-09 00:00:00.000','C'),
(111, '2012-12-11 00:00:00.000','A'),
(111, '2013-02-21 00:00:00.000','B'),
(111, '2013-03-22 00:00:00.000','B'),
(111, '2013-03-22 00:00:00.000','C'),
(111, '2013-04-12 00:00:00.000','C'),
(111, '2013-04-26 00:00:00.000','B'),
(111, '2013-04-26 00:00:00.000','C'),
(222, '2012-02-13 00:00:00.000','C'),
(222, '2012-03-02 00:00:00.000','B'),
(222, '2012-06-16 00:00:00.000','C'),
(222, '2012-07-12 00:00:00.000','C'),
(222, '2013-04-26 00:00:00.000','B'),
(222, '2013-05-23 00:00:00.000','C'),
(222, '2013-07-11 00:00:00.000','C'),
(222, '2013-09-19 00:00:00.000','C'),
(222, '2013-09-20 00:00:00.000','A')

SELECT * FROM #tTable
DROP TABLE #tTable




The Groups are created based on CDate and Dept.

For the CID, the CDate is in ASC order and if Dept=C and the next row is Dept=C...and next row is also Dept=C is a Group. This means if Dept=C value comes one after the other it is a Group.



Attachments
Image.jpg (180 views, 260.00 KB)
kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3139 Visits: 2766
the information you have provided is not complete..
can you please tell us on what basis you are creating the groups?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
etirem
etirem
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 517
The Groups are created based on CDate and Dept.

For the CID, the CDate is in ASC order and if Dept=C and the next row is Dept=C...and next row is also Dept=C is a Group. This means if Dept=C value comes one after the other it is a Group.
etirem
etirem
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 517
etirem (9/21/2013)
The Groups are created based on CDate and Dept.

For the CID, the CDate is in ASC order and if Dept=C and the next row is Dept=C...and next row is also Dept=C is a Group. This means if Dept=C value comes one after the other it is a Group.

kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3139 Visits: 2766
etirem (9/21/2013)
The Groups are created based on CDate and Dept.

For the CID, the CDate is in ASC order and if Dept=C and the next row is Dept=C...and next row is also Dept=C is a Group. This means if Dept=C value comes one after the other it is a Group.

still not clear.....
can you please provide some more detailed information.

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5573 Visits: 35465
maybe something along these lines.....???



WITH cte AS
(
SELECT CID,
CDate,
Dept,
row_number() OVER (PARTITION BY cid,dept ORDER BY cid,cdate,dept) rn
FROM #tTable
)

SELECT CID,Cdate,Dept
FROM cte
WHERE rn = 3 AND dept = 'C'
ORDER by cid,cdate




edit...simplified

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

etirem
etirem
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 517
Sorry! May be I`m not explaining it properly.
One more CID=333 is added below SQL.
IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(10))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(333, '2012-01-14 00:00:00.000','A'),
(333, '2012-02-14 00:00:00.000','C'),
(333, '2012-03-14 00:00:00.000','B'),
(333, '2012-04-14 00:00:00.000','C'),
(333, '2012-08-15 00:00:00.000','C'),
(333, '2012-09-20 00:00:00.000','C'),
(333, '2012-10-26 00:00:00.000','B'),
(333, '2013-01-14 00:00:00.000','A'),
(333, '2013-02-14 00:00:00.000','C'),
(333, '2013-03-14 00:00:00.000','B'),
(333, '2013-04-14 00:00:00.000','C'),
(333, '2013-05-14 00:00:00.000','C')

SELECT * FROM #tTable
DROP TABLE #tTable



Note that the ORDER of the CDate is in ASC order and it should not be changed for that CID.

GROUP(s) is ONLY for Dept=C:
If Dept=C is followed by another Dept=C or more Dept=C values it is considered as GROUP 1 for that CID...
AND for GROUP 2 same logic..if Dept=C is followed by another Dept=C or more Dept=C values it is considered as GROUP 2 for that CID...
Output needed is the 2nd row of the GROUP 1.


Attachments
Image1.jpg (124 views, 138.00 KB)
J Livingston SQL
J Livingston SQL
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5573 Visits: 35465
etirem (9/22/2013)
Sorry! May be I`m not explaining it properly.
One more CID=333 is added below SQL.
IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(10))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(333, '2012-01-14 00:00:00.000','A'),
(333, '2012-02-14 00:00:00.000','C'),
(333, '2012-03-14 00:00:00.000','B'),
(333, '2012-04-14 00:00:00.000','C'),
(333, '2012-08-15 00:00:00.000','C'),
(333, '2012-09-20 00:00:00.000','C'),
(333, '2012-10-26 00:00:00.000','B'),
(333, '2013-01-14 00:00:00.000','A'),
(333, '2013-02-14 00:00:00.000','C'),
(333, '2013-03-14 00:00:00.000','B'),
(333, '2013-04-14 00:00:00.000','C'),
(333, '2013-05-14 00:00:00.000','C')

SELECT * FROM #tTable
DROP TABLE #tTable



Note that the ORDER of the CDate is in ASC order and it should not be changed for that CID.

GROUP(s) is ONLY for Dept=C:
If Dept=C is followed by another Dept=C or more Dept=C values it is considered as GROUP 1 for that CID...
AND for GROUP 2 same logic..if Dept=C is followed by another Dept=C or more Dept=C values it is considered as GROUP 2 for that CID...
Output needed is the 2nd row of the GROUP 1.



ok...please post the results you get when running my code....

thanks

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

etirem
etirem
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 517
J Livingston SQL (9/22/2013)
etirem (9/22/2013)
Sorry! May be I`m not explaining it properly.
One more CID=333 is added below SQL.
IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(10))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(333, '2012-01-14 00:00:00.000','A'),
(333, '2012-02-14 00:00:00.000','C'),
(333, '2012-03-14 00:00:00.000','B'),
(333, '2012-04-14 00:00:00.000','C'),
(333, '2012-08-15 00:00:00.000','C'),
(333, '2012-09-20 00:00:00.000','C'),
(333, '2012-10-26 00:00:00.000','B'),
(333, '2013-01-14 00:00:00.000','A'),
(333, '2013-02-14 00:00:00.000','C'),
(333, '2013-03-14 00:00:00.000','B'),
(333, '2013-04-14 00:00:00.000','C'),
(333, '2013-05-14 00:00:00.000','C')

SELECT * FROM #tTable
DROP TABLE #tTable



Note that the ORDER of the CDate is in ASC order and it should not be changed for that CID.

GROUP(s) is ONLY for Dept=C:
If Dept=C is followed by another Dept=C or more Dept=C values it is considered as GROUP 1 for that CID...
AND for GROUP 2 same logic..if Dept=C is followed by another Dept=C or more Dept=C values it is considered as GROUP 2 for that CID...
Output needed is the 2nd row of the GROUP 1.



ok...please post the results you get when running my code....

thanks



Thank you for your reply but if I have another CID=444 your code doesn`t work...

IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(10))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(444, '2013-01-14 00:00:00.000','C'),
(444, '2013-02-14 00:00:00.000','C'),
(444, '2013-03-14 00:00:00.000','B'),
(444, '2013-04-14 00:00:00.000','C'),
(444, '2013-05-14 00:00:00.000','C')

SELECT * from #tTable
DROP TABLE #tTable




Attachments
Image3.jpg (106 views, 57.00 KB)
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 3317
Hi

I think this covers what you want to do. I suspect the performance will be poor.
WITH groupit AS (
-- Group up the Depts on data
SELECT CID, CDate, Dept,
ROW_NUMBER() OVER (PARTITION BY CID ORDER BY CDate ASC) - ROW_NUMBER() OVER (PARTITION BY CID, Dept ORDER BY CDate ASC) GroupNum
FROM #tTable t
),
getDeptGC AS (
-- Do counts in the groups for dept C
SELECT CID, CDate, Dept, GroupNum,
COUNT(*) OVER (PARTITION BY CID, Dept, GroupNum) C
FROM groupit a
WHERE Dept = 'C'
),
getDeptR AS (
-- Number the rows in groups of more than 1
SELECT CID, CDate, Dept, GroupNum,
ROW_NUMBER() OVER (PARTITION BY CID, Dept ORDER BY CDate ASC) N
FROM getDeptGC
WHERE C > 1
)
SELECT CID, CDate, Dept FROM getDeptR WHERE N = 2
ORDER BY CID;


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search