select help between dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select help between dates

Author

Topic

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-04-08 : 04:45:23

HI all this is my query...

DECLARE @FromMonthId int
DECLARE @FromYearId int
DECLARE @ToMonthId int
DECLARE @ToYearId int
SET @FromMonthId =3
SET @FromYearId= 2008
SET @ToMonthId= 3
SET @ToYearId= 2009

DECLARE @fromdate datetime, @todate datetime

select @fromdate=convert(varchar,@FromYearID)+'-'+convert(varchar,@FromMonthID)+'-1'
select @todate=convert(varchar,@ToYearID)+'-'+convert(varchar,@ToMonthID)+'-1'
--SELECT
--MonthAtten.USERID

DECLARE @a int
DECLARE @b int
set @a=(SELECT
sum(MonthAtten.DaysInMonth)
from Staff_MonthAttendence as MonthAtten where convert(datetime,convert(varchar,MonthAtten.YearID)+'-'+convert(varchar,MonthAtten.MonthID)+'-1')
between @fromdate and @todate )
set @b=(SELECT
Count(HolidayDate)
from SurbhiCalender
WHERE HolidayDate between @fromdate and @todate)
SELECT
MonthAtten.UserId
,sum(Present) as Present
,sum(Absent) AS Absent
,@a AS TotalDays
,@b AS Holidays
,(@a-@b) as workingdays
FROM Staff_MonthAttendence AS MonthAtten

WHERE
convert(datetime,convert(varchar,MonthAtten.YearID)+'-'+convert(varchar,MonthAtten.MonthID)+'-1')
between @fromdate and @todate
group by
MonthAtten.USERID
--,MonthAtten.present
--,MonthAtten.absent

And the small portion of the total result is..
userid,present,absent,totaldays,holidays,workingdays
25 317.5 16.5 20700 59 20641
26 347.5 17.5 20700 59 20641
27 346 19 20700 59 20641

As i am passing the value of date between march 2008 to april 2009 as declared in the starting of the query..
But we can never have so many days in a year...
pls do help thw column of total days have some problem...

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-04-08 : 04:52:25

You should be doing COUNT here

set @a=(SELECT
sum(MonthAtten.DaysInMonth)
from Staff_MonthAttendence as MonthAtten where convert(datetime,convert(varchar,MonthAtten.YearID)+'-'+convert(varchar,MonthAtten.MonthID)+'-1')
between @fromdate and @todate )

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-04-08 : 04:54:25

even
select datediff(dd,@fromdate,@todate)

will give you the number of days between 2 dates

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 04:55:58

[code]DECLARE @FromMonthId int,
@FromYearId int,
@ToMonthId int,
@ToYearId int

SELECT @FromMonthId = 3,
@FromYearId = 2008,
@ToMonthId = 3,
@ToYearId = 2009

DECLARE @fromdate datetime,
@todate datetime,
@b int

SELECT @fromdate = convert(varchar(4), @FromYearID) + '-' + convert(varchar(2), @FromMonthID) + '-01',
@todate = convert(varchar(4), @ToYearID) + '-' + convert(varchar(2), @ToMonthID) + '-01'

SELECT @b = COUNT(HolidayDate)
FROM SurbhiCalender
WHERE HolidayDate BETWEEN @fromdate AND @todate

SELECT UserId,
sum(Present) as Present,
sum(Absent) AS Absent,
SUM(DaysInMonth) AS TotalDays,
@b AS Holidays,
SUM(DaysInMonth) - @b as workingdays
FROM Staff_MonthAttendence
WHERE 100 * YearID + MonthID BETWEEN 100 * @FromYearId + @FromMonthId AND 100 * @ToYearId + @ToMonthId
GROUP BY USERID[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 04:58:17

Your REAL problem is that you do not account for UserID when SUMming DaysInMonth...

E 12°55'05.63"
N 56°04'39.26"

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-04-08 : 05:00:38

Peso these are the results of your solution....
and total days cant be such a huge number...

UserId Present Absent TotalDays Holidays workingdays
----------- ---------------------- ---------------------- ----------- ----------- -----------
25 317.5 16.5 20700 113 20587
26 347.5 17.5 20700 113 20587
27 346 19 20700 113 20587
28 353 12 20700 113 20587
29 363 2 20700 113 20587
30 305.5 28.5 20700 113 20587
31 332.5 32.5 20700 113 20587

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 05:04:23

Try again (see post made 04/08/2009 : 04:55:58 ), after reading my explanation above posted 04/08/2009 : 05:00:38.

E 12°55'05.63"
N 56°04'39.26"

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-04-08 : 05:08:37

Peso Sir i did this after your suggestion but stillllll....:(:(

DECLARE @FromMonthId int
DECLARE @FromYearId int
DECLARE @ToMonthId int
DECLARE @ToYearId int
SET @FromMonthId =1
SET @FromYearId= 2008
SET @ToMonthId= 12
SET @ToYearId= 2008

DECLARE @fromdate datetime, @todate datetime

select @fromdate=convert(varchar,@FromYearID)+'-'+convert(varchar,@FromMonthID)+'-1'
select @todate=convert(varchar,@ToYearID)+'-'+convert(varchar,@ToMonthID)+'-1'
--SELECT
--MonthAtten.USERID

DECLARE @a int
DECLARE @b int
set @a=(datediff(dd,@fromdate,@todate) )
set @b=(SELECT
Count(HolidayDate)
from SurbhiCalender
WHERE HolidayDate between @fromdate and @todate)
SELECT
MonthAtten.UserId
,sum(Present) as Present
,sum(Absent) AS Absent
,@a AS TotalDays
,@b AS Holidays
,(@a-@b) as workingdays

FROM Staff_MonthAttendence AS MonthAtten

WHERE
convert(datetime,convert(varchar,MonthAtten.YearID)+'-'+convert(varchar,MonthAtten.MonthID)+'-1')
between @fromdate and @todate
group by
MonthAtten.USERID
--,MonthAtten.present
--,MonthAtten.absent

Results are..
UserId Present Absent TotalDays Holidays workingdays
----------- ---------------------- ---------------------- ----------- ----------- -----------
25 258.5 16.5 335 47 288
26 260.5 14.5 335 47 288
27 264 11 335 47 288
28 264 11 335 47 288
29 274 1 335 47 288
30 253.5 21.5 335 47 288
31 257.5 17.5 335 47 288
32 242 33 335 47 288
33 268.5 6.5 335 47 288

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 05:10:49

That is NOT my suggestion.

THIS is my suggestion

DECLARE @FromMonthId int,
	@FromYearId int,
	@ToMonthId int,
	@ToYearId int

SELECT	@FromMonthId = 3,
	@FromYearId = 2008,
	@ToMonthId = 3,
	@ToYearId = 2009

DECLARE	@fromdate datetime,
	@todate datetime, 
	@b int

SELECT	@fromdate = convert(varchar(4), @FromYearID) + '-' + convert(varchar(2), @FromMonthID) + '-01',
	@todate = convert(varchar(4), @ToYearID) + '-' + convert(varchar(2), @ToMonthID) + '-01'

SELECT	@b = COUNT(HolidayDate)
FROM	SurbhiCalender
WHERE	HolidayDate BETWEEN @fromdate AND @todate

SELECT		UserId,
		sum(Present) as Present,
		sum(Absent) AS Absent,
		SUM(DaysInMonth) AS TotalDays,
		@b AS Holidays,
		SUM(DaysInMonth) - @b as workingdays
FROM		Staff_MonthAttendence
WHERE		100 * YearID + MonthID BETWEEN 100 * @FromYearId + @FromMonthId AND 100 * @ToYearId + @ToMonthId
GROUP BY	USERID

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 05:13:41

The datediff approach is wrong because you miss all days in ending month except the 1st of ending month.

E 12°55'05.63"
N 56°04'39.26"

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-04-08 : 05:34:30

datediff is on right path,just need complete information what abcd is trying - just do
SELECT @A=DATEDIFF(dd,@fromdate,dateadd(mm,1,@todate))-1

SUM(DaysInMonth) is the reason for getting huge number 20700.
Better way you should provide the table structure.
what is in the column DaysInMonth?

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-04-08 : 05:44:10

again if you want to include the complete month for the @ToMonth then this will give you wrong result

SELECT @b = COUNT(HolidayDate)
FROM SurbhiCalender
WHERE HolidayDate BETWEEN @fromdate AND @todate

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

abcd
Yak Posting Veteran

92 Posts

Posted - 2009-04-08 : 06:02:31

Thank you mangal and peso...
finally i got it right,,,

DECLARE @FromMonthId int
DECLARE @FromYearId int
DECLARE @ToMonthId int
DECLARE @ToYearId int
SET @FromMonthId =1
SET @FromYearId= 2008
SET @ToMonthId= 12
SET @ToYearId= 2008

DECLARE @fromdate datetime, @todate datetime

select @fromdate=convert(varchar,@FromYearID)+'-'+convert(varchar,@FromMonthID)+'-1'
select @todate=convert(varchar,@ToYearID)+'-'+convert(varchar,@ToMonthID)+'-1'
--SELECT
--MonthAtten.USERID

DECLARE @a int
DECLARE @b int
set @A=DATEDIFF(dd,@fromdate,dateadd(mm,1,@todate))-1
set @b=(SELECT
Count(HolidayDate)
from SurbhiCalender
WHERE HolidayDate between @fromdate and @todate)
SELECT
MonthAtten.UserId
,sum(Present) as Present
,sum(Absent) AS Absent
,@a AS TotalDays
,@b AS Holidays
,(@a-@b) as workingdays

FROM Staff_MonthAttendence AS MonthAtten

WHERE
convert(datetime,convert(varchar,MonthAtten.YearID)+'-'+convert(varchar,MonthAtten.MonthID)+'-1')
between @fromdate and @todate
group by
MonthAtten.USERID
--,MonthAtten.present
--,MonthAtten.absent

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources