OLE DB Command Transform And Output Columns.
Jan 10, 2007
Hi All,
I have an OLE DB transform with a SQL Command of:
sp_get_sponsor_parent ?,? OUTPUT
where sp_get_sponsor_parent is defined like:
CREATE PROCEDURE [dbo].[sp_get_sponsor_parent]
@pEID int,
@results int OUTPUT
AS
BEGIN
.
.
.
END
I map the columns, refresh & OK out of the component without trouble, but on executing the package it fails during validation on this component. I'm utterly stumped.
Any light shed would be greatly appreciated.
Many thanks in advance,
Tamim.
View 3 Replies
ADVERTISEMENT
May 8, 2007
Hi All ,
I am creating packages from a template package whicg I have built ,I have managed to implement basically everything sucessfully .Setting Properties on all the different tasks ,Connections etc except for the OLE DB Command transform.
I have not been sucessfull in getting to the properties or collections which allows me to do the mapping of the Command to parameters (Command Below),I am aware that the command executes for every row . I really need help with how to now do the mapping between the columns and the Paramaters programmatically in c#
I have set the sql command properties of the OLE DB Command Transform,as below
//Setting Update Comand ComponentProperties
IDTSComponentMetaData90 oledbCMDUpdate = dataflow.ComponentMetaDataCollection[0];
oledbCMDUpdate.Name = "name" ;
oledbCMDUpdate.RuntimeConnectionCollection[0].ConnectionManagerID = pack.Connections[0].ID;
CManagedComponentWrapper instanceCMD = oledbCMDUpdate.Instantiate();
instanceCMD.SetComponentProperty("SqlCommand", GetUpdateSQL(tablename)) ;
The Sql that is returned by the GetUpdateSQL(tablename)) method is below
UPDATE [ADM_AdjustmentAction]
SET AdjustmentActionCode = ?
,AdjustmentActionName = ?
,AdjustmentActionDescription = ?
,AdjustmentActionEFD = ?
,AdjustmentActionETD = ?
,UserID = ?
,ProcessDatetime = ?
,ModuleID = ?
WHERE AdjustmentActionID = ?
Thanks in Advance
Cedric
View 5 Replies
View Related
May 1, 2006
In one Data Flow Task (running by itself) I simply have a Raw File Source pushing rows to an OLE DB Command. This command executes an UPDATE command (UPDATE table SET field = ?, anotherfield = ? WHERE thisfield = ?) and performs extremely slow. It's possible to have 62K+ rows needing to be updated and it typically takes this task around 25 - 30 minutes to run.
Is there anything I can do to increase performance?
Are there any options other than the OLE DB Command to perform updates?
Thank you.
View 1 Replies
View Related
Dec 8, 2006
I am working on an OLAP modeled database.
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
View 9 Replies
View Related
Dec 20, 2007
I'm receiving an Error Code: 0x80004005 ... a "non-specific error" when trying to use a fairly simple insert in the oleDB command transform. SQL command works fine if I specify the parameters in the values clause of the INSERT statement. If however, I try to use the parameters in setting a variable, I get the 0x80004005 error. Is this a known issue/limitation, or am I just reallly doing something wrong?
View 4 Replies
View Related
Jan 30, 2015
I have an SSIS with several data flows I need to do some complex data evaluations so I have used a script as transform in two of the DFT's. If I run these separately everything works great and there are no problems what so ever. If I run them together I notices I was getting an error on the second one. I discovered that this seems to be some kind of namespace problem since both Scripts were using Input_0 buffer. So I changed the name of the second one and retested.
Well I no longer get the error and in fact it seems to run through the entire SSIS just fine. However when I look closer I notice that the second Script task just does not seem to do anything at all. The script task does a lot of evaluation of the incoming data and then does some calculations depending on the value in the service code. however when it runs through this in the second script task all of the define output rows are just empty.
I have gone through and made sure that all input and output buffers are unique names thinking this was a similar problem but no luck. I even changes all column and variable names to unique with no luck. Again If I run them separately everything work fine it is only when I run the entire package that this problem occurs.
View 2 Replies
View Related
Jun 26, 2006
Would anyone happen to have any pointers or know of any good code examples to either programmatically change the type of an input column when it is passed through the component, or add a new column to the output? I am extracting data from an Oracle database which is in Julian date format (represented within SSIS as a DT_NUMERIC column) and I need to to either transform the input column holding it into a date column, or to dynamically add a new output column holding the transformed data.
Many thanks
View 1 Replies
View Related
May 8, 2015
I have a Pivot Transform in SSIS (2005) working perfectly, EXCEPT for that the first column of the output (the date) repeats for each of the following columns, which are themselves falling into the correct column, but not on the same line for a particular date as the others. Snipet of result from Data Viewer is:
dbDate site1 site2
1/1/2015 0:00 0.03 NULL
1/2/2015 0:00 0.04 NULL
[code]....
View 2 Replies
View Related
Jun 18, 2008
Hi all, new to SSIS so please bear with me on the noobie question:
Situation: have a SQL database with several tables, each table has several char fields that represent dates (ex. YYYYMMDDHHMMSSMS)- this SQL database is created weekly from an extract of an old Oracle RDB database maintained by a third party vendor.
Need to copy the data to a new database and tables
Then for each table:
1. check each char date column and if the value is '1858111700000000' (Oracle dummy date) then change to SQL low date, if it's not then transform the date into SQL server date format. I' ve tried some of the data controls - just need to know which ones to use and in what order.
What would be the best controls to do iterative processing in an efficiant manner? Some tables have upto 5 million rows
Any Ideas would be appreciated! Thanks!
View 5 Replies
View Related
Feb 21, 2005
Hi guys,
I have a SQL Server 2000 table that contains a Year field and another table containing Products.
The Year table contains only a year field.
The Product table contains ProductId, Date, Quantity.
I'm trying to construct a query such that I can view the Quantity of Products by Year (derived from the Year table).
ie.
If the Year table contains the rows 2003, 2004 and 2005, the resultant query would produce the output: -
PId 2003 2004 2005
50 3 6 7
51 4 2 6
52 8 0 3
Any ideas how I can achieve this?
Cheers in advance,
Rob
View 2 Replies
View Related
Mar 22, 2004
I have a table of 1000+ rows with 100+ different items with each Id having max 10 items
Id item
1 a
1 b
2 c
2 a
2 d
3 b
3 z
each item has an order in a different lookup table
item item_seq
a 1
b 2
c 3
d 4
The item columns (below) need to use the item order from lookup table, so the output table is as follows
Id item item2 item3
1 a b
2 a c d
3 b z
PLEASE HELP !!!
View 8 Replies
View Related
May 19, 2007
Hi all,
I'm new at this SSIS but have been able to successfully create some simple packages. My situation is that at work we use a column to describe a status of applications. However, this makes for hellacious query because some of those statuses inherintly were one or more statuses previously. Example
Admit = Admit
Accept = Admit then Accept
Withdraw Accept = Admit, Accept, then Withdraw
Decline = Admit then Decline
As you can see inherintly those were all admits at one point. So what I'd like to do is instead of having long queries for example to get all my "Admits", I'd rather query another table that has the following columns as bits:
Admit
Accept
Withdraw
That way I can query the admit column and get all my admits. How can I use SSIS to transform my "Decision" column into those bit columns?
Thanks for any help or suggestions you have.
View 13 Replies
View Related
Jul 12, 2007
Is it possible to use stored procedure with output parameter and retrieves the values of that output parameter, in an OLE DB Command within a Data Flow?
What I wanted to do is to get the newly created identity of a row so that I can insert it to the main data set in data flow. I'm not even sure if there is even a much better design to achieve this. I've rummaged the internet but everything I got were all about Execute SQL Task.
View 5 Replies
View Related
May 23, 2006
Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?
TIA,
Barkingdong
View 5 Replies
View Related
Jun 7, 2012
how to get the quantity from output of sql query command for example, I could get the container name by below command
select container_name from sysibmadm.snapcontainer
get the container number by
select TBSP_NUM_CONTAINERS from SYSIBMADM.SNAPTBSP_PART
now I want to get the container number by below command output result select container_name from sysibmadm.snapcontainer..so what more command should I add on above command, I mean I want to get the container number by container name from the output of above command, not by 'join'.
View 1 Replies
View Related
Jun 25, 2014
How can i get the output (like 1 rows(s) inserted)in a file from an input file through sql command prompt.
View 1 Replies
View Related
Jul 3, 2006
Hi,
I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,
Select SerialNumber From SerialNumbers Where OrderID = @OrderID
If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.
Please help me.
Thanks in advance.
View 13 Replies
View Related
Apr 21, 2006
I have a data flow that takes an OLE DB Source, transforms it and then uses an OLE DB Command as a destination. The OLE DB Command executes a call to a stored procedure and I have the proper wild cards indicated. The entire process runs great and does exactly what is intended to do.
However, I need to know when a SQL insert fails what record failed and I need to log this in a file somewhere. I added a Flat File Destination object and configured appropriately. I created 3 column names for the headers in the flat file and matched them with column names existing for output. When I run this package the flat file log is created ok, but no data is ever pumped into the file when a failure of the OLE DB Command occurs.
I checked the Advanced Editor for the OLE DB Command object and under the OLE DB Command Error Output node on the Input and Output Properties tab I notice that the ErrorCode and ErrorColumn output columns both have ErrorRowDisposition set to RD_NotUsed. I would guess this is the problem and why no data is written to my log file, but I cannot figure out how to get this changed (fields are greyed out so no access).
Any help would be greatly appreciated.
View 3 Replies
View Related
Feb 13, 2008
Hi,
I have two tables,
Table A on Server 1 (3 ROWS)
ID Name Address
ID1 A B
ID2 X Y
ID3 M N
There is another table on a different server which looks like
Table B on Server 2
PKColumn ID Details
1 ID1 Desc1
2 ID1 Desc2
3 ID1 Desc 3
4 ID2 Desc
5 ID2 Description
As you can see the ID is the common column for these two tables,
I want to get the Query the above 2 tables and the output should be dumped into a new table on Server2.
I am using the following SSIS Package
OledbDataSource-------> OledbCommand(Select * from TableB where ID =?)
From here, how can insert the rows returned from the oledb command into another table.
Since, for each row of TableA it will return some output rows...How can I insert all these into the New Table.
Please help on configuring the output of the oledb command.
Thanks,
View 5 Replies
View Related
Jul 20, 2005
We use a number of similar databases and frequently create a newdatabase using a backup restore of another similar database. We try tokeep changes between databases in _Additional tables - like AccountAdditional, Sale_Additional so most tables stay the same. The latestrestored database (I'll call it DBaseA) is behaving differently in VB6code and I need help trying to make it work.I have been using use an ADODB.Command to execute a stored procedureusing adCmdStoredProc and Parameters.Refresh to get an output parametervalue which is an integer. Here is a sample of the VB:Public Function UnlockAccount(Account_ID As String) As BooleanDim LCmd As ADODB.CommandOn Error GoTo ERROR_UnlockAccountUnlockAccount = TrueIf Account_ID <> "" ThenIf DBConnect() ThenSet LCmd = New ADODB.CommandLCmd.ActiveConnection = CN(0)LCmd.CommandTimeout = 0LCmd.CommandType = adCmdStoredProcLCmd.CommandText = "Unlock_Account"LCmd.Parameters.RefreshLCmd.Parameters("@Account_ID").VALUE = Account_IDLCmd.Execute , , adExecuteNoRecordsIf LCmd.Parameters("@Status") <> 0 ThenUnlockAccount = FalseEnd IfElsemsgbox "UnlockAccount: DBConnect Failed", "UnlockAccount"End IfEnd IfExit FunctionERROR_UnlockAccount:UnlockAccount = Falsemsgbox "UnlockAccount: App Error: " & Err & " " & _Err.Description, "UnlockAccount"DBConnect TrueExit FunctionResume'for debuggingEnd Functionand this is one of the sps that fails - can't be more simple!Create PROCEDURE Unlock_Account( @Account_ID UNIQUEIDENTIFIER,@Status INTEGER =null OUTPUT) AS/* Strip functionality */SET @Status = 0goThis code is still working in at least 4 other databases. It fails indatabase DBaseA with the error:Invalid character for cast conversionWe are talking about the exact same table definition and the exact samestored procedure just in different databases.I fumbled around on the Internet and found a mention in a PowerBuilderweb site of additional parameters in the connect string which seems tofix the problem in SOME of the stored procs - but not all.The added parameters are:DelimitIdentifier='No';MsgTerse='Yes';CallEscape='No';FormatArgsAsExp='N'They are not documented in MS but are in Sybase?? No idea why, but someof the stored proc functions work when I add this to the connect string.The complete connect string is:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBaseA;Data Source=PHASE2-S500,1433;Network Library=DBMSSOCN;DelimitIdentifier='No';MsgTerse='Yes';CallEscape='No';FormatArgsAsExp='N'The databases are on different servers - but they are running the sameversion of SQL2000, the same version of Windows. I see no differencesin options set in the different servers or between databases that workand this one. And dbcc checkdb runs clean.The compiled code that fails, fails across the board - not just on my(developer) PC. The same source code works on the same tables in otherdatabases, including the one we copied....The final kicker - if I build a SQL statement string and get therecordset instead, it works like a charm. But I have to fix about 20 VBfunctions and check that I do have a recordset and SET NOCOUNT ON in all20 sps.I feel this is either something so obvious I will kick myself or soserious Microsoft will be digging into it.Any ideas anyone??Sandie*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jun 5, 2007
Hello
This is my first post so please be gentle!!
I have a stored proc that returns a value:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[spPK]
-- Add the parameters for the stored procedure here
@varNSC varchar(4),
@varNC varchar(2),
@varIIN varchar(7),
@varIMCDMC varchar(8),
@varOut as int output
AS
Declare
@varPK int
set @varPK = 0
BEGIN
--This checks Method 1
--NSC = @varNSC
--NC = @varNC
--IIN = @varIIN
begin
if exists
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varPK =
(select Item_id
From Item
Where NSC = @varNSC
and NC = @varNC
and IIN = @varIIN)
set @varOut = @varPK
if @varPK <> 0 Return
end
[There are some more methods here]
Return
END
How do I get at the output value?
I have tried using derived column and ole db command but can't seem to grasp how to pass the value to the derived column. I can get oledb command to run using 'exec dbo.spPK ?, ?, ?, ?, ? output' but don't know what to do from here.
View 10 Replies
View Related
Sep 25, 2006
Hi everyone,
I am new to T-SQL and am trying to do some simple database for fun. Here is my problem and hope you guys can drop me some hint or solution:
OS: Win XP pro
SQL Server 2005 Express
Tool: SQLCMD in command prompt
I created a database and then create a table. Then I tried : select * from contract_Info, it gave me this:
1> CREATE TABLE Contract_Info
2> (
3> Order_No varchar(50) NOT NULL ,
4> Company varchar(255),
5> Product_Name varchar(255) NOT NULL,
6> Discount_Rate varchar(50), --Discount_Rate (%) -> Discount_Rate
7> Status varchar(50) NOT NULL,
8> Quantity varchar(50) NOT NULL,
9> Remainder varchar(50),
10> Deleted bit
11> )
12>
14>
15>
16> INSERT INTO Contract_Info values ('00000', 'DEFAULT','DEFAULT','0','Cancelle
d','0','0',0)
17>
18> go
(1 rows affected)
1> select * from contract_Info
2> go
Order_No Company
Product_Name
Discount_Rate Status
Quantity Rema
inder Deleted
-------------------------------------------------- -----------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------------ -------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- -------------------------------------------------- --------------------------
------------------------ -------------------------------------------------- ----
---------------------------------------------- -------
00000 DEFAULT
DEFAULT
0 Cancelled
0 0
0
(1 rows affected)
1>
In SQL Express Management , the output looks perfectly fine
What is missing??
Thanks for your time
View 6 Replies
View Related
Sep 27, 2007
I have a data flow and inside the data flow , i have a ole db source and ole db command task to execute an insert transaction ( SP).. i would like to save the error output if the insert didn;t happend into a error log table..
but when I darg an error output line ( red) to another ole db command to insert an error log , i can only see two columns( error code and error column) are available in OLEDB command advance editor related to errors.. this doesn;t tell you much information about the error.how can i grap the error reson(desc) as the error output and store into a erro log table? so that i can see what the problem is?
View 1 Replies
View Related
May 25, 2006
Hello,
I created a SSIS package that has a flat file manager whose connection string is a package variable, the reason is that there is a foreachfile container loop to loop thru CSV format files in a directory and load them to a sql table.
The package execution is working in the designer studio as well as in the management studio-- a copy was saved to a sql 2005 server where a sql job is deployed to run the package. However when I ran the job, it claims successful but doesn€™t do anything in reality. If I checked the box failing the package upon validation warming, the job will fail with the error: the command line parameters are invalid. The command line looks like below:
/SQL "Package" /SERVER FTLQSQL03 /WARNASERROR /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
One thing that I think maybe wrong is on the data source tab of the job step GUI, the flat file manager€™s connection string is blank, compared to other connection managers having related values.
Does anyone know how to make the job working?
Thanks in advance
I
View 3 Replies
View Related
Jan 31, 2006
I entered an SQL Command into an OLEDB datasource and was able to preview the results but no columns were available for mapping. If anyone can tell me what I doing wrong I would much appreciate it.
Dick Campbell
View 1 Replies
View Related
May 12, 2005
I am trying to find a way how to query following data from a table that looks like this:
ColA ColB ColC
1 2 3
Output should look like this:
ColA
1
2
3
View 2 Replies
View Related
May 9, 2008
Hello.
I have a OLE DB Data Source Component that uses an SQL command as the data access mode. On the Connection Manager "tab" of the OLE DB Source Editor I can sucessfully parse my query and produce a preview of the data.
However, when I go to the Columns tab I see no available external columns? Apart from the fact I'm using a union I can't think of any reason why I can't see any columns, it doesn't make sense.
Please Help.
Thanks
View 7 Replies
View Related
Apr 2, 2002
I've got a question that I can't seem to find an answer for, I was hoping someone here might be able to point me in the right direction. I've set up a stored procedure that will email someone if any entries are added to a table . However, the output is garbled looking (see below)
Client Number SSN Client Name Old SD
New SD
------------- ----------- ---------------------------------------- --------
--------
800901 899-34-3482 John Smith 04/20/20
05/01/20
400909 144-23-0029 John Smith 04/09/20
04/11/20
447788 445-89-9967 kjl;j;j 04/05/20
04/12/20
300099 234-90-7815 John Johnson 04/08/20
04/15/20
What's happened is the client name field is too wide, so the New SD field kicks down to the next line. I'd like to clean this up. Is there a way I can either increase the length of the row before it moves to the next line, or can I re-size the client name field to match the size of the data. In other words, cli_name_vc is declared as a varchar(40). If the longest name that comes up in the query is 18 characters long, can I re-size the output so that it does not take up 40 characters?
Any help is greatly appreciated.
thanks,
-scott
View 1 Replies
View Related
Oct 27, 2004
I have a table like:
ID Disc
----------------
1 BUSH
2 JOHN
1 GOLE
2 MIKE
I would like output depending on ID to put Disc into two columns. Like:
ID Disc1 Disc2
----------------------------
1 BUSH NULL
2 NULL JOHN
1 GOLE NULL
2 NULL MIKE
Any help will be appreciated. Thanks
ZYT
View 2 Replies
View Related
Apr 24, 2012
I am trying to add two columns together and find all the entries that equal zero I have:
select table1.nbr, table2.itemvalue as CC
from table1, table2
where table1.nbr=table2.nbr and exists (select (OpnAc1+OpnAc2) as Open from table2
I want to take the sum of OpnAc1 and OpnAc2 and find all that equal 0, (meaning no open 1 or open 2)
View 2 Replies
View Related
Feb 23, 2006
Using MySQL 5, MySQLQuery (latest). Complete Newbie,
I have the following query:
SELECT i.IndivId, i.Surname, i.First_Names, i.Parents,
(SELECT i.Surname FROM individuals i, families f
where (i.IndivId = f.father_ID) and (i.indivId = f.Family_ID))
as "father"
FROM individuals i
where i.Parents>0
order by i.Parents;
It returns nulls for the subselect. There are 2 tables, individuals and Parents. I am trying to use a father id. in the PArents table to cause the individual id. (being the father of the individual(s)) to be named in the father column. I have a feeling I'm going wrong here.
I think the query above is pretty self explanatory!
Many thanks.
View 4 Replies
View Related
Nov 7, 2015
I want to call "oracle" stored procedure with output parameter from SSIS ole db command task.
Actually I am able to successfully call the procedure but my Output value is not updating in the mapped column.
I used below PL/SQL query.
DECLARE
IS_VALID VARCHAR2(200);
BEGIN
IS_VALID(
PARAM1 => ?,
PARAM2 => ?,
IS_VALID => IS_VALID
);
? := IS_VALID;
END;
If I try to supply "OUTPUT" word I get error:
"ORA-06550: line 1, column 45:
PLS-00103: Encountered the symbol "OUTPUT" when expecting one of the following:Â Â . ( ) , * @ % & = - + < / >"
BEGIN
IS_VALID(
?,
?,
? OUTPUT
);
END;
how to receive output parameter value of oledb command while calling oracle stored procedures.
View 4 Replies
View Related
Aug 27, 2007
Hi Everyone,
I am trying to do a query where I need to use as little C# as possible to build my gridview. Basically I have a column called statusID. There are about 15 options for this column but I only want to count certain ones. I want to count when statusID = 3 and output that into a column called "fullUnitsUsed" but when the value is 4 or > 13 I want it to count and put the result into a column called "halfUnitsUsed". I also want it to count based on the month. To accomplish this I have used CASE and GROUP BY. This has worked to some extent. Currently if I COUNT for one month I get the correct number of fullUnitsUsed and halfUnitsUsed used for January. Unfortunately the query returns 2 records for the month. The first one has a value for fullUnitsUsed and halfUnitsUsed is NULL, the second record has fullUnitsUsed as NULL and halfUnitsUsed has the correct value. I was hoping to output one record where both fullUnitsUsed and halfUnitsUsed have data. My other problem is that if I test for the entire year (which is what this query is supposed to do) there are 5 records returned for each month, 3 of the records have fullUnitsUsed and halfUnitsUsed both as NULL and for the other 2, one has fullUnitsUsed with data and the other has halfUnitsUsed with data and the other column in both records is NULL. The values for fullUnitsUsed and halfUnitsUsed are counted for the entire year as well, which I only want it to count based on each month. Below is my query, any suggestions about how to approach this will be greatly appreciated. If any clarification is needed please let me know. Again if I could get this to work completely with SQL and not need to use any more C# than I have to it would be preferable.
SELECT People.lastName + ', ' + People.firstName AS fullName, Property.Name, NYSDDSORegion.Description, OpenDays.[month], OpenDays.maxOpenDays,CASE Attend.statusID
WHEN 3 THEN COUNT(Attend.statusID)
END AS fullUnitsUsed,CASE Attend.statusID
WHEN 4 THEN COUNT(Attend.statusID)
WHEN 14 THEN COUNT(Attend.statusID)
WHEN 15 THEN COUNT(Attend.statusID)
WHEN 16 THEN COUNT(Attend.statusID)
WHEN 17 THEN COUNT(Attend.statusID)
WHEN 18 THEN COUNT(Attend.statusID)
WHEN 19 THEN COUNT(Attend.statusID)
WHEN 20 THEN COUNT(Attend.statusID)
END AS halfUnitsUsed
FROM Attend INNER JOIN People ON Attend.personID = People.personID
INNER JOIN Property ON Attend.propertyID = Property.propertyID
INNER JOIN NYSDDSORegion ON Property.RegionID = NYSDDSORegion.RegionID
CROSS JOIN OpenDays
WHERE (Attend.attendDate BETWEEN '1/1/2007' AND '12/31/2007')
GROUP BY Property.Name, People.lastName, NYSDDSORegion.Description, People.firstName, OpenDays.monthID, OpenDays.[month], OpenDays.maxOpenDays, Attend.statusID
ORDER BY Property.Name, fullName, NYSDDSORegion.Description
View 2 Replies
View Related
