Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

newcents1
Starting Member

4 Posts

Posted - 2009-09-24 : 12:27:55

Any help appreciated, I'm stuck!

Using MSSQL 2005.

I'm a .net/c# developer so can handle the logic side of things on that end, but putting together SQL database queries I'm fuzzy on.

Each week the number of products in 3 different locations are updated every Tuesday morning at 1pm. For example rows 1 and 2:

id product_id date_time loc1 loc2 loc3
1 1 2009-09-22 01:16:20.513 6 12 5
2 2 2009-09-22 01:16:22.283 2 1 2
3 1 2009-09-23 15:06:24.550 6 11 5
4 1 2009-09-23 15:06:35.267 2 3 3

The id column is [int] IDENTITY(1,1) NOT NULL
product_id is just the id of the product in our products table
date_time is when this entry was updated in the database
loc1 is the stock of this product at location 1
loc2 is the stock of this product at location 2
loc3 is the stock of this product at location 3

However, at anytime during the week after Tuesday morning at 1am, the inventory amounts at each location could be updated, for example rows 3 and 4 above.

1)
So what I'm trying to figure out is how to create a query that will get the most recent inventory numbers for today for all products. It needs to only go back to Tuesday of that week we are reporting on at 1am, (for example, 9/22/09 at 1am). For example, maybe we run the query today (9/24/2009 at 1pm) and the last entry for product_id=1 in the database was 9/21/09 (Monday), we don't want to report on these numbers because they are from Monday. But for example product_id=2 had three records:

47 2 2009-09-23 21:16:28.460 2 1 2
48 2 2009-09-24 01:16:30.250 1 1 1
49 2 2009-09-24 11:16:32.183 3 1 3

We would only be interested in the last record (49) as it has the most recent data. But each product could have different dates when the last inventory numbers were pulled.

The query should generate the inventory for each product like:
product_id loc1 loc2 loc3
1 13 7 2
2 3 1 3
3 1 0 9

2)
I need to get the history of inventory numbers for last week, or another week in time. For example, Tuesday, 9/1/2009 at 1am to Tuesday, 9/8/2009 at 12:59am. And the exact same results from above would be pulled. The query would only pull the most recent inventory numbers to Tuesday, 9/8/2009 at 12:59am for each product. For example, these would be the results, where 1 row is shown for each product, and the most recent numbers between Tuesday, 9/1/2009 at 1am to Tuesday, 9/8/2009 at 12:59am are shown for that period of time:

product_id loc1 loc2 loc3
1 13 7 2
2 3 1 3
3 1 0 9

3)
To make matters more difficult, I need to compare history of inventory and how much it's moved compared to last week. For example, using data like this which is a snapshot of rows taken from dates Tuesday, 9/1/2009 at 1am to Tuesday, 9/8/2009 at 12:59am:

24 1 2009-09-02 20:48:39.623 6 12 5
25 2 2009-09-02 20:48:41.027 2 1 2
26 3 2009-09-02 20:48:42.527 8 1 2
33 1 2009-09-03 21:15:33.313 6 12 5
24 1 2009-09-03 20:48:39.623 6 12 5
25 2 2009-09-03 20:48:41.027 2 1 2
26 3 2009-09-04 20:48:42.527 8 1 2
33 1 2009-09-04 21:15:33.313 6 12 5

We are only interested in the data for each product which is closest to and before Tuesday, 9/8/2009 at 12:59am and doesn't go back further than Tuesday, 9/1/2009 at 1am.

and then another snap shot of rows taken from Tuesday, 9/8/2009 at 1am to Tuesday, 9/15/2009 at 12:59am (only interested in the data for each product which is closest to and before Tuesday, 9/15/2009 at 12:59am and doesn't go back further than Tuesday, 9/8/2009 at 1am):
29 1 2009-09-09 01:52:27.630 5 11 6
30 1 2009-09-09 20:52:27.630 6 12 5
31 2 2009-09-09 20:52:29.477 2 1 2
32 3 2009-09-09 20:52:31.570 8 1 2

The query should generate, where the results are like this:
product_id loc1 loc2 loc3 loc1_move loc2_move loc3_move
1 6 12 5 1 -2 5
2 7 1 0 4 0 -3

So the numbers that show in loc1_move, loc2_move, loc3_move would show how much the product inventory has moved since the past week for that location. So with the example above, loc2 for product_id=1 has 12 in it for the week we're reporting on (9/8/2009-9/15/2009), but last week it had 14 so -2 is showing in loc2_move because the inventory level is down 2 from last week.

Hope I've explained this well and it isn't terribly confusing!

Thanks for any help!
Jeff

GeorgeMeng
Starting Member

1 Post

Posted - 2009-09-24 : 14:04:21

It is an interesting question, you want one query to get the result in Step 3? or for every step, you want one query?

Best Regards,

George Meng

newcents1
Starting Member

4 Posts

Posted - 2009-09-24 : 18:38:18

Hmm...I need a query for every step. Thanks for any help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-25 : 13:40:20


SELECT t.id,t.product_id,t.loc1,t.loc2,t.loc3
FROM yourtable t
INNER JOIN (SELECT product_id,MAX(date_time) AS latest
            FROM yourtable
            WHERE date_time>= DATEADD(dd,((DATEDIFF(dd,0,GETDATE())/7) * 7) + 1,0)
            AND date_time< DATEADD(dd,((((DATEDIFF(dd,0,GETDATE())/7)+1) * 7) +1,1)
            GROUP BY product_id)t1
ON t1.product_id=t.product_id
AND t1.latest=t.date_time


SELECT t.id,t.product_id,t.loc1,t.loc2,t.loc3
FROM yourtable t
INNER JOIN (SELECT product_id,MAX(date_time) AS latest
            FROM yourtable
            WHERE date_time>= DATEADD(dd,(((DATEDIFF(dd,0,GETDATE())/7)-1) * 7) +1 ,0)
            AND date_time< DATEADD(dd,((DATEDIFF(dd,0,GETDATE())/7) * 7) +1 ,0)
            GROUP BY product_id)t1
ON t1.product_id=t.product_id
AND t1.latest=t.date_time

newcents1
Starting Member

4 Posts

Posted - 2009-09-26 : 15:26:48

Thank you so much! This is very helpful!

For #3 if there was a way to do this:

SELECT t.id,t.product_id,t.loc1,t.loc2,t.loc3
FROM yourtable t
INNER JOIN (SELECT product_id,MAX(date_time) AS latest
FROM yourtable
WHERE date_time>= '09/22/09'
AND date_time< '09/28/09'
GROUP BY product_id)t1
ON t1.product_id=t.product_id
AND t1.latest=t.date_time

JOIN with these results below, based on product_id (the same report just covering different dates)

SELECT t.id,t.product_id,t.loc1,t.loc2,t.loc3
FROM yourtable t
INNER JOIN (SELECT product_id,MAX(date_time) AS latest
FROM yourtable
WHERE date_time>= '09/15/09'
AND date_time< '09/21/09'
GROUP BY product_id)t1
ON t1.product_id=t.product_id
AND t1.latest=t.date_time

So the results would print like:
product_id, loc1, loc2, loc3, previous_loc1, previous_loc2, previous_loc3

Is there anyway to do this?
Thank you for any help!

newcents1
Starting Member

4 Posts

Posted - 2009-09-29 : 15:28:43

Any ideas on the last query? I'm confused about this one.

Thanks for any help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-30 : 00:24:14

[code]SELECT t.id,t.product_id,t.loc1,t.loc2,t.loc3,
t.loc1-t1.loc1 AS loc1_move,
t.loc2-t1.loc2 AS loc2_move,
t.loc3-t1.loc3 AS loc3_move
FROM yourtable t
INNER JOIN (SELECT product_id,MAX(date_time) AS latest
FROM yourtable
WHERE date_time>= DATEADD(dd,(((DATEDIFF(dd,0,GETDATE())/7)-1) * 7) +1 ,0)
AND date_time< DATEADD(dd,((DATEDIFF(dd,0,GETDATE())/7) * 7) +1 ,0)
GROUP BY product_id)t1
ON t1.product_id=t.product_id
AND t1.latest=t.date_time
CROSS APPLY(SELECT TOP 1 loc1,loc2,loc3
FROM yourtable
WHERE date_time>= DATEADD(dd,(((DATEDIFF(dd,0,GETDATE())/7)-2) * 7) +1 ,0)
AND date_time< DATEADD(dd,(((DATEDIFF(dd,0,GETDATE())/7)-1) * 7) +1 ,0)
AND product_id=t.product_id
ORDER BY date_time DESC)t2
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources