Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 9th, 2008, 08:27 PM
Authorized User
 
Join Date: Jul 2006
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default select Statement

HI,
I have a table that holds printing information (clientcode, Username, PapaerSize, Pages, DatePrinted).I would like to sumerise it as follows:
Client Code Username A4 Total Letter Total
Building karbell 1 1
Building simhowa 4
Building simhowa 1
Communications elikirk 2
Communications kendyer 3
Communications raebarl 6
Communications suewall 3
Communications suewall 1

I have the following script that displays the data as follows

SELECT ClientCode, SUBSTRING(Username, 10, 10) AS Username, PaperSize, COUNT(Pages) AS Pages
FROM tbl_pcounterlog
GROUP BY ClientCode, SUBSTRING(Username, 10, 10), PaperSize
HAVING (NOT (ClientCode IS NULL))

Client Code Username PaperSize Pages
Building karbell A4 1
Building simhowa A4 4
Building simhowa Letter 1
Communications elikirk Letter 2
Communications kendyer A4 3
Communications raebarl A4 6
Communications suewall A4 3
Communications suewall Letter 1

Can anyone point me in the right direction.

Thank you

Greg



 
Old January 10th, 2008, 12:21 AM
Friend of Wrox
 
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

For client code,username and paparsize wise use following

SELECT CLIENTCODE,USERNAME,
SUM(CASE WHEN PAPERSIZE='A4' THEN PAGES END) A4TOTAL,
SUM(CASE WHEN PAPERSIZE='LETTER' THEN PAGES END) LETTERTOTAL
FROM TBL_PCOUNTERLOG A
GROUP BY CLIENTCODE,USERNAME,PAPERSIZE


For clientcode,username wise summary (all will come in one line) use following
SELECT CLIENTCODE,USERNAME,
SUM(CASE WHEN PAPERSIZE='A4' THEN PAGES END) A4TOTAL,
SUM(CASE WHEN PAPERSIZE='LETTER' THEN PAGES END) LETTERTOTAL
FROM TBL_PCOUNTERLOG A
GROUP BY CLIENTCODE,USERNAME


You can add as many cases according to you available paper size.



urt
 
Old January 14th, 2008, 03:51 PM
Authorized User
 
Join Date: Jul 2006
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks urt this has worked nicely.I have another sinario. I would like to add a field based on the value of another. The field that I would like to creat is not held in the DB. essentially this is what I want to do

ClientCode Directorate
Building Corporate Services
Policy Strategic planning

this is the statement that I am using - it works but I was wondering if there is a better way of writing it

SELECT

SUBSTRING(Username, 10, 10) AS Username,
SUBSTRING(Printer, 16, 40) AS Printer,
CONVERT(varchar(10), DatePrinted, 103) AS DatePrinted,
ClientCode AS ClientCode,
PaperSize AS PaperSize,
Pages AS Pages,
Cost AS Cost,
case when clientcode = 'Finance' then 'Corporate Services'
when clientcode = 'Information Management Services ' then 'Corporate Services'
when clientcode = 'Customer Services' then ' Corporate Services'
when clientcode = 'Committee Support Group' then 'Corporate Services'
when clientcode = 'network Infrastructure' then 'Operations and services'
when clientcode = 'Community Services' then 'Operationd and Services'
when clientcode = 'Policy' then 'Strategic Planning and Policy'
when clientcode = 'Legal' then 'Strategic Planning and Policy'
when clientcode = 'Regulatory' then 'Regulatory'
when clientcode = 'Building' then 'Regulatory'
when clientcode = 'Communications' then 'Chief Executive'
when clientcode = 'Human Resources' then 'Chief Executive'
when clientcode = 'Executives' then 'Chief Executive' end AS Directorate
FROM tbl_pcounterlog

WHERE (NOT (ClientCode IS NULL))

ORDER BY CONVERT(varchar(10), DatePrinted, 103)

Thanks Greg


 
Old January 15th, 2008, 12:00 AM
Friend of Wrox
 
Join Date: Oct 2007
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

Their is another way, but it will not work instantly, because for that, you have to change your database design and it will take some time. So right now, method that you are using, is the best one.

urt





Similar Threads
Thread Thread Starter Forum Replies Last Post
select statement ??? RinoDM SQL Server 2000 7 June 19th, 2008 08:40 AM
select statement help... RinoDM SQL Server 2000 13 January 10th, 2008 08:34 PM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM
select statement help RinoDM SQL Server 2000 2 January 16th, 2007 11:35 AM
Select Statement jmss66 Oracle 1 May 27th, 2004 02:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.