October 31, 2008 at 2:36 am
Hello,
I'm stuck at my sqlquery.
What I want is to:
Group by ID and Location. Sum Quantity and Cost for both Types, Sale and Purchase + Max(date) for an ID where Type = Sale
Example data: Table OrderData
ID Date Type Location Quantity Cost
6 2008-10-02 Sale A 4 3
6 2008-10-01 Purchase D 88 13
5 2008-10-13 Sale D 3 10
6 2008-10-14 Sale D 16 16
6 2008-10-01 Purchase X 1 13
6 2008-10-01 Purchase D 22 11
6 2008-10-01 Purchase C 54 65
5 2008-10-14 Sale D 3 10
6 2008-10-31 Sale D 6 12
4 2008-10-20 Purchase D 5 70
4 2008-10-05 Purchase D 2 1
Example of wanted output from exampel data: (Note, ID 4 has no date for type sale)
ID Location TotalQuantity TotalCost LatestSalesDateForArticleInThatLocation
6 D 32 72 2008-10-31
6 A 4 3 2008-10-02
4 D 7 71
5 D 3 10 2008-10-14
I have tried with the query below but then I get lastdate for all types, both purchase and sale and I only want latest date for type sale.
If I put Where Type = 'Sale' in the where clause in the query I dont get correct totalquantity and totalcoast as it is based on sale and purchase
SELECT
ID AS ID,
Location AS Location,
SUM(Quantit) AS TotalQuantity,
SUM(Cost) AS TotalCoast,
MAX(Date) AS LatestSalesDateForArticleInThatLocation
FROM
OrderData
Group By
ID, Location
Regards // Nils
October 31, 2008 at 2:52 am
Sorry cant give u the whole query...
Why dont you use a derived query that just selects the max dates and the ID and then join it with the query that you have.
This would be your derived table query.
SELECT MAX(Date) ,ID From table
WHERE Type = 'Sale'
GROUP BY ID
"Keep Trying"
October 31, 2008 at 4:12 am
I'm not familiar with sql derived querys. Can you give me an example?
October 31, 2008 at 4:56 am
A derived table is sometimes known as an inline view.
The derived table below has an alias of D:
SELECT O1.[ID]
    ,O1.Location
    ,SUM(O1.Quanity) AS TotalQuantity
    ,SUM(O1.Cost) AS TotalCost
    ,D.[Date] AS LatestSalesDate
FROM OrderData O1
    LEFT JOIN
    (
        SELECT O2.[ID], O2.Location, MAX(O2.[Date]) AS [Date]
        FROM OrderData O2
        WHERE O2.[Type] = 'Sale'
        GROUP BY O2.[ID], O2.Location
    ) D
        ON O1.[ID] = D.[ID]
            AND O1.Location = D.Location
GROUP BY O1.[ID], O1.Location, D.[Date]
To answer your original question, you would be better just to use a
CASE to include only Sales in the LatestSalesDate.
SELECT [ID]
    ,Location
    ,SUM(Quanity) AS TotalQuantity
    ,SUM(Cost) AS TotalCost
    ,MAX(CASE WHEN [Type] = 'Sale' THEN [Date] END) AS LatestSalesDate
FROM OrderData
GROUP BY [ID], Location
October 31, 2008 at 5:04 am
check whthr this works
SELECT
ID AS ID,
Location AS Location,
SUM(Quantit) AS TotalQuantity,
SUM(Cost) AS TotalCoast,
D.MaxSaleDate
FROM
OrderData O LEFT OUTER JOIN
(SELECT ID, MAX(DATE) as MaxSaleDate FROM OrderData WHERE type = 'Sale' Group BY ID) D
ON OrderData.ID = D.ID
Group By
ID, Location.
(SELECT ID, MAX(DATE) as MaxSaleDate FROM OrderData WHERE type = 'Sale' Group BY ID) D is a derived table. i.e. Instead of normally using a table in the from clause we are using this query.
"Keep Trying"
October 31, 2008 at 5:23 am
Great!
Both solutions work but I will go with the case scenario.
Thanks alot.
Would it be possible to group quantity and cost for lets say last month? and the month before that?
ID Location TotalQuantity TotalCost LatestSalesDateForArticleInThatLocation QTYSoldLastMont QTYSold2MontsAgo
6 D 32 72 2008-10-31
6 A 4 3 2008-10-02
4 D 7 71
5 D 3 10 2008-10-14
October 31, 2008 at 5:47 am
Sure, just use the same idea. Something like:
SELECT O.[ID]
    ,O.Location
    ,SUM(CASE WHEN O.[Date] >= M.ThisMonth THEN O.Quanity END) AS TotalQuantityThisMonth
    ,SUM(CASE WHEN O.[Date] >= M.ThisMonth THEN O.Cost END) AS TotalCostThisMonth
    ,SUM(CASE WHEN O.[Date] >= M.PreviousMonth AND O.[Date] < M.ThisMonth THEN O.Quanity END) AS TotalQuantityPreviousMonth
    ,SUM(CASE WHEN O.[Date] >= M.PreviousMonth AND O.[Date] < M.ThisMonth THEN O.Cost END) AS TotalCostPreviousMonth
    ,SUM(CASE WHEN O.[Date] >= M.NextPreviousMonth AND O.[Date] < M.PreviousMonth THEN O.Quanity END) AS TotalQuantityNextPreviousMonth
    ,SUM(CASE WHEN O.[Date] >= M.NextPreviousMonth AND O.[Date] < M.PreviousMonth THEN O.Cost END) AS TotalCostNextPreviousMonth
    ,MAX(CASE WHEN O.[Type] = 'Sale' THEN O.[Date] END) AS LatestSalesDate
FROM OrderData O
    CROSS JOIN
    (
        SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) AS ThisMonth
            ,DATEADD(m, DATEDIFF(m, 0, GETDATE()) - 1, 0) AS PreviousMonth
            ,DATEADD(m, DATEDIFF(m, 0, GETDATE()) - 2, 0) AS NextPreviousMonth
    ) M
WHERE O.[Date] >= M.NextPreviousMonth
GROUP BY O.[ID], O.Location
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply