Grouping by week in stored procedure

Hi all,

I am using the below statement to get some dates grouped by date, in my SP.

SELECT TOP 100 PERCENT COUNT(dbo.test.CallDate) AS CallCount, year(dbo.test.CallDate) AS CallYear, datepart(wk, dbo.test.CallDate) AS [Week]
FROM dbo.test

LEFT OUTER JOIN dbo.view1 ON dbo.test.CallID = dbo.view1.CallID
LEFT OUTER JOIN dbo.view2 ON dbo.test.CallID = dbo.view2.CallID

WHERE (dbo.view1.[ACCOUNT ID] = @.accountid

OR (dbo.view2.[ACCOUNT ID] = @.accountid

AND (convert(varchar(10),dbo.test.CallDate,121) BETWEEN CONVERT(DATETIME, @.StartDate, 102)AND CONVERT(DATETIME, @.EndDate, 102))
GROUP BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate)
ORDER BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate)

i gave startdate as 1/1/2007 and endDate as 2/18/2007

i am getting the reuslt as
count year week
42 2001 32
2 2001 39
1 2001 51
1 2002 17
1 2002 19
106 2002 21
183 2002 22
226 2002 23
.........................
...........................
1208 2007 1
1292 2007 2

actually i should get only the last 2 rows.
Can anyone please point out, why i am getting the 2001, 2002 data? and how to fix that?

Thanks

Looks to me like you've got your bracketing wrong round your OR clause...

It's currently WHERE ( A OR (B AND C))

Shouldn't it be WHERE (A OR B) AND C?

|||

Thanks

Great help

It worked

grouping and concatenating

i have a the following table:

CREATE TABLE [dbo].[tIndex] (
[indexID] [int] IDENTITY (1, 1) NOT NULL ,
[wordID] [int] NULL ,
[wordPos] [int] NULL ,
[paraID] [int] NULL
) ON [PRIMARY]
GO

for each wordID i have many paraID and for each wordID,paraID i have many wordPos

i will use the following convention:
paraID=p
wordID=w
wordPos=wp

i want to concatenate the columns to get the following format:

row1: w1 p1,NB1,wp1,wp2,w3... | p2,NB2,wp4,wp5,wp6... | ...
row2: w2 ...
row3: w3 ...

where NB1 is the number of wp having w1 and p1

Note: the length of a row may exceed 8000 charsOriginally posted by samham
Note: the length of a row may exceed 8000 chars If any column exceeds 8000 characters (which is what I think you are trying to say), then you have no choice... You must build those columns on the client.

-PatP|||the total rows of the table is 15 million

i am trying to copy the table content to a text file in the format i described

I put the note about the 8000 chars to say that 1 row cannot be contained in a varchar(8000) variable in case concatenating using a varchar(8000) is a solution

i am using c# as my programming language so my last option is to do this by c# code by selecting wordID and then for each rowID select the paraID and then for each wordID,paraID select the wordPos

but i was wondering if this can be done by sql and then send the result directly to a textfile|||Sorry, SQL Server can't do what you want. According to SQL Maximum Capacity Specifications (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp), the maximum row size for SQL Server is 8060 bytes. The maximum single string is 8000 bytes.

In your case, your last resort is the only one that might work.

-PatP|||Ok Pat thank you
i will just go for the c# solutionsql

Group by query returning too many rows

Can anyone help ?
Why is this query:

select bp, sum(msg) as 'msg'
from dbo.net_report
where gateway = 'sweden'
and convert(varchar, sqldate, 2) > '02.05.01'
and convert(varchar, sqldate, 2) <= '02.05.31'
group by bp

returning 666 rows, while this query

select bp
from dbo.net_report
where gateway = 'sweden'
and convert(varchar, sqldate, 2) > '02.05.01'
and convert(varchar, sqldate, 2) <= '02.05.31'
group by bp

is only returning 20.
The "correct" result is 20 rows, one for each bp.
The fisrt query returns alot of duplicate bp.

By the way: What is faster: Converting the sqldate field to a varchar and comparing with another varchar, or converting the varchar to a date and then comparing it to the sqldate field ?do you want the SUM of all the msg attributes or the number of msg (messages?) for each bp? try using COUNT(*) in place of the SUM(msg).

In your case you are probably doing table scans due to the convertion of the date attribute to a varchar. SQL Server can efficiently convert and test a (var)char variable to a date attribute + you can take advantage of indexes.|||Sorry, I guess I should have made things clearer.
What I want is the sum of all the numbers stored in the msg column, i.e. the number of msgs for each bp. So the result set should have one row for each bp. This works fine without the sum(msg) part, and the result looks something like this:

HENNES
HENTEXTRA
KANAL5

But when I add the sum(msg) to get the number of messages pr. bp then the result looks like this:

Wow. Something strange just happened. When I ran the query to produce the results I added "order by bp" at the end, and then there was suddenly just one row for each 20 in total. Without it the resultset returns 666 rows.

Is the group by clause dependent upon the order one retrieves the rows ?|||No. The order by is used to sort the result set and does not affect the group by.

I set up a simple test...

Code:
----------------------------
create table #tmp(f1 varchar(10),f2 int)
insert into #tmp values('A',2)
insert into #tmp values('A',4)
insert into #tmp values('C',3)
insert into #tmp values('C',1)
insert into #tmp values('C',1)
insert into #tmp values('B',2)
insert into #tmp values('B',3)
insert into #tmp values('B',4)

select f1,sum(f2) as 'Sum'
from #tmp
group by f1
order by f1
----------------------------

is this anything close to what you are working on?|||That is pretty much what I am working on, except that my view has alot more columns. At the moment I am really only interested in getting one row for each bp, with one sum of messages for each.
My query does produce the desired results, as long as I have the "order by bp" clause at the end.
So my problem is really solved, but I don't really understand why though. If you want to find out why, and need any more information from me just let me know.
The view I am querying is based on two other views, but I can't see that making much of a difference.

This is the result I was looking for, and I get with the order bp:

davinci 1333
E-CLIPS 1864
HENNES 1397
KANAL5 6470
MRJET 6
PASSAGEN 70
SIMONTV 12
SPORTAL 828
STARLIFE 1004
TISCALI 2484
YAHOO 3
...
...
20 rows in total

This is some of what I get without the order bp:
SPORTAL 8
davinci 11
E-CLIPS 11
davinci 1
E-CLIPS 1
davinci 7
E-CLIPS 7
davinci 9
E-CLIPS 9
davinci 2
E-CLIPS 2
davinci 8
...
...
...
666 rows in total

Group By Expressions

I'd like to dynamically create a group by expression. I have

select sourcecd
FROM dbo.ITMV_ScanCardHistory
GROUP BY dbo.fn_GroupBy (@.GroupBy)

Where:

@.groupby = sourcecd and

fn_GroupBy =

CREATE FUNCTION dbo.fn_GroupBy
(@.ColumnName Varchar(55))
RETURNS Varchar(55)
AS
BEGIN

RETURN @.ColumnName
END

I keep getting this error message:

Server: Msg 164, Level 15, State 1, Line 48
GROUP BY expressions must refer to column names that appear in the select list.

Please help.

Hello,

This won't work as the Group By clause is looking for a column name, whereas you are supplying a varchar(55) string...

Have you looked at dynamic statements via ExecuteSQL?

Have a look at EXECUTE in BOL.

Cheers

Rob

|||

The error is saying that what you have in your GROUP by you need in your select.

Why do you want to do this, there are a number of options but I would like to understand why you need this first.

|||thank you. We are designing reports in reporting services and we'd like to give the business user the option of selection which column they'd like to group their data by.|||

thanks, however SQL Books says that scalar functions or any valid expression can be used in a group by:

see "

Invoking User-Defined Functions That Return a Scalar Value"

under Accessing and Changing Relation...

|||

You can do it in RS by making the group by field dynamic. In your group define the field to group by as fields(Paramaters("GroupBy").Value).Value

Or something like that

|||Thank you, I will give this a try.|||

Hi,

By using sp_executesql you can create your dynamic sql and run it as below

declare @.sql as nvarchar(1000)
SELECT @.sql = N'
select ' + dbo.fn_GroupBy('eventid') +
N' FROM Events
GROUP BY ' + dbo.fn_GroupBy('eventid')

-- SELECT @.sql

exec sp_executesql @.sql

But you can also use "distinct" instead of "group by"

declare @.sql as nvarchar(1000)
SELECT @.sql = N'
select distinct ' + dbo.fn_GroupBy('eventid') + N' FROM Events '
-- SELECT @.sql

exec sp_executesql @.sql

Eralper

http://www.kodyaz.com

GROUP BY and populating temp table, ideas?

Hello All,
I have the following table, and want to create a report as seen below.
CREATE TABLE [dbo].[Sales] (
[ACTIVITY_ID] [varchar] (16) NOT NULL ,
[CREATED_BY] [varchar] (10) NULL,
[YEAR] [varchar] (9) NULL ,
[PERIOD] [varchar] (2) NULL ,
[WEEK] [char] (1) NULL ,
[AMOUNT] [varchar] (3) NULL
) ON [PRIMARY]
GO
I need to see count(*) of each rep for each w and current preriod.
Something like this:
REP_NAME W1 W2 W3 W4 W5 Period(Month)
======== ===== ===== ===== ===== ===== =============
DAVID 5 10 5 20
WILLIAM 2 8 5 15
JANE 10 2 10 22
Do I need to run seperate group by's for each w and populate a temp table
?
Or there can be a simpler way to do that?
Thanks,
Ada
--
SQL Server DBAFirst of all, pivoting data for presentation purposes does not belong on the
data layer.
But if you really, really, really need to do it in T-SQL read this:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML

GRMPH!

CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle (
@.description NVARCHAR(70),
@.jobTitleKey INT
) AS
UPDATE msl_JobTitle
SET Description = @.description
WHERE JobTitleKey = @.jobTitleKey
GO
Then I have some VB.NET code that runs it:
Private Sub fixJobTitles()
Dim cnstr As String =
ConfigurationSettings.AppSettings("connectionString")
Dim cn As New SqlConnection(cnstr)
Dim cmGet As New SqlCommand("SELECT * FROM dbo.msl_JobTitle", cn)
Dim cmSave As New SqlCommand("dbo.msl_UpdateMSLJobTitle", cn)
Dim dt As New DataTable()
cmGet.CommandType = CommandType.Text
cmSave.CommandType = CommandType.StoredProcedure
Dim da As New SqlDataAdapter(cmGet)
Dim tx As New Transform()
With cmSave
.Parameters.Add("@.description", "")
.Parameters.Add("@.jobTitleKey", 0) ' <-- WTF?!?
End With
da.UpdateCommand = cmSave
Try
da.Fill(dt)
Catch exc As Exception
Console.WriteLine(exc.Message)
End Try
For Each dr As DataRow In dt.Rows
dr("Description") =
tx.GetLowAlphaFromHighAlpha(dr("Description").ToString())
Next
Try
da.Update(dt)
Catch exc As Exception
'EXCEPTION! Parameter '@.jobTitleKey' was expected but not supplied
Console.WriteLine(exc.Message)
End Try
End Sub
AM I RETARDED OR SOMETHING?!?!
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James HetfieldObviously!
How do you expect the dataadapter object to know which columns in your
datatable object to use to satisfy the parameters of the updatecommand
object.
You need to do some table/column mapping to bind the correct parameter to
the correct column.
Check out the overloads of the sqlparameter object constructor.
In addition, you datatable object will not have a schema until you have
'filled' it, so you won't be able to map the columns until after the fill.
You could, however, explicitly define the schema so that the datatable is
ready for column mapping at an earlier stage.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:uswh4P9rFHA.332@.tk2msftngp13.phx.gbl...
> CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle (
> @.description NVARCHAR(70),
> @.jobTitleKey INT
> ) AS
> UPDATE msl_JobTitle
> SET Description = @.description
> WHERE JobTitleKey = @.jobTitleKey
> GO
> Then I have some VB.NET code that runs it:
> Private Sub fixJobTitles()
> Dim cnstr As String =
> ConfigurationSettings.AppSettings("connectionString")
> Dim cn As New SqlConnection(cnstr)
> Dim cmGet As New SqlCommand("SELECT * FROM dbo.msl_JobTitle", cn)
> Dim cmSave As New SqlCommand("dbo.msl_UpdateMSLJobTitle", cn)
> Dim dt As New DataTable()
> cmGet.CommandType = CommandType.Text
> cmSave.CommandType = CommandType.StoredProcedure
> Dim da As New SqlDataAdapter(cmGet)
> Dim tx As New Transform()
> With cmSave
> .Parameters.Add("@.description", "")
> .Parameters.Add("@.jobTitleKey", 0) ' <-- WTF?!?
> End With
> da.UpdateCommand = cmSave
> Try
> da.Fill(dt)
> Catch exc As Exception
> Console.WriteLine(exc.Message)
> End Try
> For Each dr As DataRow In dt.Rows
> dr("Description") =
> tx.GetLowAlphaFromHighAlpha(dr("Description").ToString())
> Next
> Try
> da.Update(dt)
> Catch exc As Exception
> 'EXCEPTION! Parameter '@.jobTitleKey' was expected but not supplied
> Console.WriteLine(exc.Message)
> End Try
> End Sub
> AM I RETARDED OR SOMETHING?!?!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||per BOL
Use caution when using this overload of the Add method to specify integer
parameter values. Because this overload takes a value of type Object, you
must convert the integral value to an Object type when the value is zero, as
the following C# example demonstrates.
parameters.Add("@.pname", Convert.ToInt32(0));
"Mike Labosh" wrote:

> CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle (
> @.description NVARCHAR(70),
> @.jobTitleKey INT
> ) AS
> UPDATE msl_JobTitle
> SET Description = @.description
> WHERE JobTitleKey = @.jobTitleKey
> GO
> Then I have some VB.NET code that runs it:
> Private Sub fixJobTitles()
> Dim cnstr As String =
> ConfigurationSettings.AppSettings("connectionString")
> Dim cn As New SqlConnection(cnstr)
> Dim cmGet As New SqlCommand("SELECT * FROM dbo.msl_JobTitle", cn)
> Dim cmSave As New SqlCommand("dbo.msl_UpdateMSLJobTitle", cn)
> Dim dt As New DataTable()
> cmGet.CommandType = CommandType.Text
> cmSave.CommandType = CommandType.StoredProcedure
> Dim da As New SqlDataAdapter(cmGet)
> Dim tx As New Transform()
> With cmSave
> .Parameters.Add("@.description", "")
> .Parameters.Add("@.jobTitleKey", 0) ' <-- WTF?!?
> End With
> da.UpdateCommand = cmSave
> Try
> da.Fill(dt)
> Catch exc As Exception
> Console.WriteLine(exc.Message)
> End Try
> For Each dr As DataRow In dt.Rows
> dr("Description") =
> tx.GetLowAlphaFromHighAlpha(dr("Description").ToString())
> Next
> Try
> da.Update(dt)
> Catch exc As Exception
> 'EXCEPTION! Parameter '@.jobTitleKey' was expected but not supplied
> Console.WriteLine(exc.Message)
> End Try
> End Sub
> AM I RETARDED OR SOMETHING?!?!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
>|||Mike,
I think you should set the sourceColumn for that parameter.
Using Parameters with a DataAdapter
http://msdn.microsoft.com/library/d...ataadapters.asp
AMB
"Mike Labosh" wrote:

> CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle (
> @.description NVARCHAR(70),
> @.jobTitleKey INT
> ) AS
> UPDATE msl_JobTitle
> SET Description = @.description
> WHERE JobTitleKey = @.jobTitleKey
> GO
> Then I have some VB.NET code that runs it:
> Private Sub fixJobTitles()
> Dim cnstr As String =
> ConfigurationSettings.AppSettings("connectionString")
> Dim cn As New SqlConnection(cnstr)
> Dim cmGet As New SqlCommand("SELECT * FROM dbo.msl_JobTitle", cn)
> Dim cmSave As New SqlCommand("dbo.msl_UpdateMSLJobTitle", cn)
> Dim dt As New DataTable()
> cmGet.CommandType = CommandType.Text
> cmSave.CommandType = CommandType.StoredProcedure
> Dim da As New SqlDataAdapter(cmGet)
> Dim tx As New Transform()
> With cmSave
> .Parameters.Add("@.description", "")
> .Parameters.Add("@.jobTitleKey", 0) ' <-- WTF?!?
> End With
> da.UpdateCommand = cmSave
> Try
> da.Fill(dt)
> Catch exc As Exception
> Console.WriteLine(exc.Message)
> End Try
> For Each dr As DataRow In dt.Rows
> dr("Description") =
> tx.GetLowAlphaFromHighAlpha(dr("Description").ToString())
> Next
> Try
> da.Update(dt)
> Catch exc As Exception
> 'EXCEPTION! Parameter '@.jobTitleKey' was expected but not supplied
> Console.WriteLine(exc.Message)
> End Try
> End Sub
> AM I RETARDED OR SOMETHING?!?!
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>
>|||You should not half to do any of that. What you have posted as code i used
daily. With out mapping the source column.
There is 1 thing that I have noticed with out mapping the source is that
your parameters order in your stored procedure must match your parameters
order in your code.
should be as so
dim cn as new sqlclient.sqlconnection("Provider string")
dim cm as new sqlclient.sqlcommand("sqltext or stored proc",cn)
cm.commandtype = commandtype.storedprocedure
cm.parameters.add("@.Parmname","parmvalue")<-- in order here
cn.open
cm.executenonquery
cn.close
that will work every time. I have never had a problem with not mapping the
source.
post your database class.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C14455AC-40C6-417A-A332-7D16FEC98D91@.microsoft.com...
> Mike,
> I think you should set the sourceColumn for that parameter.
> Using Parameters with a DataAdapter
> http://msdn.microsoft.com/library/d...ataadapters.asp
>
> AMB
> "Mike Labosh" wrote:
>|||Chris,
I do not know if you got the problem here. The code you posted has nothing
to do with the OP problem. He is using this command for the
SqlDataAdapter.UpdateCommand.

> cm.parameters.add("@.Parmname","parmvalue")<-- in order here
That is not necessary because ado.net call the sp using named parameters by
default, and not by position as ado used to do it (that is the reason why ad
o
command object has a property NamedParameters). What should match is the nam
e
of the command parameter with the name of the sp parameter. See "Using
Parameters with a SqlCommand" in the following link.
Using Stored Procedures with a Command
http://msdn.microsoft.com/library/d...withcommand.asp
AMB
"Chris" wrote:

> You should not half to do any of that. What you have posted as code i used
> daily. With out mapping the source column.
> There is 1 thing that I have noticed with out mapping the source is that
> your parameters order in your stored procedure must match your parameters
> order in your code.
>
> should be as so
> dim cn as new sqlclient.sqlconnection("Provider string")
> dim cm as new sqlclient.sqlcommand("sqltext or stored proc",cn)
> cm.commandtype = commandtype.storedprocedure
> cm.parameters.add("@.Parmname","parmvalue")<-- in order here
> cn.open
> cm.executenonquery
> cn.close
> that will work every time. I have never had a problem with not mapping the
> source.
> post your database class.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:C14455AC-40C6-417A-A332-7D16FEC98D91@.microsoft.com...
>
>|||you are correct.. Sorry about that miss post. I did not read it correct.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1F7A7D17-B6DE-4203-834A-19D7E3226F62@.microsoft.com...
> Chris,
> I do not know if you got the problem here. The code you posted has nothing
> to do with the OP problem. He is using this command for the
> SqlDataAdapter.UpdateCommand.
>
> That is not necessary because ado.net call the sp using named parameters
> by
> default, and not by position as ado used to do it (that is the reason why
> ado
> command object has a property NamedParameters). What should match is the
> name
> of the command parameter with the name of the sp parameter. See "Using
> Parameters with a SqlCommand" in the following link.
> Using Stored Procedures with a Command
> http://msdn.microsoft.com/library/d...withcommand.asp
>
> AMB
> "Chris" wrote:
>