November 8, 2011 at 10:38 pm
Hi,
After searching a lot in google i think i have to create my login in sql forum to get SQL Server 2005 help.
I need a procedure which will accept a number i.e. month and will return me the number of days something like this :
Days Count
Sunday Number of Sundays in given month
Monday Number of Mondays in given month
Tuesday ....
Wednesday ....
Thursday
Friday
Saturday
I am using SQL Server 2005 and visual basic 6 for a project. In this project, user will select Sunday,Monday... from combo box and in another combo box which will be populated by above procedure by number of sunday,mondays respectively. I wish to pass the current month to the procedure and then respective calculations.
Please guide me, how do i get. I am sure, the code is available in the forum somewhere, but not finding (new bie).
select datename(dw,date_of_alloted) from mytable is returning me the day name, i just want to calculate the number of these days name of given month with stored procedure.
Thanks and Regards
Girish Sharma
November 8, 2011 at 11:09 pm
How about this?
DECLARE @CurrentDate DATETIME
,@StartOfMonth DATETIME
,@EndofMonth DATETIME;
SELECT @CurrentDate = GETDATE();
SELECT @StartOfMonth = DATEADD(MM,DATEDIFF(MM,0,@CurrentDate),0);
SELECT @EndofMonth = DATEADD(MM,DATEDIFF(MM,0,DATEADD(MM,1,@StartOfMonth)),-1);
WITH MaxNumberOfDaysInAnyMonth (N) AS
(
SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
UNION ALL SELECT 13
UNION ALL SELECT 14
UNION ALL SELECT 15
UNION ALL SELECT 16
UNION ALL SELECT 17
UNION ALL SELECT 18
UNION ALL SELECT 19
UNION ALL SELECT 20
UNION ALL SELECT 21
UNION ALL SELECT 22
UNION ALL SELECT 23
UNION ALL SELECT 24
UNION ALL SELECT 25
UNION ALL SELECT 26
UNION ALL SELECT 27
UNION ALL SELECT 28
UNION ALL SELECT 29
UNION ALL SELECT 30
),
DaysOfCurrentMonth AS
(
SELECT DATEADD(DD, N , @StartOfMonth ) DY
FROM MaxNumberOfDaysInAnyMonth
WHERE N <= DATEDIFF(DD,@StartOfMonth,@EndofMonth)
)
SELECT DATENAME(DW,DY) NameOfTheDay , COUNT(*) CountOfDays
FROM DaysOfCurrentMonth
GROUP BY DATENAME(DW,DY);
You may have to pass the current date instead of the current month to the SP.
November 9, 2011 at 12:05 am
Thank you very much for your reply and interest in my query.
Kindly let me know how do i
1.Code the stored procedure
2.pass the required variables for the SP
So that,
I may get the recordset something like this : (Order by too)
NameofDay DayCnt
Sunday 4
Monday 5
...
Saturday 4
Regards
Girish Sharma
November 9, 2011 at 12:25 am
gksharmaajmer (11/9/2011)
Kindly let me know how do i1.Code the stored procedure
2.pass the required variables for the SP
Buddy, this is called spoon feeding. The things u asked above are so so trivial and it must be you who should take steps to learn and not copy from whatever we provide. Please remember, no one can be held responsible if the code posted here screws your PROD databases.
Now that being said, please look at how i encapsulated the code to produce a SP out of it.
BEGIN
IF OBJECT_ID('GetCountOfDaysByDayNameInAMonth','P') IS NOT NULL
DROP PROCEDURE GetCountOfDaysByDayNameInAMonth
END
GO
CREATE PROCEDURE GetCountOfDaysByDayNameInAMonth
@CurrentDate DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @StartOfMonth DATETIME
,@EndofMonth DATETIME;
SELECT @CurrentDate = ISNULL(@CurrentDate, GETDATE());
SELECT @StartOfMonth = DATEADD(MM,DATEDIFF(MM,0,@CurrentDate),0);
SELECT @EndofMonth = DATEADD(MM,DATEDIFF(MM,0,DATEADD(MM,1,@StartOfMonth)),-1)
BEGIN TRY
; WITH MaxNumberOfDaysInAnyMonth (N) AS
(
SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
UNION ALL SELECT 13
UNION ALL SELECT 14
UNION ALL SELECT 15
UNION ALL SELECT 16
UNION ALL SELECT 17
UNION ALL SELECT 18
UNION ALL SELECT 19
UNION ALL SELECT 20
UNION ALL SELECT 21
UNION ALL SELECT 22
UNION ALL SELECT 23
UNION ALL SELECT 24
UNION ALL SELECT 25
UNION ALL SELECT 26
UNION ALL SELECT 27
UNION ALL SELECT 28
UNION ALL SELECT 29
UNION ALL SELECT 30
),
DaysOfCurrentMonth AS
(
SELECT DATEADD(DD, N , @StartOfMonth ) DY
FROM MaxNumberOfDaysInAnyMonth
WHERE N <= DATEDIFF(DD,@StartOfMonth,@EndofMonth)
)
SELECT DATENAME(DW,DY) NameOfTheDay
,COUNT(*) CountOfDays
FROM DaysOfCurrentMonth
GROUP BY DATENAME(DW,DY)
ORDER BY CASE DATENAME(DW,DY)
WHEN 'Sunday' THEN 1
WHEN 'Monday' THEN 2
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 4
WHEN 'Thursday' THEN 5
WHEN 'Friday' THEN 6
WHEN 'Satureday' THEN 7
END ;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS [ERROR_MESSAGE]
END CATCH
END;
DISCLAIMER: Untested code; please test it , test it , test it till you find no risks/breakages are found. Deploy it in PROD at your own risk.
Sorry for the abrasive words, am just trying to help u learn 🙂
November 9, 2011 at 12:31 am
Kindly let me know how do i
1.Code the stored procedure
2.pass the required variables for the SP
So that,
I may get the recordset something like this : (Order by too)
Sorry Girish but we don't encourage Spoon Feeding here. We recommend following reading to you.
CREATE PROCEDURE (Transact-SQL)
November 9, 2011 at 12:44 am
"Spoon feeding"
yes, i know here i am asking spoon feeding, why, because :
1.I am not SQL Server DBA, i am Oracle DBA and here i am taking participate in my friend's visual basic project as a helper, he don't know :
1.How to search in google smartly!!!
2.How to write in forum and replies.
At this moment, he is sitting besides me and watching all the activies, how to get the help not spoon feeding and understanding that what happens if someone ask spoon feeding.
I am 100% agree with you that cost free spoon feeding makes "person" lazy and dumb, but it don't applies to the babies; because everyone on the earth had been spoon feeding by his/her most Hon'ble Mother/Father.
Any way, Thank you very much.
Best Regards
Girish Sharma
November 9, 2011 at 12:49 am
Here it is code and error please :
Dim cmddata As ADODB.Command
Dim cnConnection As ADODB.Connection
Dim rsdata As ADODB.Recordset
Set cmddata = New ADODB.Command
Set rsdata = New ADODB.Recordset
Set cnConnection = New ADODB.Connection
cnConnection.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;pwd=girish;Initial Catalog=ScheduleTask;Data Source=HOMEPC-83810B7F\MYSERVER"
cnConnection.Open
cmddata.ActiveConnection = cnConnection
cmddata.CommandText = "GetCountOfDaysByDayNameInAMonth"
cmddata.CommandType = adCmdStoredProc
cmddata.Parameters.Refresh
cmddata.Parameters("CurrentDate") = Date
rsdata.CursorType = adOpenDynamic
rsdata.CursorLocation = adUseClient
rsdata.Open cmddata
Item cannot be found in the collection corresponding to the requested name or ordinal.
Where i am wrong, i am passing the current date to the SP. I know this is not DBA's task, but if you please help.
Regards
Girish Sharma
November 9, 2011 at 12:55 am
Item cannot be found in the collection corresponding to the requested name or ordinal.
It usually appears when your resultset (SQL) doesn't match with Recordset (VB). Some columns or column order is wrong. I was doing VB programming long back and have Faded Memories only. I can’t help you more.
November 9, 2011 at 1:00 am
Yes, now i solved the error, just adding @ :
cmddata.Parameters("@CurrentDate") = Date
Thank you very much for your great help. Today i learnt something for SQL Server too....
First time seen, Management Studio and all other stuffs.
Regards
Girish Sharma
November 9, 2011 at 1:05 am
Most Welcome! 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply