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

Query Help Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 1:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:51 AM
Points: 112, Visits: 325
Hello

I have one task

Create table #x1
(
iSchoolCode int,
iSchoolYearCode int,
dtCalendarDate datetime,
iCalendarDayId int
)

drop table #x1

insert into #x1 values (301,2012,'2012-08-10 00:00:00.000',NULL)
insert into #x1 values (301,2012,'2012-08-11 00:00:00.000',NULL)
insert into #x1 values (301,2012,'2012-08-12 00:00:00.000',NULL)
insert into #x1 values (301,2012,'2012-08-13 00:00:00.000',4259)
insert into #x1 values (301,2012,'2012-08-14 00:00:00.000',4260)
insert into #x1 values (301,2012,'2012-08-15 00:00:00.000',4261)
insert into #x1 values (301,2012,'2012-08-16 00:00:00.000',4262)
insert into #x1 values (301,2012,'2012-08-17 00:00:00.000',NULL)
insert into #x1 values (301,2012,'2012-08-18 00:00:00.000',NULL)
insert into #x1 values (301,2012,'2012-08-19 00:00:00.000',4263)
insert into #x1 values (301,2012,'2012-08-20 00:00:00.000',NULL)
insert into #x1 values (301,2012,'2012-08-21 00:00:00.000',4264)
insert into #x1 values (301,2012,'2012-08-22 00:00:00.000',4265)
insert into #x1 values (301,2012,'2012-08-23 00:00:00.000',4266)


Now I need to display row number when iCalendarDayId is not null
so desired output will be

iSchoolCode	iSchoolYearCode	dtCalendarDate			iCalendarDayId	ROW_Number	
301 2012 2012-08-10 00:00:00.000 NULL NULL
301 2012 2012-08-11 00:00:00.000 NULL NULL
301 2012 2012-08-12 00:00:00.000 NULL NULL
301 2012 2012-08-13 00:00:00.000 4259 1
301 2012 2012-08-14 00:00:00.000 4260 2
301 2012 2012-08-15 00:00:00.000 4261 3
301 2012 2012-08-16 00:00:00.000 4262 4
301 2012 2012-08-17 00:00:00.000 NULL NULL
301 2012 2012-08-18 00:00:00.000 NULL NULL
301 2012 2012-08-19 00:00:00.000 4263 5
301 2012 2012-08-20 00:00:00.000 NULL NULL
301 2012 2012-08-21 00:00:00.000 4264 6
301 2012 2012-08-22 00:00:00.000 4265 7
301 2012 2012-08-23 00:00:00.000 4266 8
Post #1421827
Posted Tuesday, February 19, 2013 1:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:51 AM
Points: 112, Visits: 325
Hello

so far I got this

SELECT   iSchoolCode ,
iSchoolYearCode,
dtCalendarDate ,
ROW_NUMBER() OVER ( ORDER BY dtCalendarDate ) RowNum
FROM #x1
WHERE iCalendarDayId IS NOT NULL
UNION ALL
SELECT iSchoolCode ,
iSchoolYearCode,
dtCalendarDate ,
NULL RowNum
FROM #x1
WHERE iCalendarDayId IS NULL
order by dtCalendarDate

if someone has better way to do please let me know

Thanks
Post #1421841
Posted Wednesday, February 20, 2013 2:41 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 3:28 AM
Points: 672, Visits: 2,624
Hi

Another way without the UNION

Create table #x1
(
iSchoolCode int,
iSchoolYearCode int,
dtCalendarDate datetime,
iCalendarDayId int
)



insert into #x1 values (301,2012,'2012-08-10 00:00:00.000',NULL)
insert into #x1 values (301,2012,'2012-08-11 00:00:00.000',NULL)
insert into #x1 values (301,2012,'2012-08-12 00:00:00.000',NULL)
insert into #x1 values (301,2012,'2012-08-13 00:00:00.000',4259)
insert into #x1 values (301,2012,'2012-08-14 00:00:00.000',4260)
insert into #x1 values (301,2012,'2012-08-15 00:00:00.000',4261)
insert into #x1 values (301,2012,'2012-08-16 00:00:00.000',4262)
insert into #x1 values (301,2012,'2012-08-17 00:00:00.000',NULL)
insert into #x1 values (301,2012,'2012-08-18 00:00:00.000',NULL)
insert into #x1 values (301,2012,'2012-08-19 00:00:00.000',4263)
insert into #x1 values (301,2012,'2012-08-20 00:00:00.000',NULL)
insert into #x1 values (301,2012,'2012-08-21 00:00:00.000',4264)
insert into #x1 values (301,2012,'2012-08-22 00:00:00.000',4265)
insert into #x1 values (301,2012,'2012-08-23 00:00:00.000',4266)


SELECT *

FROM

#x1 X1

LEFT JOIN
(
SELECT
iCalendarDayId
,ROW_NUMBER() OVER ( ORDER BY dtCalendarDate ) RowNum
FROM #x1
WHERE
iCalendarDayId
IS NOT NULL
) AS Row
ON x1.iCalendarDayId = Row.iCalendarDayId

drop table #x1


Cheers

Andy


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1421995
Posted Wednesday, February 20, 2013 2:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:50 PM
Points: 1,867, Visits: 2,275
I was also going to write the script with using UNION ALL as yogi already described...

i think you get your answer now



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1421997
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse