March 5, 2012 at 11:54 am
i am just updating this topic.bcz i was not able to explain my problem earlier.i hope this might better explain my problem.
create table wrkdate(date datetime,
workday nvarchar(4),
NoWorkDaysInWeek nvarchar(10),
NoWorkdaysinMonth nvarchar(10),
NoWorkdaysInQtr nvarchar(10),
Noofworkdaysinyr nvarchar(10)
)
Insert into wrkdate(date,workday) values('2012-03-04 ','N'),
('2012-03-05','Y'),
('2012-03-05','y'),
('2012-03-06','y'),
('2012-03-07','y'),
('2012-03-08','Y'),
('2012-03-09','Y'),
('2012-03-10','N'),
('2012-03-11','N'),
('2012-03-12','Y'),
('2012-03-13','Y')
select * from wrkdate
/* In main table i have date column which has 1000 of dates from 2005 -today's date,
workday column has 'Y'and 'N' which means if DATE is working day then it is 'Y' else'N'
even we can see that 2012-03-04 is sunday that's why it is 'N' and for next 5 days it is 'y'
Now i want to calculate NoWorkDaysinWeek means where workday='Y' and similarly all other column based on where workday='Y'
bcz it will give me workdays in week,month,Qtr,year
*/
insert into wrkdate values('2012-03-04 ','N',5,22,62,252),
('2012-03-05','Y',5,22,62,252),
('2012-03-05','y',5,22,62,252),
('2012-03-06','y',5,22,62,252),
('2012-03-07','y',5,22,62,252),
('2012-03-08','Y',5,22,62,252),
('2012-03-09','Y',5,22,62,252),
('2012-03-10','N',5,22,62,252),
('2012-03-11','N',5,22,62,252),
('2012-03-12','Y',5,22,62,252),
('2012-03-13','Y',5,22,62,252)
select * from wrkdate
March 5, 2012 at 12:02 pm
Hi
not sure about your requirements...are you looking to build a "calendar" table?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 5, 2012 at 12:26 pm
i want the result in this way
insert into dt3 values(2012-03-04,'N','4','21','62','251'),
(2012-03-03,'N','4,'21','62','251')
March 5, 2012 at 12:58 pm
weston_086 (3/5/2012)
i want the result in this wayinsert into dt3 values(2012-03-04,'N','4','21','62','251'),
(2012-03-03,'N','4,'21','62','251')
to clarify
this is your "table"
CREATE TABLE dt3
(
dates DATETIME,
workday NVARCHAR(10),
numdaysweek NVARCHAR(10),
numdaysmonth NVARCHAR(10),
numdaysqtr NVARCHAR(10),
numdaysyear NVARCHAR(10)
)
please state rules for each column... I can hazard a guess...but would be much easier if you explained 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 5, 2012 at 1:18 pm
Weeks usually have 7 days.
Do you have some special meaning for the number of days in a week?
March 5, 2012 at 2:29 pm
ya..this is my table
CREATE TABLE dt3
(
dates DATETIME,
workday NVARCHAR(10),
numdaysweek NVARCHAR(10),
numdaysmonth NVARCHAR(10),
numdaysqtr NVARCHAR(10),
numdaysyear NVARCHAR(10)
)
March 5, 2012 at 2:35 pm
weston_086 (3/5/2012)
ya..this is my tableCREATE TABLE dt3
(
dates DATETIME,
workday NVARCHAR(10),
numdaysweek NVARCHAR(10),
numdaysmonth NVARCHAR(10),
numdaysqtr NVARCHAR(10),
numdaysyear NVARCHAR(10)
)
so...as asked previously
"please state rules for each column... I can hazard a guess...but would be much easier if you explained "
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 5, 2012 at 2:39 pm
ya. number of days in week means =monday to friday(working days).and for that in my table column workday has 'Y' or 'N'.where y=monday to friday and 'N' means saturday, sunday i have upated column workday by that logic so now from workday in want to update other columns.
one of logic which i got to
update numdaysweek = 'count(date) where workday = 'y' group by year num + weeknum.;
numdaysmonth='count(date) where workday = 'y' group by yearnum + weeknum;
numdaysqtr='count(date) where workday = 'y' group by yearnum and qtrnum;
numdaysyear='count(date) where workday = 'y' group by yearnum;
so that's how i want them
March 5, 2012 at 2:43 pm
There are 7 days in a week, every week. So that one is easy. Why not just store these values in a separate table as a reference table? Like this:
CREATE TABLE calendarReference (dateStart date, dateEnd date, monthYear int, monthQuarter tinyint, numFullWksMo tinyint, numDaysMo tinyint)
or something similar... Calculating these RBAR is a waste.
Jared
CE - Microsoft
March 5, 2012 at 3:46 pm
I do this in all my applications. That is why I know this. It is known as Calendar Tables
very useful for analysisng anything which has dates by just joining it on dates
The period column has to be poulated according to the Application need
CREATE TABLE [dbo].[Dates](
[Date] [datetime] NULL,
[DayType] [varchar](2) NULL,
[DayOfWeek] [smallint] NULL,
[WeekNo] [smallint] NULL,
[Period] [smallint] NULL -- To populate later with application specific periods
) ON [PRIMARY]
declare
@nYear Int,
@dStartDate dateTime,
@dEndDate dateTime,
@dWeekStartDate dateTime
set @nYear = 2012 -- we are doing it for year 2012
set @dStartDate = Str(@nYear)+'-01-01 00:00:00';
set @dEndDate = Str(@nYear)+'-12-31 00:00:00';
set @dWeekStartDate = '1999-12-26 00:00:00'
Delete from dates where date>=@dStartDate and Date<=@dEndDate;
With CTEDates (CalcDate) AS(
SELECT top (DateDiff (dd,@dStartDate,@dEndDate) + 1 )
DATEADD(dd, DateDiff(dd,0,@dStartDate) + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),0)
FROM SYS.ALL_COLUMNS AC1
CROSS JOIN SYS.ALL_COLUMNS AC2)
INSERT INTO Dates (Date,dayOfWeek,WeekNo,DayType)
SELECT CalcDate,DatePart(Weekday, CalcDate),DateDiff(d,@dWeekStartDate,Calcdate)/7 , 'dd' =
case
when (DatePart(Weekday, CalcDate)=1) then 'WE'
when (DatePart(Weekday, CalcDate)=7) then 'WE'
else 'WD'
end
FROM CTEDATES Order By CalcDate;
Select * from dates
March 5, 2012 at 3:54 pm
Maybe not the complete answer to the original question
but it gives you a table from you can get the answer to the original question
I am new to this
can someone tell me how to insert code in scrollable window
Dont tell me RTFM
March 5, 2012 at 5:19 pm
siva 20997 (3/5/2012)
can someone tell me how to insert code in scrollable windowDont tell me RTFM
Well, being that by typing in the text here it will automatically change the display, look to the of the text box when you are replying. There is a box that says "IFCode Shortcuts" and you will see a block that says code="sql" and /code in brackets. That's the tag you use. Also, make sure to have carriage returns between statements if you want them to not have to scroll left and right.
Jared
CE - Microsoft
March 5, 2012 at 5:30 pm
I thought I will give it a go
--Thanks
March 5, 2012 at 11:50 pm
weston_086 (3/5/2012)
ya. number of days in week means =monday to friday(working days).and for that in my table column workday has 'Y' or 'N'.where y=monday to friday and 'N' means saturday, sunday i have upated column workday by that logic so now from workday in want to update other columns.one of logic which i got to
update numdaysweek = 'count(date) where workday = 'y' group by year num + weeknum.;
numdaysmonth='count(date) where workday = 'y' group by yearnum + weeknum;
numdaysqtr='count(date) where workday = 'y' group by yearnum and qtrnum;
numdaysyear='count(date) where workday = 'y' group by yearnum;
so that's how i want them
does the following give you some ideas ? (untested)
SELECT [date],
row_number() over(partition by Datepart (MONTH, [date]), Datepart (YEAR, [date]) order by [date]) rn
FROM dt3
WHERE (workday = 'y')
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply