Combining two queries with Group By issues

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

General SQL Server Forums

New to SQL Server Programming

Combining two queries with Group By issues

Author

Topic

cardullo4321
Starting Member

40 Posts

Posted - 2011-04-12 : 21:13:46

I am trying to get a query to create a accounting Journal Entry. So far I have two queries that makes the entry perfectly. But I want to combine the two queries into 1. The 1st query handles the credit side of the entry and the 2nd query handles the debit side of the entry. I am getting a error with the Grouping not being the same.

Here is the Example of what I want

Bil |RevSA |DEP |Sub |Deb |Cre |LineDescription
105 |33124 |050 |000 |000 |100 |0311 MOVIE Stub Revenue Accr
105 |33126 |050 |000 |000 |200 |0311 SPORTS Stub Revenue Accr
105 |33127 |050 |000 |000 |300 |0311 EVENTS Stub Revenue Accr
105 |12008 |000 |000 |600 |000 |0311 105 Stub Revenue Accr

1st Query

select BillingEntity,
Stub_Revenue_Account,
'050' as Dept,
'000' as SubDept,
'' as Debit,
sum(Stub_Revenue_Acc) as Credit,
LineDescription = '0'+ CONVERT(varchar(100),(MONTH ('03/21/2011'))) +
CONVERT(varchar(100),(right(Year(GETDATE()),2))) + ' '+ Nickname +
' Stub Revenue Accr'

From Accrual2011_CPPMWW
Where Stub_Revenue_Account <> 'A0' and ReportDate = '03/21/2011'

Group by BillingEntity,
Stub_Revenue_Account,
Nickname

Order by BillingEntity,
Stub_Revenue_Account,
Nickname

-----------------------------------------------------------------------------------------2nd Query

select BillingEntity,
'12008' as Stub_Revenue_Account,
'000' as Dept,
'000' as SubDept,
sum(Stub_Revenue_Acc) as Debit,
' ' as Credit,
LineDescription = '0'+ CONVERT(varchar(100),(MONTH ('03/21/2011'))) +
CONVERT(varchar(100),(right(Year(GETDATE()),2))) + ' '+ BillingEntity +
' Stub Revenue Accr'

From Accrual2011_CPPMWW

Where Stub_Revenue_Account <> 'A0' and ReportDate = '03/21/2011'

Group by BillingEntity

Order by BillingEntity

Gregory Cardullo

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-12 : 21:34:55

Use UNION ALL.

Remove the order by clause from the first query and insert the key words UNION ALL between the first and second query.

cardullo4321
Starting Member

40 Posts

Posted - 2011-04-12 : 21:35:56

The 1st 3 rows are from the 1st query and the last row from my example is from the 2nd query. Events,Movies, and Sports are from the Nickname Field.

Gregory Cardullo

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-12 : 21:36:08

In your 1st query, you group by 3 columns (BillingEntity, Stub_Revenue_Account, Nickname), but in the 2nd query you group by BillingEntity. Why the different in grouping ?

Also, in the 2nd query you are selecting all Stub_Revenue_Account that is not equal to 'A0' but in the result you return as 12008


select BillingEntity,
'12008' as Stub_Revenue_Account,
'000' as Dept,

Is this what you want ?

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

cardullo4321
Starting Member

40 Posts

Posted - 2011-04-12 : 21:56:30

Cause the second query should always have the 12008 Account. The 12008 is a hard coded Stub_Revenue_Account Number not in my database but 12008 should equal the totals of Real Revenue_Stub_Accounts not including A0. Nickname is not necessary in the Line Description column for the 2nd query. Dept and Sub Dept are hard coded in both queries.

Gregory Cardullo

cardullo4321
Starting Member

40 Posts

Posted - 2011-04-12 : 22:00:16

Stub_Revenue_Acc and Revenue_Stub_Account are different fields. The Stub_Revenue_ACC field means Stub_Revenue_Accrual Amount and the Revenue_Stub_Account is where the Stub_Revenue_Acc amount is coded too.

Gregory Cardullo

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-12 : 22:26:46

basically as sunitabeck said, UNION ALL the 2 query.


select 	BillingEntity,
	Stub_Revenue_Account,
	'050' as Dept,
	'000' as SubDept,
	0 as Debit,
	sum(Stub_Revenue_Acc) as Credit,
	LineDescription = right('0' + convert(varchar(10), datepart(month, ReportDate)), 2) + right(datepart(year, ReportDate), 2)
			+ ' ' + Nickname + ' Stub Revenue Accr'
From 	Accrual2011_CPPMWW
Where 	Stub_Revenue_Account 	<> 'A0' 
and 	ReportDate 		= '20110321'
Group by BillingEntity,
	Stub_Revenue_Account,
	Nickname

union all

select 	BillingEntity,
	'12008' as Stub_Revenue_Account,
	'000' as Dept,
	'000' as SubDept,
	sum(Stub_Revenue_Acc) as Debit,
	0 as Credit,
	LineDescription = right('0' + convert(varchar(10), datepart(month, ReportDate)), 2) + right(datepart(year, ReportDate), 2)
			+ ' ' + BillingEntity +	' Stub Revenue Accr'
From 	Accrual2011_CPPMWW
Where 	Stub_Revenue_Account <> 'A0' 
and 	ReportDate = '03/21/2011'
Group by BillingEntity

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

cardullo4321
Starting Member

40 Posts

Posted - 2011-04-12 : 23:19:51

I will try it tomorrow. I basically did the same thing but I did have the 'Order by' after each quote.

Gregory Cardullo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources