My head is bursting please help:

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)

My head is bursting please help:

Author

Topic

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-07-15 : 16:07:11

Suppose: I have this query.

Select col1,qtr,sum(isnull(col2,0))as amt,sum(isnull(col3,0)as telamt
from table1
group by col1, qtr

Output is:
col1 qtr amt telamt
AA Ist 20 30
AA 2nd 0 50
BB ISt 60 0
BB 2nd 70 40

what i have to do to get like this:(I have other table which has all 4 qtr,col1 (table 2)

col1 qtr amt telamt
AA Ist 20 30
AA 2nd 0 50
AA 3rd 0 0
AA 4th 0 0
BB ISt 60 0
BB 2nd 70 40
BB 3rd 0 0
BB 4th 0 0

So I want repeat col1 4 times with respect to qtr.The amt and telamt can be null.So how do i do this?

Cross join , Union all

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-15 : 16:18:59

[code]
Select a.Col1,b.qtr,a.Telamt
FROM
(
Select col1,b.qtr,sum(isnull(col2,0))as amt,sum(isnull(col3,0)as telamt
from table1
group by col1, qtr
) a
left Join
(Select 'Ist' as Qtr Union all
Select '2nd' Union all
Select '3rd' Union all
Select '4th'
) b
on a.Qtr = b.Qtr
[/code]

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-07-15 : 16:31:59

Sorry i forgot that i have year column too like

col year qtr amt telamt
AA 2004 Ist 20 30
AA 2004 2nd 0 0
AA 2004 3rd 0 0
AA 2004 4th 0 0
AA 2005 Ist 0 50
AA 2005 2nd 0 0
.................. like this:
How do i add col, year ,qtr with respect to year and qtr to row which doesn't have data ( want to force it null with ISNULL function)
Thanks in advance.

Original query:
Select col1,year,qtr,sum(isnull(col2,0))as amt,sum(isnull(col3,0)as telamt
from table1
group by col1,year,qtr
order by col1,year,qtr

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-07-15 : 16:34:31

quote:

Originally posted by Vinnie881


Select a.Col1,b.qtr,a.Telamt from(
Select col1,b.qtr,sum(isnull(col2,0))as amt,sum(isnull(col3,0)as telamt
from table1
group by col1, qtr
) a
left Join
(Select 'Ist' as Qtr Union all
Select '2nd'  Union all
Select '3rd'  Union all
Select '4th' 
)  b
on a.Qtr = b.Qtr

Missed this

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-15 : 16:49:52

Use the same method. Just create a table that has the years in it and left join it, or use the table that has the qtr's in it then just add a year column (In that scenerio you will need a qtr and year record for all possibly qtr's (i.e Ist 2006, Ist 2007, etc..)

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-07-15 : 16:51:31

How to do buddy? I want all 0 on nondata row and no duplicates as well. Please show.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-15 : 17:17:25

This query assumes col1 is your year column



Select a.Qtr,a.[Year],coalesce(a.[TelAmt],0),coalesce(b.[Amt],0)
From
(	Select *
	  From
		(Select 'Ist' as Qtr Union all
		Select '2nd'  Union all
		Select '3rd'  Union all
		Select '4th' 
		) aa
		Cross Join
		(Select '2005' as [Year] Union All
		Select '2006' as [Year] Union All
		Select '2007' as [Year] 
		) bb
) a
Left Join --I am assuming col1 is actually your year colum
(
Select aaa.col1,aaa.qtr,sum(isnull(aaa.col2,0))as amt,sum(isnull(aaa.col3,0)as telamt
from table1 aaa
group by aaa.col1, aaa.qtr
) b
on a.Qtr = b.Qtr
and a.[Year] =b.[Col1]

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-07-15 : 20:14:40

Thanks a lot Vinnie.

Ok, I have 2 groups 1)AA 2)BB
and I want to add them in Previous query:

Output Should be:
group year qtr AMT Telamt
AA 2005 Ist 20 30
AA 2005 2nd 0 40
AA 2005 3rd 90 0
AA 2005 4th 0 0
AA 2006 Ist 70 0
AA 2006 2nd 20 0
AA 2006 3rd 40 90
AA 2006 4th 60 30
BB 2005 Ist 70 0
...................
.................. Like this:
Where do i include in cross join? Anyway your query works perfect.But i need by groups too.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-07-15 : 20:45:33

Any one Please??

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-07-15 : 20:58:56

Any idea Khtan? I would appreciate.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 00:45:34

quote:
Originally posted by SCHEMA

Any idea Khtan? I would appreciate.

Why do you keep on adding new columns to requirement. why cant you specify these in beginning. Also you could have easily modified solution provided to suit your needs. Anyways here's the solution asked.Please ensure you give us complete info about columns in future.

select m.col1,m.Year,m.Qtr,ISNULL(amt,0),ISNULL(telamt,0)
from
(Select cc.col1,bb.Year,aa.Qtr
	  From
		(Select 'Ist' as Qtr Union all
		Select '2nd'  Union all
		Select '3rd'  Union all
		Select '4th' 
		) aa
		Cross Join
		(Select '2005' as [Year] Union All
		Select '2006' as [Year] Union All
		Select '2007' as [Year] 
		) bb
              Cross Join
               (select distinct col1 from table1) cc
)m
left join
(
Select col1,year,qtr,sum(isnull(col2,0))as amt,sum(isnull(col3,0)as telamt
from table1 
group by col1, year,qtr)dat
on dat.col1 = m.col1 
and dat.year=m.year
and dat.qtr=m.qtr

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-07-16 : 08:07:08

Thanks Visakh. you are great. How do i repeat col1(groups) exactly 10 times?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 08:08:03

quote:
Originally posted by SCHEMA

Thanks Visakh. you are great. How do i repeat col1(groups) exactly 10 times?

10 times? didnt get that? why repeat them?

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-07-16 : 08:16:27

I get 12 rows for each group(AA && BB). I want only 10 rows. Thanks.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-16 : 08:22:55

quote:
Originally posted by SCHEMA

I get 12 rows for each group(AA && BB). I want only 10 rows. Thanks.

...am i missing something here? 3 years and 4 quarters for each...? how could that possibly be 10?

Em

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-07-16 : 08:26:54

So what should I do to remove 2 extra rows from each group? Thanks.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-16 : 08:31:46

which 2 rows? surely you either want 4 quarters in a year or you don't?

Em

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-07-16 : 08:44:34

quote:
Originally posted by elancaster

which 2 rows? surely you either want 4 quarters in a year or you don't?

Em

For 2007, I don't want 3rd and 4th qtr for both group. Thanks.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-16 : 08:58:10

off the top of my head....?

select m.col1,m.Year,m.Qtr,ISNULL(amt,0),ISNULL(telamt,0)
from
(Select cc.col1,bb.Year,aa.Qtr
	  From
		(Select 'Ist' as Qtr Union all
		Select '2nd'  Union all
		Select '3rd'  Union all
		Select '4th' 
		) aa
		Cross Join
		(Select '2005' as [Year] Union All
		Select '2006' as [Year] Union All
		Select '2007' as [Year] 
		) bb
              Cross Join
               (select distinct col1 from table1) cc
        where Qtr+[Year] not in ('3rd2007','4th2007')
)m
left join
(
Select col1,year,qtr,sum(isnull(col2,0))as amt,sum(isnull(col3,0)as telamt
from table1 
group by col1, year,qtr)dat
on dat.col1 = m.col1 
and dat.year=m.year
and dat.qtr=m.qtr

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 09:34:26

quote:
Originally posted by SCHEMA

quote:
Originally posted by elancaster

which 2 rows? surely you either want 4 quarters in a year or you don't?

Em

For 2007, I don't want 3rd and 4th qtr for both group. Thanks.

why? whats your rule for avoiding them?

SCHEMA
Posting Yak Master

192 Posts

Posted - 2008-07-16 : 11:52:39

quote:

Originally posted by elancaster

off the top of my head....?

select m.col1,m.Year,m.Qtr,ISNULL(amt,0),ISNULL(telamt,0)
from
(Select cc.col1,bb.Year,aa.Qtr
	  From
		(Select 'Ist' as Qtr Union all
		Select '2nd'  Union all
		Select '3rd'  Union all
		Select '4th' 
		) aa
		Cross Join
		(Select '2005' as [Year] Union All
		Select '2006' as [Year] Union All
		Select '2007' as [Year] 
		) bb
              Cross Join
               (select distinct col1 from table1) cc
        where Qtr+[Year] not in ('3rd2007','4th2007')
)m
left join
(
Select col1,year,qtr,sum(isnull(col2,0))as amt,sum(isnull(col3,0)as telamt
from table1 
group by col1, year,qtr)dat
on dat.col1 = m.col1 
and dat.year=m.year
and dat.qtr=m.qtr

We don't want to include 3rd and 4th Quarter for 2007 because customer doesn't need.
Also I am getting error in blue portion.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources