Group on particular date every year

Hi,
We have a load of personal records like (e.g.) these
Name, Function, Stardate,Enddate
Fred,assistent, div1, 1/1/2001, 31/12/2004
Richard,assistent, div2, 1/1/2001, 28/2/2003
Richard,director, div2, 1/3/2003, 31/7/2003
...
Now we need a matrix showing how many people works at date 1/7 of a
particular year and what function they had
2001 2002 2003 2004
ass dir ass dir ass dir ass dir
Div1 1 0 1 0 1 0 0 0
Div2 1 0 1 0 0 1 0 0
---
Total 2 0 2 0 1 1 0 0
The biggest problem I have is determining who is at 1/7 in what function and
group on that info.
Can somebody get me started? I'm trying to work with the IIF function but
with no good results.
Thak you very much
DanielDaniel,
If you aren't using SQL Server 2005, then this won't work. SQL Server
2005 introduced a function called PIVOT. Check out the SQL below to see
how it works.
SQL:
SELECT
div as Division, [2001-assistant], [2001-director], [2002-assistant],
[2002-director], [2003-assistant], [2003-director], [2004-assistant],
[2004-director]
FROM
(
SELECT div, '2001-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2001-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2001-01-07
00:00:00', 102))
UNION
SELECT div, '2002-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2002-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2002-01-07
00:00:00', 102))
UNION
SELECT div, '2003-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2003-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2003-01-07
00:00:00', 102))
UNION
SELECT div, '2004-' + [function] AS yearFunction, name FROM
divFunctions WHERE (startDate <= CONVERT(DATETIME, '2004-01-07
00:00:00', 102)) AND (endDate >= CONVERT(DATETIME, '2004-01-07
00:00:00', 102))
) AS sourceTable
PIVOT
(count(name) FOR yearFunction IN
([2001-assistant],[2001-director],[2002-assistant],[2002-director],[2003-assistant],[2003-director],[2004-assistant],[2004-director]))
AS pivotTable
ORDER BY
div
Results: (formatting might get messed up because of word wrap)
Division 2001-assistant 2001-director 2002-assistant 2002-director
2003-assistant 2003-director 2004-assistant 2004-director
-- -- -- -- --
-- -- -- --
div1 1 0 1 0 1
0 1 0
div2 1 0 1 0 1
1 0 0
Hope your using 2005. Hope this helps.
-Josh
Daniel wrote:
> Hi,
> We have a load of personal records like (e.g.) these
> Name, Function, Stardate,Enddate
> Fred,assistent, div1, 1/1/2001, 31/12/2004
> Richard,assistent, div2, 1/1/2001, 28/2/2003
> Richard,director, div2, 1/3/2003, 31/7/2003
> ...
> Now we need a matrix showing how many people works at date 1/7 of a
> particular year and what function they had
> 2001 2002 2003 2004
> ass dir ass dir ass dir ass dir
> Div1 1 0 1 0 1 0 0 0
> Div2 1 0 1 0 0 1 0 0
> ---
> Total 2 0 2 0 1 1 0 0
> The biggest problem I have is determining who is at 1/7 in what function and
> group on that info.
> Can somebody get me started? I'm trying to work with the IIF function but
> with no good results.
> Thak you very much
> Daniel

group by function not returning expected

Hi,
I am having trouble get the numbers that I need. I have a table that records
positions and the action that happened at that positon and the operator that
caused the action. I need to total the amounts per Operator per Action Code.
I tried to use GROUP BY but it only gave me one operator for each LotID. Thi
s
was the statement I used:
SELECT DISTINCT LotID, UserName, SUM(EncoderpositionDetectionEnd1) AS [End
1], ActionCode
FROM dbo.OperatorData
GROUP BY UserName, ActionCode, LotID
ORDER BY LotID
The following is an example of the data that is stored in the table.
LotID Operator Position Action Code
826O3 Priscilla 923797449 11
826O3 Priscilla 926950347 10
826O3 Priscilla 923797449 11
826O3 Priscilla 926950347 10
A1073 Susan 2946519574 11
826O3 Priscilla 960248867 11
82603 Gloria 885226642 10
82603 Gloria 893352901 11
82603 Angela 924485652 11
82603 Gloria 896646927 10
826O3 Priscilla 960248867 11
A1073 Susan 2946519574 11
82603 Angela 927628980 10
82603 Gloria 885226642 10
82603 Gloria 893352901 11
82603 Gloria 896646927 10
A1073 Carolyn 2915880805 10
82603 Angela 924485652 11Can you clarify this? The columns in your data do not match the columns in
your query. Also, do you need to sum (add stuff up) our count the rows? Also
,
you say you need the amounts per operator per action code but your group by
includes lot id.
"A.B." wrote:

> Hi,
> I am having trouble get the numbers that I need. I have a table that recor
ds
> positions and the action that happened at that positon and the operator th
at
> caused the action. I need to total the amounts per Operator per Action Cod
e.
> I tried to use GROUP BY but it only gave me one operator for each LotID. T
his
> was the statement I used:
> SELECT DISTINCT LotID, UserName, SUM(EncoderpositionDetectionEnd1) AS [End
> 1], ActionCode
> FROM dbo.OperatorData
> GROUP BY UserName, ActionCode, LotID
> ORDER BY LotID
> The following is an example of the data that is stored in the table.
> LotID Operator Position Action Co
de
> 826O3 Priscilla 923797449 11
> 826O3 Priscilla 926950347 10
> 826O3 Priscilla 923797449 11
> 826O3 Priscilla 926950347 10
> A1073 Susan 2946519574 11
> 826O3 Priscilla 960248867 11
> 82603 Gloria 885226642 10
> 82603 Gloria 893352901 11
> 82603 Angela 924485652 11
> 82603 Gloria 896646927 10
> 826O3 Priscilla 960248867 11
> A1073 Susan 2946519574 11
> 82603 Angela 927628980 10
> 82603 Gloria 885226642 10
> 82603 Gloria 893352901 11
> 82603 Gloria 896646927 10
> A1073 Carolyn 2915880805 10
> 82603 Angela 924485652 11
>|||SELECT DISTINCT LotID, UserName 'Operator',
SUM(EncoderpositionDetectionEnd1) AS [End
The LotID in order to connect the results to another query I have that gives
me the Lots that were run last w. Also I need the Sum of the rows.
"Kathi Kellenberger" wrote:
> Can you clarify this? The columns in your data do not match the columns in
> your query. Also, do you need to sum (add stuff up) our count the rows? Al
so,
> you say you need the amounts per operator per action code but your group b
y
> includes lot id.
>
>
> "A.B." wrote:
>|||With your query, you should get a row for every possible combination of
lotID, operator and action code. I'm not sure if that's what you are after.
"A.B." wrote:
> SELECT DISTINCT LotID, UserName 'Operator',
> SUM(EncoderpositionDetectionEnd1) AS [End
> The LotID in order to connect the results to another query I have that giv
es
> me the Lots that were run last w. Also I need the Sum of the rows.
> "Kathi Kellenberger" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. And your narrative is useless.|||That is what i want but this is an example of the results that I am getting:
62324 Pamela 9969832955 10
62324 Pamela 19966076115 11
62332 Susan 9641299760 11
62332 Teresa 9633011910 11
62334 Carolyn 9978382505 10
62334 Carolyn 19983575455 11
62334 Melissa 9719717360 10
I am only getting a one result for certain lots and several for others.
"Kathi Kellenberger" wrote:
> With your query, you should get a row for every possible combination of
> lotID, operator and action code. I'm not sure if that's what you are afte
r.
>
>
> "A.B." wrote:
>|||A.B.,
Kathy says you'll get one row for each combination of lotID, operator,
and action code,
and you say "That is what i want". This is exactly what you are
getting. The combinations
in your results are
(62324, Pamela, 10)
(62324, Pamela, 11)
(62332, Susan, 11)
(62332, Teresa, 11)
(62334, Carolyn, 10)
(62334, Carolyn, 11)
(62334, Melissa, 10)
You have more than one name and/or action code for some lotID values,
so you will get more than one row for those values. For example, for lotID
62334, you have information for Melissa with action code 10, and you have
information for Carolyn with action codes both 10 and 11. If you want only
one row for this lotID, do you want it to say Melissa or Carolyn, and do you
want the action code to be 10 or 11? You need to be more specific about
what your result is supposed to be.
Steve Kass
Drew University
A.B. wrote:
>That is what i want but this is an example of the results that I am getting
:
> 62324 Pamela 9969832955 10
> 62324 Pamela 19966076115 11
> 62332 Susan 9641299760 11
> 62332 Teresa 9633011910 11
> 62334 Carolyn 9978382505 10
> 62334 Carolyn 19983575455 11
> 62334 Melissa 9719717360 10
>I am only getting a one result for certain lots and several for others.
>"Kathi Kellenberger" wrote:
>
>|||No, because I am only getting the operator Pamela for Lot 62324 when actuall
y
there is four or five operators.
"Steve Kass" wrote:

> A.B.,
> Kathy says you'll get one row for each combination of lotID, operator,
> and action code,
> and you say "That is what i want". This is exactly what you are
> getting. The combinations
> in your results are
> (62324, Pamela, 10)
> (62324, Pamela, 11)
> (62332, Susan, 11)
> (62332, Teresa, 11)
> (62334, Carolyn, 10)
> (62334, Carolyn, 11)
> (62334, Melissa, 10)
> You have more than one name and/or action code for some lotID values,
> so you will get more than one row for those values. For example, for lotI
D
> 62334, you have information for Melissa with action code 10, and you have
> information for Carolyn with action codes both 10 and 11. If you want onl
y
> one row for this lotID, do you want it to say Melissa or Carolyn, and do y
ou
> want the action code to be 10 or 11? You need to be more specific about
> what your result is supposed to be.
> Steve Kass
> Drew University
>
> A.B. wrote:
>
>|||Ah. When you said "only one" for some and "several" for others, I
thought the problem was the "several", not the "one". ;)
My guess is that you are not showing us the entire query, since if there
is a row with LotID 62324 and operator <> 'Pamela' in dbo.OperatorData,
the result of
SELECT DISTINCT
LotID,
UserName 'Operator',
SUM(EncoderpositionDetectionEnd1) AS [End 1],
ActionCode
FROM dbo.OperatorData
GROUP BY UserName, ActionCode, LotID
ORDER BY LotID
will definitely include a row showing 62324 with another operator.
Perhaps you
are noting the omission only after this query is used in a larger one, maybe
with an inner join that should be a left join - I can't be sure.
If you are certain that this is your query and that results are missing,
please show us both the results of this query and the result of
SELECT TOP 10
LotID,
UserName, 'Operator',
EncoderpositionDetectionEnd1,
ActionCode
FROM dbo.OperatorData
WHERE LotID = '62324'
AND UserName <> 'Pamela'
-- optionally add ORDER BY something...
SK
A.B. wrote:
>No, because I am only getting the operator Pamela for Lot 62324 when actual
ly
>there is four or five operators.
>"Steve Kass" wrote:
>
>|||I had a date in the where clause to make my results alot smaller and by
taking the date out of the where clause it allowed me to see all of the
operators. I am not sure why this happened but it is working now. Thanks for
your help man.
"Steve Kass" wrote:

> Ah. When you said "only one" for some and "several" for others, I
> thought the problem was the "several", not the "one". ;)
> My guess is that you are not showing us the entire query, since if there
> is a row with LotID 62324 and operator <> 'Pamela' in dbo.OperatorData,
> the result of
> SELECT DISTINCT
> LotID,
> UserName 'Operator',
> SUM(EncoderpositionDetectionEnd1) AS [End 1],
> ActionCode
> FROM dbo.OperatorData
> GROUP BY UserName, ActionCode, LotID
> ORDER BY LotID
> will definitely include a row showing 62324 with another operator.
> Perhaps you
> are noting the omission only after this query is used in a larger one, may
be
> with an inner join that should be a left join - I can't be sure.
> If you are certain that this is your query and that results are missing,
> please show us both the results of this query and the result of
> SELECT TOP 10
> LotID,
> UserName, 'Operator',
> EncoderpositionDetectionEnd1,
> ActionCode
> FROM dbo.OperatorData
> WHERE LotID = '62324'
> AND UserName <> 'Pamela'
> -- optionally add ORDER BY something...
> SK
> A.B. wrote:
>
>

GROUP BY FUNCTION

can anyone tell me what is the use of group by in rs tables and how to use group by in tables (when we have to use )...if possible please gimme some examples..

Groups can be seens as a presentation hierarchy. Within a group, only the values defined by the grouped columns are available (e.g. Grouping by order id will make the values of an order per orderid accessible within the group). in addition you can use the group footer or header to aggregate values (like sums of OrderdItems).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

GridView delete function

This is killing me. I've searched the forums for hours and can't find the answer. My SQLDataSource is working fine except when I want to delete. I've allowed the delete function to be shown on the gridview. This is my SQLDataSource:

 <asp:SqlDataSource ID="IndexDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:IndexConnectionString%>" SelectCommand="SELECT * FROM [Index] WHERE (Type LIKE '%' + @.SearchText2 + '%') OR (Product LIKE '%' + @.SearchText2 + '%') OR (Version LIKE '%' + @.SearchText2 + '%') OR (Binder LIKE '%' + @.SearchText2 + '%') OR (Language LIKE '%' + @.SearchText2 + '%') OR (CDName LIKE '%' + @.SearchText2 + '%') OR (Details LIKE '%' + @.SearchText2 + '%') OR (ISOLink LIKE '%' + @.SearchText2 + '%')" DeleteCommand="DELETE FROM [Index] WHERE [ID] = @.original_ID" UpdateCommand="UPDATE [Index] SET Type = @.Type, Product = @.Product , Version = @.Version, Binder = @.Binder, Language = @.Language, CDName = @.CDName, Details = @.Details, ISOLink = @.ISOLink WHERE ID = @.ID"> <SelectParameters> <asp:ControlParameter Name="SearchText2" Type="String" ControlID="SearchText2" PropertyName="Text" ConvertEmptyStringToNull="False" /> </SelectParameters> <DeleteParameters> <asp:Parameter Name="original_ID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="Type" /> <asp:Parameter Name="Product" /> <asp:Parameter Name="Version" /> <asp:Parameter Name="Binder" /> <asp:Parameter Name="Language" /> <asp:Parameter Name="CDName" /> <asp:Parameter Name="Details" /> <asp:Parameter Name="ISOLink" /> </UpdateParameters> </asp:SqlDataSource>

It doesn't give me an error if I click delete but it doesn't delete the record. I've tried changing the DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> but it gives me the error "Must declare the scaler variable of '@.ID'"... I saw in this post http://forums.asp.net/p/1077738/1587043.aspx#1587043 that the answer was that "The variable you have declared in the definition of the proc isdifferent from the variable you are using in the WHERE clause." when they are both the same. Thanks for any help.

-Brandan

Hello

What if you add a semicolumn after @.original_ID ? like this"DELETE FROM [Index] WHERE [ID] = @.original_ID;"

|||

Is the ID column your table's primary key? If yes, you need to make sure that the ID is set in your GridView's DadaKeyNames and you should change your DeleteParameter to <asp:Parameter Name="ID" Type="Int32" /> and DeleteCommand="DELETE FROM [Index] WHERE [ID] = @.ID"

If you cannot make this work, please post your GridView part here and if you can list all your columns' name instead of a * in your SELECT statement, that would be great. Thanks.

|||

If it doesn't fixes it it might just be the DataKeyNames field of the gridview that need to be set to ID .

|||

**RESOLVED**

It was definitely the DataKeyNames. I had recreated the gridview so many times I forgot to put it back in. thanks ya'll.

Gridview / SqlDataSource error - Procedure or function <stored procedure name> has t

Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.

Post your Gridview and SQL Proceedure code.|||

SqlDataSource:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:TPCConnectionString %>"SelectCommand="SELECT ID, AccountNumber, CompanyName, ExceptionDescription, PricingAdjustments FROM ExceptionList ORDER BY CompanyName"DeleteCommand="DELETE FROM ExceptionList WHERE (ID = @.ID)"ProviderName="<%$ ConnectionStrings:TPCConnectionString.ProviderName %>"UpdateCommand="updExceptionList"UpdateCommandType="StoredProcedure"><DeleteParameters>

<asp:ParameterName="ID"/>

</DeleteParameters><UpdateParameters><asp:ControlParameterControlID="GridView2"Name="AccountNumber"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="CompanyName"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="ExceptionDescription"PropertyName="SelectedValue"/></UpdateParameters></asp:SqlDataSource>

Stored Procedure:

CREATE PROCEDURE updExceptionList @.ID numeric(5), @.AccountNumber nvarchar(255),@.CompanyName nvarchar(255), @.ExceptionDescription nvarchar(255) AS

UPDATE ExceptionList SET AccountNumber = @.AccountNumber, CompanyName = @.CompanyName, ExceptionDescription = @.ExceptionDescription WHERE ID = @.ID
GO

But also fails if I specify ID parameter in SqlDataSpurce--here is the error:

Procedure or function updExceptionList has too many arguments specified.

|||

SqlDataSource:

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:xxxConnectionString %>"SelectCommand="SELECT ID, AccountNumber, CompanyName, ExceptionDescription, PricingAdjustments FROM ExceptionList ORDER BY CompanyName"DeleteCommand="DELETE FROM ExceptionList WHERE (ID = @.ID)"ProviderName="<%$ ConnectionStrings:xxxConnectionString.ProviderName %>"UpdateCommand="updExceptionList"UpdateCommandType="StoredProcedure"><DeleteParameters>

<asp:ParameterName="ID"/>

</DeleteParameters><UpdateParameters><asp:ControlParameterControlID="GridView2"Name="AccountNumber"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="CompanyName"PropertyName="SelectedValue"/><asp:ControlParameterControlID="GridView2"Name="ExceptionDescription"PropertyName="SelectedValue"/></UpdateParameters></asp:SqlDataSource>

Stored Procedure:

CREATE PROCEDURE updExceptionList @.ID numeric(5), @.AccountNumber nvarchar(255),@.CompanyName nvarchar(255), @.ExceptionDescription nvarchar(255) AS

UPDATE ExceptionList SET AccountNumber = @.AccountNumber, CompanyName = @.CompanyName, ExceptionDescription = @.ExceptionDescription WHERE ID = @.ID
GO

But also fails if I specify ID parameter in SqlDataSpurce--here is the error:

Procedure or function updExceptionList has too many arguments specified.

|||

Change your UpadteParametrs their Control Id's are wronge.

Are u using some DropDownlists inside a Gridview?

|||

In what way are they wrong? They are for control Gridview2.

I actually solved this problem by entering the entirety of the stored procedure in the SqlDataSource configuration, which is the most unideal solution I could make work. I do not want any sql at all in my application but it seems that I'm forced to put it there.

|||

I got the same error.

As it turned out, the ConflictDetection on my datasource was set to "CompareAllValues" which forces the datasource to supplies all the columns to my stored procdure. Hence, the error because the stored procedure only take one parameter.

My fix, was just change the ConflictDetection to "OverwriteChanges". Then it worked.

NOTE: I did NOT have to write any code to add new parameter or set the parameter's value for the delete command at all.

Regards,

Minh

|||

correction on the "NOTE".

I did have to add parameter and value for the stored procdure in the RowDeleting event.

But make sure you don't add the parameters in your designer.

protectedvoid GridView1_RowDeleting(object sender,GridViewDeleteEventArgs e)

{

foreach (DictionaryEntry entryin e.Keys)

{

this.SqlDataSource1.DeleteParameters.Add(entry.Key.ToString(), entry.Value.ToString());

}

}

|||

Minh,

Thank you for looking at the issue. I revisited this page and found that the ConflictDetection parameter was set to "OverwriteChanges" so that doesn't seem to be the issue. I am finding many other gridview / parameter problems, although I've had some better success since this post. I would like to understand why you had to delete all the parameters in code, does the designer not function properly? I'm not really interested in writing code for my next update which has like 20 parameters.

|||

Hi sestyd,

I've had a similar problem before, where the update command is sending more parameters than I have defined in the Sqldatasource. (Assuming you have this connected to a grid view), what it seems to be doing is sending any parameter that is defined in the grid view that is not specified as read only (as well as the data keys).

I pretty much just either made the parameters read only in the grid view (if that was viable) or defined the parameters in the stored procedure, then just ignored them.

BTW here is some code that I wrote that will display all of the parameters and their values in a label on the web page for an update function and stop the function from executing, this helped me work out what was going on.

[VB Code]

Protected Sub MyDataSource_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles MyDataSource.Updating lblTest.Text =""For iAs Integer = 0To e.Command.Parameters.Count - 1Step 1 lblTest.Text &= e.Command.Parameters.Item(i).ParameterName.ToString &" :: " & e.Command.Parameters.Item(i).Value &"<br>"Next e.Cancel =TrueEnd Sub

[/VB Code]

HTH