Skip to Content
0
Dec 17, 2015 at 12:13 PM

Weekly Report query help in crystal report

22 Views

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

Attachments

Capture.PNG (45.9 kB)