Need Help: Challenging SELECT Query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need Help: Challenging SELECT Query

Author

Topic

ray.ragan
Starting Member

13 Posts

Posted - 2007-09-13 : 16:26:24

I have a table of items. In the table, I have two columns, one that tells me the date the item was put on a shelf and a column that tells me when the item expires. Here's the challenge, how do I write a query that will tell me how many items I have on the shelf (non-expired items) on any given date or date range?

I've been racking my brain trying to solve this, to no avail.

Any suggestions would be appreciated.

Thank you,

Ray K. Ragan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 16:30:53

Do you have any sample data?
And expected output?

E 12°55'05.25"
N 56°04'39.16"

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-13 : 16:33:29

challenging or...??
hints:
select with a where condition on the date columns,
possibly group by

--------------------
keeping it simple...

ray.ragan
Starting Member

13 Posts

Posted - 2007-09-13 : 16:44:13

Jen, here's some sample data:

Desc shelf_date exp_date
Item 1 13 sep 07 23 sep 07
Item 2 13 sep 07 20 sep 07
Item 3 1 sep 07 10 sep 07

Easy part:
I need to know how many items I have on the shelf on 19 sep 07

Hard part:
I need to know how many items I have on the shelf from 3 sep 07 through 15 sep 07

I tried a GROUP BY, but I couldn't get it to work on the hard part.

Thank you,

Ray K. Ragan

ray.ragan
Starting Member

13 Posts

Posted - 2007-09-13 : 16:45:50

I should be more explicit, I have the easy part working fine. It's the hard part that is stumping me.

Thank you,

Ray K. Ragan

ray.ragan
Starting Member

13 Posts

Posted - 2007-09-13 : 16:58:56

quote:
Originally posted by Peso

Do you have any sample data?
And expected output?

Thank you for responding, yes here's a some sample data:

Desc shelf_date exp_date
Item 1 13 sep 07 23 sep 07
Item 2 13 sep 07 20 sep 07
Item 3 1 sep 07 10 sep 07

I need to know how many items I have on the shelf given a date range, so from 9 Sep 07 to 14 sep 07 would look something like this:

9 sep 07
1

10 sep 07
1

11 sep 07
0

12 sep 07
0

13 sep 07
2

14 sep 07
2

I played with GROUP BY, but it would tell me how many products I had expiring on a given date, but I need the sum on the shelf for given date range by day.

Thoughts?

Ray K. Ragan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 17:13:12

[code]-- Prepare sample data
DECLARE @Sample TABLE (Descr VARCHAR(20), ShelfDate DATETIME, ExpDate DATETIME)

INSERT @Sample
SELECT 'Item 1', '13 sep 07', '23 sep 07' UNION ALL
SELECT 'Item 2', '13 sep 07', '20 sep 07' UNION ALL
SELECT 'Item 3', '1 sep 07', '10 sep 07'

-- Show the expected output
SELECT COUNT(*)
FROM @Sample
WHERE '20070914' BETWEEN ShelfDate AND ExpDate[/code]

E 12°55'05.25"
N 56°04'39.16"

ray.ragan
Starting Member

13 Posts

Posted - 2007-09-13 : 17:32:53

quote:

Originally posted by Peso

-- Prepare sample data
DECLARE @Sample TABLE (Descr VARCHAR(20), ShelfDate DATETIME, ExpDate DATETIME)

INSERT	@Sample
SELECT	'Item 1', '13 sep 07', '23 sep 07' UNION ALL
SELECT	'Item 2', '13 sep 07', '20 sep 07' UNION ALL
SELECT	'Item 3', '1 sep 07', '10 sep 07'

-- Show the expected output
SELECT	COUNT(*)
FROM	@Sample
WHERE	'20070914' BETWEEN ShelfDate AND ExpDate

Thank you, I have that.

However, I need to return COUNT of items for each date within a given date range. I can do it programmatically via C# by recursively calling a stored proc. But I want to write one stored proc where I can pass two parameters, a start and end date. Then have it return a count and date for each date within the parameter range. Thereby keeping the load on the DBMS side.

Am I asking the impossible?

Thank you,

Ray K. Ragan

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-13 : 17:33:24

exasperated?

quote:

Originally posted by Peso

-- Prepare sample data
DECLARE @Sample TABLE (Descr VARCHAR(20), ShelfDate DATETIME, ExpDate DATETIME)

INSERT	@Sample
SELECT	'Item 1', '13 sep 07', '23 sep 07' UNION ALL
SELECT	'Item 2', '13 sep 07', '20 sep 07' UNION ALL
SELECT	'Item 3', '1 sep 07', '10 sep 07'

-- Show the expected output
SELECT	COUNT(*)
FROM	@Sample
WHERE	'20070914' BETWEEN ShelfDate AND ExpDate

E 12°55'05.25"
N 56°04'39.16"

--------------------
keeping it simple...

mayurcreation
Starting Member

16 Posts

Posted - 2007-09-13 : 17:34:51

This might help you....

select * from itemdetail
description shelf_dt exp_dt
-----------------------------------
Item2 2007-09-14 2007-09-27
Item3 2007-09-14 2007-09-28
Item4 2007-09-13 2007-09-26
Item5 2007-09-13 2007-09-23
Item6 2007-09-13 2007-09-20
Item7 2007-09-01 2007-09-10

Now here we go.....

select shelf_dt, count(*) as Count from itemdetail
where shelf_dt >= '9/13/07'
and shelf_dt <= '9/29/07'
group by shelf_dt

Output....
shelf_dt Count
---------------------
2007-09-13 3
2007-09-14 2

This answers to your question ???

ray.ragan
Starting Member

13 Posts

Posted - 2007-09-13 : 17:37:20

quote:

Originally posted by jen

exasperated?

quote:

Originally posted by Peso

-- Prepare sample data
DECLARE @Sample TABLE (Descr VARCHAR(20), ShelfDate DATETIME, ExpDate DATETIME)

INSERT	@Sample
SELECT	'Item 1', '13 sep 07', '23 sep 07' UNION ALL
SELECT	'Item 2', '13 sep 07', '20 sep 07' UNION ALL
SELECT	'Item 3', '1 sep 07', '10 sep 07'

-- Show the expected output
SELECT	COUNT(*)
FROM	@Sample
WHERE	'20070914' BETWEEN ShelfDate AND ExpDate

E 12°55'05.25"
N 56°04'39.16"

--------------------
keeping it simple...

Does it show?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 17:38:04

No. You only have to explain better.

-- Prepare sample data
DECLARE @Sample TABLE (Descr VARCHAR(20), ShelfDate DATETIME, ExpDate DATETIME)

INSERT	@Sample
SELECT	'Item 1', '13 sep 07', '23 sep 07' UNION ALL
SELECT	'Item 2', '13 sep 07', '20 sep 07' UNION ALL
SELECT	'Item 3', '1 sep 07', '10 sep 07'

-- Show the expected output
SELECT		f.Date,
		COUNT(s.ShelfDate) AS Items
FROM		F_TABLE_DATE('20070831', '20070924') AS f
LEFT JOIN	@Sample AS s ON s.ShelfDate <= f.Date AND s.ExpDate >= f.Date
GROUP BY	f.Date
ORDER BY	f.Date

The function F_TABLE_DATE can be found in the Script Library.

E 12°55'05.25"
N 56°04'39.16"

mayurcreation
Starting Member

16 Posts

Posted - 2007-09-13 : 17:41:11

ray.ragan
Starting Member

13 Posts

Posted - 2007-09-13 : 17:44:38

quote:
Originally posted by mayurcreation

This might help you....

select * from itemdetail
description shelf_dt exp_dt
-----------------------------------
Item2 2007-09-14 2007-09-27
Item3 2007-09-14 2007-09-28
Item4 2007-09-13 2007-09-26
Item5 2007-09-13 2007-09-23
Item6 2007-09-13 2007-09-20
Item7 2007-09-01 2007-09-10

Now here we go.....

select shelf_dt, count(*) as Count from itemdetail
where shelf_dt >= '9/13/07'
and shelf_dt <= '9/29/07'
group by shelf_dt

Output....
shelf_dt Count
---------------------
2007-09-13 3
2007-09-14 2

This answers to your question ???

Close. I experimented with GROUP BY, but couldn't get it to deliver the results I needed, which was a COUNT of items on the shelf for each date rolling through a date range.

So what I need is:

Output....
shelf_dt Count
---------------------
2007-09-13 3
2007-09-14 2
2007-09-15 2
2007-09-16 2
2007-09-17 2
2007-09-18 2
2007-09-19 2
2007-09-20 1
2007-09-21 1
2007-09-22 1
...

Ideas?

Ray K. Ragan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 17:52:31

Too bad you are not satisfied with the suggestion posted to you at 09/13/2007 : 17:38:04

E 12°55'05.25"
N 56°04'39.16"

ray.ragan
Starting Member

13 Posts

Posted - 2007-09-13 : 18:04:49

quote:
Originally posted by Peso

Too bad you are not satisfied with the suggestion posted to you at 09/13/2007 : 17:38:04

E 12°55'05.25"
N 56°04'39.16"

It took me a minute to find the function and load it. I must say it works brilliantly!

Thank you so much. Reading on the function, it appears to be a good one to have altogether, so thank you again.

Ray K. Ragan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources