Weekly Report query help in crystal report

[former_member229757]

Hi Experts,

                 This report shows the sales order doctotal country wise                    

I need  this weekly report  column. The below query shows the month and year i need weekly report. Here with i have attached the sample output screen shot for reference.

DECLARE @CurrentYear as Integer;

DECLARE @CurrentMonth as Integer;

DECLARE @HistoricYears as Integer;

DECLARE @CURRENTWEEK AS INTEGER;

SET @CurrentYear = DATEPART(YEAR,GETDATE());

SET @CurrentMonth = DATEPART(MONTH,GETDATE());

SELECT ORDR.CardCode,ORDR.CardName,ORDR.CardCode,(ORDR.DocTotal - ORDR.VatSum) As DocTotal,ORDR.DocDate,ORDR.SlpCode

,OCRD.Country

,OCRY.Name

,DATEPART("Month",DocDate) As 'Month'

,DATEPART("Year",DocDate) As 'Year'

,DATEPART("WEEK",DOCDATE) AS 'WEEK'

FROM ORDR

INNER JOIN OCRD ON

ORDR.CardCode = OCRD.CardCode

INNER JOIN OCRY ON

OCRD.Country = OCRY.Code

WHERE ORDR.CANCELED = 'N'

AND (CAST(ORDR.DocDate AS DATE) BETWEEN CAST(CAST((@CurrentYear - 1)AS Varchar) + '-01-01' AS DATE) 

AND DATEADD(YEAR,-1,GETDATE())

OR

CAST(ORDR.DocDate AS DATE) BETWEEN CAST(CAST((@CurrentYear)AS Varchar) + '-01-01' AS DATE) 

AND CAST(GETDATE()AS DATE))

Formula in crystal report:

Today:

-----

IF {Command.DocDate} = CurrentDate THEN

{Command.DocTotal}

ELSE 0

month

-----

IF DatePart("m",{Command.DocDate}) = Datepart("m",CurrentDate)

AND Datepart("YYYY",{Command.DocDate}) = Datepart("YYYY",CurrentDate) THEN

{Command.DocTotal}

ELSE 0

Current Year:

------------

IF Datepart("YYYY",{Command.DocDate}) = Datepart("YYYY",Currentdate) THEN

{Command.DocTotal}

ELSE 0

This week:   I need this report formula

??????

Thanks & Regards

Vinoth

[former_member413900]

if(

{Command.DocDate} >= dateadd('d',-weekday(currentdate,crmonday),currentdate)

and

{Command.DocDate} <= dateadd('d',7,dateadd('d',-weekday(currentdate,crmonday),currentdate)))

then

{Command.DocTotal}

else

0

[former_member229757]

Hi,

     Weekly column showing blank. but values in today column

Thanks & Regards

Vinoth

[former_member229757]

Hi,

     We are not showing any where in weeks in query.so its not fetching the data.

Thanks & Regards

vinoth

[former_member413900]

I may be misunderstanding you but the formula I posted above determines if the returned DocDate from query falls within this current week and sets the value if it does. Is that what you are looking for?

[former_member229757]

Hi,

     Is it possible that weekly column????

Thanks & Regards

Vinoth