HELP! Crosstab Query by Week

[Dr. Know]

I have worked at this for hours, and cannot understand why it doesn't
work properly.

A customer wants a summary of sales by customer, summarized weekly,
going back for 16 weeks, and beginning with the last FULL week. I
have an algorithm worked out for selecting the last full week dates,
but the SQL portion won't work. For this example, I have removed all
the date calculation stuff from the snippet of code, and have hard-
coded dates into the SQL statement.

Sounds easy, until you get to the part about the 53'd week on year
overlaps and the "mm" not sorting properly from 1 to 9.

I ended up with this, but the totals do not come out quite right.
If I manually add up ALL transactions for this hard coded time period
for a given customer, I get $121,968.31. But when running the query
below, I get 122,667.71. I cannot account for the discrepancy.

Most of the weekly totals come out right for most customers, but
several don't. What on earth am I doing wrong?

As an experiment, I pre-selected the date range with another
make-table select query, and then ran this (modified) query against
it, and it works. There is a problem with the SQL date screening
(WHERE) selections here... It is sporadically mixing properly
WHERE'd data with NON WHERE'd data as it performs the TRANSFORM.

Any Ideas?

Thanks,
Greg

------------------------------------------------------------------------------------------------------

TRANSFORM Sum(ECNHMaster.TotalAmount) AS [WeeklyTotal]
SELECT ECNCMaster.CustomerNumber, ECNCMaster.CustomerName,
Sum(ECNHMaster.TotalAmount) AS [Total Sales]
FROM ECNCMaster LEFT JOIN ECNHMaster ON ECNCMaster.CustomerNumber =
ECNHMaster.CustomerNumber
WHERE (((ECNHMaster.InvoiceDate) Between #10/26/2003# And #2/14/2004#)
AND ((ECNCMaster.Department)=""))
GROUP BY ECNCMaster.CustomerNumber, ECNCMaster.CustomerName
ORDER BY ECNCMaster.CustomerNumber
PIVOT "Week " & DateDiff("ww",[InvoiceDate],#02/14/2004#) In ("Week
1","Week 2","Week 3","Week 4","Week 5","Week 6","Week 7","Week
8","Week 9","Week 10","Week 11","Week 12","Week 13","Week 14","Week
15","Week 16");

------------------------------------------------------------------------------------------------------







Dr. Know

 

[Paul Overway]

Try this:

PARAMETERS [Starting Date] DateTime, [Ending Date] DateTime;
TRANSFORM Sum(ECNHMaster.TotalAmount) AS SumOfTotalAmount
SELECT ECNCMaster.CustomerNumber, ECNCMaster.CustomerName
FROM ECNCMaster LEFT JOIN ECNHMaster ON ECNCMaster.CustomerNumber =
ECNHMaster.CustomerNumber
WHERE (((ECNHMaster.InvoiceDate) Between [Starting Date] And [Ending Date])
AND ((Len([Department]))=0))
GROUP BY ECNCMaster.CustomerNumber, ECNCMaster.CustomerName
PIVOT Format([InvoiceDate],"yyyy") & "-" &
Format(Format([InvoiceDate],"ww"),"00")



--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com

I have worked at this for hours, and cannot understand why it doesn't
work properly.

A customer wants a summary of sales by customer, summarized weekly,
going back for 16 weeks, and beginning with the last FULL week. I
have an algorithm worked out for selecting the last full week dates,
but the SQL portion won't work. For this example, I have removed all
the date calculation stuff from the snippet of code, and have hard-
coded dates into the SQL statement.

Sounds easy, until you get to the part about the 53'd week on year
overlaps and the "mm" not sorting properly from 1 to 9.

I ended up with this, but the totals do not come out quite right.
If I manually add up ALL transactions for this hard coded time period
for a given customer, I get $121,968.31. But when running the query
below, I get 122,667.71. I cannot account for the discrepancy.

Most of the weekly totals come out right for most customers, but
several don't. What on earth am I doing wrong?

As an experiment, I pre-selected the date range with another
make-table select query, and then ran this (modified) query against
it, and it works. There is a problem with the SQL date screening
(WHERE) selections here... It is sporadically mixing properly
WHERE'd data with NON WHERE'd data as it performs the TRANSFORM.

Any Ideas?

Thanks,
Greg

-------------------------------------------------------------------------- ----------------------------

TRANSFORM Sum(ECNHMaster.TotalAmount) AS [WeeklyTotal]
SELECT ECNCMaster.CustomerNumber, ECNCMaster.CustomerName,
Sum(ECNHMaster.TotalAmount) AS [Total Sales]
FROM ECNCMaster LEFT JOIN ECNHMaster ON ECNCMaster.CustomerNumber =
ECNHMaster.CustomerNumber
WHERE (((ECNHMaster.InvoiceDate) Between #10/26/2003# And #2/14/2004#)
AND ((ECNCMaster.Department)=""))
GROUP BY ECNCMaster.CustomerNumber, ECNCMaster.CustomerName
ORDER BY ECNCMaster.CustomerNumber
PIVOT "Week " & DateDiff("ww",[InvoiceDate],#02/14/2004#) In ("Week
1","Week 2","Week 3","Week 4","Week 5","Week 6","Week 7","Week
8","Week 9","Week 10","Week 11","Week 12","Week 13","Week 14","Week
15","Week 16");

-------------------------------------------------------------------------- ----------------------------







Dr. Know

 

[Dr. Know]

Paul Overway said:

Thanks, Paul,

Well, I'm a moron. Right after I posted this plea for help, I
imported the table into Excel and summed the columns myself. Turns
out that the command I posted actually DOES work! The girl adding up
the columns manually for me was adding negative balances as
positive...
(I like to confirm that these sorts of queries extract the proper
results...)

Actually, your post is what I started with, but it has a problem when
crossing certain year boundaries. This particular CMD shows a week 53
and a week 1 that should actually be combined as week 1 of the
following year...

All is well, however...

Thanks, again!
Greg

Try this:

PARAMETERS [Starting Date] DateTime, [Ending Date] DateTime;
TRANSFORM Sum(ECNHMaster.TotalAmount) AS SumOfTotalAmount
SELECT ECNCMaster.CustomerNumber, ECNCMaster.CustomerName
FROM ECNCMaster LEFT JOIN ECNHMaster ON ECNCMaster.CustomerNumber =
ECNHMaster.CustomerNumber
WHERE (((ECNHMaster.InvoiceDate) Between [Starting Date] And [Ending Date])
AND ((Len([Department]))=0))
GROUP BY ECNCMaster.CustomerNumber, ECNCMaster.CustomerName
PIVOT Format([InvoiceDate],"yyyy") & "-" &
Format(Format([InvoiceDate],"ww"),"00")

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com

I have worked at this for hours, and cannot understand why it doesn't
work properly.

A customer wants a summary of sales by customer, summarized weekly,
going back for 16 weeks, and beginning with the last FULL week. I
have an algorithm worked out for selecting the last full week dates,
but the SQL portion won't work. For this example, I have removed all
the date calculation stuff from the snippet of code, and have hard-
coded dates into the SQL statement.

Sounds easy, until you get to the part about the 53'd week on year
overlaps and the "mm" not sorting properly from 1 to 9.

I ended up with this, but the totals do not come out quite right.
If I manually add up ALL transactions for this hard coded time period
for a given customer, I get $121,968.31. But when running the query
below, I get 122,667.71. I cannot account for the discrepancy.

Most of the weekly totals come out right for most customers, but
several don't. What on earth am I doing wrong?

As an experiment, I pre-selected the date range with another
make-table select query, and then ran this (modified) query against
it, and it works. There is a problem with the SQL date screening
(WHERE) selections here... It is sporadically mixing properly
WHERE'd data with NON WHERE'd data as it performs the TRANSFORM.

Any Ideas?

Thanks,
Greg

--------------------------------------------------------------------------

TRANSFORM Sum(ECNHMaster.TotalAmount) AS [WeeklyTotal]
SELECT ECNCMaster.CustomerNumber, ECNCMaster.CustomerName,
Sum(ECNHMaster.TotalAmount) AS [Total Sales]
FROM ECNCMaster LEFT JOIN ECNHMaster ON ECNCMaster.CustomerNumber =
ECNHMaster.CustomerNumber
WHERE (((ECNHMaster.InvoiceDate) Between #10/26/2003# And #2/14/2004#)
AND ((ECNCMaster.Department)=""))
GROUP BY ECNCMaster.CustomerNumber, ECNCMaster.CustomerName
ORDER BY ECNCMaster.CustomerNumber
PIVOT "Week " & DateDiff("ww",[InvoiceDate],#02/14/2004#) In ("Week
1","Week 2","Week 3","Week 4","Week 5","Week 6","Week 7","Week
8","Week 9","Week 10","Week 11","Week 12","Week 13","Week 14","Week
15","Week 16");

Dr. Know