Year and Date Imports Exports Conditionally

[codrutza]

SQL Server Management Studio Express

Please help me with this:

CREATE procedure [dbo].[test] @Year int as
DECLARE @DateStart datetime
DECLARE @DateEnd datetime

SET @DateStart = cast(@Year-1 as varchar(4))+'0101'
-- Set date start to be 01/01/year-1 00:00:00

SET @DateEnd = cast(@Year+1 as varchar(4))+'0101'
-- Set date end to be 01/01/year+1 00:00:00

IF @Year= YEAR(GETDATE())
-- Current year, must get last full month
BEGIN
SET @DateEnd = CAST(CONVERT(varchar(8),DateAdd(dd,-DAY(GetDate()), GetDate()), 112) as datetime) + 1

END
SET NOCOUNT ON;
SELECT
@Year cy,
@Year-1 py,

---jan @year
case when year(FM.ReportingDate)=@year and month(FM.ReportingDate)=1
then count(FM.JobNumber) else 0 end cyJanJobs,

case when year(FM.ReportingDate)=@year and month(FM.ReportingDate)=1
then round(sum(FM.Sales),0) else 0 end cyJanSales,
…..
---feb @year
…
---jan @year-1
case when year(FM.ReportingDate)=@year-1 and month(FM.ReportingDate)=1
then count(FM.JobNumber) else 0 end pyJanJobs,

case when year(FM.ReportingDate)=@year-1 and month(FM.ReportingDate)=1
then round(sum(FM.Sales),0) else 0 end pyJanSales,
…

FROM
FM

WHERE
…
FM.Type in (‘Import’, ‘Exports’)
AND (FM.ReportingDate >= @DateStart AND FM.ReportingDate<@DateEnd)

group by year(FM.ReportingDate),
month(FM.ReportingDate)

and I develop a report based on this.

Now I have to develop a report where when they are imports I have to get the idate from the FM, and when they are exports I have to get edate, instead of ReportingDate. As well, I have to get the year(idate) or year(edate) instead to get the year(ReportingDate); parameter should be the same @year

 

[simian336]

I am not sure what the question is?

Simi

 

[codrutza]

I'm sorry. I start with beginning and I’ll try to explain better.
I have to write a stored procedure that retrieve the records from
previous year and
current year up to the last full month.

This is sorted when I wanted to retrieve the year from ReportingDate.

Now I have to retrieve data where when they are imports I have to get the idate from the FM instead of ReportingDate, and when they are exports I have to get edate, instead of ReportingDate.
As well, I have to get the year(idate) or year(edate) instead to get the year(ReportingDate)

I don’t know how to write in the stored procedure this. To be an only parameter @year, based on the 2 date idata and edate, for example
AND (FM.iedate >= @DateStart AND FM.iedate<@DateEnd)

I have to have some condition, or some CASE, or something

I tried to write for beginning
…
AND
(
((FM.MType ='Import' AND YEAR(FM.idate))
OR
((FM.MType ='Export' AND YEAR(FM.edate))
)
And it’s gives me error

 

[simian336]

Could you include some sample data and expected result.

Simi

 

[sabinUE]

You said :

…
AND
(
((FM.MType ='Import' AND YEAR(FM.idate))
OR
((FM.MType ='Export' AND YEAR(FM.edate))
)
And it’s gives me error


ok but after YEAR(FM.idate) must be = or >= and a value

the demand is still unclear for me

 

[codrutza]

Hi

SQL Server Management Studio Express

I need to write a stored procedure, which has the same result as in Crystal Reports XI.

In Crystal Reports is like that:

@record selection
…..
and ({@Year}={?Year} or {@year}=({?Year}-1))
and date({@dep arr})<date(year(currentdate),month(currentdate),1)

where the formulas are:
@dep arr
if {M.MType} ="Import" then {M.ArrivalDate}
else if {M.MType} ="Export" then {M.DepartureDate}

@Year
if {M.MType} ="Import" then {%yeararr}
else if {M.MType} ="Export" then {%yeardep}

%yeararr
year("M"."ArrivalDate")

%yeardep
year("M"."DepartureDate")

Please help