How to filter if data was not there

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » How to filter if data was not there

How to filter if data was not there

Rate Topic

Display Mode

Topic Options

Author

Message

dwilliscp

Posted Wednesday, October 31, 2012 1:40 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187, Visits: 332

I have a query (shown below) that I have been asked to filter so that only stock activity that has record's 6 months or more back. I am trying to see if the cube keeps records for all parts in the master or if it only loads those with activity... but at anyrate, what is the best way to filter out those records for Plant + Part that only show up in the last 6 months? (My guess is a CTE that groups by Plant, Part) but I was wondering if there was a faster way. The below query takes about 30 min to run now.

; with base as
(
Select a.Dim_ID
, Day_DT_CD
, MRP_Area_CD
, Material_CD
, Material_Desc
, Material_Type
, Profit_Center
, SBU
, Stocking_Status
, Standard_Cost
, Total_Replenishment_Lead_Time
, Safety_Stock
, Total_Usable_Inventory
, min(Total_Usable_Inventory) Over(Partition by MRP_Area_CD, Material_CD) as Min_Stock_Lvl
FROM Inv_Cube_Fact A
JOIN Inv_Cube_Dimension B
ON A.Dim_ID = B.Dim_ID
where safety_stock > 0 and Stocking_Status like 'MTS'
)

Select a.Dim_ID
, Day_DT_CD
, MRP_Area_CD
, Material_CD
, Material_Desc
, Material_Type
, Profit_Center
, SBU
, Stocking_Status
, Standard_Cost
, Total_Replenishment_Lead_Time
, Safety_Stock
, Total_Usable_Inventory
, Min_Stock_Lvl
from base
where min_stock_lvl > 0

Thanks
dwcp

Post #1379506

GSquared

Posted Wednesday, October 31, 2012 1:43 PM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

Is this SSAS or a regular set of tables? It looks like T-SQL, not MDX, but the object names and explanation gave me a doubt about that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1379509

dwilliscp

Posted Thursday, November 01, 2012 7:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:52 AM
Points: 187, Visits: 332

There is a third party that handles these databases.. my history with cubes is in PICK, not SQL Server. Can you tell from the create statement if it realy is a cube or not?

CREATE TABLE [dbo].[Inv_Cube_Fact](
[Dim_ID] [int] NOT NULL,
[Day_DT_CD] [char](10) NOT NULL,
[Current_Inventory] [real] NULL,
[QualityInsp_Inventory] [real] NULL,
[Restricted_Inventory] [real] NULL,
[Blocked_Inventory] [real] NULL,
[InTransit_Inventory] [real] NULL,
[VCons_Current] [real] NULL,
[VCons_QualityInsp] [real] NULL,
[VCons_Restricted] [real] NULL,
[VCons_Blocked] [real] NULL,
[CCons_Current] [real] NULL,
[CCons_QualityInsp] [real] NULL,
[CCons_Restricted] [real] NULL,
[PctQuantity] [real] NULL,
[Storage_Cnt] [int] NULL,
[Shipments_Avg_1] [real] NULL,
[Shipments_Avg_3] [real] NULL,
[Consumption_Avg_1] [real] NULL,
[Consumption_Avg_3] [real] NULL,
[STO_Avg_1] [real] NULL,
[STO_Avg_3] [real] NULL,
[IntMvmt_Avg_1] [real] NULL,
[IntMvmt_Avg_3] [real] NULL,
[Ship_Fcst_Avg_1] [real] NULL,
[Ship_Fcst_Avg_3] [real] NULL,
[Consumption_Fcst_Avg_1] [real] NULL,
[Consumption_Fcst_Avg_3] [real] NULL,
[STO_Fcst_Avg_1] [real] NULL,
[STO_Fcst_Avg_3] [real] NULL,
[IntMvmt_Fcst_Avg_1] [real] NULL,
[IntMvmt_Fcst_Avg_3] [real] NULL,
[Standard_Cost] [real] NULL,
[Current_Inventory_Cost] [real] NULL,
[Planned_Delivery_Time] [real] NULL,
[Total_Replenishment_Lead_Time] [real] NULL,
[GR_Processing_Time] [real] NULL,
[Planning_Time_Fence] [real] NULL,
[Min_Lot_Size] [real] NULL,
[Max_Lot_Size] [real] NULL,
[Fixed_Lot_Size] [real] NULL,
[Round_Value] [real] NULL,
[Safety_Time] [real] NULL,
[Min_Safety_Stock] [real] NULL,
[Safety_Stock] [real] NULL,
[Max_Stock_Level] [real] NULL,
[Max_Target_Days] [real] NULL,
[Max_Target_Qty] [real] NULL,
[Total_Usable_Inventory] [real] NULL,
[Total_Usable_Inventory_Cost] [real] NULL,
[Total_Daily_Usage] [real] NULL,
[Total_Inventory_Cost] [real] NULL,
[Excess_Kgs] [real] NULL,
[Excess_$] [real] NULL,
[ISA_Safety_Stock] [real] NULL,
[NearTerm_Open_Orders] [real] NULL,
[NearTerm_STO] [real] NULL,
[Material_ID] [int] NULL,
[MRP_Area_ID] [int] NULL,
[Storage_Location_Group_ID] [int] NULL,
[Material_MRPArea_ID] [int] NULL,
[Region] [varchar](100) NULL,
CONSTRAINT [pk_Dim_Day_DT_pid] PRIMARY KEY CLUSTERED
(
[Dim_ID] ASC,
[Day_DT_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Post #1379793

mmartin1

Posted Wednesday, November 07, 2012 11:46 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 194, Visits: 643

Since this creates a table you are looking at a relational database that happens holds dimensional data. These serve as the basis for the cube (itself a database-non relational) but is not the cube itself. Normally to speed up queries you need to understand what indexes exist and if statistics are being updated. In your case it might depend on the frequency of how the data is populated. Normally these have a one day latency so you can build a few indexes on the table or rebuild existing indexes to help with performance.

Post #1382111

« Prev Topic | Next Topic »

Permissions