April 25, 2008 at 8:37 am
What I'm trying to achieve:
Let's assume I have a Table with columns like this:
firstname,lastname,itemType,created
Data could look like this (select * from myTable)
firstname,lastname,itemType,created
-----------------------------------
john, doe, B, 2008-04-01
martha, miller, A, 2008-04-02
john, doe, A, 2008 -04-05
henry,higgins,A,2007-05-01
john, doe, B, 2008 -04-05
I want to create a procedure which gives me an entry for each person, with one column showing the count of ItemType 'A' and one for ItemType 'B'. BUT: I need a start an end Date as input parameters to that procedure (...and created between @starDate and @endDate). Persons who doesn't have entries in the choosen period shall also appear in the result.
Let's further assume that I need two different queries to get the values for counting the 'A's and 'B's (because of columns not mentioned here), but I can use the same parameter values for startDate and endDate
Results for April 2008 should look like this:
firstname,lastname, count 'A', count'B'
---------------------------------------
john, doe, 1,2
martha, miller,1,0
henry,higgins,0,0
From my Newbie point of View I would
- create one Query with the names grouped and count(A)
- create one Query with the names grouped and count(B)
A third query with a List of all names (grouped by firstname,lastname) left outer joined with my two queries.
But how can I query three times with the same parameters an put all together at the end?
All I found in the Docs is the WITH expression, but I can't see how this may help me here.
Many Thanks for any advice in advance
April 25, 2008 at 9:32 am
This should work:
[font="Courier New"];WITH cteCountByNameType AS
(
SELECT
firstname,
lastname,
itemType,
COUNT(*) AS itemCount
FROM
myTable
WHERE
created >= @start_date AND created < @end_date
GROUP BY
firstname,
lastname,
itemType
)
SELECT
A.firstname,
A.lastname,
ISNULL((SELECT itemCount FROM cteCountByNameType WHERE firstname = A.firstname AND lastname=A.lastname AND itemtype = 'A'), 0) AS countA,
ISNULL((SELECT itemCount FROM cteCountByNameType WHERE firstname = A.firstname AND lastname=A.lastname AND itemtype = 'B'), 0) AS countB
FROM
myTable A LEFT JOIN
cteCountByNameType B ON
A.firstname = B.firstname AND
A.lastname =B.lastname
GROUP BY
A.firstname,
A.lastname
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 25, 2008 at 9:33 am
Here's one way...
declare @myTable table (firstname varchar(10), lastname varchar(10), itemType varchar(10), created datetime)
insert @myTable
select 'john', 'doe', 'B', '2008-04-01'
union all select 'martha', 'miller', 'A', '2008-04-02'
union all select 'john', 'doe', 'A', '2008-04-05'
union all select 'henry', 'higgins', 'A', '2007-05-01'
union all select 'john', 'doe', 'B', '2008-04-05'
; with
people as (select distinct firstname, lastname from @myTable),
dataForPeriod as (select * from @myTable where '20080401' <= created and created < '20080501'),
summaryForPeriod as (select firstname, lastname,
sum(case when itemType = 'A' then 1 else 0 end) as countA,
sum(case when itemType = 'B' then 1 else 0 end) as countB
from dataForPeriod group by firstname, lastname)
select a.*, isnull(b.countA, 0) as countA, isnull(b.countB, 0) as countB from people a
left outer join summaryForPeriod b on a.firstname = b.firstname and a.lastname = b.lastname
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 25, 2008 at 9:35 am
Jack Corbett (4/25/2008)
I hate it when that happens 😀
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 25, 2008 at 9:40 am
Yeah me too! Just glad I was first this time.:D
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 25, 2008 at 9:53 am
Thank you both!
I'll give it a try immediately. But I'm still confused about the order of converting parameters an building up the queries. That is because I first have to convert my parameters from varChar to datetime. In one of my existing procedures this looks that way:
CREATE Procedure [dbo].[allRecordsInPeriod]
@startDateVarChar varchar(20), @endDateVarChar varchar(20)
AS
DECLARE @startDate datetime, @endDate datetime
Select @startDate = CONVERT(datetime, @startDateVarChar,104)
Select @endDate = CONVERT(datetime, @endDateVarChar,104)
SELECT *
FROM myTable
WHERE
...and (created between @startDate and @endDate)
...and so on
May I put your example code right after "Select @endDate = CONVERT(datetime, @endDateVarChar,104)"? (Overwriting my SELECT * from... and so on)
Sorry, I have tried so many variations yet, but haven't found out the right order to convert my variables and then use the WITH statement. Never saw an example with two "AS" anywhere :blush:
April 25, 2008 at 10:04 am
The "With" defines that you are creating a CTE (Common Table Expression) and it needs to be the first part of a statement. You need to either put
"; With" or end your previous statement with ";" which is the statement terminator like in java and C variations. In SQL Server the ";" is optional EXCEPT before a CTE declaration.
So yes put with my or Ryan's code after your date conversion, just make sure you have the ";".
Another warning is using Between with dates in SQL Server because between 04/01/2008 and 04/30/2008 is really between 04/01/2008 00:00:00 and
04/30/2008 00:00:00 which will miss 04/30/2008 00:00:01 and later so you should use date >= 04/01/2008 00:00:00 and < 05/01/2008 00:00:00
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 25, 2008 at 2:17 pm
Thanks again Jack!
And yes, I've also noticed the "between" Problem. But because my Application originally sends varChars as Parameters, it's easy to add " 23:59:59" to the given String of the endDate Parameter. So "between" works fine for me.
Kind Regards
Peter
April 28, 2008 at 4:15 am
YES!!! Strike!! :w00t:
Monday morning, back to work, and straight back to my database problem. With a little bit of customizing to your proposals everything now works as expected.
Thanks again, you both have no idea how much time you saved me!
Peter
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply