Weekly 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

General SQL Server Forums

New to SQL Server Programming

Weekly report

Author

Topic

alanhuro
Starting Member

34 Posts

Posted - 2008-12-30 : 14:17:13

Hello Guys/Gals, Merry Christmas and Happy new year.

It is sucked that getting close to new year but I'm still in my cubical debugging SQL code.

I try to write a daily report and could not figure out how to fill in the days that are missing from database. For instance I have

date___________Test Result
2008-12-01________12
2008-12-03________19
2008-12-03________18
2008-12-03________17
2008-12-07________30
2008-12-07________32
...

I want to generate a report which include all the days for a month. For a day which does not have data the result will be null. here is an example

Date___________Test Result
2008-12-01________12
2008-12-02________Null
2008-12-03________avg(19, 18, 17)
2008-12-04________Null
2008-12-05________Null
2008-12-06________Null
2008-12-07________avg(30, 32)

Thanks
Alan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 14:27:39

[code]
DECLARE @Dates TABLE
(
ID int identity(1,1),
dateval datetime
)
DECLARE @MaxDate datetime,@minDate datetime,@Days int
SELECT @MaxDate=MAX(date),@MinDate=MIN(date)
FROM YourTable

SET @Days =DATEDIFF(dd,@MinDate,@MaxDate)+1

WHILE @Days>=0
BEGIN
INSERT INTO @Dates(dateval)
SELECT DATEADD(dd,-1*@Days,@MaxDate)
SET @Days=@Days-1
END

SELECT d.dateval,t.TestResult
FROM @Dates d
LEFT JOIN (SELECT date,AVG(TestResult*1.0) AS TestResult
FROM YourTable
GROUP BY date)t
ON t.date=d.dateval
[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-30 : 23:50:45

Try this

declare @temp1 table ( date1 datetime,testresult1 int)
declare @i int,@date varchar(32)
set @i = 1
while (@i<=30)
begin
select @date = date from @temp
select @date = DATEADD(D,0,DATEADD(D,-DAY(@date)+cast(@i as varchar(10)),@date))
select @i = @i +1
insert into @temp1
select @date,avg(testresult) from @temp where datediff(d,@date,date) = 0
end
select * from @temp1

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-31 : 00:11:34

quote:

Originally posted by visakh16


DECLARE @Dates TABLE
(
ID int identity(1,1),
dateval datetime
)
DECLARE @MaxDate datetime,@minDate datetime,@Days int
SELECT @MaxDate=MAX(date),@MinDate=MIN(date)
FROM YourTable

SET @Days =DATEDIFF(dd,@MinDate,@MaxDate)+1

WHILE @Days>=0
BEGIN
INSERT INTO @Dates(dateval)
SELECT DATEADD(dd,-1*@Days,@MaxDate)
SET @Days=@Days-1
END

SELECT d.dateval,t.TestResult
FROM @Dates d
LEFT JOIN (SELECT date,AVG(TestResult*1.0) AS TestResult
           FROM YourTable
           GROUP BY date)t
ON t.date=d.dateval

Slight modification to visakh's solutin to give the expected result

change SET @Days =DATEDIFF(dd,@MinDate,@MaxDate)+1 to

SET @Days =DATEDIFF(dd,@MinDate,@MaxDate)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 04:05:20

[code]-- Prepare dummy or sample data here
DECLARE @Sample TABLE
(
dt DATETIME,
rslt INT
)

INSERT @Sample
SELECT '2008-12-01', 12 UNION ALL
SELECT '2008-12-03', 19 UNION ALL
SELECT '2008-12-03', 18 UNION ALL
SELECT '2008-12-03', 17 UNION ALL
SELECT '2008-12-07', 30 UNION ALL
SELECT '2008-12-07', 32

-- The solution starts here
;WITH Yak (theDate, maxDate)
AS (
SELECT MIN(dt),
MAX(dt)
FROM @Sample

UNION ALL

SELECT theDate + 1,
maxDate
FROM Yak
WHERE theDate < maxDate
)

-- Display the final resultset
SELECT dt AS theDate,
AVG(1.0E * rslt) AS theAverage
FROM (
SELECT dt,
rslt
FROM @Sample

UNION ALL

SELECT theDate,
NULL
FROM Yak
) AS d
GROUP BY dt
ORDER BY dt[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 04:12:53

I choose to use UNION ALL in favor of LEFT JOIN because of performance.
A NULL values doesn't add to the average anyway.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources