Difficult counting question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Difficult counting question

Author

Topic

fuzzyip
Starting Member

35 Posts

Posted - 2008-07-15 : 13:56:59

The tables orders is where we track the products. Basically I'm trying to create a query/view that lists the total numbers of items sitting in inventory on any particular day (example below).
With only the first day the item checks in, and the last day the item checks in. If the item has a LastDate entry of today (like item AAAAA) then it is still inventory.
However if the LastDate entry is a date in the past then we know the item is gone and was sold on that day (eg. BBBBB first came into inventory on 7/5/2008, we sold it
on 7/10/2008)

Table: ORDERS

FirstDate LastDate ItemID Price
7/1/2008 7/15/2008 AAAAA $38,225
7/5/2008 7/10/2008 BBBBB $27,991
7/12/2008 7/14/2008 CCCCC $27,925
7/2/2008 7/3/2008 DDDDD $53,875
7/5/2008 7/5/2008 EEEEE $23,584

Table: Inventory

Date Inventory (Result that we want)
7/1/2008 1
7/2/2008 2
7/3/2008 2
7/4/2008 1
7/5/2008 3
7/6/2008 2
7/7/2008 2
7/8/2008 2
7/9/2008 2
7/10/2008 2
7/11/2008 1
7/12/2008 2
7/13/2008 2
7/14/2008 2
7/15/2008 1

Not sure if this is possible to do. Another way of structuring that sold table is having one column for both First and Last Date.

FirstDate ] ItemID Price
7/1/2008 AAAAA $38,225
7/15/2008 AAAAA $38225
7/5/2008 BBBBB $27991
7/10/2008 BBBBB $27,991
7/12/2008 CCCCC $27925
7/14/2008 CCCCC $27,925
7/2/2008 DDDDD $53875
7/3/2008 DDDDD $53,875
7/5/2008 EEEEE $23584
7/5/2008 EEEEE $23,584

thanks for any help, the ideas I've tried so far havn't worked,

I've tried a select DATEADD(DAY, 0, DATEDIFF(DAY, 0, column) as Day,
COUNT(DISTINCT id) as NumOfSales
from table

first, because i thought it wolud have to add up each day automatically because its not listed in the column (only first and latest day are listed), but I got nowhere, then i tried an

if (select

type query but was havnt been able to figure it out because there are missing dates.

My first guess would be that its something like...

if date for product AAAAA falls between the firstdate for AAAAA and lastdate for AAAAAA then count it as present for that day, then add 1 day to date, and repeat, do it for every item

but I have no idea how to write that out in code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 14:13:11

DECLARE @StartDate datetime,@EndDate datetime--dates input by user

SELECT dat.DateValue,ISNULL(cnt.Inventory,0)
FROM
(SELECT DATEADD(d,number,@StartDate) AS DateValue
FROM master..spt_values
WHERE type='p'
and DATEADD(d,number,@StartDate)<=@EndDate)dat
OUTER APPLY (SELECT COUNT(*) AS Inventory
             FROM ORDERS
             WHERE dat.DateValue >=FirstDate
             AND dat.DateValue <=LastDate)cnt

this will give you count of inventories b/w entered dates.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 14:21:36

[code];WITH Yak (theDate, LastDate)
AS (
SELECT FirstDate,
LastDate
FROM Orders

UNION ALL

SELECT DATEADD(DAY, 1, theDate),
LastDate
FROM Yak
WHERE theDate < LastDate

)

SELECT theDate,
COUNT(*) AS Items
FROM Yak
GROUP BY theDate
ORDER BY theDate[/code]

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

fuzzyip
Starting Member

35 Posts

Posted - 2008-07-15 : 15:31:23

quote:

Originally posted by Peso

;WITH Yak (theDate, LastDate)
AS (
	SELECT	FirstDate,
		LastDate
	FROM	Orders

	UNION ALL

	SELECT	DATEADD(DAY, 1, theDate),
		LastDate
	FROM	Yak
	WHERE	theDate < LastDate
	
)

SELECT		theDate,
		COUNT(*) AS Items
FROM		Yak
GROUP BY	theDate
ORDER BY	theDate

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

Your code seems to be working! Wow, I really appreciate it, I'm only having one problem left in normalazing the way its counting. The dates are in datetime format with different times. so its counting by too many different times.

its showing up as

7/1/2008 1:32:00 43
7/1/2008 1:43:00 23
7/1/2008 2:23:01 39
7/1/2008 3:21:34 12
7/2/2008 1:23:11 33
7/2/2008 1:22:20 9

etc

I tried modifying the code in by adding

convert(varchar, convert(datetime, columnnamehere),101)

to the places where a date column is called by im getting an error

"Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "theDate" of recursive query "Yak"."

The columns firstdate and lastedate in the tables Orders have been outfitted with my convert functions to display only dd/mm/yyyy format

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 15:34:41

Keep the dates as DATETIME.
Just modify the anchor part in the recursive CTE like this

;WITH Yak (theDate, LastDate)
AS (
	SELECT	DATEADD(DAY, DATEDIFF(DAY, '19000101', FirstDate), '19000101'),
		DATEADD(DAY, DATEDIFF(DAY, '19000101', LastDate), '19000101')
	FROM	Orders

	UNION ALL

	SELECT	DATEADD(DAY, 1, theDate),
		LastDate
	FROM	Yak
	WHERE	theDate < LastDate
	
)

SELECT		theDate,
		COUNT(*) AS Items
FROM		Yak
GROUP BY	theDate
ORDER BY	theDate
OPTION		(MAXRECURSION 0) -- Just in case there are more than 100 consequtive days

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

fuzzyip
Starting Member

35 Posts

Posted - 2008-07-15 : 16:22:59

quote:

Originally posted by Peso

Keep the dates as DATETIME.
Just modify the anchor part in the recursive CTE like this

;WITH Yak (theDate, LastDate)
AS (
	SELECT	DATEADD(DAY, DATEDIFF(DAY, '19000101', FirstDate), '19000101'),
		DATEADD(DAY, DATEDIFF(DAY, '19000101', LastDate), '19000101')
	FROM	Orders

	UNION ALL

	SELECT	DATEADD(DAY, 1, theDate),
		LastDate
	FROM	Yak
	WHERE	theDate < LastDate
	
)

SELECT		theDate,
		COUNT(*) AS Items
FROM		Yak
GROUP BY	theDate
ORDER BY	theDate
OPTION		(MAXRECURSION 0) -- Just in case there are more than 100 consequtive days

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

GREAT! Wow it worked!

Only one thing, I'm trying to create this as a view it won't let me because of the "OPTION (MAXRECURSION 0)" part

I did a little research and saw that it wont let you do this in a view (correct me if i'm mistaken).

From your comment about that part I gather that it will only return the first 100 dates? I'm at 96 dates right now, and in about 4 days it will go over this number.

I guess I will have to just save it as a query and run the query everytime I want the output?

for reference:

Msg 156, Level 15, State 1, Procedure carsunsoldbydayv2_1, Line 24
Incorrect syntax near the keyword 'OPTION'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 17:54:20

I would suggest to you to make an aggregation table and have a trigger populate the aggregation table.
It is very easy to do. For every insert, delete or update you transfer information to an aggregation table with the information you need.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources