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
 sql query help

Author  Topic 

anjali5
Posting Yak Master

121 Posts
Posted - 2014-05-20 : 02:37:23
Hi All,
I have the following table
Table1

 PK      Part      revision     date         year     Type    task1   activity  amount  deleteDate   Notes
      1       CA        BASIC        12/23/2010   2010      T       TBD     travel    4000                TestNotes1
      2       CA        BASIC        12/23/2010   2010      T       TBD1    travel1   5000                TestNotse2
      3       CA        BASIC        12/23/2010   2010      T       TBD1    travel2   5000                TestNotse3
      4       CA        BASIC        12/23/2010   2010      A       TBA     travel3   6000                TestNotes4
      5       CA        BASIC        12/23/2010   2010      A       TBA     travel4   6000                TestNotes5
      6       CA        BASIC        12/23/2010   2010      A       TBA     travel5   6000                TestNotes6
      7       CA        BASIC        12/23/2010   2010      A       TBA     travel6   6000                TestNotes7
      8       CA        BASIC        12/23/2010   2012      A       TBA     travel5   6000                TestNotes8
      9       CA        BASIC        12/23/2010   2012      A       TBA     travel100 6000                TestNotes9
      10      CA        BASIC        12/23/2010   2012      T       TBA     travel10  6000                TestNotes 10
      11      CA        BASIC        12/23/2010   2012      T       TBA     travel9   6000                TestNotes11


I want to produce the output like this


Part      revision     date         year        Type    task1   activity  amount   deleteDate            Notes

CA          BASIC        12/23/2010   2010      T       TBD     travel    4000                            TestNotes1
CA          BASIC        12/23/2010   2010      T       TBD1    travel1   5000                            TestNotse2
CA          BASIC        12/23/2010   2010      T       TBD1    travel2   5000                            TestNotes
                                                                         --------
                                                                         14000

CA          BASIC        12/23/2010   2010      A       TBA     travel3   6000                            TestNotes4
CA          BASIC        12/23/2010   2010      A       TBA     travel4   6000                            TestNotes5
CA          BASIC        12/23/2010   2010      A       TBA     travel5   6000                            TestNotes6
CA          BASIC        12/23/2010   2010      A       TBA     travel6   6000                            TestNotes7
CA          BASIC        12/23/2010   2012      A       TBA     travel5   6000                            TestNotes8
CA          BASIC        12/23/2010   2012      A       TBA     travel100 6000                            TestNotes9
                                                                          -----
                                                                          36000



so basically for one particular part , when the type changes then I want to calculate sum and when the year changes then I want to get another output:




Part      revision     date         year        Type    task1   activity  amount   deleteDate            Notes


      CA        BASIC        12/23/2010   2012      T       TBA     travel10  6000                        TestNotes 10
      CA        BASIC        12/23/2010   2012      T       TBA     travel9   6000                        TestNotes11 
                                                                            --------
                                                                            12000

      CA        BASIC        12/23/2010   2012      A       TBA     travel5   6000                        TestNotes8
      CA        BASIC        12/23/2010   2012      A       TBA     travel100 6000                        TestNotes9
                                                                             -------
                                                                             12000


I always want T at the top as the type.

Below is the table data


declare @tab table(PK int, Part varchar(10), revision varchar(10), date datetime, year int, Type char(1)
, task1 varchar(20), activity varchar(20), amount int,  deleteDate datetime, Notes varchar(20))
insert into @tab
select    1,'CA', 'BASIC', '12/23/2010',2010,'T','TBD','travel',4000,null,'TestNoes1'
union all select 2,'CA', 'BASIC', '12/23/2010',2010,'T','TBD1','travel1',5000,null,'TestNose2'
union all select 3,'CA', 'BASIC', '12/23/2010',2010,'T','TBD1','travel2',5000,null,'TestNose3'
union all select 4,'CA', 'BASIC', '12/23/2010',2010,'A','TBA','travel3',6000,null,'TestNoes4'
union all select 5,'CA', 'BASIC', '12/23/2010',2010,'A','TBA','travel4',6000,null,'TestNoes5'
union all select 6,'CA', 'BASIC', '12/23/2010',2010,'A','TBA','travel5',6000,null,'TestNoes6'
union all select 7,'CA', 'BASIC', '12/23/2010',2010,'A','TBA','travel6',6000,null,'TestNoes7'
union all select 8,'CA', 'BASIC', '12/23/2010',2012,'A','TBA','travel5',6000,null,'TestNoes8'
union all select 9,'CA', 'BASIC', '12/23/2010',2012,'A','TBA','travel100',6000,null,'TestNoes9'
union all select 10,'CA','BASIC', '12/23/2010',2012,'T','TBA','travel10',6000,null,'TestNoes 10'
union all select 11,'CA','BASIC', '12/23/2010',2012,'T','TBA','travel9',6000,null,'TestNoes11'




I always want T at the top. I am using sql server 2005 so I cannot use grouping sets.

any help will be appreciated.

bandi
Master Smack Fu Yak Hacker

2242 Posts
Posted - 2014-05-20 : 03:44:41

declare @tab table(PK int, Part varchar(10), revision varchar(10), date datetime, year int, Type char(1)
, task1 varchar(20), activity varchar(20), amount int, deleteDate datetime, Notes varchar(20))
insert into @tab
select 1,'CA', 'BASIC', '12/23/2010',2010,'T','TBD','travel',4000,null,'TestNoes1'
union all select 2,'CA', 'BASIC', '12/23/2010',2010,'T','TBD1','travel1',5000,null,'TestNose2'
union all select 3,'CA', 'BASIC', '12/23/2010',2010,'T','TBD1','travel2',5000,null,'TestNose3'
union all select 4,'CA', 'BASIC', '12/23/2010',2010,'A','TBA','travel3',6000,null,'TestNoes4'
union all select 5,'CA', 'BASIC', '12/23/2010',2010,'A','TBA','travel4',6000,null,'TestNoes5'
union all select 6,'CA', 'BASIC', '12/23/2010',2010,'A','TBA','travel5',6000,null,'TestNoes6'
union all select 7,'CA', 'BASIC', '12/23/2010',2010,'A','TBA','travel6',6000,null,'TestNoes7'
union all select 8,'CA', 'BASIC', '12/23/2010',2012,'A','TBA','travel5',6000,null,'TestNoes8'
union all select 9,'CA', 'BASIC', '12/23/2010',2012,'A','TBA','travel100',6000,null,'TestNoes9'
union all select 10,'CA','BASIC', '12/23/2010',2012,'T','TBA','travel10',6000,null,'TestNoes 10'
union all select 11,'CA','BASIC', '12/23/2010',2012,'T','TBA','travel9',6000,null,'TestNoes11'

;WITH CTE
AS (
SELECT DENSE_RANK() OVER(PARTITION BY part order by year, type desc) as RN, * FROM @tab
)
SELECT c1.RN, c1.Part, c1.revision, c1.date, c1.year, c1.Type, c1.task1, c1.activity, c1.amount, c1.deleteDate,c1.Notes
FROM CTE c1
union all
SELECT RN, NULL, NULL, NULL, NULL, NULL, NULL, NULL, SUM(Amount), NULL, NULL FROM CTE group by rollup(RN)
ORDER BY RN, PART, revision, date, year, Type desc, task1

--
Chandu
Go to Top of Page

anjali5
Posting Yak Master

121 Posts
Posted - 2014-05-20 : 11:23:45
This is not giving me the type T at the top.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts
Posted - 2014-05-20 : 11:28:35
You'll need to put Type at the start of the ORDER BY clause.
Go to Top of Page

anjali5
Posting Yak Master

121 Posts
Posted - 2014-05-20 : 14:32:09
I am getting an error saying :

Msg 195, Level 15, State 10, Line 8
'rollup' is not a recognized built-in function name.

I am using sql server 2005.

Also, I need Type T in the first row. T is not the first one in the alphabetical order. A is coming first.
Go to Top of Page

GouravSaxena1987
Starting Member

23 Posts
Posted - 2014-05-21 : 07:01:59
Hello Anjali,

You should use WITH ROLLUP instead of ROLLUP() since ROLLUP() function is available from 2008 version onwards.r

In Order to get Type T first, you need to change into to ORDER BY clause.

Just change last row of query which has been provided by chandu and make it -

"ORDER BY Type desc ,RN, PART, revision, date, year, task1"

Regards,
Gourav Saxena
Data Warehouse Counsultant
GouravSaxena1987@gmail.com
Go to Top of Page

anjali5
Posting Yak Master

121 Posts
Posted - 2014-05-21 : 11:31:51
Thanks. I will try that.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site