November 25, 2005 at 4:07 pm
Ok, here's my problem. I am going to describe what I am needing using appropriate tables from the Northwind sample database, because it gets kind of difficult to explain otherwise. What I need to to be able to select the total number of products sold for ALL employees for each day that anything was sold for a given date range (From September 1, 1997 through September 30, 1997 for the Northwind Orders table.) This needs to include employees that did NOT sell anything that day, with either a null for the number sold, or have the number sold converted to 0, doesn't matter which.
I am running SQL Server 2000 as my server and would really like to be able to do this as a T-SQL Statement, although if that is not possible I can and will figure something different out.
Thanks ahead of time for any help that anyone can give me on this!!!
November 25, 2005 at 6:40 pm
For these type of problems, an auxilary table is needed with two options:
1. Containing all of the numbers from zero to something large
2. A calendar table.
See "Why should I consider using an auxiliary calendar table?" at http://www.aspfaq.com/show.asp?id=2519 and "Why should I consider using an auxiliary numbers table?" at http://www.aspfaq.com/show.asp?id=2516
I will use a variation of the numbers table named "Sequences" that will be used to generate a table named "PeriodDates" which will have one row for each day within a specified time period. This PeriodDates table is then cross joined to the Employee table resulting in another table that one row for all combinations of Employee and Day. This last table is then joined to some order information.
Cheers
DECLARE@StartDtdatetime
,@EndDtdatetime
set @StartDt= '1997-09-01'
set@EndDt= '1997-09-30'
select Employees.EmployeeID
, Employees.LastName
, Employees.FirstName
,PeriodDates.PeriodDate
,EmployeeDailyProducts.ProductId
,EmployeeDailyProducts.Quantity
from dbo.Employees
cross join
(Select DATEADD(dd,Sequences.Seq,@StartDt)
from master.dbo.Sequences as Sequences
where Sequences.Seq between 0 and DATEDIFF(dd,@StartDt,@EndDt))
As PeriodDates (PeriodDate)
left outer join
(select Orders.EmployeeID
,Orders.OrderDate
,OrderDetails.ProductId
,SUM(OrderDetails.Quantity) Quantity
fromdbo.Orders
joindbo.[Order Details] as OrderDetails
on OrderDetails.OrderId = Orders.OrderId
group by Orders.EmployeeID
,Orders.OrderDate
,OrderDetails.ProductId
) as EmployeeDailyProducts
on EmployeeDailyProducts.EmployeeID = Employees.EmployeeID
and EmployeeDailyProducts.OrderDate = PeriodDates.PeriodDate
Here is the SQL to create the SEQUENCES table:
use master
go
set nocount on
set xact_abort on
create table Sequences
( Seq smallint not null
, constraint Sequences_PK primary key (Seq) )
-- Create a new table to turn columns into rows or rows into columns
declare @SmallIntMax integer
, @SeqMax integer
set @SmallIntMax= power(2,15) - 1
-- Populate the Sequences table:
-- zero is also useful.
Insert into Sequences (Seq) values (0)
Insert into Sequences (Seq) values (1)
-- Now repeat the following insert 10 times to get 1024 sequence Sequences
set @SeqMax = 1
while @SeqMax < @SmallIntMax
begin
Insert into Sequences
SELECT NewSeq
FROM (select Seq + @SeqMax + 1 AS NewSeq
from Sequences
) as S
Order by NewSeq
select @SeqMax = max(Seq) from Sequences
end
go
select 'Largest Seq is ' , max(Seq) from Sequences
-- Check for gaps
select top 1 * from sequences p
where seq < 32767
and not exists
(select 1 from sequences as n where n.seq = p.seq + 1 )
SQL = Scarcely Qualifies as a Language
November 28, 2005 at 7:47 am
Ok, thanks for your answering! I will check out the article and see what I can do to set this up.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply