Problem with parameter when using lastfullweek

[reacha]

The query that i have to build the report is


declare @test table(pkgid int,processid int,EventName varchar(100),Auditstamp datetime)

insert into @test(pkgid,processid,EventName,Auditstamp)

SELECT AUDIT.PKGID, AUDIT.PROCESSID, EVENT.NAME, AUDIT.AUDITSTAMP
FROM AUDIT INNER JOIN
EVENT ON AUDIT.PROCESSID = EVENT.PROCESSID AND AUDIT.RESULTEVENTID = EVENT.EVENTID
WHERE (AUDIT.AUDITTYPE = 6) AND (AUDIT.AUDITMSG = 'Routed by user')
and AUDIT.PROCESSID in (3,5)

ORDER BY AUDIT.AUDITSTAMP DESC;

with test as
(
select *, rn = row_number() over(partition by pkgid order by auditstamp desc) from @test)
select pkgid,processid,eventname,auditstamp from test where rn = 1


but when i mention parameter as lastfullweek it was taking like this


declare @test table(pkgid int,processid int,EventName varchar(100),Auditstamp datetime)

insert into @test(pkgid,processid,EventName,Auditstamp)

SELECT AUDIT.PKGID, AUDIT.PROCESSID, EVENT.NAME, AUDIT.AUDITSTAMP
FROM AUDIT INNER JOIN
EVENT ON AUDIT.PROCESSID = EVENT.PROCESSID AND AUDIT.RESULTEVENTID = EVENT.EVENTID
WHERE (AUDIT.AUDITTYPE = 6) AND (AUDIT.AUDITMSG = 'Routed by user')
and AUDIT.PROCESSID in (3,5)

ORDER BY AUDIT.AUDITSTAMP DESC;

with test as
(
select *, rn = row_number() over(partition by pkgid order by auditstamp desc) from @test)
select pkgid,processid,eventname,auditstamp from test where rn = 1
AND AUDITSTAMP >= CONVERT(varchar(10), GETDATE() - 12, 110)
AND AUDITSTAMP < CONVERT(varchar(10), GETDATE() - 5, 110)



instead of this i need a query like this



declare @test table(pkgid int,processid int,EventName varchar(100),Auditstamp datetime)

insert into @test(pkgid,processid,EventName,Auditstamp)

SELECT AUDIT.PKGID, AUDIT.PROCESSID, EVENT.NAME, AUDIT.AUDITSTAMP
FROM AUDIT INNER JOIN
EVENT ON AUDIT.PROCESSID = EVENT.PROCESSID AND AUDIT.RESULTEVENTID = EVENT.EVENTID
WHERE (AUDIT.AUDITTYPE = 6) AND (AUDIT.AUDITMSG = 'Routed by user')
and AUDIT.PROCESSID in (3,5)
AND AUDITSTAMP >= CONVERT(varchar(10), GETDATE() - 12, 110)
AND AUDITSTAMP < CONVERT(varchar(10), GETDATE() - 5, 110)
ORDER BY AUDIT.AUDITSTAMP DESC;

with test as
(
select *, rn = row_number() over(partition by pkgid order by auditstamp desc) from @test)
select pkgid,processid,eventname,auditstamp from test where rn = 1


Please help me out!!

Thanks,
reacha

 

[Madawc]

Is this an SQL Command you have written? Or is it SQL that the Crystal generates on the basis of your commands?



Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[reacha]

This is the SQL command that i have written

 

[Madawc]

I don't see anything obviously wrong. Can anyone else help?

Note also that there is an SQL forum, they might help.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP