Reverse Cross Tab

[Jim W]

Is is possible to write a query that does the reverse of a cross tab query, I have a table that is mirrored for our Main frame, the table contains a item number a warehouse number and a date field and then 43 fields like field01 thru field43 which store quantities, each field out from 01 is another date our from the date field and I need to look for the item, warehouse and return a date where one of the many field is greater than or equal to the quantity I am looking for, I obviously will have to calculate the date field but thought If I could do the reverse of a cross tab giving a resuld set of 43 records for everyone in the first file I could do a quick update for item number, warehouse match and quantity field greater than what i need returning the date

blah blah blah , sorry I made it sound so confusing

THANKS IN ADVANCE

If anyone can figure out what Jim is looking to do, I think you've got the problem 99% solved.

Jim: Can you simplify this by giving an example of some of the data and cut the 43 fields down to say, 3?


------------
Jim W at 2/19/2002 5:22:35 PM


Is is possible to write a query that does the reverse of a cross tab query, I have a table that is mirrored for our Main frame, the table contains a item number a warehouse number and a date field and then 43 fields like field01 thru field43 which store quantities, each field out from 01 is another date our from the date field and I need to look for the item, warehouse and return a date where one of the many field is greater than or equal to the quantity I am looking for, I obviously will have to calculate the date field but thought If I could do the reverse of a cross tab giving a resuld set of 43 records for everyone in the first file I could do a quick update for item number, warehouse match and quantity field greater than what i need returning the date

blah blah blah , sorry I made it sound so confusing

THANKS IN ADVANCE

Jim
OK...I see what you're trying to do now. Looks like the item#, warehouse, and date are the real key and the 43 avail fields are an "index" from the base date field.
I don't see a good way to produce what you are looking for without normalizing the data. I know this is a mirrored source and that you don't want to change it, but is this a read-only source in SQL server? If so, upon importing, replicating, etc., you might consider creating a second table as a "querying" table consisting of item# whse date qty.
Other than that, I think efficiency would be terrible, but you COULD create a view to query such as vYourTable which is like:

SELECT item,whse,dateadd(dd,0,date),avail1 where avail1 is not NULL
UNION ALL
SELECT item,whse,dateadd(dd,1,date),avail2 where avail2 is not NULL
UNION ALL
...


------------
Jim W at 2/20/2002 9:41:51 AM

my file looks like this

item# whse date avail1 avail2 avail3
12345 1 20020101 0 0 3
12345 2 20020101 0 2 2
23456 1 20020101 1 1 2

the avail1 field is the quantity available on the date in the date field, the next avail date is for the next day and so on, I want to write a query that will make this more searchable so what I would like for a result would be

item# whse date qty
12345 1 20010101 0
12345 1 20020102 0
12345 1 20020103 3
12345 2 20020101 0
12345 2 20020102 2
12345 2 20020103 2
23456 1 20020101 1
23456 1 20020102 1
23456 1 20020103 2

as the first file is a mirror for another source an we can't modify it I am looking for a very fast way to browse this table with a query like

SELECT from ???? where item = @item and whse = @whse and date > @date and qty > @qty

------------
at 2/19/2002 6:46:16 PM

If anyone can figure out what Jim is looking to do, I think you've got the problem 99% solved.

Jim: Can you simplify this by giving an example of some of the data and cut the 43 fields down to say, 3?


------------
Jim W at 2/19/2002 5:22:35 PM


Is is possible to write a query that does the reverse of a cross tab query, I have a table that is mirrored for our Main frame, the table contains a item number a warehouse number and a date field and then 43 fields like field01 thru field43 which store quantities, each field out from 01 is another date our from the date field and I need to look for the item, warehouse and return a date where one of the many field is greater than or equal to the quantity I am looking for, I obviously will have to calculate the date field but thought If I could do the reverse of a cross tab giving a resuld set of 43 records for everyone in the first file I could do a quick update for item number, warehouse match and quantity field greater than what i need returning the date

blah blah blah , sorry I made it sound so confusing

THANKS IN ADVANCE