Post reply

Help Please

  • May 25, 2006 at 6:52 am

    #113199

    Hi All

    Want make report which tell me that how many items with aparticular item code i sold in last 8 weeks.

    Here is my query:-

    SELECT     NewStock.Itemcode, ItemD.ItemDescription, ItemD.ItemGroupMajor, NewStock.Costperunit,

                          NewStock.Physicalquantity, NewStock.Stockissueunitsonorder, NewStock.Stockissueunitsallocated, NewStock.Stockissueunitsbackordered,

                          NewStock.Stockissueunitsavailable, StockMovement.[Movement Qty] AS Qty, Date_Calendar.WeekNumber

    FROM         NewStock INNER JOIN

                          ItemD ON NewStock.Itemcode = ItemD.ItemCode INNER JOIN

                          StockMovement ON ItemD.ItemCode = StockMovement.Item INNER JOIN

                          Date_Calendar ON StockMovement.[Date of Movement] = Date_Calendar.S21Date

    where    (NewStock.StockDate = '1060524') AND (StockMovement.[Transaction Type] = 'O')

    Table which i want having all fields in the same order which i mentioned above

    The thing is Stockmovement table has daily stock movement according to item code Transaction Type means sold out items.I want information about all items which has in NewStock table.Need to do sum of particular item with in week.

    i need to add 12 new fields in my table first 8 fields are 8 week data (WK1....WK8) one for 8week avg , 4 week avg and week to day sale.

    for eg If A item movement on monday is 2 on tuesday 7 rest days 0 so on week1 it will show 9 then same for WK2.....

    Please let me know what i can do i tried so many things but my output is not right.

  • May 25, 2006 at 7:35 am

    #640033

    use select count(item)

    where datesold between getdate() and dateadd('ww',getdate(),-8)

    group by item

     

     

    www.sql-library.com[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply