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 12»»

T-SQl help... Expand / Collapse
Author
Message
Posted Saturday, September 21, 2013 11:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 12:03 PM
Points: 74, Visits: 387
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.




  Post Attachments 
Image.jpg (176 views, 260.24 KB)
Post #1497153
Posted Saturday, September 21, 2013 12:49 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,890, Visits: 2,329
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/
Post #1497155
Posted Saturday, September 21, 2013 1:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 12:03 PM
Points: 74, Visits: 387
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.
Post #1497158
Posted Saturday, September 21, 2013 4:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 12:03 PM
Points: 74, Visits: 387
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.
Post #1497171
Posted Saturday, September 21, 2013 9:55 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,890, Visits: 2,329
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/
Post #1497178
Posted Sunday, September 22, 2013 5:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:16 AM
Points: 1,920, Visits: 19,336
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
Post #1497200
Posted Sunday, September 22, 2013 2:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 12:03 PM
Points: 74, Visits: 387
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.



  Post Attachments 
Image1.jpg (120 views, 138.29 KB)
Post #1497231
Posted Sunday, September 22, 2013 2:34 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:16 AM
Points: 1,920, Visits: 19,336
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
Post #1497233
Posted Sunday, September 22, 2013 2:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 12:03 PM
Points: 74, Visits: 387
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



  Post Attachments 
Image3.jpg (101 views, 57.39 KB)
Post #1497235
Posted Sunday, September 22, 2013 3:35 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:57 PM
Points: 1,023, Visits: 3,069
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;

Post #1497240
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse