|
Hi,
How to calculate no of sundays in between two dates in sql.
Thanks in advance.
|
|
|
|
|
There is no system defined function for this task!
For this you have to write a userdefined function/stored procedure
For the function/store procedure send the start and end dates as input parameters
Declare a variable with value as 0 for count purposes
check the day of the start day using the datepart function
if weekday is sunday increase the count by 1
Continue the steps till the end date
return your count
gauthee
|
|
|
|
|
-- modified at 4:20 Monday 3rd September, 2007
sorry guys,
the query doesn't work....
Regards
J O H N ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
"Even eagles need a push." David McNally
|
|
|
|
|
hi i check it this is not working fine here is the example.
declare @startdate varchar(10)
declare @enddate varchar(10)
set @startdate='08/01/2007'
set @enddate='08/31/2007'
SELECT (DATEDIFF(day, @StartDate, @EndDate)/7) + (SELECT CASE WHEN (DATEDIFF(day, @StartDate, @EndDate)%7) > 0 THEN 1 ELSE 0 END)
This returns me 5 instead of 4.
|
|
|
|
|
hi here is the code for calculating the no of sudays between two given sqldates.
create PROCEDURE pr_displaysundays
-- Add the parameters for the stored procedure here
@fromdate varchar(10),
@todate varchar(10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
declare @diffDay int
declare @Cnt int
declare @Nos int
select @Diffday=datediff(day,@fromdate,@todate)
set @cnt=0
set @Nos=0
while @cnt<@diffday
begin
if datepart(dw,cast(convert(varchar(10),@fromdate,101) as datetime)+@cnt)=1
begin
set @Nos=@Nos+1
end
set @cnt=@cnt+1
end
print @Nos
END
|
|
|
|
|
I answered this same question back in April. For a psuedo-code solution, check out http://www.codeproject.com/script/comments/forums.asp?msg=1993362&forumid=1725&mode=all&userid=3403440#xx1993362xx[^]
For a SQL solution, see http://www.codeproject.com/script/comments/forums.asp?forumid=1725&mode=all&userid=3403440&select=1993823&df=100&mpp=50&fr=4038#xx1993823xx[^]
Here is some of my personal code I use within Access. I have tested it, and use it within my projects.
CREATE PROCEDURE numCertainDays(date1 DATE, date2 DATE, wkdy INTEGER) AS
SELECT CINT(@date2 - @date1) \ 7
+ IIF( WEEKDAY(@date2) < WEEKDAY(@date1), 1,
IIF( WEEKDAY(@date2) = @wkdy OR WEEKDAY(@date1) = @wkdy, 1, 0 ))
;
To get the number of Sundays, you would call it with wkdy=1.
David
|
|
|
|
|
from amployee table i want to get 10 employees who get the heigest 10 salaries . To do this how can i use the max function , or is there any other way to do this . 
|
|
|
|
|
Use the order by on the salaries instead. If you order by descending, then the highest salaries will be first. If you don't care about duplicate salaries this is the way to do it.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
thank you for the reply, but i want to get the first ten max salaries and don't care about duplication ..
|
|
|
|
|
Then, use select top 10 ... from ... order by salary desc.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
thank you very much ,I think it will help me ...;)
|
|
|
|
|
You are welcome
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
|
|
|
|
|
hi Prasadbuddhika
SELECT top 10 *
FROM Scoring
WHERE Score = (select Max(Score) from Scoring);
hope it helps
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
|
|
|
|
|
thank you very much ,I think it will help me...;)
|
|
|
|
|
Have you really run that?
This query does not return the desired result. It returns only the record with the max Score.
And up to ten records when multiple records hold the max score.
@Prasadbuddhika:
Furthermore this is a very basic requests, therefore I suggest investing some time in studying!
-^-^-^-^-^-
no risk no funk ................... please vote ------>
|
|
|
|
|
|prasadbuddhika wrote:
from amployee table i want to get 10 employees who get the heigest 10 salaries
And he is not complaining its working for him and it brings desired results for me too
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
|
|
|
|
|
I have been told the answer is very simple - but despite asking on many forums nobody has given me the answer.
Could you help me.
What would the code in VB.net, to - within a single msaccess database - (on a batch basis) examine a record in one table, and then dependant on the results of the examination(ie the value of a status field), add a new record to a second table, and then continue looping through this function until the end of the first table.
Sorry if it seems simple - but I think I am - simple that is...
:->
|
|
|
|
|
The approach of retrieving and looping through individual records is very time consuming when you consider that SQL is set based. Consider using an INSERT INTO based on a select instead. Here's an example:
INSERT INTO table2 (value1, value2, value3)
SELECT value1, value2, value3
FROM table1
WHERE status = 'Hello' This will insert all the records that match the criteria in table1 into table2.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I am schema qualifying a stored procedure when calling it such that if I have a database named 'MyDB1' and the stored procedure 'GetContacts' that belongs to 'dbo' schema, I can call it like:
EXEC MyDB1.dbo.GetContacts
However, I need to be able to dynamically call the stored procedure from different databases that that have the same stored procedure such that i can call them like:
EXEC MyDB1.dbo.GetContacts
EXEC MyDB2.dbo.GetContacts
EXEC MyDB3.dbo.GetContacts
Is there a way I can use a variable name in place of the database name such that I can call the stored procedure like:
DECLARE @DBName NVARCHAR(20)
SET @DBName = 'MyDB1'
EXEC @DBName.dbo.GetContacts
and so forth, ...
Or, what options do I have?
|
|
|
|
|
Hi,
you can use sp_executesql N'exec <stored_procedure_name>'
where <stored_procedure_name> is constructed as @DBName.dbo.GetContacts
You can also pass parameters on this way. Take a look at http://msdn2.microsoft.com/en-us/library/ms188001.aspx
Best regards,
DLM@bypsoft.com
|
|
|
|
|
Can anyone tel me what is the difference between Number datatype and integer datatype in Tsql and how much the capacity they are having??
prasannaarumugam
|
|
|
|
|
Do you mean numeric? If so, the numeric datatype can have a precision.
An int is stored in 4 bytes and has the capacity of -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). The numeric is functionally equivalent to the decimal datatype, and when maximum precision is applied, valid values range from - 10^38 +1 to 10^38 - 1.
All of this information was easily identifiable in SQL Server Books On-line or Google. Please use those as your first port of call next time and don't ask us to do your research for you.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I have several excel files(*.xls) that I'm extracting data from using Visual Basic and I'm having problems with my SQL statement. I would like to do a count & group by in the statement to input this data into a control sheet but I continue to get an error message when filling the dataset.
Error: An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in microsoft.visualbasic.dll
Code Snippet:
Function groupxls(ByRef xlfile As String, ByRef xlsfile As String)
Dim ex1 As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & "data source=" & xlfile & ";" & "Extended Properties=Excel 8.0;"
Dim conn1 As New OleDbConnection(ex1)
Dim dtset1 = New DataSet
Dim flnme As String
y = xlsfile.LastIndexOfAny(".")
flnme = xlsfile.Remove(y, 4)
Dim col1 As String = "sc_typname" '<-----Column to group by
Dim MyCommand1 As New OleDbDataAdapter("select count(*) from flnme group by col1", conn1)
MyCommand1.Fill(dtset1)
DataGrid1.Refresh()
DataGrid1.DataSource = dtset1.Tables(0)
conn1.Close()
conn1.Dispose()
End Function
If anyone can take the time to look at this for me please know that it will be very much appreciated! Thanks in advance! 
Swish
|
|
|
|
|
Swisher24 wrote: Dim col1 As String = "sc_typname" '<-----Column to group by
Dim MyCommand1 As New OleDbDataAdapter("select count(*) from flnme group by col1", conn1)
You are passing the name of the variable to the query string instead of the value of the variable, try the following:
Dim MyCommand1 As New OleDbDataAdapter("select count(*) from flnme group by " + col1, conn1)
|
|
|
|
|
Thanks aztekka!
I tried this sql statement and I'm still getting an Unhandled Exception Error. I'm thinking that VB 2003 doesn't like the statement somehow. I will continue to work on this and any more help that can be provided will be very appreciated.
Swish
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
