>Again I'm converting an MS Access query to SQL Server. I could use another pair of eyes on this.
>
>Here's the original MS Access code:
>
>
>SELECT Date() AS [Snapshot Date],
> PK_NV_CUST_INFO.CUKEY AS [Customer Code],
> PK_CU.CU_NAME AS [Customer Name],
> PK_MOC.MOC_PART_NO AS Item,
> IIf(PK_IM!IM_REV="COMMON SET"," ",PK_IM!IM_REV) AS Rev,
> PK_MOC.MOC_PART_NO AS [Item No Rev],
> IIf(IsNull(PK_IM!IM_TYPE),Null,IIf(PK_IM!IM_TYPE=1 Or PK_IM!IM_TYPE=3,"Manufactured","Purchased")) AS [Item Type],
> PK_SH.SH_MOOHNUMBER AS [Sales Order],
> PK_SH.SH_MOREFNO AS [Sales Order Line],
> PK_SH.SH_PRICE AS [Sales Price],
> PK_SH!SH_STD_MAT AS [Shipment DM],
> PK_RQ.RQ_DATE_REQ AS [Due Date],
> [Date Conversion Table_2].[Friday W/E] AS [Due Week],
> [Date Conversion Table_2].[Fiscal Mth] AS [Due Month],
> PK_RQ.RQ_DATE_PROM AS [Promise Date],
> [Date Conversion Table_1].[Friday W/E] AS [Promise Week],
> [Date Conversion Table_1].[Fiscal Mth] AS [Promise Month],
> PK_SH.SH_DATE AS [Ship Date],
> [Date Conversion Table].[Friday W/E] AS [Ship Week],
> [Date Conversion Table].[Fiscal Mth] AS [Ship Month],
> Sum(PK_SH.SH_QTY) AS [Ship Qty],
> 1 AS [Fake Field]
> INTO [DW Shipments]
> FROM ((((((PK_RQ
> LEFT JOIN [Date Conversion Table] AS [Date Conversion Table_1] ON PK_RQ.RQ_DATE_PROM = [Date Conversion Table_1].Date)
> LEFT JOIN [Date Conversion Table] AS [Date Conversion Table_2] ON PK_RQ.RQ_DATE_REQ = [Date Conversion Table_2].Date)
> RIGHT JOIN PK_SH ON PK_RQ.RQ_SEQNUM = PK_SH.SH_RQSEQNUM)
> LEFT JOIN [Date Conversion Table] ON PK_SH.SH_DATE = [Date Conversion Table].Date)
> LEFT JOIN (PK_MOC
> LEFT JOIN (PK_IM
> LEFT JOIN PK_NV_CUST_INFO ON PK_IM.IM_CATALOG = PK_NV_CUST_INFO.CUCODE) ON PK_MOC.MOC_PART_NO = PK_IM.IM_KEY) ON (PK_SH.SH_MOOHNUMBER = PK_MOC.MOC_OHNUMBER) AND (PK_SH.SH_MOREFNO = PK_MOC.MOC_REF_NO))
> LEFT JOIN PK_OH ON PK_MOC.MOC_OHNUMBER = PK_OH.OH_NUMBER)
> LEFT JOIN PK_CU ON PK_OH.OH_CUKEY = PK_CU.CU_KEY
> GROUP BY Date(), PK_NV_CUST_INFO.CUKEY, PK_CU.CU_NAME, PK_MOC.MOC_PART_NO, IIf(PK_IM!IM_REV="COMMON SET"," ",PK_IM!IM_REV),
> PK_MOC.MOC_PART_NO, IIf(IsNull(PK_IM!IM_TYPE),Null,IIf(PK_IM!IM_TYPE=1 Or PK_IM!IM_TYPE=3,"Manufactured","Purchased")),
> PK_SH.SH_MOOHNUMBER, PK_SH.SH_MOREFNO, PK_SH.SH_PRICE, PK_SH!SH_STD_MAT, PK_RQ.RQ_DATE_REQ, [Date Conversion Table_2].[Friday W/E],
> [Date Conversion Table_2].[Fiscal Mth], PK_RQ.RQ_DATE_PROM, [Date Conversion Table_1].[Friday W/E], [Date Conversion Table_1].[Fiscal Mth],
> PK_SH.SH_DATE, [Date Conversion Table].[Friday W/E], [Date Conversion Table].[Fiscal Mth], 1
> HAVING (((PK_MOC.MOC_PART_NO) Not Like "*-CR" And (PK_MOC.MOC_PART_NO) Not Like "*RMA*" And (PK_MOC.MOC_PART_NO) Not Like "*RW" And (PK_MOC.MOC_PART_NO) Not Like
> "*RWK") AND ((PK_SH.SH_DATE)>#3/1/2008#) AND ((Sum(PK_SH.SH_QTY))<>0));
>
>
>It returns 1.649 rows.
>
>
>and here's what I have. I have replaced the Date Conversion table with a function, other than that, the rest is the same
>
>
>SELECT GETDATE() AS Snapshot_Date,
> NV_CUST_INFO.CUKEY,
> CU.CU_NAME,
> MOC.MOC_PART_NO,
>
> CASE WHEN IM.IM_REV = 'COMMON SET' THEN ''
> ELSE IM.IM_REV
> END AS IM_REV,
> CASE WHEN IM.IM_TYPE IS NULL THEN NULL
> ELSE
> CASE WHEN IM.IM_TYPE IN (1, 3) THEN 'Manufactured'
> ELSE 'Purchased'
> END
> END AS Item_Type,
> SH.SH_MOOHNUMBER AS SH_MOOHNUMBER,
> SH.SH_MOREFNO AS SH_MOREFNO,
> SH.SH_PRICE AS SH_PRICE,
> SH.SH_STD_MAT AS SH_STD_MAT,
> RQ.RQ_DATE_REQ AS RQ_DATE_REQ,
> RQ.RQ_DATE_PROM AS RQ_DATE_PROM,
> SH.SH_DATE AS SH_DATE,
> SUM(SH.SH_QTY) AS SH_QTY,
> Apex.dbo.WeekEnding(SH_DATE, 6) AS ShipWeek,
> Apex.dbo.FiscalMonth(SH_DATE) AS ShipMonth,
> Apex.dbo.WeekEnding(RQ_DATE_PROM, 6) AS PromiseWeek,
> Apex.dbo.FiscalMonth(RQ_DATE_PROM) AS PromiseMonth,
> Apex.dbo.WeekEnding(RQ_DATE_REQ, 6) AS DueWeek,
> Apex.dbo.FiscalMonth(RQ_DATE_REQ) AS DueMonth
> FROM PK5.dbo.RQ
> RIGHT JOIN PK5.dbo.SH ON SH.SH_RQSEQNUM = RQ.RQ_SEQNUM
> LEFT JOIN PK5.dbo.MOC ON MOC_OHNUMBER = SH.SH_MOOHNUMBER
> LEFT JOIN PK5.dbo.OH ON OH.OH_NUMBER = MOC.MOC_OHNUMBER
> LEFT JOIN PK5.dbo.CU ON CU.CU_KEY = OH.OH_CUKEY
> LEFT JOIN PK5.dbo.IM ON IM_KEY = MOC.MOC_PART_NO
> LEFT JOIN ONCORE_SM.dbo.NV_CUST_INFO ON IM.IM_CATALOG = NV_CUST_INFO.CUCODE
> GROUP BY NV_CUST_INFO.CUKEY, CU.CU_NAME, MOC.MOC_PART_NO, IM.IM_REV, IM.IM_TYPE, SH.SH_MOOHNUMBER,
> SH.SH_MOREFNO, SH.SH_PRICE, SH.SH_STD_MAT, RQ.RQ_DATE_REQ, RQ.RQ_DATE_PROM, SH.SH_DATE
> HAVING ((MOC.MOC_PART_NO NOT LIKE '%-CR' AND MOC.MOC_PART_NO NOT LIKE '%RMA%' AND MOC.MOC_PART_NO NOT LIKE '%RW'
> AND MOC.MOC_PART_NO NOT LIKE '%RWK') AND (SH.SH_DATE > '2008-03-01') AND (SUM(SH.SH_QTY) <> 0))
>
>It returns 16,848 rows.
In one of them you include [Date Conversion Table],fields in grouping and in second you don't.
Cetin
