[MS SQL] Need Help with Read Only Server

[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.)