Complex View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex View

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2012-07-19 : 17:10:39

I have a view I'm creating. This portion of the view works perfectly:

SELECT     TOP (100) PERCENT dbo.Master_Parent.Vendor, dbo.Master_Parent.Master_Parent, dbo.Master_Child.WholesaleCost, 
                      SUM(ISNULL(dbo.Master_Child.OS_3D, 0) + ISNULL(dbo.Master_Child.AZ_3D, 0) + ISNULL(dbo.Master_Child.TG_3D, 0) 
                      + ISNULL(dbo.Master_Child.SH_3D, 0) + ISNULL(dbo.Master_Child.BY_3D, 0) + ISNULL(dbo.Master_Child.QV_3D, 0) 
                      + ISNULL(dbo.Master_Child.PP_3D, 0) + ISNULL(dbo.Master_Child.EB_3D, 0) + ISNULL(dbo.Master_Child.SR_3D, 0) 
                      + ISNULL(dbo.Master_Child.MA_3D, 0) + ISNULL(dbo.Master_Child.NB_3D, 0)) AS Total3D, SUM(ISNULL(dbo.Master_Child.OS_7D, 0) 
                      + ISNULL(dbo.Master_Child.AZ_7D, 0) + ISNULL(dbo.Master_Child.TG_7D, 0) + ISNULL(dbo.Master_Child.SH_7D, 0) 
                      + ISNULL(dbo.Master_Child.BY_7D, 0) + ISNULL(dbo.Master_Child.QV_7D, 0) + ISNULL(dbo.Master_Child.PP_7D, 0) 
                      + ISNULL(dbo.Master_Child.EB_7D, 0) + ISNULL(dbo.Master_Child.SR_7D, 0) + ISNULL(dbo.Master_Child.MA_7D, 0) 
                      + ISNULL(dbo.Master_Child.NB_7D, 0)) AS Total7D, SUM(ISNULL(dbo.Master_Child.OS_30D, 0) + ISNULL(dbo.Master_Child.AZ_30D, 0) 
                      + ISNULL(dbo.Master_Child.TG_30D, 0) + ISNULL(dbo.Master_Child.SH_30D, 0) + ISNULL(dbo.Master_Child.BY_30D, 0) 
                      + ISNULL(dbo.Master_Child.QV_30D, 0) + ISNULL(dbo.Master_Child.PP_30D, 0) + ISNULL(dbo.Master_Child.EB_30D, 0) 
                      + ISNULL(dbo.Master_Child.SR_30D, 0) + ISNULL(dbo.Master_Child.MA_30D, 0) + ISNULL(dbo.Master_Child.NB_30D, 0)) AS Total30D, 
                      SUM(ISNULL(dbo.Master_Child.OS_60D, 0) + ISNULL(dbo.Master_Child.AZ_60D, 0) + ISNULL(dbo.Master_Child.TG_60D, 0) 
                      + ISNULL(dbo.Master_Child.SH_60D, 0) + ISNULL(dbo.Master_Child.BY_60D, 0) + ISNULL(dbo.Master_Child.QV_60D, 0) 
                      + ISNULL(dbo.Master_Child.PP_60D, 0) + ISNULL(dbo.Master_Child.EB_60D, 0) + ISNULL(dbo.Master_Child.SR_60D, 0) 
                      + ISNULL(dbo.Master_Child.MA_60D, 0) + ISNULL(dbo.Master_Child.NB_60D, 0)) AS Total60D, (((SUM(ISNULL(dbo.Master_Child.OS_3D, 0) 
                      + ISNULL(dbo.Master_Child.AZ_3D, 0) + ISNULL(dbo.Master_Child.TG_3D, 0) + ISNULL(dbo.Master_Child.SH_3D, 0) 
                      + ISNULL(dbo.Master_Child.BY_3D, 0) + ISNULL(dbo.Master_Child.QV_3D, 0) + ISNULL(dbo.Master_Child.PP_3D, 0) 
                      + ISNULL(dbo.Master_Child.EB_3D, 0) + ISNULL(dbo.Master_Child.SR_3D, 0) + ISNULL(dbo.Master_Child.MA_3D, 0) 
                      + ISNULL(dbo.Master_Child.NB_3D, 0)) / 3 + SUM(ISNULL(dbo.Master_Child.OS_7D, 0) + ISNULL(dbo.Master_Child.AZ_7D, 0) 
                      + ISNULL(dbo.Master_Child.TG_7D, 0) + ISNULL(dbo.Master_Child.SH_7D, 0) + ISNULL(dbo.Master_Child.BY_7D, 0) 
                      + ISNULL(dbo.Master_Child.QV_7D, 0) + ISNULL(dbo.Master_Child.PP_7D, 0) + ISNULL(dbo.Master_Child.EB_7D, 0) 
                      + ISNULL(dbo.Master_Child.SR_7D, 0) + ISNULL(dbo.Master_Child.MA_7D, 0) + ISNULL(dbo.Master_Child.NB_7D, 0)) / 7) 
                      + SUM(ISNULL(dbo.Master_Child.OS_30D, 0) + ISNULL(dbo.Master_Child.AZ_30D, 0) + ISNULL(dbo.Master_Child.TG_30D, 0) 
                      + ISNULL(dbo.Master_Child.SH_30D, 0) + ISNULL(dbo.Master_Child.BY_30D, 0) + ISNULL(dbo.Master_Child.QV_30D, 0) 
                      + ISNULL(dbo.Master_Child.PP_30D, 0) + ISNULL(dbo.Master_Child.EB_30D, 0) + ISNULL(dbo.Master_Child.SR_30D, 0) 
                      + ISNULL(dbo.Master_Child.MA_30D, 0) + ISNULL(dbo.Master_Child.NB_30D, 0)) / 30) + SUM(ISNULL(dbo.Master_Child.OS_60D, 0) 
                      + ISNULL(dbo.Master_Child.AZ_60D, 0) + ISNULL(dbo.Master_Child.TG_60D, 0) + ISNULL(dbo.Master_Child.SH_60D, 0) 
                      + ISNULL(dbo.Master_Child.BY_60D, 0) + ISNULL(dbo.Master_Child.QV_60D, 0) + ISNULL(dbo.Master_Child.PP_60D, 0) 
                      + ISNULL(dbo.Master_Child.EB_60D, 0) + ISNULL(dbo.Master_Child.SR_60D, 0) + ISNULL(dbo.Master_Child.MA_60D, 0) 
                      + ISNULL(dbo.Master_Child.NB_60D, 0)) / 60) / 4 AS [Average Daily Sellthrough], SUM(dbo.Master_Child.PoolInventory) AS SumOfpoolinventory, 
                      dbo.Master_Child.WholesaleCost * dbo.Master_Child.PoolInventory AS Exposure
FROM         dbo.Master_Child INNER JOIN
                      dbo.Master_Parent ON dbo.Master_Child.Master_Parent = dbo.Master_Parent.Master_Parent
GROUP BY dbo.Master_Parent.Vendor, dbo.Master_Parent.Master_Parent, dbo.Master_Child.WholesaleCost, 
                      dbo.Master_Child.WholesaleCost * dbo.Master_Child.PoolInventory
HAVING      (SUM(dbo.Master_Child.PoolInventory) > 0)
ORDER BY [Average Daily Sellthrough], Exposure DESC

However, I need the results of this data set to interface with another table called StaleInventoryTracking. That table has Master_Parent just like the coding in the view. I was thinking a statement like this:

WHERE     (NOT EXISTS
          (SELECT     Master_Parent
FROM          dbo.StaleInventoryTracking
            WHERE      (dbo.Master_Parent.Master_Parent = Master_Parent)
            GROUP BY dbo.StaleInventoryReport.Date
            HAVING      (Date < GETDATE()-14)))

The goal is to have the results from the main portion of the view narrowed by whether there is a matching sku on the staleinventorytracking table that was placed on that table within the last 14 days.

Any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-19 : 21:18:49

You should be able add a WHERE clause like shown below (although I was not quite clear on the condition that you are trying to enforce). What I am showing below will pick up only those records for whcih there is a corresponding row in the StaleInventoryTracking that has Date within the last 14 days.

This will also take into account the time - i.e, it will require the Date to be within 14 days of the current date and time. If that is not what you want, the comparison will need slight change.

WHERE
      EXISTS
      (
          SELECT *
          FROM   dbo.StaleInventoryTracking x
          WHERE  (dbo.Master_Parent.Master_Parent = x.Master_Parent)
          AND x.Date > DATEADD(dd,-14,GETDATE())
      )

benking9987
Posting Yak Master

124 Posts

Posted - 2012-07-20 : 09:29:53

I will try to implement this statement when I get back in front of my computer. I ACTUALLY want the filter to work the other direction. Instead of showing if a records EXISTS on the StaleInventoryTracking table within the last 14 days, I only want them to display if a records DOES NOT EXIST on that table and was placed there within the last 14 days. I'll change up the logic slightly and let you know if I continue to have problems.

Thank you for your help.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-07-20 : 18:24:33

FYI,
It's a minor point but you don't need all of the "IsNull(xxx,0)" code. The SUM aggregate will ignore null values.

=================================================
Show me a sane man and I will cure him for you. -Carl Jung, psychiatrist (1875-1961)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-23 : 23:45:13

You will quite possibly find your ordering changes when you change the view. You are getting away with it now but the ORDER BY/TOP 100 PERCENT in the view is optimised out in 2005 upwards. When you join to another table it could easily change the plan so the query becomes driven off the other table and you lose the lucky ordering from the view's query.
You have to ORDER BY on your select to make this guaranteed to be sorted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources