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

How to write a Query to get related result Expand / Collapse
Author
Message
Posted Wednesday, February 5, 2014 8:18 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
dwain.c (2/5/2014)
SQLRNNR (2/5/2014)

Nice solution.



Actually, I am not too proud to admit that solution stunk up the house! Not sure what I was thinking there.



Well, it worked with limited information. Besides, tuning the query could have been a good exercise for the OP.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1538452
Posted Thursday, February 6, 2014 2:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:28 AM
Points: 48, Visits: 273
Thank you so much...it help me lot....

Big thankful to all the members....

Thanks,
Post #1538871
Posted Thursday, February 6, 2014 2:53 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
Good to hear. Glad there was some great stuff that worked for you on this thread.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1538900
Posted Thursday, February 6, 2014 5:23 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:53 AM
Points: 3,422, Visits: 5,368
suresh0534 (2/6/2014)
Thank you so much...it help me lot....

Big thankful to all the members....

Thanks,


I'm glad to hear it helped too. It got me to thinking that this is sort of a common problem and what kinds of business cases need the support of this kind of query.

Too bad you're not using SQL 2012. The LEAD function there is your ticket to the fastest possible solution.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1538951
Posted Friday, February 7, 2014 4:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:28 AM
Points: 48, Visits: 273
One More Small Issue can you help...

create table #table (id int, cid int , startdate datetime)
insert #table

select 1, 100, '02/23/2014'
union select 1, 100, '06/25/2013'
union select 1, 100, '06/04/2013'
union select 1, 100, '06/17/2013'
union select 2, 200, '08/9/2013'
union select 2, 200, '08/3/2013'

union select 4, 600, '06/11/2013'
union select 4, 600, '06/18/2013'
union select 4, 600, '06/17/2013'
union select 4, 600, '09/17/2013'
union select 4, 600, '01/20/2014'
union select 4, 600, '01/24/2014'

SELECT id, cid, StartDate
,EndDate=CASE
WHEN DATEPART(year, StartDate) = DATEPART(year, GETDATE()) THEN NULL
WHEN DATEPART(year, EndDate) = DATEPART(year, StartDate) AND EndDate <> StartDate THEN EndDate
ELSE DATEADD(year, 1, DATEADD(year, DATEDIFF(year, 0, StartDate), 0))-1
END
FROM #table a
OUTER APPLY
(
SELECT TOP 1 StartDate-1
FROM #table b
WHERE a.id = b.id AND a.cid = b.cid AND a.startdate < b.startdate
ORDER BY b.startdate
) b(EndDate)
ORDER BY id, cid, StartDate;

id	cid	StartDate	EndDate
1 100 2013-06-04 00:00:00.000 2013-06-16 00:00:00.000
1 100 2013-06-17 00:00:00.000 2013-06-24 00:00:00.000
1 100 2013-06-25 00:00:00.000 2013-12-31 00:00:00.000
1 100 2014-02-23 00:00:00.000 NULL
2 200 2013-08-03 00:00:00.000 2013-08-08 00:00:00.000
2 200 2013-08-09 00:00:00.000 2013-12-31 00:00:00.000
4 600 2013-06-11 00:00:00.000 2013-06-16 00:00:00.000
4 600 2013-06-17 00:00:00.000 2013-12-31 00:00:00.000 --This End Date should Come 2013-06-17 But it showing 2013 12-31
4 600 2013-06-18 00:00:00.000 2013-09-16 00:00:00.000
4 600 2013-09-17 00:00:00.000 2013-12-31 00:00:00.000
4 600 2014-01-20 00:00:00.000 NULL
4 600 2014-01-24 00:00:00.000 NULL

Remaining Data Every Thing Perfect...only that one has Issue...

Thanks,
Post #1539435
Posted Friday, February 7, 2014 4:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:28 AM
Points: 48, Visits: 273
One More Small Issue can you help...

create table #table (id int, cid int , startdate datetime)
insert #table

select 1, 100, '02/23/2014'
union select 1, 100, '06/25/2013'
union select 1, 100, '06/04/2013'
union select 1, 100, '06/17/2013'
union select 2, 200, '08/9/2013'
union select 2, 200, '08/3/2013'

union select 4, 600, '06/11/2013'
union select 4, 600, '06/18/2013'
union select 4, 600, '06/17/2013'
union select 4, 600, '09/17/2013'
union select 4, 600, '01/20/2014'
union select 4, 600, '01/24/2014'

SELECT id, cid, StartDate
,EndDate=CASE
WHEN DATEPART(year, StartDate) = DATEPART(year, GETDATE()) THEN NULL
WHEN DATEPART(year, EndDate) = DATEPART(year, StartDate) AND EndDate <> StartDate THEN EndDate
ELSE DATEADD(year, 1, DATEADD(year, DATEDIFF(year, 0, StartDate), 0))-1
END
FROM #table a
OUTER APPLY
(
SELECT TOP 1 StartDate-1
FROM #table b
WHERE a.id = b.id AND a.cid = b.cid AND a.startdate < b.startdate
ORDER BY b.startdate
) b(EndDate)
ORDER BY id, cid, StartDate;

id	cid	StartDate	EndDate
1 100 2013-06-04 00:00:00.000 2013-06-16 00:00:00.000
1 100 2013-06-17 00:00:00.000 2013-06-24 00:00:00.000
1 100 2013-06-25 00:00:00.000 2013-12-31 00:00:00.000
1 100 2014-02-23 00:00:00.000 NULL
2 200 2013-08-03 00:00:00.000 2013-08-08 00:00:00.000
2 200 2013-08-09 00:00:00.000 2013-12-31 00:00:00.000
4 600 2013-06-11 00:00:00.000 2013-06-16 00:00:00.000
4 600 2013-06-17 00:00:00.000 2013-12-31 00:00:00.000 --This End Date should Come 2013-06-17 But it showing 2013 12-31
4 600 2013-06-18 00:00:00.000 2013-09-16 00:00:00.000
4 600 2013-09-17 00:00:00.000 2013-12-31 00:00:00.000
4 600 2014-01-20 00:00:00.000 NULL
4 600 2014-01-24 00:00:00.000 NULL

Remaining Data Every Thing Perfect...only that one has Issue...

Thanks,
Post #1539437
Posted Friday, February 7, 2014 4:38 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
That end date was one of the questions around the requirements that we already asked about.

How do you know that the end date should be 12/31 or 6/17 when there is no future start date in that classification (cid 100)?




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1539438
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse