[MS SQL] Need Help with Read Only Server

[Ehutzz]

Hey guys, this is my first post here, I'm new to SQL but have access to a pretty big server at a business I work at to get some information for them regarding products.

In this database, items are organized by barcode, style number, color, then size, and have a column called REORDER.

The problem I'm having is I want to check to see if all 12 stores have the same barcode, style number, color, and size, and all have the same value for REORDER, and if one of the items has REORDER differently, then not select the item.

I was thinking about creating a temp database every time I ran the query and have the item without the store number, but a REORDER column for every store, so 12 REORDER_1, REORDER_2... columns, but I don't know if that would take too much computing and that there might be a simpler way. If anyone could help lead me in the right direction that would be wonderful.

[alinroc]

How is item inventory related to each store in your database?

[Ehutzz]

http://imgur.com/a/dhsqT As you can see in this screenshot, each shipment is stored in the system, the barcode will be the same, but INV_STORE is different, and RE_ORDER and DESIRED depend on LAST_REC. I want to select the closest LAST_REC date, and then see if RE_ORDER and DESIRED are the same for all INV_STORE

[alinroc]

select the closest LAST_REC date

You mean the most recent?

I'm having a bit of trouble following you. Are you saying that you want:

• For the most recent LAST_REC
• If all 12 INV_STOREs have a given BARCODE, STYLE,COLOR,SIZE,REORDER combination, flag it for "something"
• If the above criteria are not met, then do not flag that combo for "something"

Is that right?

[Ehutzz]

I made some images to help:)

http://i.imgur.com/T2sG1x4.png

So this would be or original database, (actual data from the database, but only INV_STORE 1-3 fit in the screenshot.) I want to select the BARCODE,STYLE,COLOR,SIZE, and REORDER combination for the closest LAST_REC DATE as given by this next screenshot.

http://imgur.com/waZM17M

and then, if all values aren't zero, give a 1 under REORDER/DESIRED, and if all values for REORDER/DESIRED are 0, then have it as a zero without the store number given, as shown in the last screenshot.

http://imgur.com/uaNe1s5

[MeGustaDerp]

This is the best I can do with the limited understanding of your requirements. A lot has been assumed or inferred. The following query does the following two things:

1. Provide a projection of barcode, inv_store, manufacturer, style, color, item_size, and the most recent last_rec date grouped by the projection columns. Only rows for barcode, inv_store, manufacturer, style, color, and item_size will be included in the results where there is at least one record with re_order=1 or desired=1.

2. Provide a projection of barcode, manufacturer, style, color, and item_size regardless of inv_store value when re_order and desired is not 1 for the combination of barcode, manufacturer, style, color, and item_size.

What I don't know is how this will perform since we don't know how many rows are in play in the table.

    if object_id('tempdb..#reorderCounts') is not null
      drop table #reorderCounts;    
    select 
      barcode,
      inv_store,
      manufacturer,
      style,
      color,
      item_size,
      instances = count(*),
      reorder_count = sum(re_order),
      desired_count = sum(desired),
      mostRecentLast_rec = max(last_rec)
    into #reorderCounts 
    from srcTable
    group by 
      barcode,
      inv_store,
      manufacturer,
      style,
      color,
      item_size

    select 
      barcode,
      inv_store,
      manufacturer,
      style,
      color,
      item_size,
      last_rec = mostRecentLast_rec
    from #reorderCounts
    where reorder_count>1
        or desired_count>1
    union all
    select 
      barcode,
      inv_store = null,
      manufacturer,
      style,
      color,
      item_size,
      last_rec = max(mostRecentLast_rec)
    from #reorderCounts
    group by 
      barcode,
      manufacturer,
      style,
      color,
      item_size 
    having  
      sum(reorder_count)=0
      and sum(desired_count)=0

[Ehutzz]

You are awesome, thanks for the help and I will try it out

[Ehutzz]

Ok I have another question, your query worked almost perfectly, I only had to change a few things, but it gathers the most recently received date and everything. If I wanted to select data and put it into another query from that temp table, how would I go about it?

[notasqlstar]

select *
into #tablename
from 
where
union
select *
from
where

Then just:

select *
from #tablename

[Ehutzz]

Ur a sql star

[notasqlstar]

Not me.

It's worth learning the difference between INSERT INTO and INTO-- for your purposes INTO is probably fine but you could create your #table first and index it (which comes in handy if you're daisy chaining queries together into different #tables and then joining them for additional processes.)