How to write query for Detail and total records

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 2008 Forums

Transact-SQL (2008)

How to write query for Detail and total records

Author

Topic

madhuri99m
Starting Member

1 Post

Posted - 2011-03-24 : 16:16:46

I have my table with these columns
InvoiceNO,order date,orderid,orderQTY,Price

20001205BRT 2011-03-22 200510255 1 14.00
20006505BRT 2011-03-22 200510255 1 8.98
20006505WRX 2011-03-22 200510256 3 14.00
22891235DFG 2011-03-22 200510256 3 12.98
22896505TCP 2011-03-22 200510256 3 14.00
2289ERTY104 2011-03-22 200510256 2 8.98
2289JPMW786 2011-03-22 200510256 3 14.00
2289KLWSQ18 2011-03-22 200510256 3 14.00
228M23YHNPF 2011-03-22 200510256 5 14.00
228MRGH23MP 2011-03-22 200510256 5 14.00
228MRGRTPSD 2011-03-22 200510256 5 14.00
228MRTP3858 2011-03-22 200510256 1 14.00

i have to write a query so that under each invoice it has to show total record by calculating (orderQty*price). I have to display IT for each item record and TT for total records and add all the column values . For example take the 3rd row from my table

the details record should be like this
IT20006505WRX2011-03-22200510256314.00
TT20006505WRX2011-03-22200510256342.00 --This is total record where the price is (3*14=42)

I have to repeat this for all the records. I have to show IT(which is first then below that I have to show TTrecord

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-03-24 : 19:03:27

select '20001205BRT' as InvoiceNO, cast('2011-03-22' as datetime) as [order date], '200510255' as orderid, 1 as orderQTY, 14.00 as Price
into #tmppmd
insert into #tmppmd values ('20006505BRT', '2011-03-22' , '200510255', 1 ,8.98)
insert into #tmppmd values ('20006505WRX', '2011-03-22' , '200510256', 3 ,14.00)
insert into #tmppmd values ('22891235DFG', '2011-03-22' , '200510256', 3 ,12.98)
insert into #tmppmd values ('22896505TCP', '2011-03-22' , '200510256', 3 ,14.00)
insert into #tmppmd values ('2289ERTY104', '2011-03-22' , '200510256', 2 ,8.98)
insert into #tmppmd values ('2289JPMW786', '2011-03-22' , '200510256', 3 ,14.00)
insert into #tmppmd values ('2289KLWSQ18', '2011-03-22' , '200510256', 3 ,14.00)
insert into #tmppmd values ('228M23YHNPF', '2011-03-22' , '200510256', 5 ,14.00)
insert into #tmppmd values ('228MRGH23MP', '2011-03-22' , '200510256', 5 ,14.00)
insert into #tmppmd values ('228MRGRTPSD', '2011-03-22' , '200510256', 5 ,14.00)
insert into #tmppmd values ('228MRTP3858', '2011-03-22' , '200510256', 1 ,14.00)

select *
from
(
select 'IT' as Type,*
from #tmppmd pmd
union all
select 'TT', invoiceno,[order date],orderid,orderqty,orderqty*price
from #tmppmd pmd
) a
order by invoiceno, type

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources