week report wrong values gettin

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 2000 Forums

Transact-SQL (2000)

week report wrong values gettin

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-10-09 : 04:01:59

hello friends

i prepared weekly report like

create proc RPT_WeeklyTest -- @StDate = '2006/08/04',@EdDate = '2006/09/25'
@Day int = NULL,
@StDate varchar(12) = NULL,
@EdDate varchar(12) = NULL
AS

declare @DFormat1 varchar(10)
set @DFormat1 = ''
declare @DFormat2 varchar(10)
set @DFormat2 = ''
declare @F2 varchar(12)
set @F2 = ''
declare @I1 int
set @I1 = ''
declare @I int
set @I = ''
declare @F varchar(12)
set @F = ''
declare @F1 varchar(12)
set @F1 = ''
declare @F3 varchar(12)
set @F3 = ''

truncate table AF_Weekly_01T
truncate table AF_Weekly_01T_01

if (@StDate = '' and @EdDate = '') OR (@StDate is null and @EdDate is null)
begin
insert into AF_Weekly_01T(dtdate)
select date from AFDate_Calender where date between (getdate() - @Day) And (getdate())
end
if (@Day = '' or @Day is null) and (@StDate <> '' and @EdDate <> '')
begin
insert into AF_Weekly_01T(dtdate)
select date from AFDate_Calender where date between @StDate And @EdDate
end

declare @D as datetime
declare @MonChk as datetime
declare CurDate cursor for select dtdate from AF_Weekly_01T where intdate%5 = 0
open CurDate
fetch next from CurDate into @D

while @@Fetch_status = 0
begin

print convert(varchar(12),@D,101)
set @Dformat1 = (cast(day(@D) as varchar(2)) + '/' + cast(Month(@D) as varchar(2)))
print @Dformat1

set @Dformat2 = (cast(day(@D-4) as varchar(2)) + '/' + cast(Month(@D) as varchar(2)))
print @Dformat2

insert AF_Weekly_01T_01
select (select @Dformat2+'-'+@DFormat1),(select count(tktsubmittedby) from tickets where (datecreated between (@D - 4) and @D) and TktSubmittedBy = 'U'),
(select count(tktsubmittedby) from tickets where (datecreated between (@D - 4) and @D) and TktSubmittedBy = 'A')
fetch next from CurDate into @D
set @DFormat1 = ''
set @DFormat2 = ''
set @F2 = @D

end

if (@StDate = '' and @EdDate = '') OR (@StDate is null and @EdDate is null)
begin
select * from AF_Weekly_01T_01
end

if (@Day = '' or @Day is null) and (@StDate <> '' and @EdDate <> '')

begin

set @I = (select count(tktsubmittedby) from tickets where (datecreated between (@D + 1 ) and @EdDate) and TktSubmittedBy = 'U')
set @I1 =(select count(tktsubmittedby) from tickets where (datecreated between (@D + 1 ) and @EdDate) and TktSubmittedBy = 'A')
set @F1 = (cast(day(@EdDate) as varchar(2)) + '/' + cast(Month(@EdDate) as varchar(2)))
set @F3 = (cast(day(@D+1) as varchar(2)) + '/' + cast(Month(@D) as varchar(2)))
set @F = @F3 +'-'+ @F1

insert into AF_Weekly_01T_01(dtTempDate,intCountRdsU,intCountRdsA) values(@F,@I,@I1)

select * from AF_Weekly_01T_01

End

close CurDate
deallocate CurDate

GO

but i am getting out put like

Week =========Count(1)==count(2)
4/8-8/8---------0-------0
9/8-13/8---------0-------0
14/8-18/8---------0-------0
19/8-23/8---------0-------4
24/8-28/8---------1-------0
29/9-2/9---------0-------0
3/9-7/9-----------12-------0
8/9-12/9---------0-------0
13/9-17/9---------0-------6
18/9-22/9---------0-------0
23/9-25/9---------0-------0

problem is with that weekdays

can any body help me how to sort the things

T.I.A

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-09 : 04:07:58

Use the function F_TABLE_DATE found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

Peter Larsson
Helsingborg, Sweden

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-10-09 : 04:36:01

hello

i used that function only to fill data in AFDate_Calender table...but still i am getting only one wrong data that i shown in red color...so can you help me how to get correct date for that row

T.I.A

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-09 : 04:53:21

declare CurDate cursor for select dtdate from AF_Weekly_01T where intdate%5 = 0 ORDER BY dtdate

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-09 : 05:20:19

Try this for easier reading and not using cursors

CREATE PROCEDURE RPT_WeeklyTest
(
	@StartDate DATETIME,
	@EndDate DATETIME,
	@DateInterval TINYINT
)
AS

SET NOCOUNT ON

IF @StartDate IS NULL OR @EndDate IS NULL
	BEGIN
		RAISERROR ('All dates are not supplied.', 16, 1)
		RETURN
	END
ELSE
	SELECT	@StartDate = DATEADD(day, DATEDIFF(day, 0, @StartDate), 0),
		@EndDate = DATEADD(day, DATEDIFF(day, 0, @EndDate), 0)

SELECT		MIN(LEFT(CONVERT(varchar, fdt.DATE, 103), 5) + '-' + LEFT(CONVERT(varchar, DATEADD(day, @DateInterval - 1, fdt.DATE), 103), 5)) [Week],
		ISNULL(SUM(d.Alt1), 0) [Count(1)],
		ISNULL(SUM(d.Alt2), 0) [Count(2)]
FROM		F_TABLE_DATE(@StartDate, @EndDate) fdt
LEFT JOIN	(
			SELECT		DATEADD(day, DATEDIFF(day, 0, DateCreated), 0) DateCreated,
					SUM(CASE WHEN TktSubmittedBy = 'U' THEN 1 ELSE 0 END) Alt1,
					SUM(CASE WHEN TktSubmittedBy = 'A' THEN 1 ELSE 0 END) Alt2
			FROM		Tickets
			WHERE		DATEADD(day, DATEDIFF(day, 0, DateCreated), 0) BETWEEN @StartDate AND @EndDate
			GROUP BY	DATEADD(day, DATEDIFF(day, 0, DateCreated), 0)
		) d ON fdt.DATE = d.DateCreated
GROUP BY	DATEDIFF(day, @StartDate, fdt.DATE) / @DateInterval
ORDER BY	DATEDIFF(day, @StartDate, fdt.DATE)

Call with
EXEC RPT_WeeklyTest '20060804', '20060925', 5

Peter Larsson
Helsingborg, Sweden

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-10-09 : 07:34:44

hey

good work!!!

Thanks peso :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-09 : 08:06:54

I hope the code works for you. There are some optimizations to be done regarding the format of DateCreated.
If you provide some table layout and some sample data from TICKET table, the code above can be more optimized.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources