SQL statement for a report...

The Eeediot

Hello, SQL gurus!

This message use HTML tables (in case you see a bunch of gobbledy-gook).

I need to modify an SQL statement:

SELECT DISTINCT Trim(InvtID)+', '+Trim(Descr) AS Item, month, Sum(QtyShip) AS Qty
FROM Sales_View
GROUP BY Trim(InvtID)+', '+Trim(Descr), month, CustID
HAVING (CustID)= @CustID )
ORDER BY Trim(InvtID)+', '+Trim(Descr), month;

Whose output is something like the following:

Item
month
Qty

26479F, Report Cover Sheets (100/pkg)
3
2

26479F, Report Cover Sheets (100/pkg)
8
1

26479F, Report Cover Sheets (100/pkg)
9
1

46217C, Manual- 5th Ed
6
1

46217C, Manual- 5th Ed
8
1

514560, Profile Report
8
1

51900C, Technical Manual
1
1

ADHAC0, Adults and Children (kit)
8
6

ADS04, Technical Manual 2nd Ed
7
15

And turn it into something like this:

Item
JanQty
FebQty
MarQty
AprQty
MayQty
JunQty
JulQty
AugQty
SepQty
OctQty
NovQty
DecQty

26479F, Report Cover Sheets (100/pkg)
0
0
2
0
0
0
0
1
1
0
0
0

46217C, Manual- 5th Ed
0
0
0
0
0
1
0
1
0
0
0
0

514560, Profile Report
0
0
0
0
0
0
0
1
0
0
0
0

51900C, Technical Manual
1
0
0
0
0
0
0
0
0
0
0
0

ADHAC0, Adults and Children (kit)
0
0
0
0
0
0
0
6
0
0
0
0

ADS04, Technical Manual 2nd Ed
0
0
0
0
0
0
15
0
0
0
0
0

Can anyone help?!

TIA...

Jul 20 '05 #1

Erland Sommarskog

The Eeediot (ee*****@hotmail.com) writes:

Hello, SQL gurus!

This message use HTML tables (in case you see a bunch of gobbledy-gook).

I need to modify an SQL statement:

SELECT DISTINCT Trim(InvtID)+', '+Trim(Descr) AS Item, month, Sum(QtyShip)
AS Qty
FROM Sales_View
GROUP BY Trim(InvtID)+', '+Trim(Descr), month, CustID
HAVING (CustID)= @CustID )
ORDER BY Trim(InvtID)+', '+Trim(Descr), month;

Whose output is something like the following:

Item
month
Qty
...
And turn it into something like this:

Item
JanQty
FebQty
MarQty
AprQty
MayQty
JunQty
JulQty
AugQty
SepQty
OctQty
NovQty
DecQty

JanQty = SUM(CASE month WHEN 1 THEN QtyShip ELSE 0 END),
FebQty = SUM(CASE month WHEN 2 THEN QtyShip ELSE 0 END),
....
DecQty = SUM(CASE month WHEN 12 THEN QtyShip ELSE 0 END),

I'm not really sure why have the condition on CustID in a HAVING clause.
Why isn't in the WHERE clause?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #2

The Eeediot

Thanks for the tip!

The HAVING claused is used in conjuction with GROUP BY. It is used to
organize column data with aggregate functions...so says the manual!

I used MS Access to create the SQL statement.

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

The Eeediot (ee*****@hotmail.com) writes:

Hello, SQL gurus!

This message use HTML tables (in case you see a bunch of gobbledy-gook).

I need to modify an SQL statement:

SELECT DISTINCT Trim(InvtID)+', '+Trim(Descr) AS Item, month, Sum(QtyShip) AS Qty
FROM Sales_View
GROUP BY Trim(InvtID)+', '+Trim(Descr), month, CustID
HAVING (CustID)= @CustID )
ORDER BY Trim(InvtID)+', '+Trim(Descr), month;

Whose output is something like the following:

Item
month
Qty
...
And turn it into something like this:

Item
JanQty
FebQty
MarQty
AprQty
MayQty
JunQty
JulQty
AugQty
SepQty
OctQty
NovQty
DecQty

JanQty = SUM(CASE month WHEN 1 THEN QtyShip ELSE 0 END),
FebQty = SUM(CASE month WHEN 2 THEN QtyShip ELSE 0 END),
...
DecQty = SUM(CASE month WHEN 12 THEN QtyShip ELSE 0 END),

I'm not really sure why have the condition on CustID in a HAVING clause.
Why isn't in the WHERE clause?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #3

The Eeediot

Okay, I feel stupid.

I found a way to get a similar result by using (you guessed it) a Crosstab Query! And this was right in MS Access, too.

The resulting SQL statement being...

TRANSFORM Sum(Sales_View.QtyShip) AS [The Value]
SELECT Trim([InvtID])+', '+Trim([Descr]) AS Item
FROM Sales_View
WHERE (((Sales_View.CustID)='100070') AND ((Sales_View.Year)=2003))
GROUP BY Trim([InvtID])+', '+Trim([Descr])
PIVOT Sales_View.month;

"The Eeediot" <ee*****@hotmail.com> wrote in message news:es********************@giganews.com...
Hello, SQL gurus!

This message use HTML tables (in case you see a bunch of gobbledy-gook).

I need to modify an SQL statement:

SELECT DISTINCT Trim(InvtID)+', '+Trim(Descr) AS Item, month, Sum(QtyShip) AS Qty
FROM Sales_View
GROUP BY Trim(InvtID)+', '+Trim(Descr), month, CustID
HAVING (CustID)= @CustID )
ORDER BY Trim(InvtID)+', '+Trim(Descr), month;

Whose output is something like the following:

Item
month
Qty

26479F, Report Cover Sheets (100/pkg)
3
2

26479F, Report Cover Sheets (100/pkg)
8
1

26479F, Report Cover Sheets (100/pkg)
9
1

46217C, Manual- 5th Ed
6
1

46217C, Manual- 5th Ed
8
1

514560, Profile Report
8
1

51900C, Technical Manual
1
1

ADHAC0, Adults and Children (kit)
8
6

ADS04, Technical Manual 2nd Ed
7
15

And turn it into something like this:

Item
JanQty
FebQty
MarQty
AprQty
MayQty
JunQty
JulQty
AugQty
SepQty
OctQty
NovQty
DecQty

26479F, Report Cover Sheets (100/pkg)
0
0
2
0
0
0
0
1
1
0
0
0

46217C, Manual- 5th Ed
0
0
0
0
0
1
0
1
0
0
0
0

514560, Profile Report
0
0
0
0
0
0
0
1
0
0
0
0

51900C, Technical Manual
1
0
0
0
0
0
0
0
0
0
0
0

ADHAC0, Adults and Children (kit)
0
0
0
0
0
0
0
6
0
0
0
0

ADS04, Technical Manual 2nd Ed
0
0
0
0
0
0
15
0
0
0
0
0

Can anyone help?!

TIA...

Jul 20 '05 #4

Danny

Careful. This may be a performance nightmare. Sounds like you are using MS Access with linked tables. On small databases this may be acceptable but as the data grows, the functions that access performs on the backend usually become detrimental to performance. A cross tab in access is probably going to do one of two things. Either submit a cross join on the backend or pull multiple result sets down and do the joining on the front end. Both are very inefficient.

As you can tell I'm not a big fan of using MS Access against SQL Server unless you use SQL pass through and write custom SQL.

Danny
"The Eeediot" <ee*****@hotmail.com> wrote in message news:pI********************@giganews.com...
Okay, I feel stupid.

I found a way to get a similar result by using (you guessed it) a Crosstab Query! And this was right in MS Access, too.

The resulting SQL statement being...

TRANSFORM Sum(Sales_View.QtyShip) AS [The Value]
SELECT Trim([InvtID])+', '+Trim([Descr]) AS Item
FROM Sales_View
WHERE (((Sales_View.CustID)='100070') AND ((Sales_View.Year)=2003))
GROUP BY Trim([InvtID])+', '+Trim([Descr])
PIVOT Sales_View.month;

"The Eeediot" <ee*****@hotmail.com> wrote in message news:es********************@giganews.com...
Hello, SQL gurus!

This message use HTML tables (in case you see a bunch of gobbledy-gook).

I need to modify an SQL statement:

SELECT DISTINCT Trim(InvtID)+', '+Trim(Descr) AS Item, month, Sum(QtyShip) AS Qty
FROM Sales_View
GROUP BY Trim(InvtID)+', '+Trim(Descr), month, CustID
HAVING (CustID)= @CustID )
ORDER BY Trim(InvtID)+', '+Trim(Descr), month;

Whose output is something like the following:

Item
month
Qty

26479F, Report Cover Sheets (100/pkg)
3
2

26479F, Report Cover Sheets (100/pkg)
8
1

26479F, Report Cover Sheets (100/pkg)
9
1

46217C, Manual- 5th Ed
6
1

46217C, Manual- 5th Ed
8
1

514560, Profile Report
8
1

51900C, Technical Manual
1
1

ADHAC0, Adults and Children (kit)
8
6

ADS04, Technical Manual 2nd Ed
7
15

And turn it into something like this:

Item
JanQty
FebQty
MarQty
AprQty
MayQty
JunQty
JulQty
AugQty
SepQty
OctQty
NovQty
DecQty

26479F, Report Cover Sheets (100/pkg)
0
0
2
0
0
0
0
1
1
0
0
0

46217C, Manual- 5th Ed
0
0
0
0
0
1
0
1
0
0
0
0

514560, Profile Report
0
0
0
0
0
0
0
1
0
0
0
0

51900C, Technical Manual
1
0
0
0
0
0
0
0
0
0
0
0

ADHAC0, Adults and Children (kit)
0
0
0
0
0
0
0
6
0
0
0
0

ADS04, Technical Manual 2nd Ed
0
0
0
0
0
0
15
0
0
0
0
0

Can anyone help?!

TIA...

Jul 20 '05 #5