0

I am trying to fetch data between the (max date in the entrydate column) and (max date in the entrydate column-15days)!!

Table1:Totally it has 953rows enter image description here

Table2: Totally it has 400rows enter image description here

i got this error ,Please help me !!

    Select  
o_material_transaction_inward.Mat_Code,
s_material_details.Mat_Spec,
s_material_details.Mat_Make,
o_material_transaction_inward.Sup_Name,
o_material_transaction_inward.Entry_Date,
o_material_transaction_inward.DC_qty,
o_material_transaction_inward.Received_qty,
    from 
o_material_transaction_inward 
    join 
s_material_details 
    on 
s_material_details.Mat_Code=o_material_transaction_inward.Mat_Code and s_material_details.Mat_Group_Id=o_material_transaction_inward.Mat_Group_id
    where 
o_material_transaction_inward.Entry_Date between Max(o_material_transaction_inward.Entry_Date) and Max(o_material_transaction_inward.Entry_Date - 15)
Ranjit Kumar
  • 779
  • 2
  • 8
  • 20

3 Answers3

2

Update: Fixed BETWEEN predicate:

The reason it wasn't working is that the lowest value need to be the first value in the BETWEEN predicate, so in order to get those rows from the two tables having the entry_date between the max entry date - 15 and max entry date try this:

SELECT
  o.Mat_Code,
  s.Mat_Spec,
  s.Mat_Make,
  o.Sup_Name,
  DATE_FORMAT(o.Entry_Date, '%Y-%m-%d') AS Entry_Date,
  o.DC_qty,
  o.Received_qty
FROM o_material_transaction_inward AS o
INNER JOIN s_material_details      AS s  ON s.Mat_Code     = o.Mat_Code
WHERE o.Entry_Date BETWEEN ((SELECT Max(Entry_Date) 
                            FROM o_material_transaction_inward) - 15)
                       AND (SELECT Max(Entry_Date) 
                            FROM o_material_transaction_inward) ;

SQL Fiddle Demo

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • 0rows affected as the output , i used Having instead of between but it fetched all the rows except the 15rows i expected. – Ranjit Kumar Feb 06 '13 at 04:31
  • @RanjitKumar - Can you please add some sample data of these two tables `o_material_transaction_inward`, `s_material_details` and the expected result that you want to get from these two tables. This will be very helpful. thanks. – Mahmoud Gamal Feb 06 '13 at 04:34
0

Try:

    Select  
o_material_transaction_inward.Mat_Code,
s_material_details.Mat_Spec,
s_material_details.Mat_Make,
o_material_transaction_inward.Sup_Name,
o_material_transaction_inward.Entry_Date,
o_material_transaction_inward.DC_qty,
o_material_transaction_inward.Received_qty,
    from 
o_material_transaction_inward 
    join 
s_material_details 
    on 
s_material_details.Mat_Code=o_material_transaction_inward.Mat_Code and s_material_details.Mat_Group_Id=o_material_transaction_inward.Mat_Group_id
    having 
o_material_transaction_inward.Entry_Date between Max(o_material_transaction_inward.Entry_Date) and Max(o_material_transaction_inward.Entry_Date - 15)

(see the accepted answer of MySQL: Invalid use of group function for an explanation of where vs having)

Community
  • 1
  • 1
tmuguet
  • 1,165
  • 6
  • 10
0

This should work:

SELECT
  o.Mat_Code,
  s.Mat_Spec,
  s.Mat_Make,
  o.Sup_Name,
  o.Entry_Date,
  o.DC_qty,
  o.Received_qty,
FROM o_material_transaction_inward AS o
INNER JOIN s_material_details      AS s  ON s.Mat_Code     = o.Mat_Code
                                        AND s.Mat_Group_Id = o.Mat_Group_id
WHERE o.Entry_Date BETWEEN (SELECT Max(Entry_Date) 
                            FROM o_material_transaction_inward) 
                       AND (SELECT Max(Entry_Date) 
                            FROM o_material_transaction_inward) - 15);
user2001117
  • 3,727
  • 1
  • 18
  • 18
  • No error and also 0 rows affected as output .It havent fetched the Data between the (Max date) and (Max date-15) – Ranjit Kumar Feb 06 '13 at 04:27
  • For selecting the Max(Entry Date) i used this below query but it haven't worked. (SELECT DATE_SUB(Max(Entry_Date),INTERVAL 15 DAY) FROM o_material_transaction_inward) in the last line – Ranjit Kumar Feb 06 '13 at 04:29