February 6, 2007 at 9:26 am
create table a
(ids int
startdate smalldatetime)
insert into a values(1, '12/2/2006')
insert into a values(2, '11/15/2006')
insert into a values(3, '1/3/2007')
insert into a values(4, '2/9/2006')
I need the report which shows how many days for any month if the user enter.
For Example user enter month 2 and year 2006
Output will looks like
1 28 ( if the startdate is before then Just total days in that month)
2 28 (above explnation)
4 20 (b'cas feb almost 9 days are gone)
Thanks
NIta
February 6, 2007 at 10:16 am
Your example output does not seem to make much sense. You may want something along the lines of:
-- *** Start of test data ***
DECLARE @t TABLE
(
UserID int NOT NULL PRIMARY KEY
,StartDate smalldatetime NOT NULL
)
INSERT INTO @t
SELECT 1, '20061202' UNION ALL -- StartDate after Feb 2006
SELECT 2, '20061115' UNION ALL -- StartDate after Feb 2006
SELECT 3, '20070103' UNION ALL
SELECT 4, '20060209' UNION ALL
SELECT 5, '20060104'
-- *** End of test data ***
DECLARE @Month int
,@Year int
,@MonthStart smalldatetime
,@MonthEnd smalldatetime
-- These 2 can be parameters in a SP.
SELECT @Month = 2
,@Year = 2006
SELECT @MonthStart = CAST(@Year AS char(4)) + REPLACE(STR(@Month, 2), ' ', '0') + '01'
,@MonthEnd = CASE @Month
WHEN 12
THEN CAST(@Year + 1 AS char(4)) + '0101'
ELSE CAST(@Year AS char(4)) + REPLACE(STR(@Month + 1, 2), ' ', '0') + '01'
END
SELECT UserID
,CASE
WHEN StartDate < @MonthStart
THEN DATEDIFF(d, @MonthStart, @MonthEnd)
ELSE DATEDIFF(d, StartDate, @MonthEnd)
END AS DaysInMonth
FROM @t
WHERE StartDate < @MonthEnd
February 6, 2007 at 10:24 am
Thanks Ken,
Pretty Much what I wanted thanks for you valuable help, this was basically for one of the report.
Nita
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply