SQL Query

Leo Torres
Leo Torres used Ask the Experts™
on
The query below has no error and works its excluding data I need..

When I join S and F I am only getting the values from F. I need the select to bring in both values for the most part all the values in S that are not in F ..

Just some info.
P -> Select lastest pricing for Client
S -> Returns all the Sales at Item Level for each customer for the last 2 years
F -> This is the Forecast of the items by Customer

Right now the query tells me what I forecasted for each Customer and what was there shortage or surplus

What I dont know is what they Ordered that I did not Forecast for (This is what I need that's why I want to bring in the values that dont exist in Forecast)
; WITH P AS (SELECT     ItemNumber, UnitPrice, CustomerNo, row_Number() OVER (Partition BY ItemNumber, CustomerNo
                            ORDER BY DateCode DESC) rn
FROM         GSDatabase.dbo.CustomerPricing)
, S AS
    (SELECT     CustomerNo, ItemCode, ItemCodeDesc, QuantityShipped, Sum(ISNULL(ExtensionAmt, 0)) AS MonthTotal, dateadd(mm, datediff(mm, 0, InvoiceDate), 
                             0) AS Date, Month(InvoiceDate) AS MonthOrder, Cast(Year(InvoiceDate) AS varchar) AS DateName
      FROM          dbo.V_SalesData
      WHERE      (dateadd(mm, datediff(mm, 0, InvoiceDate), 0) >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) AND (dateadd(mm, datediff(mm, 0, InvoiceDate), 0) 
                             <= GETDATE()) OR
                             (dateadd(mm, datediff(mm, 0, InvoiceDate), 0) > DATEADD(ms, - 3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0))) AND (dateadd(mm, datediff(mm, 
                             0, InvoiceDate), 0) < DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
      GROUP BY CustomerNo, ItemCode, ItemCodeDesc, dateadd(mm, datediff(mm, 0, InvoiceDate), 0), Month(InvoiceDate), Cast(Year(InvoiceDate) AS varchar), 
                             dateadd(mm, datediff(mm, 0, InvoiceDate), 0), QuantityShipped)


SELECT     F.ForecastName, F.ForecastDate, F.CountryCode, F.CustomerNo, F.ItemCode, I.itemCodeDesc, ISNULL(S.QuantityShipped, 0) AS SQuantityShipped, 
                            ISNULL(S.MonthTotal, 0) AS SMonthTotal, F.QuantityShipped AS FQuantityShipped, sum(F.QuantityShipped * P.UnitPrice) AS FMonthlyTotal, ISNULL(S.QuantityShipped, 0) - sum(F.QuantityShipped) AS UnitDiffMonthlyTotal,
                            ISNULL(S.MonthTotal, 0) - sum(F.QuantityShipped * P.UnitPrice) AS DiffMonthlyTotal, InvoiceDate, row_number() OVER (Partition BY F.InvoiceDate, 
                            f.CustomerNo
     ORDER BY ISNULL(S.MonthTotal, 0) - sum(F.QuantityShipped * P.UnitPrice)) ranking
FROM         Financial.dbo.Forecast F FULL outer JOIN
                      S ON S.CustomerNo = F.CustomerNo AND F.InvoiceDate = S.Date AND S.ItemCode = F.Itemcode LEFT JOIN
                      P ON F.CustomerNo = p.CustomerNo AND F.ItemCode = p.ItemNumber FULL OUTER JOIN
                      CI_Item I ON I.Itemcode = F.itemCode
WHERE     Year(InvoiceDate) = Year(getDate()) AND p.rn = 1
GROUP BY F.ForecastName, F.ForecastDate, F.CountryCode, F.CustomerNo, InvoiceDate, S.MonthTotal, F.ItemCode, I.itemcodeDesc, F.QuantityShipped, 
                      S.QuantityShipped
ORDER BY CustomerNo, InvoiceDate

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you post some sample data of what this query brings and what is the expected result?
In the mean time be careful with your where clause at line 25. Maybe this is what you're looking for:
; WITH P AS (
	SELECT  ItemNumber, 
		UnitPrice, 
		CustomerNo, 
		row_Number() OVER (Partition BY ItemNumber, CustomerNo ORDER BY DateCode DESC) rn 

	FROM         GSDatabase.dbo.CustomerPricing
) 

, S AS 
    (SELECT     CustomerNo, 
		ItemCode, 
		ItemCodeDesc, 
		QuantityShipped, 
		Sum(ISNULL(ExtensionAmt, 0)) AS MonthTotal, 
		dateadd(mm, datediff(mm, 0, InvoiceDate), 0) AS Date, 
		Month(InvoiceDate) AS MonthOrder, 
		Cast(Year(InvoiceDate) AS varchar) AS DateName 
      FROM          dbo.V_SalesData 

      WHERE      (dateadd(mm, datediff(mm, 0, InvoiceDate), 0) >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) AND (dateadd(mm, datediff(mm, 0, InvoiceDate), 0)  
                             <= GETDATE()) OR 
                             (dateadd(mm, datediff(mm, 0, InvoiceDate), 0) > DATEADD(ms, - 3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0))) AND (dateadd(mm, datediff(mm,  
                             0, InvoiceDate), 0) < DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) 
      GROUP BY CustomerNo, ItemCode, ItemCodeDesc, dateadd(mm, datediff(mm, 0, InvoiceDate), 0), Month(InvoiceDate), Cast(Year(InvoiceDate) AS varchar),  
                             dateadd(mm, datediff(mm, 0, InvoiceDate), 0), QuantityShipped
) 
 
 
SELECT  F.ForecastName, 
	F.ForecastDate, 
	F.CountryCode, 
	F.CustomerNo, 
	F.ItemCode, 
	I.itemCodeDesc, 
	ISNULL(S.QuantityShipped, 0) AS SQuantityShipped,  
	ISNULL(S.MonthTotal, 0) AS SMonthTotal, 
	F.QuantityShipped AS FQuantityShipped, 
	sum(F.QuantityShipped * P.UnitPrice) AS FMonthlyTotal, 
	ISNULL(S.QuantityShipped, 0) - sum(F.QuantityShipped) AS UnitDiffMonthlyTotal, 
	ISNULL(S.MonthTotal, 0) - sum(F.QuantityShipped * P.UnitPrice) AS DiffMonthlyTotal, 
	InvoiceDate, 
	row_number() OVER (Partition BY F.InvoiceDate, f.CustomerNo ORDER BY ISNULL(S.MonthTotal, 0) - sum(F.QuantityShipped * P.UnitPrice)) ranking 

FROM         (select * from Financial.dbo.Forecast where Year(InvoiceDate) = Year(getDate()) ) F 
FULL outer JOIN S ON S.CustomerNo = F.CustomerNo AND F.InvoiceDate = S.Date AND S.ItemCode = F.Itemcode 
LEFT JOIN (select * from P where p.rn = 1) ON F.CustomerNo = p.CustomerNo AND F.ItemCode = p.ItemNumber
FULL OUTER JOIN CI_Item I ON I.Itemcode = F.itemCode 

GROUP BY F.ForecastName, F.ForecastDate, F.CountryCode, F.CustomerNo, InvoiceDate, S.MonthTotal, F.ItemCode, I.itemcodeDesc, F.QuantityShipped,  
                      S.QuantityShipped 
ORDER BY CustomerNo, InvoiceDate

Open in new window

Author

Commented:
Actual Data or screen Shot.. I dont think this is a where statement issue.. Because the data I am getting is accurate just not complete because I am selecting from one table I need both table to be selected at the same time I tried to implement a combine query but was not successful so I didnt want to post garbage..

Below is a sample of what I am trying to do with this query... Notices the Combined variable
SELECT  combined.Country
      , combined.Client
      , combined.Item
      , F.Description
      , a.DESC1
      ,isnull(A.Jan,0) as A_Jan
      ,isnull(F.Jan,0) as F_Jan
      ,isnull((A.Jan-F.Jan),0) As Jan_Act_Fore
      ,isnull(A.Feb,0) as A_Feb
      ,isnull(f.Feb,0) as F_Feb
      ,isnull((A.Feb-F.feb),0) As Feb_Act_Fore
      ,isnull(A.Mar,0) as A_Mar
      ,isnull(F.Mar,0) as F_mar
      ,isnull((A.Mar-F.Mar),0) As Mar_Act_Fore
      ,isnull(A.Apr,0) as A_Apr
      ,isnull(F.Apr,0) as F_Apr
      ,isnull((A.Apr-F.Apr),0) As Apr_Act_Fore
      ,isnull(A.May,0) as A_May
      ,isnull(F.May,0) as F_May
      ,isnull((A.May-F.May),0) As May_Act_Fore
      ,isnull(A.Jun,0) as A_Jun
      ,isnull(F.Jun,0) as F_Jun
      ,isnull((A.jun-F.jun),0) As Jun_Act_Fore
FROM  (SELECT Country, Client, Item FROM Forecast2
       UNION
       SELECT Country, Client, Item FROM Actual2) AS combined
LEFT OUTER JOIN
  Forecast2 F ON (combined.Country = F.Country AND combined.Client = F.client
                 AND combined.Item = F.Item)
LEFT OUTER JOIN Actual2 A
ON      combined.Item = A.ITEM AND combined.Country = A.COUNTRY AND combined.CLIENT = A.CLIENT

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
There are quite a few tables involved in this query. Not sure how much data you would need to make this work.. Let me know if the sample is good enough..

thanks for your help
the point I was trying to make with the where clause is that it will sort of "transform" your outer joins into inner joins to put it in a way (not the best term, but...). Basically it will make it filter more than what you are expecting. Give it a try and we will take it from there.

Author

Commented:
There is a syntax error on your code

Server Msg: 156, State: 42000, [Microsoft][SQL Server Native Client 10.0][SQL Server]
Incorrect syntax near the keyword 'ON'.

The first 2 queries run fine independently but the last one has an issue I like at the ON and all three seam fine to me..
Chris LuttrellSenior Database Architect
Commented:
your problem is in your where clause:
      WHERE   Year(InvoiceDate) = Year(getDate())
that is looking at InvoiceDate which is in your Forecast query (F) which may not always be there.
Change that to :
      WHERE   Year(S.Date) = Year(getDate())
which is from your Sales query and always there and it should work.
Chris LuttrellSenior Database Architect

Commented:
ltorres321, I am refering to your original query.
I wanted to ask where is the InvoiceDate coming from? :) but then for some reason forgot. Anyhow, in my query the problem is that I missed an alias. So give this a try:
; WITH P AS ( 
        SELECT  ItemNumber,  
                UnitPrice,  
                CustomerNo,  
                row_Number() OVER (Partition BY ItemNumber, CustomerNo ORDER BY DateCode DESC) rn  
 
        FROM         GSDatabase.dbo.CustomerPricing 
)  
 
, S AS  
    (SELECT     CustomerNo,  
                ItemCode,  
                ItemCodeDesc,  
                QuantityShipped,  
                Sum(ISNULL(ExtensionAmt, 0)) AS MonthTotal,  
                dateadd(mm, datediff(mm, 0, InvoiceDate), 0) AS Date,  
                Month(InvoiceDate) AS MonthOrder,  
                Cast(Year(InvoiceDate) AS varchar) AS DateName  
      FROM          dbo.V_SalesData  
 
      WHERE      (dateadd(mm, datediff(mm, 0, InvoiceDate), 0) >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) AND (dateadd(mm, datediff(mm, 0, InvoiceDate), 0)   
                             <= GETDATE()) OR  
                             (dateadd(mm, datediff(mm, 0, InvoiceDate), 0) > DATEADD(ms, - 3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0))) AND (dateadd(mm, datediff(mm,   
                             0, InvoiceDate), 0) < DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))  
      GROUP BY CustomerNo, ItemCode, ItemCodeDesc, dateadd(mm, datediff(mm, 0, InvoiceDate), 0), Month(InvoiceDate), Cast(Year(InvoiceDate) AS varchar),   
                             dateadd(mm, datediff(mm, 0, InvoiceDate), 0), QuantityShipped 
)  
  
  
SELECT  F.ForecastName,  
        F.ForecastDate,  
        F.CountryCode,  
        F.CustomerNo,  
        F.ItemCode,  
        I.itemCodeDesc,  
        ISNULL(S.QuantityShipped, 0) AS SQuantityShipped,   
        ISNULL(S.MonthTotal, 0) AS SMonthTotal,  
        F.QuantityShipped AS FQuantityShipped,  
        sum(F.QuantityShipped * P.UnitPrice) AS FMonthlyTotal,  
        ISNULL(S.QuantityShipped, 0) - sum(F.QuantityShipped) AS UnitDiffMonthlyTotal,  
        ISNULL(S.MonthTotal, 0) - sum(F.QuantityShipped * P.UnitPrice) AS DiffMonthlyTotal,  
        InvoiceDate,  
        row_number() OVER (Partition BY F.InvoiceDate, f.CustomerNo ORDER BY ISNULL(S.MonthTotal, 0) - sum(F.QuantityShipped * P.UnitPrice)) ranking  
 
FROM         (select * from Financial.dbo.Forecast where Year(InvoiceDate) = Year(getDate()) ) F  
FULL outer JOIN S ON S.CustomerNo = F.CustomerNo AND F.InvoiceDate = S.Date AND S.ItemCode = F.Itemcode  
LEFT JOIN (select * from P where rn = 1) P ON F.CustomerNo = p.CustomerNo AND F.ItemCode = p.ItemNumber 
FULL OUTER JOIN CI_Item I ON I.Itemcode = F.itemCode  
 
GROUP BY F.ForecastName, F.ForecastDate, F.CountryCode, F.CustomerNo, InvoiceDate, S.MonthTotal, F.ItemCode, I.itemcodeDesc, F.QuantityShipped,   
                      S.QuantityShipped  
ORDER BY CustomerNo, InvoiceDate

Open in new window

Author

Commented:
Thanks guys I will be on vacation starting tonite till monday Morning.. I will try to remote in .. but I will keep you posted I really need this.. thank u both

Author

Commented:
OK great this looks like what I am Looking for.. Made a few adjustments to the query...


I just need one more thing I thought it was going to be easy (but it never is)
Below is the edited Query..

There is a line that is commented out near the bottom (this one to be exact)
and (ISNULL(F.QuantityShipped,0) <> 0 and ISNULL(F.QuantityShipped,0) <> 0)

when I use this condition it look like it looks at the filters seperatly becasue it filters any of the rows that has either field as 0..

I need the condition to be filtered out if both are 0 not just 1 column...

Thanks for your help and patience..
; WITH P AS ( 
        SELECT  ItemNumber,  
                UnitPrice,  
                CustomerNo,  
                row_Number() OVER (Partition BY ItemNumber, CustomerNo ORDER BY DateCode DESC) rn  
 
        FROM         GSDatabase.dbo.CustomerPricing 
)  
 
, S AS  
    (SELECT     CustomerNo,  
                ItemCode,  
                ItemCodeDesc,  
                QuantityShipped,  
                Sum(ISNULL(ExtensionAmt, 0)) AS MonthTotal,  
                dateadd(mm, datediff(mm, 0, InvoiceDate), 0) AS Date,  
                Month(InvoiceDate) AS MonthOrder,  
                Cast(Year(InvoiceDate) AS varchar) AS DateName  
      FROM          dbo.V_SalesData  
 
      WHERE      (dateadd(mm, datediff(mm, 0, InvoiceDate), 0) >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) AND (dateadd(mm, datediff(mm, 0, InvoiceDate), 0)   
                             <= GETDATE()) OR  
                             (dateadd(mm, datediff(mm, 0, InvoiceDate), 0) > DATEADD(ms, - 3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0))) AND (dateadd(mm, datediff(mm,   
                             0, InvoiceDate), 0) < DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))  
      GROUP BY CustomerNo, ItemCode, ItemCodeDesc, dateadd(mm, datediff(mm, 0, InvoiceDate), 0), Month(InvoiceDate), Cast(Year(InvoiceDate) AS varchar),   
                             dateadd(mm, datediff(mm, 0, InvoiceDate), 0), QuantityShipped 
)      
SELECT  F.ForecastName,  
        F.ForecastDate,  
        F.CountryCode,  
        F.CustomerNo,  
        F.ItemCode,  
        I.itemCodeDesc,  
        ISNULL(S.QuantityShipped, 0) AS SQuantityShipped,   
        ISNULL(S.MonthTotal, 0) AS SMonthTotal,  
        ISNULL(F.QuantityShipped,0) AS FQuantityShipped,  
       (ISNULL(sum(F.QuantityShipped),0) * ISNULL(P.UnitPrice,0)) AS FMonthlyTotal,  
        ISNULL(S.QuantityShipped,0) - ISNULL(sum(F.QuantityShipped),0) AS UnitDiffMonthlyTotal,  
       (ISNULL(S.MonthTotal,0) - ISNULL(sum(F.QuantityShipped),0) * ISNULL(P.UnitPrice,0)) AS DiffMonthlyTotal,  
        InvoiceDate,  
        row_number() OVER (Partition BY F.InvoiceDate, f.CustomerNo ORDER BY ISNULL(S.MonthTotal, 0) - sum(F.QuantityShipped * P.UnitPrice)) ranking  
 
FROM         (select * from Financial.dbo.Forecast where Year(InvoiceDate) = Year(getDate()) ) F  
FULL outer JOIN S ON S.CustomerNo = F.CustomerNo AND F.InvoiceDate = S.Date AND S.ItemCode = F.Itemcode  
LEFT JOIN (select * from P where rn = 1) P ON F.CustomerNo = p.CustomerNo AND F.ItemCode = p.ItemNumber 
FULL OUTER JOIN CI_Item I ON I.Itemcode = F.itemCode  
Where f.CustomerNo not in ('MISC')
--and (ISNULL(F.QuantityShipped,0) <> 0 and ISNULL(F.QuantityShipped,0) <> 0)  
GROUP BY F.ForecastName, F.ForecastDate, F.CountryCode, F.CustomerNo, InvoiceDate, S.MonthTotal, F.ItemCode, I.itemcodeDesc, F.QuantityShipped,   
                      S.QuantityShipped, p.unitPrice  
ORDER BY CustomerNo, InvoiceDate

Open in new window

well your're using the same column from the same table F.QuantityShipped
Maybe it should be from the S table.
and (ISNULL(F.QuantityShipped,0) <> 0 and ISNULL(S.QuantityShipped,0) <> 0)

Author

Commented:
LOL :) DAU Error (Dumb AS? User Error) sorry your right..

I made the correction tho and still applies it with an either or Condition instead of both

Thanks for pointed that out!!
Chris LuttrellSenior Database Architect

Commented:
Do you all know you have changed the functionality of the query by shifting to a FULL OUTER JOIN.  Your question started out with wanting to select "all the values in S that are not in F .." which implies a LEFT OUTER JOIN from S to F not a FOJ.  I just wanted to point that out, if you have changed the logic then fine, but that means some of the other parts of the query may need to be adjusted as you are seeing int this statement.
I will wait to see the direction before I offer other sugestions as it makes a difference and will be real confusing if you are thinking FOJ and I am thinking LOJ.
hmm that's strange. Anyhow it could also be simplified like this:

and (F.QuantityShipped > 0 and S.QuantityShipped > 0)  

Author

Commented:
No I give up.. You will get full credit for this.. The query gives me exactly what I need..  I will just have to filter it at the report level for now.. I Have been trying that for sometime and its not working I will open a related question to this one in just a second...
Chris LuttrellSenior Database Architect

Commented:
What, No Assist?  I think it was my pointing out which table the date was coming from that made the difference.

Author

Commented:
Sorry good Point I will request for question reopen!!

Author

Commented:
Thank you both!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial