February 19, 2009 at 11:41 am
I ran into an interesting problem today and have yet to figure out a good solution in checking to see if a customer has activity in the each week of January. I thought I would post the problem here to see if anyone had a better solution.
I am trying to compare invoice activity in Jan 2008 verus Jan 2009 for customers that did business in both years and if there was a change in number of orders/year. I realized I needed to ensure each customer had invoices in each of the 5 work weeks in Jan of both years to ensure the comparison was valid.
Here is an example data structure:
CREATE TABLE [dbo].[Invoices08](
[PartNo] [varchar](22) NOT NULL,
[Invoice] [varchar](15) NOT NULL,
[InvoiceDate] [smalldatetime] NOT NULL,
[Company] [varchar](16) NULL
) ON [PRIMARY]
First, just make sure there are 5 work weeks in January (1,2,3,4,5). I use DISTINCT since there can be multiple PartNo(s) in one Invoice and multiple Invoices per one InvoiceDate.
SELECT DISTINCT DATEPART(ww,InvoiceDate)
from dbo.Invoices09 PQ
WHERE PQ.InvoiceDate BETWEEN '1/1/09' AND '1/31/09'
and the sum of them is 15:
SELECT SUM(DISTINCT DATEPART(ww,InvoiceDate))
from dbo.Invoices09 PQ
WHERE PQ.InvoiceDate BETWEEN '1/1/09' AND '1/31/09'
So I can then get a grouping of customers by number of work weeks in January:
SELECT DISTINCT PQ.Company, SUM(DISTINCT DATEPART(ww,InvoiceDate))
from dbo.Invoices09 PQ
WHERE PQ.InvoiceDate BETWEEN '1/1/09' AND '1/31/09'
GROUP BY PQ.Company
Here is the final solution... note that I am into table variables as an alternative to derived select statements.
---- Get list of 2008 Companies
DECLARE @Company08 TABLE (Company varchar(50) NOT NULL, CntWorkWeek int NOT NULL)
INSERT INTO @Company08
SELECT PQ.Company, SUM(DISTINCT DATEPART(ww,InvoiceDate))
from dbo.Invoices08 PQ
WHERE PQ.InvoiceDate BETWEEN '1/1/08' AND '1/31/08'
GROUP BY PQ.Company
---- Get list of 2009 Companies
DECLARE @Company09 TABLE (Company varchar(50) NOT NULL, CntWorkWeek int NOT NULL)
INSERT INTO @Company09
SELECT DISTINCT PQ.Company, SUM(DISTINCT DATEPART(ww,InvoiceDate))
from dbo.Invoices09 PQ
WHERE PQ.InvoiceDate BETWEEN '1/1/09' AND '1/31/09'
GROUP BY PQ.Company
SELECT PQ8.Company, PQ8.CntParts
FROM (
SELECT COUNT(PQ.PartNo) as CntParts, C.Company
FROM dbo.Invoices08 PQ
INNER JOIN @Company09 C -- Companies in 2009 and in 2008
ON PQ.Company = C.Company
WHERE PQ.InvoiceDate BETWEEN '1/1/08' AND '2/1/08'
AND C.CntWorkWeek = '15'
GROUP BY C.Company ) PQ8
INNER JOIN (
SELECT COUNT(PQ.PartNo) as CntParts, C.Company
FROM dbo.Invoices09 PQ
INNER JOIN @Company08 C -- Companies in 2008 and in 2009
ON PQ.Company = C.Company
WHERE PQ.InvoiceDate BETWEEN '1/1/09' AND '2/1/09'
AND C.CntWorkWeek = '15'
GROUP BY C.Company ) PQ9
ON PQ8.Company = PQ9.Company
WHERE (PQ9.CntParts - PQ8.CntParts) < 0 -- Looking for More activity in 2008 versus 2009
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
February 19, 2009 at 12:28 pm
Please try:
SELECT a.Company, a.InvoiceCount
FROM (SELECT COUNT(1) InvoiceCount, Company
FROM dbo.Invoices
WHERE DATEPART(YEAR,InvoiceDate) = 2009
AND DATEPART(MONTH,InvoiceDate) = 1
GROUP BY Company) b
INNER JOIN (SELECT COUNT(1) InvoiceCount, Company
FROM dbo.Invoices
WHERE DATEPART(YEAR,InvoiceDate) = 2008
GROUP BY Company) a
ON a.Company = b.Company
WHERE a.Company NOT IN (SELECT i.Company
FROM dbo.Invoices i
WHERE DATEPART(YEAR,InvoiceDate) IN (2008,2009)
GROUP BY i.Company, DATEPART(YEAR,InvoiceDate)
HAVING COUNT(DISTINCT DATEPART(ww,InvoiceDate)) < 5)
AND a.InvoiceCount > b.InvoiceCount
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply