Hi all,
I have got a query that returns values based on a date range and grouped by
week. I need to return a value of 0 if there are no entries for that week. At
the moment it just skips that week all together.
Any ideas?
Thanks
Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
left outer join if you're using multiple tables and a lack of entries for
that week table is eliminating the week row. But to give a specific answer,
we'd need more details.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
> Hi all,
> I have got a query that returns values based on a date range and grouped
> by
> week. I need to return a value of 0 if there are no entries for that week.
> At
> the moment it just skips that week all together.
> Any ideas?
> Thanks
|||Hi Paul,
My current query is as follows.
SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
Type_QRY].CountOfType) AS SumOfCountOfType
FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN [Counting
Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting Type_QRY].Branch,
[Counting Type_QRY].Type;
This works great counting my entries and putting then grouping by week.
Trouble is if there is no info for a week it skipps that week.
34 = 4
35 = 2
37 = 7
I need it to return
34 = 4
35 = 2
36 = 0
37 = 7
I am fairly new to this so please excuse the query if it is not great. Just
trying to grow my skills in the real world.
Thanks
"Paul Nielsen" wrote:
> Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
> left outer join if you're using multiple tables and a lack of entries for
> that week table is eliminating the week row. But to give a specific answer,
> we'd need more details.
> --
> -Paul Nielsen, SQL Server MVP
> SQL Server 2000 Bible, Wiley Press
>
> "Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
> news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
>
>
|||Andrew Jurgens wrote:
> Hi Paul,
> My current query is as follows.
> SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
> Type_QRY].CountOfType) AS SumOfCountOfType
> FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN
> [Counting Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
> GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type;
> This works great counting my entries and putting then grouping by
> week. Trouble is if there is no info for a week it skipps that week.
> 34 = 4
> 35 = 2
> 37 = 7
> I need it to return
> 34 = 4
> 35 = 2
> 36 = 0
> 37 = 7
> I am fairly new to this so please excuse the query if it is not
> great. Just trying to grow my skills in the real world.
> Thanks
Sounds like you need an OUTER JOIN. I don't know your data, so use an
outer join against the table that may not have a foreign key
relationship. If you wanted all Accounts from the accounts table even if
some of the accounts didn't have a comment in the comments table, you
would:
From Accounts Outer Join Comments on Accounts.id = Comments.id
David Gugick
Imceda Software
www.imceda.com
|||Thanks David,
My problem is that I am querying a date range but there may not be entries
for all dates within that range in the table. I still need to return all
dates even if there is no data. Hence my previous
This works great counting my entries and putting then grouping by[vbcol=seagreen]
I really appreciate your input. Have been stuck for a bit and need to get
out of this hole!
Thanks.
"David Gugick" wrote:
> Andrew Jurgens wrote:
>
> Sounds like you need an OUTER JOIN. I don't know your data, so use an
> outer join against the table that may not have a foreign key
> relationship. If you wanted all Accounts from the accounts table even if
> some of the accounts didn't have a comment in the comments table, you
> would:
> From Accounts Outer Join Comments on Accounts.id = Comments.id
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||On Thu, 14 Oct 2004 01:59:20 -0700, Andrew Jurgens wrote:
>Thanks David,
>My problem is that I am querying a date range but there may not be entries
>for all dates within that range in the table. I still need to return all
>dates even if there is no data. Hence my previous
Hi Andrew,
Looks like you need a calendar table.
See http://www.aspfaq.com/show.asp?id=2516.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label grouped. Show all posts
Showing posts with label grouped. Show all posts
Monday, March 26, 2012
Tuesday, March 20, 2012
Retrieving grouped data
Hi,
I have a table that contains a simple collection of rows, the rows have the
following fields...
URN, Year, Period, Cost, Value
As an example, the data is as follows...
2005|08|089.32|123.45
2005|10|056.68|045.68
2004|10|156.32|068.23
2005|11|123.56|548.12
2005|11|078.23|569.12
2005|7|078.25|875.657
2004|7|009.50|320.512
What I'd like to achieve is a stored procedure that retrieves the data in
the following...
Period | Sum Of Cost For A Year | Sum Of Value For A Year | Sum Of Cost For
Year -1 | Sum Of Value For Year - 1
I've got the following...
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE watson_GetLitresReporting_Monthly
@.intYear As INT
AS
SELECT NLPeriod,
NLYear,
LineValue,
Litres
INTO #thisYear
FROM dbo.Watson_FinsData
WHERE NLYear = @.intYear
GROUP BY NLYear,
NLPeriod,
LineValue,
Litres
SELECT NLPeriod,
NLYear,
LineValue,
Litres
INTO #lastYear
FROM dbo.Watson_FinsData
WHERE NLYear = @.intYear - 1
GROUP BY NLYear,
NLPeriod,
LineValue,
Litres
SELECT #thisYear.NLPeriod,
SUM(#thisYear.LineValue) AS Value,
SUM(#thisYear.Litres) AS Quatity,
SUM(#lastYear.LineValue) AS LastYear,
SUM(#lastYear.Litres) AS Qty
FROM #thisYear
FULL OUTER JOIN #lastYear
ON #thisYear.NLPeriod = #lastYear.NLPeriod
GROUP BY #thisYear.NLYear,
#thisYear.NLPeriod
ORDER BY #thisYear.NLYear,
#thisYear.NLPeriod DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
But that doesn't work at all.
Could someone explain what I'm doing wrong please?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.
Also, what is a URN? To me it, it is a vase with ashes of loved one in
it.|||On Thu, 24 Nov 2005 08:58:08 -0800, JMH wrote:
>Hi,
>I have a table that contains a simple collection of rows, the rows have the
>following fields...
>URN, Year, Period, Cost, Value
>As an example, the data is as follows...
>2005|08|089.32|123.45
>2005|10|056.68|045.68
>2004|10|156.32|068.23
>2005|11|123.56|548.12
>2005|11|078.23|569.12
>2005|7|078.25|875.657
>2004|7|009.50|320.512
>What I'd like to achieve is a stored procedure that retrieves the data in
>the following...
>Period | Sum Of Cost For A Year | Sum Of Value For A Year | Sum Of Cost For
>Year -1 | Sum Of Value For Year - 1
Hi JMH,
Try if this works:
SELECT COALESCE(a.Period, b.Period) AS Period,
SUM(a.Cost) AS Cost2005,
SUM(a.Value) AS Value2005,
SUM(b.Cost) AS Cost2004,
SUM(b.Value) AS Value2004
FROM (SELECT Period, Cost, Value
FROM dbo.Watson_FinsData
WHERE Year = 2005) AS a
FULL OUTER JOIN (SELECT Period, Cost, Value
FROM dbo.Watson_FinsData
WHERE Year = 2004) AS b
ON b.Period = a.Period
GROUP BY COALESCE(a.Period, b.Period)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||--CELKO-- wrote:
> Also, what is a URN? To me it, it is a vase with ashes of loved one in
> it.
What exactly is wrong with this name? (I, indeed, never allowed
a thought that you are not aware of the very well known abbreviation.)
I have a table that contains a simple collection of rows, the rows have the
following fields...
URN, Year, Period, Cost, Value
As an example, the data is as follows...
2005|08|089.32|123.45
2005|10|056.68|045.68
2004|10|156.32|068.23
2005|11|123.56|548.12
2005|11|078.23|569.12
2005|7|078.25|875.657
2004|7|009.50|320.512
What I'd like to achieve is a stored procedure that retrieves the data in
the following...
Period | Sum Of Cost For A Year | Sum Of Value For A Year | Sum Of Cost For
Year -1 | Sum Of Value For Year - 1
I've got the following...
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE watson_GetLitresReporting_Monthly
@.intYear As INT
AS
SELECT NLPeriod,
NLYear,
LineValue,
Litres
INTO #thisYear
FROM dbo.Watson_FinsData
WHERE NLYear = @.intYear
GROUP BY NLYear,
NLPeriod,
LineValue,
Litres
SELECT NLPeriod,
NLYear,
LineValue,
Litres
INTO #lastYear
FROM dbo.Watson_FinsData
WHERE NLYear = @.intYear - 1
GROUP BY NLYear,
NLPeriod,
LineValue,
Litres
SELECT #thisYear.NLPeriod,
SUM(#thisYear.LineValue) AS Value,
SUM(#thisYear.Litres) AS Quatity,
SUM(#lastYear.LineValue) AS LastYear,
SUM(#lastYear.Litres) AS Qty
FROM #thisYear
FULL OUTER JOIN #lastYear
ON #thisYear.NLPeriod = #lastYear.NLPeriod
GROUP BY #thisYear.NLYear,
#thisYear.NLPeriod
ORDER BY #thisYear.NLYear,
#thisYear.NLPeriod DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
But that doesn't work at all.
Could someone explain what I'm doing wrong please?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.
Also, what is a URN? To me it, it is a vase with ashes of loved one in
it.|||On Thu, 24 Nov 2005 08:58:08 -0800, JMH wrote:
>Hi,
>I have a table that contains a simple collection of rows, the rows have the
>following fields...
>URN, Year, Period, Cost, Value
>As an example, the data is as follows...
>2005|08|089.32|123.45
>2005|10|056.68|045.68
>2004|10|156.32|068.23
>2005|11|123.56|548.12
>2005|11|078.23|569.12
>2005|7|078.25|875.657
>2004|7|009.50|320.512
>What I'd like to achieve is a stored procedure that retrieves the data in
>the following...
>Period | Sum Of Cost For A Year | Sum Of Value For A Year | Sum Of Cost For
>Year -1 | Sum Of Value For Year - 1
Hi JMH,
Try if this works:
SELECT COALESCE(a.Period, b.Period) AS Period,
SUM(a.Cost) AS Cost2005,
SUM(a.Value) AS Value2005,
SUM(b.Cost) AS Cost2004,
SUM(b.Value) AS Value2004
FROM (SELECT Period, Cost, Value
FROM dbo.Watson_FinsData
WHERE Year = 2005) AS a
FULL OUTER JOIN (SELECT Period, Cost, Value
FROM dbo.Watson_FinsData
WHERE Year = 2004) AS b
ON b.Period = a.Period
GROUP BY COALESCE(a.Period, b.Period)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||--CELKO-- wrote:
> Also, what is a URN? To me it, it is a vase with ashes of loved one in
> it.
What exactly is wrong with this name? (I, indeed, never allowed
a thought that you are not aware of the very well known abbreviation.)
Friday, March 9, 2012
retrieving >1000 records from AD into Crystal
Hello all,
I am having a couple of problems selecting records from Active Directory. What I want to do is create a report that is grouped on a user object field in AD. Our users are not just contained with the 'Users' container, but also in other areas of the directory.
I've come across the problem that AD will only return the first 1000 records when you query it (mentioned here: http://support.businessobjects.com/library/kbase/articles/c2013533.asp). I believe you can get around this by somehow specifying the 'range' property, however I'm not 100% sure how to do this. This is my query as it stands:
Select displayName, ExtensionAttribute3, ExtensionAttribute2,
sAMAccountName, objectClass FROM 'LDAP://dc=blah,dc=blah2,dc=blah3,dc=blah4;;;Range=0-1000;subtree' WHERE objectClass='user'
Whenever I click OK to this I get the error "An invalid directory pathname was passed".
I guess I actually have 2 questions:
1. How do you get the range property to work (i.e. how can I return more than 1000 rows)
2. How can I get the query to search the subtrees of the directory (I think you need to specify the 'subtree' keyword, but again, this isn't working in my query.
Any help would be appreciated!
Cheers,
DanielIf you dont solve the problem search at http://support.businessobjects.com/
I am having a couple of problems selecting records from Active Directory. What I want to do is create a report that is grouped on a user object field in AD. Our users are not just contained with the 'Users' container, but also in other areas of the directory.
I've come across the problem that AD will only return the first 1000 records when you query it (mentioned here: http://support.businessobjects.com/library/kbase/articles/c2013533.asp). I believe you can get around this by somehow specifying the 'range' property, however I'm not 100% sure how to do this. This is my query as it stands:
Select displayName, ExtensionAttribute3, ExtensionAttribute2,
sAMAccountName, objectClass FROM 'LDAP://dc=blah,dc=blah2,dc=blah3,dc=blah4;;;Range=0-1000;subtree' WHERE objectClass='user'
Whenever I click OK to this I get the error "An invalid directory pathname was passed".
I guess I actually have 2 questions:
1. How do you get the range property to work (i.e. how can I return more than 1000 rows)
2. How can I get the query to search the subtrees of the directory (I think you need to specify the 'subtree' keyword, but again, this isn't working in my query.
Any help would be appreciated!
Cheers,
DanielIf you dont solve the problem search at http://support.businessobjects.com/
Subscribe to:
Posts (Atom)