Derived tables and grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Derived tables and grouping

Author

Topic

Gareth_Hastings
Starting Member

7 Posts

Posted - 2007-10-22 : 08:59:35

Hi,

I have a table of transactions and I'm trying to get a nice list of total sales/purchases made per day by a single account. If the account doesn't have any transactions for that day then the query should return a row indicating 0 transactions.

Here is a simplified table


create table garethTest ([date] datetime, accountid int, transactionType nvarchar(4), price int)

and some random data


insert garethTest
	select '2007-10-19', 1, 'sale', 45 union all
	select '2007-10-19', 1, 'buy', 221 union all
	select '2007-10-19', 2, 'buy', 334 union all
	select '2007-10-19', 2, 'sale', 12 union all
	select '2007-10-21', 2, 'sale', 15 union all
	select '2007-10-21', 2, 'sale', 18 union all
	select '2007-10-21', 2, 'sale', 9 union all
	select '2007-10-21', 2, 'sale', 33 union all
	select '2007-10-22', 2, 'sale', 21 union all
	select '2007-10-22', 2, 'buy', 142 union all
	select '2007-10-23', 1, 'sale', 17 union all
	select '2007-10-25', 2, 'sale', 1

Now the query I have I think should nearly be there!! The only difference is I have a date lookup/tally table instead of doing the dateadd part.

Now it works fine until I add a where clause in at the bottom to decide which account I want to view.

I would like to see results that look like


Date        Sales  Buys
2007-10-19  45     221
2007-10-20  0      0
2007-10-21  0      0
2007-10-22  0      0
2007-10-23  17     0
2007-10-24  0      0
2007-10-25  0      0

Instead my query just returns the rows with data


Date        Sales  Buys
2007-10-19  45     221
2007-10-23  17     0

Here it is


select
	[Date],
	sum(Sale) as [Sales],
	sum(Buy) as [Buys]
from
	(select
		dateadd(d, numbers.number, '2007-10-18') as [Date],
		case when transactionType = 'sale' then price else 0 end Sale,
		case when transactionType = 'buy'  then price else 0 end Buy,
		isnull(accountid, 0) as accountid
	from
		(select 1 as number union all 
		 select 2 union all 
		 select 3 union all 
		 select 4 union all 
		 select 5 union all 
		 select 6 union all 
		 select 7) as numbers
	left join garethtest gt on 
		dateadd(d, number, '2007-10-18') = gt.date
	) as d
where accountid = 1 -- I remove this line and it works but shows me everyone
group by date

Any ideas? I was thinking maybe I need to use that query as a derived table from another select and group by the accountid and date??

Thanks

Gareth

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-22 : 09:08:28

[code]SELECT [DATE],
Sales = MAX(CASE WHEN transactionType = 'sale' THEN price ELSE 0 END),
Buys = MAX(CASE WHEN transactionType = 'buy' THEN price ELSE 0 END)
FROM F_TABLE_DATE('20071019', '20071025') left JOIN garethTest g
ON [DATE] = g.[date]
AND accountid = 1
GROUP BY [DATE][/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-22 : 09:20:13

quote:

Originally posted by Gareth_Hastings

select
	[Date],
	sum(Sale) as [Sales],
	sum(Buy) as [Buys]
from
	(select
		dateadd(d, numbers.number, '2007-10-18') as [Date],
		case when transactionType = 'sale' then price else 0 end Sale,
		case when transactionType = 'buy'  then price else 0 end Buy,
		isnull(accountid, 0) as accountid
	from
		(select 1 as number union all 
		 select 2 union all 
		 select 3 union all 
		 select 4 union all 
		 select 5 union all 
		 select 6 union all 
		 select 7) as numbers
	left join garethtest gt on 
		dateadd(d, number, '2007-10-18') = gt.date
	) as d
where accountid = 1 -- I remove this line and it works but shows me everyone
group by date

Move the WHERE clause to a second INNER JOIN criteria.

...
	left join garethtest gt on 
		dateadd(d, number, '2007-10-18') = gt.date AND accountid = 1
	) as d
group by date

E 12°55'05.25"
N 56°04'39.16"

Gareth_Hastings
Starting Member

7 Posts

Posted - 2007-10-22 : 09:40:54

Peso and khtan

Thanks both for your very quick answers and also to khtan for the introduction of the F_TABLE_DATE function which has just be installed on my server

I can see that getting LOADS of use!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-22 : 09:45:44

credit goes to MVJ for the F_TABLE_DATE

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources