jeffrey.demulling at usbank.com
jeffrey.demulling at usbank.com
Thu Jan 13 09:51:20 CST 2005
Add a comma after "END AS StaffMan"
paul.hartland at fsm
ail.net
Sent by: To
dba-sqlserver-bou dba-sqlserver at databaseadvisors.com
nces at databaseadvi cc
sors.com
Subject
Re: RE: RE: [dba-SQLServer] SQL
01/13/2005 09:36 Views Code For IIF
AM
Please respond to
dba-sqlserver at dat
abaseadvisors.com
Just tried the following code and got an error CASE is not supported any
idea's why ?
SELECT tblAvailabilityAll.JobNo, tblAvailabilityAll.lblStaffingCode,
CASE lblStaffingCode
WHEN 'Manager' THEN COUNT([PayrollNo])
WHEN 'Supervisor' THEN COUNT([PayrollNo])
WHEN 'Pharm Super' THEN COUNT([PayrollNo])
WHEN 'Water Super' THEN COUNT([PayrollNo])
WHEN 'Wilk Manager' THEN COUNT([PayrollNo])
END AS StaffMan
CASE lblStaffingCode
WHEN 'Counter' THEN COUNT([PayrollNo])
WHEN 'Checker' THEN COUNT([PayrollNo])
WHEN 'Control' THEN COUNT([PayrollNo])
WHEN 'Planner' THEN COUNT([PayrollNo])
WHEN 'Dispensary' THEN COUNT([PayrollNo])
WHEN 'Pharmacy' THEN COUNT([PayrollNo])
WHEN 'Planner/Counter' THEN COUNT([PayrollNo])
WHEN 'MB Driver Only' THEN COUNT([PayrollNo])
WHEN 'Traveller' THEN COUNT([PayrollNo])
WHEN 'Trainer' THEN COUNT([PayrollNo])
WHEN 'Planner/Checker' THEN COUNT([PayrollNo])
WHEN 'Site Visitor' THEN COUNT([PayrollNo])
WHEN 'Pharm Counter' THEN COUNT([PayrollNo])
WHEN 'Pharm Traveller' THEN COUNT([PayrollNo])
WHEN 'Wilk Estimator' THEN COUNT([PayrollNo])
WHEN 'Wilk Planner' THEN COUNT([PayrollNo])
WHEN 'Wilk Checker' THEN COUNT([PayrollNo])
WHEN 'Wool Core Team' THEN COUNT([PayrollNo])
WHEN 'Water NSA' THEN COUNT([PayrollNo])
WHEN 'Pharm Counter' THEN COUNT([PayrollNo])
END AS StaffOther
WHERE (((tblAvailabilityAll.JobDate) >= GETDATE() - 30))
GROUP BY tblAvailabilityAll.JobNo,
tblAvailabilityAll.lblStaffingCode
HAVING (((tblAvailabilityAll.JobNo) IS NOT NULL))
Message date : Jan 13 2005, 02:44 PM
>From : "Mackin, Christopher"
To : dba-sqlserver at databaseadvisors.com
Copy to :
Subject : RE: RE: [dba-SQLServer] SQL Views Code For IIF
You can, but you just can't see them graphically once you do it, but
they're perfectly fine in Views.
-Chris Mackin
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of
paul.hartland at fsmail.net
Sent: Thursday, January 13, 2005 7:41 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: RE: [dba-SQLServer] SQL Views Code For IIF
Didn't think you could use the case statement in Views ?
Message date : Jan 13 2005, 02:40 PM
>From : "Mackin, Christopher"
To : dba-sqlserver at databaseadvisors.com
Copy to :
Subject : RE: [dba-SQLServer] SQL Views Code For IIF
Lok up the CASE statement for T-SQL, it provides the IIF functionality.
-Chris Mackin
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of
paul.hartland at fsmail.net
Sent: Thursday, January 13, 2005 2:45 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] SQL Views Code For IIF
To all,
I have the following code in an Access database:
SELECT tblAvailabilityAll.JobNo, tblAvailabilityAll.lblStaffingCode,
IIf([lblStaffingCode]="Manager" Or
[lblStaffingCode]="Supervisor",Count([PayrollNo]),0) AS StaffMan,
IIf([lblStaffingCode]<>"Manager" And
[lblStaffingCode]<>"Supervisor",Count([PayrollNo]),0) AS StaffOther
FROM tblAvailabilityAll
WHERE (((tblAvailabilityAll.JobDate)>=Date()-30))
GROUP BY tblAvailabilityAll.JobNo, tblAvailabilityAll.lblStaffingCode
HAVING (((tblAvailabilityAll.JobNo) Is Not Null))
How can I put this into a view in SQL Server 7.0, The only thing I'm sure
of is changing the Date()-30 to GETDATE()-30 and changing the " (double
quotes) to ' (single quotes)
Thanks for any help in advance
Paul Hartland
--
Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/
This email has been checked for most known viruses - find out more at:
http://www.wanadoo.co.uk/help/id/7098.htm
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
--
Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/
This email has been checked for most known viruses - find out more at:
http://www.wanadoo.co.uk/help/id/7098.htm
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
--
Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/
This email has been checked for most known viruses - find out more at:
http://www.wanadoo.co.uk/help/id/7098.htm
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
------------------------------------------------------------------------------
Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation.
==============================================================================