Trying to include to the same row, the last and first dates with to-date monies and quantities to-date in SQL script

We have a table of purchases that consists of a header and detail tables and the products or items to purchase table.  We are trying to create a script that would produce the following:

Product  LastPurchDate  FirstPurchDate  LastVendor LastPurchNo PurchToDateMonies PurchToDateQtys
Prod1    4/15/2013      1/1/2013        TWA        Purch3      66                11
Prod2    4/15/2013      2/28/2013       TWA        Purch3      30.45              3

Open in new window


As displayed, we are trying to script so it can show the products or item' status based on thier activities where it would display: Last-Purchase-Date, First-Purchase-Date, Last-Vendor, Last-Purchase-No, Purchase-To-Date-Monies and Purchase-To-Date-Qtys.


The table contents are as follows:

ProductTable:

Products
--------
Prod1
Prod2

PurchaseHeaderTable:

PurchNo  PurchDate  Vendor
-------  ---------  -------
Purch1   1/1/2013    IBM
Purch2   2/28/2013   TWA
Purch3   4/15/2013   TWA

PurchaseDetailTable:

PurchNo  Prod   Qty Cost
-------  -----  --- -----
Purch1   Prod1  5   4.25
Purch2   Prod2  2   10.15
Purch2   Prod1  4   1.25
Purch2   Prod1  1   4.25
Purch3   Prod1  2   4.25
Purch3   Prod2  1   10.15


We have tried a combination of sub-scripts using max but can't seem to get.
rayluvsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rayluvsAuthor Commented:
Forgot, prepared the data as virtual tables:

DECLARE @Products TABLE(
        Prod VARCHAR(20))
DECLARE @PurchHeader TABLE(
        PurchNo VARCHAR(20),
        PurchDate DATE,
        Vendor VARCHAR(20))
DECLARE @PurchDetail TABLE(
        PurchNo VARCHAR(20),
        Prod VARCHAR(20),
        Qty INT,
        Cost FLOAT)
       
INSERT INTO @Products SELECT 'Prod1' 
INSERT INTO @Products SELECT 'Prod2' 
INSERT INTO @PurchHeader SELECT 'Purch1','1/1/2013','IBM'
INSERT INTO @PurchHeader SELECT 'Purch2','2/28/2013','TWA'
INSERT INTO @PurchHeader SELECT 'Purch3','4/15/2013','TWA'
INSERT INTO @PurchDetail SELECT 'Purch1','Prod1',5,4.25 
INSERT INTO @PurchDetail SELECT 'Purch2','Prod2',2,10.15 
INSERT INTO @PurchDetail SELECT 'Purch2','Prod1',4,1.25 
INSERT INTO @PurchDetail SELECT 'Purch2','Prod1',1,4.25 
INSERT INTO @PurchDetail SELECT 'Purch3','Prod1',2,4.25 
INSERT INTO @PurchDetail SELECT 'Purch3','Prod2',1,10.15 

select * from @Products 
select * from @PurchHeader
select * from @PurchDetail

SELECT p.Prod, p1.PurchNo, p1.Vendor, p2.Qty, (p2.Qty*p2.Cost) 'Total' from @Products p
INNER JOIN @PurchDetail p2 ON p.Prod=p2.Prod
INNER JOIN @PurchHeader p1 ON p2.PurchNo=p1.PurchNo 

Open in new window


THE RESULTS DESIRED:

Product  LastPurchDate  FirstPurchDate  LastVendor LastPurchNo PurchToDateMonies PurchToDateQtys
Prod1    4/15/2013      1/1/2013        TWA        Purch3      66                11
Prod2    4/15/2013      2/28/2013       TWA        Purch3      30.45              3

Open in new window

0
PortletPaulfreelancerCommented:
Just noticed the cost was treated as int, but aside from that issue this result (from the sample data):
| PRODUCT |                LASTPURCHDATE |                  FIRSTPURCHDATE | LASTVENDOR | LASTPURCHNO | PURCHTODATEMONIES | PURCHTODATEQTYS |
|---------|------------------------------|---------------------------------|------------|-------------|-------------------|-----------------|
|   Prod1 | April, 15 2013 00:00:00+0000 |  January, 01 2013 00:00:00+0000 |        TWA |      Purch3 |                36 |              12 |
|   Prod2 | April, 15 2013 00:00:00+0000 | February, 28 2013 00:00:00+0000 |        TWA |      Purch3 |                30 |               3 |

Open in new window

Produced by the following query:
SELECT
        prod                                          AS Product
      , max(CASE WHEN latest = 1 THEN Purchdate END)  AS LastPurchDate
      , min(CASE WHEN oldest = 1 THEN Purchdate END)  AS FirstPurchDate
      , min(CASE WHEN latest = 1 THEN Vendor END)     AS LastVendor
      --, min(case when oldest = 1 then Vendor end)     as FirstVendor
      , min(CASE WHEN latest = 1 THEN PurchNo END)     AS LastPurchNo
      --, min(case when oldest = 1 then PurchNo end)     as FirstPurchNo
      , SUM(Qty*Cost)                                 AS PurchToDateMonies
      , SUM(Qty)                                      AS PurchToDateQtys
FROM (
       SELECT
                pd.prod, pd.qty, pd.cost, ph.*
              , row_number() over (partition BY pd.prod ORDER BY ph.purchdate ASC) oldest
              , row_number() over (partition BY pd.prod ORDER BY ph.purchdate DESC) latest
       FROM PurchDetail PD
       INNER JOIN PurchHeader PH ON PD.PurchNo=PH.PurchNo
     ) AS derived
GROUP BY
  prod

/*
Expected
Product  LastPurchDate  FirstPurchDate  LastVendor LastPurchNo PurchToDateMonies PurchToDateQtys
Prod1    4/15/2013      1/1/2013        TWA        Purch3      66                11
Prod2    4/15/2013      2/28/2013       TWA        Purch3      30.45              3
*/



CREATE TABLE Products
    ([Prod] varchar(5))
;
    
INSERT INTO Products
    ([Prod])
VALUES
    ('Prod1'),
    ('Prod2')
;


CREATE TABLE PurchHeader
    ([PurchNo] varchar(6), [Purchdate] datetime, [Vendor] varchar(3)) 
;
    
INSERT INTO PurchHeader
    ([PurchNo], [Purchdate], [Vendor])
VALUES
    ('Purch1', '2013-01-01 00:00:00', 'IBM'),
    ('Purch2', '2013-02-28 00:00:00', 'TWA'),
    ('Purch3', '2013-04-15 00:00:00', 'TWA')
;

CREATE TABLE PurchDetail 
    ([PurchNo] varchar(6), [Prod] varchar(5), [Qty] int, [Cost] int)
;
    
INSERT INTO PurchDetail 
    ([PurchNo], [Prod], [Qty], [Cost])
VALUES
    ('Purch1', 'Prod1', 5, 4.25),
    ('Purch2', 'Prod2', 2, 10.15),
    ('Purch2', 'Prod1', 4, 1.25),
    ('Purch2', 'Prod1', 1, 4.25),
    ('Purch3', 'Prod1', 2, 4.25),
    ('Purch3', 'Prod2', 1, 10.15)
;



[1]: http://sqlfiddle.com/#!3/308f5/1

Open in new window

0
PortletPaulfreelancerCommented:
corrected that int problem, and added date formats (you can change to suit):
| PRODUCT | LASTPURCHDATE | FIRSTPURCHDATE | LASTVENDOR | LASTPURCHNO | PURCHTODATEMONIES | PURCHTODATEQTYS |
|---------|---------------|----------------|------------|-------------|-------------------|-----------------|
|   Prod1 |    2013-04-15 |     2013-01-01 |        TWA |      Purch3 |                39 |              12 |
|   Prod2 |    2013-04-15 |     2013-02-28 |        TWA |      Purch3 |             30.45 |               3 |

SELECT
        prod                                          AS Product
      , convert(varchar(10),max(CASE WHEN latest = 1 THEN Purchdate END),121)  AS LastPurchDate
      , convert(varchar(10),min(CASE WHEN oldest = 1 THEN Purchdate END),121)  AS FirstPurchDate
      , min(CASE WHEN latest = 1 THEN Vendor END)     AS LastVendor
      --, min(case when oldest = 1 then Vendor end)     as FirstVendor
      , min(CASE WHEN latest = 1 THEN PurchNo END)     AS LastPurchNo
      --, min(case when oldest = 1 then PurchNo end)     as FirstPurchNo
      , SUM(Qty*Cost)                                 AS PurchToDateMonies
      , SUM(Qty)                                      AS PurchToDateQtys
FROM (
       SELECT
                pd.prod, pd.qty, pd.cost, ph.*
              , row_number() over (partition BY pd.prod ORDER BY ph.purchdate ASC) oldest
              , row_number() over (partition BY pd.prod ORDER BY ph.purchdate DESC) latest
       FROM PurchDetail PD
       INNER JOIN PurchHeader PH ON PD.PurchNo=PH.PurchNo
     ) AS derived
GROUP BY
  prod
;

http://sqlfiddle.com/#!3/8fb07/3

Open in new window

{+ edit - correction - typo}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

rayluvsAuthor Commented:
Works, we are validating the result.  

Noticed that the table 'Products' was not used in your example.  We need to include the product 'Description' in the result.

Here's what we need (please advice):
[The modifications are woth the "-- << --" CAPITAL LETTER
SELECT
        prod                                          AS Product
      , Description  -- << THIS IS THE COLUMN WE NEED FROM Product Table
      , convert(varchar(10),max(CASE WHEN latest = 1 THEN Purchdate END),121)  AS LastPurchDate
      , convert(varchar(10),min(CASE WHEN oldest = 1 THEN Purchdate END),121)  AS FirstPurchDate
      , min(CASE WHEN latest = 1 THEN Vendor END)     AS LastVendor
      --, min(case when oldest = 1 then Vendor end)     as FirstVendor
      , min(CASE WHEN latest = 1 THEN PurchNo END)     AS LastPurchNo
      --, min(case when oldest = 1 then PurchNo end)     as FirstPurchNo
      , SUM(Qty*Cost)                                 AS PurchToDateMonies
      , SUM(Qty)                                      AS PurchToDateQtys
FROM (
       SELECT
                pd.prod, pd.qty, pd.cost, ph.*, p.* -- << ADDED ALIAS OF Product 'p.*'
              , row_number() over (partition BY pd.prod ORDER BY ph.purchdate ASC) oldest
              , row_number() over (partition BY pd.prod ORDER BY ph.purchdate DESC) latest
       FROM PurchDetail PD
       INNER JOIN PurchHeader PH ON PD.PurchNo=PH.PurchNo
       INNER JOIN Products p ON p.Prod = pd.Prod -- << JOINED PurchDetail and Products to display the details Product related 'Descriptiopm'
     ) AS derived
GROUP BY
  prod

Open in new window

0
PortletPaulfreelancerCommented:
>>Noticed that the table 'Products' was not used in your example.
correct, it wasn't needed in the example provided  (and I don't like to include things that are not needed); but what you have added is correct.

Note, if you don't need/want the 'oldest', just the MIN(Purchdate ), then it could be further simplified as below:
SELECT
        prod                                          AS Product
      , Description  -- << THIS IS THE COLUMN WE NEED FROM Product Table
      , convert(varchar(10),MAX(CASE WHEN latest = 1 THEN Purchdate END),121)  AS LastPurchDate
      , convert(varchar(10),MIN(Purchdate),121)  AS FirstPurchDate
      , MAX(CASE WHEN latest = 1 THEN Vendor END)     AS LastVendor
      , MAX(CASE WHEN latest = 1 THEN PurchNo END)    AS LastPurchNo
      , SUM(Qty*Cost)                                 AS PurchToDateMonies
      , SUM(Qty)                                      AS PurchToDateQtys
FROM (
       SELECT
              pd.prod
            , pd.qty
            , pd.cost
            , ph.Purchdate --<< specify the fields needed, don't use * :)
            , ph.Vendor
            , ph.PurchNo
            , p.Description -- << ADDED ALIAS OF Product 'p.*'
            , row_number() over (partition BY pd.prod ORDER BY ph.purchdate DESC) latest
       FROM PurchDetail PD
       INNER JOIN PurchHeader PH ON PD.PurchNo = PH.PurchNo
       INNER JOIN Products p ON p.Prod = pd.Prod -- << JOINED PurchDetail and Products to display the details Product related 'Descriptiopm'
     ) AS derived
GROUP BY
  prod
; 

Open in new window

Please note that although I used ph.* using * isn't actually recommended and that the fields should be specified. Also note I changed some MIN() to MAX() which is more "conventional" and may be easier to understand - but as we are using row_number() to determine that data you could use either min() or max() and the result would be the same.
0
rayluvsAuthor Commented:
got the error:

Msg 8120, Level 16, State 1, Line 86
Column 'derived.Description' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Trying troubleshoot it, if i change "p.Description" to "p.*", I get:

Msg 8156, Level 16, State 1, Line 104
The column 'Prod' was specified multiple times for 'derived'.
(This i understand since Prod is in p.*)

If I comment out or delete the lines:
, Description  -- << THIS IS THE COLUMN WE NEED FROM Product Table
, p.Description -- << ADDED ALIAS OF Product 'p.*'

It works

Please advice (we continue)
0
PortletPaulfreelancerCommented:
>>Column 'derived.Description' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

so, add it to the GROUP BY clause

>>The column 'Prod' was specified multiple times for 'derived'.

This could occur if you ignored my advice to avoid using *
--<< specify the fields needed, don't use * :)

Your actions:

add description into the group by clause
don't use p.* (or ph.* or pd.*) in the selection list specify the fields you actually need
0
rayluvsAuthor Commented:
Thanx it worked!
0
rayluvsAuthor Commented:
Thanx
0
PortletPaulfreelancerCommented:
Great, well done. Thanks for the grading. Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.