February 19, 2013 at 1:20 pm
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
iSchoolCodeiSchoolYearCodedtCalendarDateiCalendarDayIdROW_Number
30120122012-08-10 00:00:00.000NULLNULL
30120122012-08-11 00:00:00.000NULLNULL
30120122012-08-12 00:00:00.000NULLNULL
30120122012-08-13 00:00:00.00042591
30120122012-08-14 00:00:00.00042602
30120122012-08-15 00:00:00.00042613
30120122012-08-16 00:00:00.00042624
30120122012-08-17 00:00:00.000NULLNULL
30120122012-08-18 00:00:00.000NULLNULL
30120122012-08-19 00:00:00.00042635
30120122012-08-20 00:00:00.000NULLNULL
30120122012-08-21 00:00:00.00042646
30120122012-08-22 00:00:00.00042657
30120122012-08-23 00:00:00.00042668
February 19, 2013 at 1:58 pm
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
February 20, 2013 at 2:41 am
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
February 20, 2013 at 2:55 am
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply