CREATE PROC DayinMonthCount
@MonthDate datetime,
@day varchar(10)
AS
DECLARE @MonthStart datetime,@EndDate datetime
SELECT @MonthStart=DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0),@EndDate=DATEADD(mm,DATEDIFF(mm,0,@MonthDate)+1,0)-1

SELECT [Day],COUNT(*) AS daycnt
FROM dbo.CalendarTable(@MonthStart,@EndDate,0,0)
GROUP BY [Day]

quote:

---

Originally posted by GirishKumarSharma

Thank you very much for such a great help. Kindly let me know how do i execute and pass the variable.

I have created the procedure though.

When i tried to run using Management Studio's Query Analyzer windows :
USE [ScheduleTask]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[DayinMonthCount]
@MonthDate = N'09/11/2011',
@day = N'Sunday'

SELECT 'Return Value' = @return_value

GO
I got :
Friday 5
Monday 4
Saturday 4
Sunday 4
Thursday 5
Tuesday 4
Wednesday 4

I wish to order by too as starting from Sunday and ending at Saturday.

Regards
Girish Sharma

---

quote:

---

Originally posted by visakh16

something like

CREATE PROC DayinMonthCount
@MonthDate datetime,
@day varchar(10)
AS
DECLARE @MonthStart datetime,@EndDate datetime
SELECT @MonthStart=DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0),@EndDate=DATEADD(mm,DATEDIFF(mm,0,@MonthDate)+1,0)-1

SELECT [Day],COUNT(*) AS daycnt
FROM dbo.CalendarTable(@MonthStart,@EndDate,0,0)
GROUP BY [Day]

calendar table can be found in below link

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

NameofDay     Ch1, Ch2, Ch3, Ch4, Ch5
Wednesday     Yes       Yes

quote:

---

Originally posted by GirishKumarSharma

>how you want to pass variable?

I wish to pass the current date to the procedure and then in visual basic 6 i have one combo box with day names like :
Sunday, Monday.... Saturday.
User selected any day...
After that selection of day there will be 4/5 check boxes will be visible and in these check boxes user will check/uncheck the value of these check boxes.
These check boxex values will be stored in a table something like :

NameofDay     Ch1, Ch2, Ch3, Ch4, Ch5
Wednesday     Yes       Yes

Means, user has selected Wednesday and he checked the first and 3rd check box. Here 5 check boxes are visible because there are 5 wednesdays in the current month / given date from dtpicker control.
If user selected a day which is having 4 counts then 4 check boxes will be visible accordingly.

Please let me know, i am clear in my reply or not?

Regards
Girish Sharma

---

CREATE PROC DayinMonthCount
@MonthDate datetime,
@day varchar(10)
AS
DECLARE @MonthStart datetime,@EndDate datetime
SELECT @MonthStart=DATEADD(mm,DATEDIFF(mm,0,@MonthDate),0),@EndDate=DATEADD(mm,DATEDIFF(mm,0,@MonthDate)+1,0)-1

SELECT [Day],COUNT(*) AS daycnt
FROM dbo.CalendarTable(@MonthStart,@EndDate,0,0)
WHERE [Day]=@day
GROUP BY [Day]