February 15, 2012 at 7:59 am
the thing is -:
SELECT count(HolidayDate)as Count,Datename(month,HolidayDate) as Months
FROM tblHoliday_Master
where HolidayDate
between ''+CONVERT(nvarchar, '2012-01-01', 110) +'' and ''+CONVERT(nvarchar, '2012-12-01', 110) +''
GROUP BY Datename(month,HolidayDate) order by Months desc
the data am getting is
5-->jan
2-->feb
;WITH
MY_OTHER_CTE AS
( SELECT TOP (DATEDIFF(mm,
DATEADD(mm,DATEDIFF(mm,0,@DateStart),0), --First of start month
DATEADD(mm,DATEDIFF(mm,0,@DateEnd)+1,0))) --First of month after end month
N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT
NumberOfDays = DATEPART (dd, DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N,0)-1 )
FROM MY_OTHER_CTE t
this query gives me ..
31-->january
29--feb
i wana substract 31 -5 to get
26-->jan
27-feb
am binding both query
any other way plz let me know ....nding togthter to one
February 15, 2012 at 8:08 am
sqlcentral2 (2/15/2012)
the thing is -:
Your question is totally unclear. Can you try to explain clearly what it is you want to accomplish. It would probably be best to post ddl (create table scripts), sample data (insert statements) and desired results based on the sample data.
Take a look at the first link in my signature for best practices on posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 15, 2012 at 8:19 am
Am creating a query such that
name block Janurary Feburary
ksh IT 24 25
NOW I WANT to add 2 more columns
name block Janurary Feburary Janurary Feburary
ksh IT 24 25 x y
the x and y data
data is calulated based on
x = 31 days from january - sundays on januarys
y = same goes for january
in order to get 31 days am using this query
;WITH
MY_OTHER_CTE AS
( SELECT TOP (DATEDIFF(mm,
DATEADD(mm,DATEDIFF(mm,0,@DateStart),0), --First of start month
DATEADD(mm,DATEDIFF(mm,0,@DateEnd)+1,0))) --First of month after end month
N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT
NumberOfDays = DATEPART (dd, DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N,0)-1 )
FROM MY_OTHER_CTE t
and pubic holildy i have taken the above query
how can i do it .
February 15, 2012 at 8:33 am
Well that is even less clear than your original post. I assume there is a language barrier but step back and ask yourself if anybody could possibly understand what you are looking for based on what you posted. It seems you may need to use a calendar table. There is a great article about their usage here. http://www.sqlservercentral.com/articles/T-SQL/70482/[/url]
If that is now what you are looking for you need to post some clear requirements.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 15, 2012 at 9:31 am
I agree with Sean that you'll probably want to use a calendar table.
There are also problems with your posted code. You're trying to convert a date to nvarchar—which is a bad idea anyhow—but what you are actually doing is converting a char string to nvarchar. The formatting parameter is ignored when converting from char to nvarchar as you can see from the following code.
SELECT CONVERT(nvarchar, '2012-01-01', 110)
,CONVERT(nvarchar, '2012-01-01', 100)
,CONVERT(nvarchar, '2012-01-01', 101)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 15, 2012 at 9:48 am
sorry for everything
SEE THE HAVE A QUERY LIKE
Name Block Januar(Hours)
Ksh IT 25
I WANT to add more columns now based on this hours called jan(%centage)
Name Block Januar(Hours) jan(%perce)
Ksh IT 44 24
the formula for this percent is
januar = days 31
jan = holiday = 5
den
am using jan % = (31-5)*100 / 44 =24
so now jan(%)
24
now are u getting ???
how can i caluate these 24% ????thats my question
am using 2 dates start_date and end_Date
start_date = '2012-01-01'
end_Date ='2012-01-31'
my months are all dynamic .... so my start_Date and end_Date will alwwzzzz change
February 15, 2012 at 9:55 am
sorry for everything
SEE THE HAVE A QUERY LIKE
Name....Block.......January(Hours)
Jhon.....ITEBI.......44
I WANT to add more columns now based on this hours called jan(%centage)
Name...Block.... Januar(Hours) jan(%perce)
JhonY...TEBI.......44 ........................24
the formula for this percent is
januar = days 31
jan = holiday = 5
den
am using jan % = (31-5)*100 / 44 =24
so now jan(%) =24
now are u getting ???
how can i caluate these 24% ????thats my question
am using 2 dates start_date and end_Date
start_date = '2012-01-01'
end_Date ='2012-01-31'
my months are all dynamic .... so my start_Date and end_Date will alwwzzzz change
February 15, 2012 at 10:36 am
Are you trying to get the (workdays versus holydays+sundays) percentual between two dates?
February 15, 2012 at 10:41 am
yessss! for that i have pasted my query above in that am gettiing 2 result set but i dont know how to bind them 2 gther now got it
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply