January 13, 2016 at 9:10 am
Hi,
In a report, I need to show weeks column in the header regardless of missing data.
Can somebody please advise on how to achieve this ? Please find the report and the output screenshot attached where the report shows missing week numbers where data is absent.
The SQL query is below:
Declare @Temp Table
(
POI_LOCATION_CD varchar(5),
EDI_PARTNER_CD varchar(35),
Create_Dt datetime,
Vessel_Type_cd varchar(4),
BL_ID Numeric(10, 0),
Week_no varchar(15),
Mnth Varchar(15),
Week varchar(10)
)
Insert into @Temp
Select a.POI_LOCATION_CD,a.EDI_PARTNER_CD ,a.Create_Dt,B.Vessel_Type_cd,
a.BL_ID,(cast(datepart(yy,a.create_dt)as varchar) + right('0' + cast(datepart(wk,a.create_dt) as varchar(2)),2)) as Week_no,convert(varchar(6),a.create_dt,112) As Mnth,'W_'+ convert(varchar,datepart(yy,a.create_dt))+ RIGHT( '0' + LTRIM( RTRIM(convert(varchar,Datepart(wk,a.Create_dt))) ), 2 ) as Week
From minigapp..mg_bill_of_lading a
Inner Join minigapp..mg_vessel_particular (NOLOCK) b
on a.VESSEL_CD = b.VESSEL_CD
inner join minigapp..MG_COUNTRY c on
substring(a.poi_location_cd,1, 2) = c.country_cd
where A.BL_OK_FLG = 'Y'
And b.VESSEL_TYPE_CD IN (@VesselType)-- OR '-1' IN (@VesselType))
and c.CONTINENT_CD in ('MED', 'EUR')
And ISNULL(A.EDI_Partner_CD, '') IN (@EDIPartner) -- OR '-1' IN (@EDIPartner)
AND A.DELETED_FLG = 'N'
AND a.CREATE_DT >= '2009-01-01' Order by Datepart(wk,a.Create_dt)
If CharIndex('Week',@View) > 0
Begin
Select POI_LOCATION_CD,EDI_PARTNER_CD ,Create_Dt,Vessel_Type_cd,
BL_ID,Week_no,Mnth,Week
From @Temp
Where (Week_no IN (@Values) OR '-1' in (@Values))
Order by Create_dt
End
Else if CharIndex('Month',@View) > 0
Begin
Select POI_LOCATION_CD,EDI_PARTNER_CD ,Create_Dt,Vessel_Type_cd,
BL_ID,Week_no, Mnth,Week
From @Temp
where (Mnth IN (@Values) OR '-1' in (@Values))
Order by Create_dt
End
January 13, 2016 at 1:53 pm
First, create a table source (e.g. temporary table, CTE, derived table, ...) that has all the weeks you need to include in your report.
Second, replace the
From @Temp
Where (Week_no IN (@Values) OR '-1' in (@Values))
with
From #WeeksToReport AS w -- Replace #WeeksToReport with your source of weeks to report on
LEFT OUTER JOIN @Temp AS t
ON t.Week_no = w.Week_no
You may have to add a COALESCE in the SELECT list to replace NULL values with zero or blank or whatever else you want to show if your reporting tool doesn't handle NULL values well.
January 13, 2016 at 2:21 pm
I'd do what Hugo suggests, using a Tally Table, which is a great way to do this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply