|
PanzerAttack
Yak Posting Veteran
71 Posts |
 Posted - 2011-09-02 : 07:38:05
|
| Hi, I might be going about this the wrong way but I have created a table that is a list of each day of the year in the format 2011-09-02In the Column next to this I have a free text area where I can assign the import a Description to be used withing our reports.I want to use this table in a Left Outer Join in my View (that feeds my Pivot) against a field I've created that transforms the ImportDate into the format 2011-09-02. I'm not sure how to do this. Below I have the text in my View, how can I link my table named "tblImportDate" to the field in my view named "Import Date" (CONVERT (varchar(10), dbo.T003_Trade_Reporting.ImportDate, 121))Code is:SELECT CONVERT(varchar(10), dbo.T003_Trade_Reporting.ImportDate, 121) AS [Import Date], dbo.T003_Trade_Reporting.SalesOrganization, dbo.Tbl_SalesOrganization.SalesOrganizationDesc, dbo.Tbl_DistributionChannel.DistributionChannelDesc, dbo.Tbl_Business.BusinessDesc, dbo.Tbl_CL3.CL3Desc, dbo.Tbl_CL4.CL4Desc, dbo.Tbl_Category.CategoryDesc, dbo.Tbl_SubCategory.SubCategoryDesc, CONVERT(varchar(3), dbo.Tbl_CalendarYearMonth.CalendarYearMonthDesc, 121) AS [Year Month Desc], dbo.Tbl_CalendarYearMonth.Quarter, dbo.T003_Trade_Reporting.CustomerChannel3, COALESCE (SUM(dbo.T003_Trade_Reporting.CCSNPS), 0) AS [CCS NPS], COALESCE (SUM(dbo.T003_Trade_Reporting.BBDBuildingBlockSpend), 0) AS [Building Block Spend], COALESCE (SUM(dbo.T003_Trade_Reporting.CCSTradeDevelopmentSpend), 0) AS TDS, COALESCE (SUM(dbo.T003_Trade_Reporting.CCSCostofGoodSold), 0) AS [Cost of Good Sold], COALESCE (SUM(dbo.T003_Trade_Reporting.CCSCUSTOMERCONTRIBUTION), 0) AS CC, COALESCE (SUM(dbo.T003_Trade_Reporting.CCSNETCUSTOMERSALES), 0) AS NCS, COALESCE (SUM(dbo.T003_Trade_Reporting.CCSNNSNETPROCEEDSOFSALES), 0) AS [COPA NPS], COALESCE (SUM(dbo.T003_Trade_Reporting.CCSNNSTradeDevelopmentSpend), 0) AS [COPA TDS], COALESCE (SUM(dbo.T003_Trade_Reporting.CCSNNSCostofGoodsSold), 0) AS [COPA COGS], COALESCE (SUM(dbo.T003_Trade_Reporting.CCSNNSCUSTOMERCONTRIBUTION), 0) AS [COPA CC], COALESCE (SUM(dbo.T003_Trade_Reporting.CCSNNSNETCUSTOMERSALES), 0) AS [COPA NCS], COALESCE (SUM(dbo.T003_Trade_Reporting.CCSNNSProductAllowances), 0) AS ProductAllowances, COALESCE (SUM(dbo.T003_Trade_Reporting.CCSNNSPlacementAllowances), 0) AS PlacementAllowances, COALESCE (SUM(dbo.T003_Trade_Reporting.CCSNNSPromotionAllowances), 0) AS PromotionAllowances, COALESCE (SUM(dbo.T003_Trade_Reporting.CCSNNSPriceAllowances), 0) AS PriceAllowancesFROM dbo.T003_Trade_Reporting LEFT OUTER JOIN dbo.Tbl_DistributionChannel ON dbo.T003_Trade_Reporting.DistributionChannel = dbo.Tbl_DistributionChannel.DistributionChannel LEFT OUTER JOIN dbo.Tbl_SubCategory ON dbo.T003_Trade_Reporting.SubCategory = dbo.Tbl_SubCategory.SubCategory LEFT OUTER JOIN dbo.Tbl_SalesOrganization ON dbo.T003_Trade_Reporting.SalesOrganization = dbo.Tbl_SalesOrganization.SalesOrganization LEFT OUTER JOIN dbo.Tbl_Category ON dbo.T003_Trade_Reporting.Category = dbo.Tbl_Category.Category LEFT OUTER JOIN dbo.Tbl_CL4 ON dbo.T003_Trade_Reporting.CL4 = dbo.Tbl_CL4.CL4 LEFT OUTER JOIN dbo.Tbl_CL3 ON dbo.T003_Trade_Reporting.CL3 = dbo.Tbl_CL3.CL3 LEFT OUTER JOIN dbo.Tbl_CalendarYearMonth ON dbo.T003_Trade_Reporting.CalendarYearMonth = dbo.Tbl_CalendarYearMonth.CalendarYearMonth LEFT OUTER JOIN dbo.Tbl_Business ON dbo.T003_Trade_Reporting.Business = dbo.Tbl_Business.BusinessGROUP BY CONVERT(varchar(10), dbo.T003_Trade_Reporting.ImportDate, 121), dbo.T003_Trade_Reporting.SalesOrganization, dbo.Tbl_SalesOrganization.SalesOrganizationDesc, dbo.Tbl_Business.BusinessDesc, dbo.Tbl_CL3.CL3Desc, dbo.Tbl_CL4.CL4Desc, dbo.Tbl_Category.CategoryDesc, dbo.Tbl_SubCategory.SubCategoryDesc, CONVERT(varchar(3), dbo.Tbl_CalendarYearMonth.CalendarYearMonthDesc, 121), dbo.Tbl_DistributionChannel.DistributionChannelDesc, dbo.T003_Trade_Reporting.CustomerChannel3, dbo.Tbl_CalendarYearMonth.QuarterHAVING (dbo.Tbl_Business.BusinessDesc = 'GBL2 BEVERAGE') |
|
Lookup up table on View Field