Query for sumary report - 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)

Query for sumary report

Author

Topic

xholax
Starting Member

12 Posts

Posted - 2010-10-20 : 14:45:13

Hello, I hope you can help me , Im new at building querys and Transact SQL and I need one query for my job.

As you can see in the picture above, I have the table (that is a result from another query, that recieve 2 paramares @startdate, @enddate), so I need a summary from there , omiting some restrictions as you can see in teh picture, please I hope you can help me and sorry for my bad english.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 15:35:02

[code]-- a table variable for test data
declare @Sample table ([date] datetime, shift varchar(10), fruit varchar(10), times int)

-- inserting the test data
insert @Sample
select '20101001','Dia','Apple',2 union all
select '20101001','Noche','Apple',3 union all
select '20101002','Dia','Apple',4 union all
select '20101002','Noche','Apple',5 union all
select '20101003','Dia','Apple',9 union all
select '20101003','Noche','Apple',9 union all
select '20101004','Noche','Apple',7 union all
select '20101005','Dia','Apple',8 union all
select '20101005','Noche','Apple',5

select dia.[date],dia.fruit,dia.times+noche.times as [sum times]
from @Sample as dia
join @Sample as noche
on noche.[date] = dateadd(dd,-1,dia.[date])
and noche.shift='Noche'
where dia.shift='Dia'

-- date fruit sum times
-- ----------------------- ---------- -----------
-- 2010-10-02 00:00:00.000 Apple 7
-- 2010-10-03 00:00:00.000 Apple 14
-- 2010-10-05 00:00:00.000 Apple 15
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.