Complex Statistics Query

Dec 4, 2007

I'm creating a site for a national league and am having difficulty querying for a particular type of statistic that I'm hoping an expert on here can help me with


My data structure is such that an Event is a league meeting (all games take place on the same day) which has Fixtures. These Fixtures have Fixture_Events (essentially someone scoring a goal, a timeout being called, a penalty being awarded). I also have a Teams table, a Players table, a TeamRoster table (all players registered to a team) and a FixtureAttendees table (players from team who played in game). I'm trying to return 2 statistics.


The first is "How many Shutouts has a goalie had?"
The rule for this in English, how many games has a player participated in, where they have played in goals, and the score has been x-0 in their favour.


The second is "How many game winning goals has a player scored?"
The rule for this is essentially, how many goals has a player scored where the game was previously tied (e.g. 2-2) and they have scored the last goal in the fixture (e.g. 3-2).


My tables



Code BlockCREATE TABLE [dbo].[Fixture_Events](
[FixtureEventID] [int] IDENTITY(1,1) NOT NULL,
[FixtureID] [int] NOT NULL,
[EventType] [nvarchar](50) NOT NULL,
[EventTime] [nchar](5) NOT NULL,
[TeamID] [int] NOT NULL,
[Player1] [int] NOT NULL,
[Player2] [int] NULL,
[EventCode] [nvarchar](50) NULL,
[PenaltyMinutes] [int] NULL,
CONSTRAINT [PK_Fixture_Events] PRIMARY KEY CLUSTERED
(
[FixtureEventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]







Code BlockCREATE TABLE [dbo].[Events_Fixtures](
[FixtureID] [int] IDENTITY(1,1) NOT NULL,
[EventID] [int] NOT NULL,
[LocationID] [int] NOT NULL,
[FixtureDate] [smalldatetime] NOT NULL,
[HomeTeam] [int] NOT NULL,
[AwayTeam] [int] NOT NULL,
CONSTRAINT [PK_Seasons_Fixtures] PRIMARY KEY CLUSTERED
(
[FixtureID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]





Code BlockCREATE TABLE [dbo].[Fixture_Attendees](
[FixtureID] [int] NOT NULL,
[PlayerID] [int] NOT NULL,
[Goalkeeper] [bit] NOT NULL CONSTRAINT [DF_Fixture_Attendees_Goalkeeper] DEFAULT ((0)),
CONSTRAINT [PK_Fixture_Attendees] PRIMARY KEY CLUSTERED
(
[FixtureID] ASC,
[PlayerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]







Code BlockCREATE TABLE [dbo].[Fixture_Attendees](
[FixtureID] [int] NOT NULL,
[PlayerID] [int] NOT NULL,
[Goalkeeper] [bit] NOT NULL CONSTRAINT [DF_Fixture_Attendees_Goalkeeper] DEFAULT ((0)),
CONSTRAINT [PK_Fixture_Attendees] PRIMARY KEY CLUSTERED
(
[FixtureID] ASC,
[PlayerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]





It's a complicated database as I'm tried to model it as accurately as I can. It may actually be easier if I made a backup of the database available rather than trying to post code.


If anybody can help me and required further info, please let me know.


Thanks in advance.

View 3 Replies


ADVERTISEMENT

Auto Created Statistics And Missing Statistics

Jul 20, 2005

Hello group.I have an issue, which has bothered me for a while now:I'm wondering why the column statistics, which SQL Server wants me tocreate, if I turn off auto-created statistics, are so important to theoptimizer?Example: from Northwind (with auto create stats off), I do the following:SELECT * FROM Customers WHERE Country = 'Sweden'My query plan show a clustered index scan, which is expected - no indexexists for Country. BUT, the query plan also shows, that the optimizer ismissing a statistic on Country, which tells me, that the optimizer wouldbenefit from knowing this.I cannot see why? (and I've been trying for a while now).If I create the missing statistics, nothing happens in the query plan (andwhy should it?). I could understand it, if the optimizer suggested an indexon Country - this would make sense, but if creating the missing index, queryanalyzer creates the statistics with an empty index, which seems to me to beless than usable.I've been thinking long and hard about this, but haven't been able to reacha conclusion :) It has some relevance to my work, because allowing theoptimizer to create missing statistics limits my options for designingindexes (e.g. covering) for some rather wide tables, so I'm thinking why notturn it off altogether. But I would like to know the consequences - hopesomebody has already delved into this, and knows a good explanation.RgdsJesper

View 5 Replies View Related

Unit Of Time-statistics In Client Statistics

Aug 1, 2006

What is the unit of the numbers you get in the Time Statistics-part when running a query in Microsoft SQL Server Management Studio with Client Statistics turned on?

Currently I get mostly 0īs, but if I try and *** up a query on purpose I can get
it up to around 30... Is it milliseconds or som made up number based on clockcycles or... ?

I would also like to know if itīs possible to change the precision.


- Nikolaj

View 3 Replies View Related

Query Plans && Statistics

Jan 23, 2004

Gurus,

I'm trying to get an application finished that works like Query Analizer in
terms of returning query plans and statistics.

Problem the co-author is having:

>In using ADO to connect to SQL Server, I'm trying to retrieve multiple
>datasets AND statistics that are usually returned via the OnInfoMessage
>event. For those that are familiar with SQL Server, I need the results
>returned by the SET STATISTICS IO ON and SET STATISTICS PROFILE ON options.
>Anyone had any luck doing this before?

Can anyone shed any light on this please?

Thanks.

BTW if anyone wants to take a look at the tool so far - to see what I'm
delving into:
http://81.130.213.94/myforum/forum_posts.asp?TID=78&PN=1


Much Appreciated!!

View 3 Replies View Related

SQL 2012 :: Interpreting Query Statistics

Jun 5, 2014

I'm designing a new database which will be the back-end to a heavily-used web-based application (all these terms are relative - I guess the use won't be that heavy in the grand scheme of things, I'm only talking 100 users or so at the very most). Data from the old application database will be migrated to this one, and the old database is around 7GB in size after 5 years of use.

I have two different ways of linking some tables in mind, one which is slightly more complex than the other but which potentially has benefits over the simpler method. However, I'm concerned that I might be 'over-cooking' the design, and that performance would suffer as a result, so I've tried creating the two different versions of the database (the part of it I'm concerned with, anyway), one for each of the solutions I've got in mind, migrated the data into the relevant tables and carried out some queries on the data to collect some statistics.

The problem is that, whilst I can see that the more complex method is more expensive, as expected, I don't really understand if the difference is significant. Since I don't know what the numbers in the Client Statistics window actually mean (there are no units! I'm guessing times are in milliseconds?), or how much of real-world impact the difference will have, I'm finding it hard to interpret my statistics and come to a decision.

Querying the entirety of my tables to return ~20,000 records listing one column from each of the main tables I'm playing with, the simpler method had a Total Execution Time of 199, and the more complex a Total Execution Time of 272. Is that the statistic I should be most concerned with? Is that a difference I should be concerned about? Is the difference likely to be magnified when the database is much larger and in use, such that a difference of 73 milliseconds in this test scenario could end up being as much as a whole second in production, for example?

View 1 Replies View Related

Wht Statistics Are Updated After SELECT Query?

Jul 31, 2006

Hi

My understanding is that whenever any INSERT, DELETE, or UPDATE statements execute that impacts significant amount of rows in a table, its statistics must be automatically updated by SQL Server. But it was surprise to see in the profiler that after INSERT no update statistics event happen. But the moment a SELECT is executed on the table the event 'Auto Stats' is shown up. What is the reason behind this? Why the Auto Stats not happening immediately after the INSERT statement?

Following code can be used to illustrate this (in the profiler select the Auto Stats event under Performance):

IF(SELECT OBJECT_ID('t1')) IS NOT NULL
DROP TABLE t1
GO
CREATE TABLE t1(c1 INT, c2 INT IDENTITY)
INSERT INTO t1 (c1) VALUES(1)
INSERT INTO t1 (c1) VALUES(2)
INSERT INTO t1 (c1) VALUES(3)
CREATE NONCLUSTERED INDEX i1 ON t1(c1)

GO

--Now add 10,000 rows to update so that statistics are updated. Look into the profiler:

SET NOCOUNT ON
GO

DECLARE @n INT
SET @n = 1
WHILE @n <= 10000
BEGIN
INSERT INTO t1 (c1) VALUES(2)
SET @n = @n + 1
END

SET NOCOUNT OFF
GO


--Next runt he following statement. The profiler will now show 'Auto Stats' event

SELECT * FROM t1 WHERE c1 = 2



Regards

Sanjay Singh

View 3 Replies View Related

Index Distribution Statistics And Show Query Plan

Jan 15, 1999

I have an index that shows distribution statistics of 98.20%, which is very poor. I set show query plan and show statis I/O on. This table has 1113675 rows of data.

*************
select orderID, custId, intertcsi from tblorders
where intertcsi = '2815'

STEP 1
The type of query is SELECT
FROM TABLE
tblorders
Nested iteration
Index : indxInterTCSI
orderID custId intertcsi
----------- ----------- ---------
1015245 1011313 2815
2556392 2556392 2815
....


Table: tblOrders scan count 1, logical reads: 104, physical reads: 58, read ahead reads: 0
***************
Then I use the same select statement to force a table scan:

select orderID, custId, intertcsi from tblorders (index=0)
where intertcsi = '2815'

STEP 1
The type of query is SELECT
FROM TABLE
tblorders
Nested iteration
Table Scan
orderID custId intertcsi
----------- ----------- ---------
60472 61084 2815
102184 102333 2815
...
Table: tblOrders scan count 1, logical reads: 110795, physical reads: 6891, read ahead reads: 103980

When the index is not provided, the logical reads and physical reads increased dramatically. Does this tell me that I should keep that index though it is a poor selection? Is that because a huge table like this make the optimizer use the index. The query without using index takes longer time to run.
Any idea or comment would be very appreciated.

View 4 Replies View Related

I Need Some Help With A Complex Query

Jan 6, 2007

I've written a lot of queries in the past, but I'm having a lot of trouble with this one.

View 4 Replies View Related

Very Complex Query

Mar 19, 2007

I'm sure there is a way of cracking this, but I can't think of a good solution. Right now I am not happy with the solutions I come up with, one of which takes 4 minutes to run on SQL Server
The scenario: User is presented with search page where one or more search terms can be entered/selected. There are no required parameters. It can be any or all of the possibilities presented. Below is a model of the search parameters presented.
The user will either select to show more options under Profile ABC, or go down to Profile STU or Profile XYZ to show more options, or even select all Profiles and then select from Type 1 and either a. or. b. or. c. or ALL of the above.
I cannot predict what a user will make part of the search query so I have to have a stored procedure ready which can handle any or all of the parameters  a user may select.
Am I biting off more than I can chew (it seems so)? Or is there an elegant way of handling the unknown combination of search parameters that a user might throw into my sql query?
I'm running this under ASP 1.0 and SQL Server 2000.
 
[check to show the options below] Profile ABC
[check to shore more options] Type 1




A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to show more options] Type 2





A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to show more options] Type 3





A. Contains fields for entering another data string and selecting from drop-down boxes




B. ditto
C. ditto
D. ditto
[check to select more options] Type 4





A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to show more options] Profile XYZ (as above)
[check to select more options] Profile STU (as above)

View 6 Replies View Related

Complex Query

Oct 30, 2007

HI.
I have 3 tables
1- std with : stdID , programID.
2- Programs with :ProgramID , Cost
3 - Movements with : stdID , balance.
the first table contain the stdID and ProgramID , some times the std hasn't programID that mean he hasn't programID. then we return null.
if the std has programID there is to cases.
the first one he have a movement on his balance then we get the biggest balance for the std.
the second case he hasn't any moventen then we get his balance from Programs  table by the ProgramID .
 
I need sql server function that return table like this
stdID , Balance
 that means every std with his Balance.
Regards.

View 11 Replies View Related

Complex Query

May 13, 2008

This is too complex anyone know how to make it less complex.
I am trying to get all the selected fields from contacts into a datagrid where the other fields contain a string in textbox1.
This works
SELECT [company], [contactname], [emailaddress], [secondemailaddress], [phonenumber], [webpage] FROM [contacts] WHERE (([AB] LIKE '%' + ? + '%') AND ([AL] LIKE '%' + ? + '%'))
When i add all the rest of the fields it says its too complex. Please Help
 
SELECT [company], [contactname], [emailaddress], [secondemailaddress], [phonenumber], [webpage] FROM [contacts] WHERE (([AB] LIKE '%' + ? + '%') AND ([AL] LIKE '%' + ? + '%') AND ([B] LIKE '%' + ? + '%') AND ([BB] LIKE '%' + ? + '%') AND ([BD] LIKE '%' + ? + '%') AND ([BA] LIKE '%' + ? + '%') AND ([BH] LIKE '%' + ? + '%') AND ([BL] LIKE '%' + ? + '%') AND ([BN] LIKE '%' + ? + '%') AND ([BR] LIKE '%' + ? + '%') AND ([BS] LIKE '%' + ? + '%') AND ([BT] LIKE '%' + ? + '%') AND ([CA] LIKE '%' + ? + '%') AND ([CB] LIKE '%' + ? + '%') AND ([CF] LIKE '%' + ? + '%') AND ([CH] LIKE '%' + ? + '%') AND ([CM] LIKE '%' + ? + '%') AND ( LIKE '%' + ? + '%') AND ([CR] LIKE '%' + ? + '%') AND ([CT] LIKE '%' + ? + '%') AND ([CV] LIKE '%' + ? + '%') AND ([CW] LIKE '%' + ? + '%') AND ([DA] LIKE '%' + ? + '%') AND ([DD] LIKE '%' + ? + '%') AND ([DE] LIKE '%' + ? + '%') AND ([DG] LIKE '%' + ? + '%') AND ([DH] LIKE '%' + ? + '%') AND ([DL] LIKE '%' + ? + '%') AND ([DN] LIKE '%' + ? + '%') AND ([DT] LIKE '%' + ? + '%') AND ([DY] LIKE '%' + ? + '%') AND ([E] LIKE '%' + ? + '%') AND ([EC] LIKE '%' + ? + '%') AND ([EH] LIKE '%' + ? + '%') AND ([EN] LIKE '%' + ? + '%') AND ([EX] LIKE '%' + ? + '%') AND ([FK] LIKE '%' + ? + '%') AND ([FY] LIKE '%' + ? + '%') AND ([G] LIKE '%' + ? + '%') AND ([GL] LIKE '%' + ? + '%') AND ([GU] LIKE '%' + ? + '%') AND ([GY] LIKE '%' + ? + '%') AND ([HA] LIKE '%' + ? + '%') AND ([HD] LIKE '%' + ? + '%') AND ([HG] LIKE '%' + ? + '%') AND ([HP] LIKE '%' + ? + '%') AND ( LIKE '%' + ? + '%') AND ([HU] LIKE '%' + ? + '%') AND ([HX] LIKE '%' + ? + '%') AND ([IM] LIKE '%' + ? + '%') AND ([IP] LIKE '%' + ? + '%') AND ([IV] LIKE '%' + ? + '%') AND ([JE] LIKE '%' + ? + '%') AND ([KA] LIKE '%' + ? + '%') AND ([KT] LIKE '%' + ? + '%') AND ([KW] LIKE '%' + ? + '%') AND ([KY] LIKE '%' + ? + '%') AND ([L] LIKE '%' + ? + '%') AND ([LA] LIKE '%' + ? + '%') AND ([LD] LIKE '%' + ? + '%') AND ([LE] LIKE '%' + ? + '%') AND ([LL] LIKE '%' + ? + '%') AND ([LN] LIKE '%' + ? + '%') AND ([LS] LIKE '%' + ? + '%') AND ([LU] LIKE '%' + ? + '%') AND ([M] LIKE '%' + ? + '%') AND ([ME] LIKE '%' + ? + '%') AND ([MK] LIKE '%' + ? + '%') AND ([ML] LIKE '%' + ? + '%') AND ([N] LIKE '%' + ? + '%') AND ([NE] LIKE '%' + ? + '%') AND ([NG] LIKE '%' + ? + '%') AND ([NN] LIKE '%' + ? + '%') AND ([NP] LIKE '%' + ? + '%') AND ([NR] LIKE '%' + ? + '%') AND ([NW] LIKE '%' + ? + '%') AND ([OL] LIKE '%' + ? + '%') AND ([OX] LIKE '%' + ? + '%') AND ([PA] LIKE '%' + ? + '%') AND ([PE] LIKE '%' + ? + '%') AND ([PH] LIKE '%' + ? + '%') AND ([PL] LIKE '%' + ? + '%') AND ([PO] LIKE '%' + ? + '%') AND ([PR] LIKE '%' + ? + '%') AND ([RG] LIKE '%' + ? + '%') AND ([RH] LIKE '%' + ? + '%') AND ([RM] LIKE '%' + ? + '%') AND ([S] LIKE '%' + ? + '%') AND ([SA] LIKE '%' + ? + '%') AND ([SE] LIKE '%' + ? + '%') AND ([SG] LIKE '%' + ? + '%') AND ([SK] LIKE '%' + ? + '%') AND ([SL] LIKE '%' + ? + '%') AND ([SM] LIKE '%' + ? + '%') AND ([SN] LIKE '%' + ? + '%') AND ([SO] LIKE '%' + ? + '%') AND ([SP] LIKE '%' + ? + '%') AND ([SR] LIKE '%' + ? + '%') AND ([SS] LIKE '%' + ? + '%') AND ([ST] LIKE '%' + ? + '%') AND ([SW] LIKE '%' + ? + '%') AND ([SY] LIKE '%' + ? + '%') AND ([TA] LIKE '%' + ? + '%') AND ([TF] LIKE '%' + ? + '%') AND ([TN] LIKE '%' + ? + '%') AND ([TQ] LIKE '%' + ? + '%') AND ([TR] LIKE '%' + ? + '%') AND ([TS] LIKE '%' + ? + '%') AND ([TW] LIKE '%' + ? + '%') AND ([UB] LIKE '%' + ? + '%') AND ([W] LIKE '%' + ? + '%') AND ([WA] LIKE '%' + ? + '%') AND ([WC] LIKE '%' + ? + '%') AND ([WD] LIKE '%' + ? + '%') AND ([WN] LIKE '%' + ? + '%') AND ([WR] LIKE '%' + ? + '%') AND ([WS] LIKE '%' + ? + '%') AND ([WV] LIKE '%' + ? + '%') AND ([YO] LIKE '%' + ? + '%'))
 

View 10 Replies View Related

A Complex Query

May 20, 2008

hi how are you please help me in my problem which i can't make it.
 Now, i have a project in ASP.NET and SQL Server 2005. let's call the project an image gallery, in my project i have a table named "Category" in which all the categories are in this table. also while adding a new category a new table will be created automatically with the name of that category.
now, what i want is that to build a query that reads the contents of the tables that the tables name are the names of the each record in the "Category" table.
 is that possible ?
please if any one help can me in my problem.

View 4 Replies View Related

A Complex Query

Feb 10, 2004

I have the following SQL 2000 database table:
NEWS (IDNews, Country, PublishDate, Title)

I have to get a dataset containing only one record for each country, having most recent publish date.
Any suggestions? Thanks.

View 3 Replies View Related

Complex Query?

Feb 8, 2005

I have 2 tables, say table1, and table2. There is a DocID (primary key) in table1. In table2, DocID is the foriegn key. There can be more than 1 DocID.

this is the table structure (sample)

Table 1:
DocID DocName OtherID etc
1 test 2
2 test2 3

Table2:
TblID DocID OtherID
1 1 10
2 1 13
3 1 25

how do I join these two tables, such that I get all the otherID's for each DocID.
ie.,
DocID OtherID
1 2 and 10 and 13 and 25
2 3

i am writing this query to display search results on a search page (with keyword search) and so, if I display the result in more than one row, then the user might think that there is more than document...whereas the case is that there is only one document with more than one other ID's.

is there any way I can do this? display...more than 1otherID in the same row for the same DociD?
Currently, I am using a left outer join of table1 and table2.

An suggestions on how to do this?

View 6 Replies View Related

COMPLEX Sql Query PLEASE HELP!!

Oct 1, 2005

I cant get "order by" to work in this sql query..I use this query:
"SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn"and I want to add this some where to get 12 random records: "ORDER BY NewID()"I tried this: "SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn ORDER BY NewID()"" but get the error:"ORDER BY items must appear in the select list if SELECT DISTINCT is specified"I canÂīt figure out how I should write the query..Somebody have any ideas??/Radiwoi

View 2 Replies View Related

Complex Query

Apr 11, 2003

Hi,

I need a way to retrieve records from a table with a 30 min interval between the records.

For e.g., Lets say I have the following data in a table :-

userid hitdt
1 4/1/2003 10:00 AM
1 4/1/2003 10:15 AM
1 4/1/2003 10:31 AM
1 4/1/2003 11:10 AM
1 4/1/2003 11:30 AM
1 4/1/2003 11:41 AM

I need a query which would return me the following recordset :-

userId hitId
1 4/1/2003 10:00 AM
1 4/1/2003 10:31 AM
1 4/1/2003 11:10 AM
1 4/1/2003 11:41 AM

Is there a way to do this without using a cursor ?

Thanks

View 7 Replies View Related

Complex Query

Jun 10, 2005

Code:

ID GroupID User
1 101 Tom
2 101 Mark
3 101 Clark
4 102 Tom
5 102 Mark
6 103 Tom
7 103 Clark
8 104 Tom
9 104 Clark
10 105 Tom
11 105 Bred



the users of Group 101 are Tom,Mark,Clark
the users of Group 102 are Tom,Mark
the users of Group 103 are Tom,Clark
the users of Group 104 are Tom,Clark
the users of Group 105 are Tom,Bred

I want to show Tom that

Both You and Clark are together in 3 groups
Both You and Mark are together in 2 groups
Both You and Bred are together in 1 group

View 5 Replies View Related

Complex Query Help

Jan 27, 2007

I might have to redesign the tables for this, but I'll ask anyway. I have a table with the following fields:

Email - VarChar
Seminar - Int
PeckingOrder - Int.

As I add addresses to the table, each one has a Seminar, and then each Seminar has a Pecking Order Value. If an email address shows up for more than one seminar, it can have multiple records. IE:

email1 - 1523 - 424
email1 - 1526 - 124
email1 - 1524 - 235
email2 - 1526 - 124
email2 - 1524 - 235

for address, seminar, and pecking order would be sample entries into the table. Give or Take 1000 records in the table at any given time. What I want to pull out is:

Distinct Email Addresses
For each Email Address - The Max(PeckingOrder)
And the Seminar that's associated with Max(PeckingOrder)

For the sample data set above, I'd want to see these two records returned by the query:

email1 - 1523 - 424
email2 - 1524 - 235

I can't seem to get the Having / Where clause right to pull those two records properly. Anyone have any suggestions?

Thanks,
--Daniel

Edited - Didn't realize the BB removed email addresses.

View 1 Replies View Related

Need Help With A Somewhat Complex Query

Oct 10, 2007

Code:


SELECT
goto_last_name as 'Manager',
advisor_name,
advisor_ao_number as 'AO',
COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) as '#Advisors',
SUM(Weekly_TOS_GDC) as 'TOS GDC'
FROM weekly_condensed_tb
WHERE advisor_platform_number = 1
AND report_date BETWEEN CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -28
AND CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME)
GROUP BY goto_last_name,advisor_ao_number,advisor_name
HAVING COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) > 1



In the query above i select a range from the date - 28 days and do a sum on the TOS GDC column. What i am trying to do is have another query where the range will be - 56 and maybe - 86 so i get the SUMS for that as well. I need to display this in SQL Reporting Services and i can only have one DataSet returned otherwise i will not be able to bind it to one table.

How can i go about this so i return one set of data for 3 different date ranges.

Thanks

View 2 Replies View Related

Complex Query In Sp

Jun 2, 2004

I'm struggling with a syntax error in my sp. I'm hoping someone can give it a fresh look and straighten me out. Thanks.
Background:
1. The innermost query (using the dynamic statements) will run fine on its own.
2. I can wrap that in another query (with the innermost as a DERIVEDTBL) and it will run fine UNLESS I apply the GROUP BY. Then I get syntax errors.
3. Also, the entire query runs fine as a view (with hard coded vals for column and value).
4. I don't know what kind of formatting this message will apply, so just know that I've checked my line truncations and they all include a trailing '+. Also, I know that Occured is misspelled. That is the correct object name. Not all our DB admins are lit majors.

Here's the code:

CREATE PROCEDURE [dbo].[sp_ninetydayavgtotals]
@column nvarchar(100),
@value smallint
AS
DECLARE @SelectString nvarchar(500)

SET @SelectString = 'SELECT AVG(total_attacks) AS avg_attacks ' +
'FROM (SELECT TOP 100 PERCENT thedate AS [day], COUNT(total) AS total_attacks, SUM(enemyKIA) AS EKIA, SUM(enemyWIA) AS EWIA, SUM(coalitionKIA) AS CKIA, SUM(coalitionWIA) AS CWIA ' +
'FROM SELECT(TOP 100 PERCENT CONVERT(nvarchar, dateOccured, 11) AS thedate, txtCategory AS total, enemyKIA, enemyWIA, coalitionKIA, coalitionWIA ' +
'FROM dbo.v_maindata ' +
'WHERE (CONVERT(nvarchar, dateOccured, 11) > CONVERT(nvarchar, (getdate()-90), 11)) AND (CONVERT(nvarchar, dateOccured, 11) != CONVERT(nvarchar, getdate(), 11)) AND ' + @column + ' = @value ' +
'ORDER BY dateOccured DESC) DERIVEDTBL ' +
'GROUP BY thedate ' +
'ORDER BY thedate DESC) DERIVEDTBL'

EXEC sp_executesql @SelectString, N'@column nvarchar(100), @value smallint', @column, @value
GO

-------------------------------------------------------------------------
Owen Eustice
MNC-I Webmaster
Victory Base South

View 5 Replies View Related

Need Help On This Complex Query

Aug 11, 2004

Result of the select from dbUsers is

db_name User Group
DB1 U1 db_datareader
DB1 U1 db_datawriter
DB1 U1 db_ddladmin

Db2 U1 db_dataReader
Db2 U1 db_datawriter

Db2 U2 db_dataReader
Db2 U2 db_datawriter

I somehow want it in a way so that the Db_name and USer_name are not repeated .

IS it possible ?

thanks

View 3 Replies View Related

Complex Query - Need Help

Oct 29, 2004

Hi,

I have a sql table with corpcode, EmpName, rate, reportdate and Amount fields. I need to write a query that can return corpcode, EmpName, rate and sum of Amount for January, Sum of Amount for Feburary, Sum of Amount for March, Sum of Amount for April, ..........., Sum of Amount for December, Total for All months, Average for all months.

I tried few option, it didn't work for me, Is it possible to do? Have some tried like this earlier?

Thanks!

View 12 Replies View Related

Complex Query

Feb 23, 2005

Hi,
I want to create a query with which I must compare the production demands with the production results. The production demands can be get by the join of two tables. The production results can be get from an aggregate of 4 tables. The connection of these two objects rely on two fields that exist in both two objects. In order to show all the production demands I must left join the two fields from the demands object to the two fields exist in the aggegate production object. In MsAccess the only way to do it is to create 2 queries one for the demands and one aggregate for the production and in a third query create two left joins from the demands query to the production query and get the right results. How can I do it with MSSQL Server with a query??

Best Regards,
Manolis

View 6 Replies View Related

DB Query (complex Maybe)

Mar 6, 2007

I have database like below.

BLOG
--POSTER_ID
--POSTED_BY (a FK refer to USERID)
--UPDATED_BY (a FK refer to USERID)


USER
--USER_ID
--NAME

I want to retrieve the info for all posts that posted and updated by different users, also the query returns both user names for every posted and updated action.

I can write two queries to do that

select B.POSTER_ID, U.NAME from Blog B inner join USER U ON B.POSTED_BY = U.USER_ID

select B.POSTER_ID, U.NAME from Blog B inner join USER U ON B.UPDATED_BY = U.USER_ID

Then write some code to combine the result for the posted and updated user name. But this is not efficient.

I am wondering how can I write one query to archeive that?

Thanks.

View 9 Replies View Related

Complex Query

May 22, 2007

Hi Guys Hope you can help with this - it's certainly got me scratching my head. I'm Querying a Call Centre Database I have a Table of Call Data with a Start Time & End Time of the Call. (CallID,Started,Ended)10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.0001003855355 2007-04-01 00:01:24.000 2007-04-01 00:01:24.00010942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.00010942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.00010942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.00010942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.00010942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.00010942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000I'm trying to produce Stats that tell me how many Calls were live in any one given minute. Ultimately I will be producing a Line Graph of No of Calls Connected grouped by Minute. I've gone as far as creating a temp table with every minute in a month with the following query maybe to join to but not sure if this will help me. WHILE(@cnt <= 43200)BEGINSELECT @MaxDate =DATEADD(mi,1,MAX(DTBlock))FROM AprilMinutesINSERTINTO AprilMinutes VALUES(@MaxDate,NULL)SET @cnt = @Cnt +1 ENDWhich produces a nifty little table with 01/04/2007 00:09:0001/04/2007 00:10:0001/04/2007 00:11:0001/04/2007 00:12:0001/04/2007 00:13:0001/04/2007 00:14:0001/04/2007 00:15:0001/04/2007 00:16:00 If one individual Call Spans 2 minutes I'll count it as 1 in the first minute & 1 in the second minute. Overall I'm trying analyze how many telephone lines we need Any Help much, much appreciated Thanks GW

View 14 Replies View Related

Complex SQL Query

Mar 31, 2004

Hi all,

I am developing an application using SQL Server as Back-end. I am facing a problem in creating a SQL Query. The details are as follows:

There are three tables in the Database, Data Type of all Columns is Numeric in all three tables:

1. T1

(column names and sample data)

en
==

1
2
3


2) T2


(column names and sample data)

en gn
== ==

1 10
1 11
2 10
2 12
2 13


3) T3

(column names and sample data)

en pn
== ==

1 20
1 21
1 22
2 20


Now I have to create a SQL Query, whereby I can get the following result:


en gn pn
== == ==
1 10 20
1 11 21
1 NULL 22
2 10 20
2 12 NULL
2 13 NULL


I have tried various combination of Joins, but unable to get the desired result as the tables have many-to-many relationships, therefore I get many duplicate rows in the result. UNION will not solve the problem, as that will add the additional rows for the third table. Although I can achieve this by writing few lines of code, but I have to create a SQL Query for getting this result. Kindly tell me the way for creating the required Query for this. Many Thanks for your help.

View 3 Replies View Related

A Complex Query

May 20, 2008

Now, i have a project in ASP.NET and SQL Server 2005. let's call the project an image gallery, in my project i have a table named "Category" in which all the categories are in this table. also while adding a new category a new table will be created automatically with the name of that category.

now, what i want is that to build a query that reads the contents of the tables that the tables name are the names of the each record in the "Category" table.

Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com

View 3 Replies View Related

Complex Query

Apr 10, 2006

Table Name: Story
Table Columns: user_id, date, name, yr_left, city_origin, country_origin, yr_arrival, city_arrival, state_arrival, address, city, state, zip, AorV (audio or video), filename, approved

I need to create two queries. One based on date in descending order, and one based on country_origin in alphabetical order. For both of these queries, the approved field has to = Y for yes, and when I return results I need it to return everything in the table EXCEPT for the name.

I'm a beginner at SQL queries so I was hoping someone could shed some light and help me??? Thanks a bunch!

View 1 Replies View Related

Complex Query (for Me)

Jan 24, 2007

Below are the two tables (with sample records) that I am trying to include into my query:

[Journal] (ID, EntryDate, Description)
1, 1/24/2007, Fred Meyer

[Journal_Splits] (ID, JournalID, AccountID, Amount, Type)
1, 1, 11, 19.95, 2
2, 1, 8, 24.99, 2
3, 1, 3, 5.49, 2

The AccountID refers to another account table that holds income and expense categories such as Groceries, Leisure, Clothing, Household, etc...

I want to create a query that will return the following:

[JE Query]
ID, EntryDate, Comment, Income, Expense


ID, EntryDate and Comment are all easily returned from the [Journal] table. The problem I am having is with the Income and Expense fields. For the Income field, I want to SUM the Journal_Split.Amount fields where Journal_Splts.JEID = Journal.ID AND where Journal_Splits.Type = 1 (1 being the code for Income). The Expense field is just the same, except the Journal_Splits.Type will be equal to 2. When the query is done there should only be as many rows as there are in the [Journal] table, for the [Journal_Splits] table can contain up to 3 records for each [Journal] record. [Journal_Splits] just holds the individual amounts split out to different income/expense categories for a single transaction.

This to me has a lot of conditions that I can't even seem to build with access's query builder (yes I am using access for now.. will change over to sql at a later time).

Anyone have a good idea how to do this? Or have a better idea for structuring the database to make is easier to query? Right now I have it working, but instead of an Amount field, I have 1 Income and 1 Expense field, but since a transaction can only be either income or expense, one field will always be 0, which seems like a waste of space.

That may be a bit confusing so let me know if I can clarify. Much appreciated.

View 1 Replies View Related

Very Complex Query

May 14, 2007

I have a table like this:

Customers:

CustomerID
CustomerName
person1
Person2

Persons:

PersonID
PersonName
PersonAdress

No can person2 be empty !!!! But person1 is almost everytime filled.
I like to get a query with this fields:

CustomerName Persons.PersonName (of Person1) Persons.PersonName (of Person2, only if this field is filled)

Is that possible, and how?

View 4 Replies View Related

Is It A Complex Sql Query?

Jan 15, 2006

HelloI am using stored procedure with sql 2005 (with Visual studio 2005)I have two tables .. TABLE1 And TABLE2[color=blue]>From TABLE1 i need to retrive the OrderID's of the 4 most top rows. so[/color]i did:SELECT TOP 4 OrderID FROM TABLE1 order by OrderID descNow what i am trying to do is take the 4 row results (4 OrderID's) igot fromTABLE1 and check if the 4 rows (4 OrderID's) exist in TABLE2 for aspecificuserID i get by INPUT varible (@UserId)..What i want to return is only which OrderID'S existed in TABLE2 for thespecific user.If only 2 OrderID'S i retrived from TABLE1 exist in TABLE2 i willreturn only 2 OrderID's (so i can do my output in visual studio 2005using the reader())I would appreciate this if anyone knows how to do this sql query , isit possible to do this in 1 query? i want to put it in a storedprocedure.

View 15 Replies View Related

Complex Query

Apr 2, 2006

Hi everybody,I have a table storing the quiz data of the students like this:Date | Time | Name | GradeI can show the data of specific student by the query below:SELECT *FROM tbl_quizWHERE name ='xxx'Suppose I have 10 records for each student, and I wanna display thedata like this:Previous quiz date | Previous quiz time | Date | Time | Name | GradeHow can I make the "Previous quiz date" and "Previous quiz time"? Howcan I query it? Please adviceFRANK

View 5 Replies View Related

Complex Query

May 31, 2006

I am converting a date to a variable, then stripping out unwantedhyphens, making sure there is a 0 in the month/day, and chomping theyear to the last 2 digits. The end result is a 6 digit integer that Ican use in my SQL.05-31-2006 changes to 053106Everything works except chomping the year to the last 2 digits. Substrwould normally work, but my results are ommiting the substringcompletely.BEGINRIGHT('00' + CAST(DATEPART(mm, @date) as varchar(2)), 2 ) +RIGHT('00' + CAST(DATEPART(dd, @date) as varchar(2)), 2 ) +SUBSTRING(CAST(DATEPART(yyyy, @date) as varchar(2)), -2, 2)Any clues? Corrections?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved