How can I select data with input date and data with 1 month previous? 2

[snoopy80]

Greeting.

I have a situation where I have to display a comparison view of data of an input date to the data of a month earlier than the input date. Does it make sense?
Let say I have a table as follow:
Item Date Qty
----- ------ -----
AAA 5/1/08 10
AAA 5/15/08 20
AAA 6/7/08 2
AAA 6/28/08 18

Now let say the user input a date range 6/01/08 to 6/30/08 then the select statement should return as this:
Item Qty lastmonth
---- ----- -----------
AAA 20 30

I sum all qty sold in June for item AAA which is 20 and also show a comparison for last month the same item has 30 as qty had been sold.
Any idea how to create in the same table showing a current selected month and a previous month data? Thanks for help

 

[bborissov]

-- Creating a test data
DECLARE @Test TABLE (Item varchar(200), Date datetime, Qty int)
INSERT INTO @Test VALUES ('AAA','20080501', 10)
INSERT INTO @Test VALUES ('AAA','20080515', 20)
INSERT INTO @Test VALUES ('AAA','20080607', 2)
INSERT INTO @Test VALUES ('AAA','20080628', 18)

DECLARE @InputDate DATETIME
SET @InputDate = '20080630' -- Jul 30, 2008
-- END creating a test data

--- Just change @Test to your actual table name
--- to test that query
SELECT Item,
       SUM(CASE WHEN DATEDIFF(mm,Date,@InputDate) = 0
                THEN Qty
                ELSE 0 END) AS Qty,
        SUM(CASE WHEN DATEDIFF(mm,Date,@InputDate) = 1
                THEN Qty
                ELSE 0 END) AS LastMonth
FROM @Test
WHERE DATEDIFF(mm,@InputDate,Date) < 2
GROUP BY Item

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[memdiscount]

Hi;

I have created your data, here and see the query.

-- crate table
Create table Item (Item varchar(20), itemdate datetime, qty int)

-- insert data
Insert into Item
Select 'AAA' , '5/1/08' , 10
union all
Select 'AAA' , '5/15/08' , 20
union all
Select 'AAA' , '6/7/08' , 2
union all
Select 'AAA' , '6/28/08' , 18
union all
Select 'BBB' , '5/1/08' , 15
union all
Select 'BBB' , '5/15/08' , 20
union all
Select 'BBB' , '6/7/08' , 10
union all
Select 'BBB' , '6/28/08' , 15

-- define variables and set values
Declare @L_StartDate datetime , @L_EndDate datetime
SET @L_StartDate = '6/01/08'
SET @L_EndDate = '6/30/08'

-- final query.
Select
Item.Item ,
Isnull(Sum(Qty),0) as CurrentQty ,
Max(A.LastMonthQty) from item
,
( Select Item,
Isnull(Sum(Qty),0) as LastMonthQty
from Item Where
ItemDate between Dateadd( m , -1 , @L_StartDate)
and Dateadd( m , -1 , @L_EndDate) Group by Item
) A
Where ItemDate between @L_StartDate and @L_EndDate
and A.Item = Item.Item
Group by Item.Item

I hope it will help, it is starter for you, you might have some cases to cover later on.

Thanks

 

[memdiscount]

I like the first solution as well, Thanks Borissov. However, it is only for input date, you can change it to support date ranges.

Thanks

 

[bborissov]

I can, but see no reason
That was not the question.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[snoopy80]

Thanks bborissov and memdiscount for your helps.

Sorry my post may have not been very clear but I do need to consider date range.
I have a question for memdiscount, what does the stmnt below do
Max(A.LastMonthQty) from item
and why
( Select Item,
Isnull(Sum(Qty),0) as LastMonthQty
from Item Where
ItemDate between Dateadd( m , -1 , @L_StartDate)
and Dateadd( m , -1 , @L_EndDate) Group by Item
) A

have a A in the end?

I sure appreciate both of your helps. THANKS

 

[SQLSister]

this is slightly clearer code

Select 
    i.Item , 
    Isnull(Sum(Qty),0) as CurrentQty , 
    Max(A.LastMonthQty) 
from item i
Join
    ( Select  Item, 
        Isnull(Sum(Qty),0) as LastMonthQty 
        from Item Where 
        ItemDate between Dateadd( m , -1 , @L_StartDate)  
        and Dateadd( m , -1   , @L_EndDate) Group by Item 
    )  A 
on  A.Item  = i.Item
Group by i.Item

the A is becasue the query in the () is being used as a derived table (which is one reason why I explicitly showed the join, although I recommend never using the , join syntax anyway) and SQL Server requires all derived tables to have an alias which is what the A is.

I also delted some extra where clasue items which did not change the result set.

Max(will give you the highest LastMonthQty for that item. That will prevent you from having two records if there are two records in the records set. If you wanted to see them if there were, you could group by lastMonth QTY instead.



"NOTHING is more important in a database than integrity." ESquared

 

[snoopy80]

Thanks SQLsister for your explanation.
I still have one more question. I tested memdiscount code and it works great. However, if i change the date range to '5/1/08' to '5/30/08' then the select statements does not return any row. How can I have it to still return the rows for current Qty but lastmonthqty to be Null or 0? Because eventhough last month I did not sell any item AAA, I still need to know that I sold some Qty in the Month of May.
Thanks for all of your Helps.

 

[memdiscount]

Hi ;

Thanks SQLSiter for explaining my code.

snoopy80 you can use left join in the above query.

*****************************
Select
i.Item ,
Isnull(Sum(Qty),0) as CurrentQty ,
Max(A.LastMonthQty)
from item i
left Join
( Select Item,
Isnull(Sum(Qty),0) as LastMonthQty
from Item Where
ItemDate between Dateadd( m , -1 , @L_StartDate)
and Dateadd( m , -1 , @L_EndDate) Group by Item
) A
on A.Item = i.Item
Group by i.Item

*************************

Thanks

 

[snoopy80]

Thanks memdiscount for your helps. it works great!

also appreciate SQLsister and bborissov.