return default values - 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)

return default values

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-07-05 : 13:19:38

Hi

I have a query that return the sum of numbered items within a week range, but since there might not be any items ordered all of the day, there maybe a result like this...

Qty DayOfWeek
12 1
23 4

Is there a way to get default value 0 for the days that there is nothing ordered so I get a result like this..

Qty DayOfWeek
12 1
8 2
9 3
23 4
45 5
2 6
88 7

My query looks like this...


SELECT     SUM(Qty) AS Qty, DATENAME(dd, DateAdded) AS TheDay
FROM         dbo.tbl_test
WHERE     (DateAdded BETWEEN CONVERT(DATETIME, '2009-06-28', 102) AND CONVERT(DATETIME, '2009-07-05', 102))

GROUP BY DATENAME(dd, DateAdded)
Order By DATENAME(dd, DateAdded)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 13:29:09

i think what you've shown in query is daywise total for period rather than weekday wise. then you need below:-

DECLARE @StartDate datetime,@EndDate datetime
SELECT @StartDate='2009-06-28',@EndDate='2009-07-05'

SELECT t.Day,ISNULL(t1.Qty,0) AS Qty
FROM
(SELECT DATEADD(dd,number,@StartDate) AS Day
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@StartDate)<=@EndDate
)t
LEFT JOIN
(
SELECT     SUM(Qty) AS Qty, DATEPART(dd, DateAdded) AS TheDay
FROM         dbo.tbl_test
WHERE     (DateAdded BETWEEN @StartDate AND @EndDate)
GROUP BY DATEPART(dd, DateAdded)
)t1
ON t1.TheDay=t.Day
Order By t.Day

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-05 : 14:37:09

And to use the index on master..spt_values table

~~AND DATEADD(dd,number,@StartDate)<=@EndDate~~

AND Number < DATEDIFF(DAY, @StartDate, @EndDate)

Microsoft SQL Server MVP

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-07-05 : 14:53:43

Hi

Not sure whats wrong, but I get no results at all in the qty column for any of the days. I know that there should be results other than zero...

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-07-05 : 17:00:04

Anyone got any ideas, I'm lost...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-05 : 19:14:07

post the query that you use

KH
[spoiler]Time is always against us[/spoiler]

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-07-05 : 23:53:13

Here it is, it's the same structure


DECLARE @StartDate datetime,@EndDate datetime
SELECT @StartDate='2009-06-28',@EndDate='2009-07-05'

SELECT t.Day,ISNULL(t1.Qty,0) AS Qty
FROM
(SELECT DATEADD(dd,number,@StartDate) AS Day
FROM master..spt_values
WHERE type='p'
--AND DATEADD(dd,number,@StartDate)<=@EndDate
AND Number < DATEDIFF(DAY, @StartDate, @EndDate)

)t
LEFT JOIN
(
SELECT     SUM(Qty) AS Qty, DATEPART(dd, DateAdded) AS TheDay
FROM         dbo.tbl_Test
WHERE     (DateAdded BETWEEN @StartDate AND @EndDate)
GROUP BY DATEPART(dd, DateAdded)
)t1
ON t1.TheDay=t.Day
Order By t.Day

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 00:00:27

you are not comparing the same thing. You are comparing Date with the Day of Month.
Change to


(
	SELECT DATEPART(DAY, DATEADD(dd,number,@StartDate)) AS Day
	FROM master..spt_values
	WHERE type='p'
	AND Number < DATEDIFF(DAY, @StartDate, @EndDate)
)t

KH
[spoiler]Time is always against us[/spoiler]

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-07-06 : 00:05:51

Ahh, that did it. Thanks a lot!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 00:12:58

Just a note, If your date range is span more than 1 month, you will be summing up the same day qty for 2 different month. Unless this is what you desired.

KH
[spoiler]Time is always against us[/spoiler]

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-07-06 : 03:37:24

If I would like to display the actual date instead of the day number, what would I need to change?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 03:45:53

[code]
DECLARE @StartDate datetime,@EndDate datetime
SELECT @StartDate='2009-06-28',@EndDate='2009-07-05'

SELECT t.Day,ISNULL(t1.Qty,0) AS Qty
FROM
(
SELECT DATEADD(dd,number,@StartDate) AS Day
FROM master..spt_values
WHERE type='p'
AND Number < DATEDIFF(DAY, @StartDate, @EndDate)
)t
LEFT JOIN
(
SELECT SUM(Qty) AS Qty, DateAdded AS TheDay
FROM dbo.tbl_Test
WHERE (DateAdded BETWEEN @StartDate AND @EndDate)
GROUP BY DateAdded
)t1
ON t1.TheDay=t.Day
Order By t.Day
[/code]

KH
[spoiler]Time is always against us[/spoiler]

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-07-06 : 03:48:20

But that give me no result in the qty column even if there are values there..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 03:59:28

does your DateAdd contain time ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 04:01:03

[code]
SELECT t.Day,ISNULL(t1.Qty,0) AS Qty
FROM
(
SELECT DATEADD(dd,number,@StartDate) AS Day
FROM master..spt_values
WHERE type='p'
AND Number < DATEDIFF(DAY, @StartDate, @EndDate)
)t
LEFT JOIN
(
SELECT SUM(Qty) AS Qty, dateadd(day, datediff(day, 0, DateAdded), 0) AS TheDay
FROM dbo.tbl_Test
WHERE DateAdded >= @StartDate
AND DateAdded < dateadd(day, 1, @EndDate)
GROUP BY dateadd(day, datediff(day, 0, DateAdded), 0)
) t1
ON t1.TheDay=t.Day
Order By t.Day
[/code]

KH
[spoiler]Time is always against us[/spoiler]

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-07-06 : 04:49:29

I'm not sure this is the right forum or excactly what is wrong, when I run this query in sql server manager it work fine, but if I retrieve the values from my web application the qty column is always 0, and the timepart of the day column is always the current time. Anyone got any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-06 : 04:54:13

How are you executing the query from your web app?

Microsoft SQL Server MVP

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-06 : 04:54:29

your variable @StartDate and @EndDate contain time ?

use this to remove the time part


select @StartDate = dateadd(day, datediff(day, 0, @StartDate), 0)

KH
[spoiler]Time is always against us[/spoiler]

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-07-06 : 05:05:07

Yes, and that made it work, thanks I really appreciate it!

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-07-21 : 02:36:08

There seem to be something wrong with this query, it does not sum the total number for each day. I get results like this..

Day Qty
2009-07-15 = 0
2009-07-16 = 1
2009-07-16 = 1
2009-07-16 = 1
2009-07-16 = 5
2009-07-16 = 13
2009-07-16 = 4
2009-07-17 = 1
2009-07-18 = 1
2009-07-19 = 0
2009-07-20 = 1
2009-07-21 = 0

When I really should get result like this..

2009-07-15 = 0
2009-07-16 = 25
2009-07-17 = 1
2009-07-18 = 1
2009-07-19 = 0
2009-07-20 = 1
2009-07-21 = 0

This is the query I use...


@FromDate DateTime,
@ToDate DateTime
AS
BEGIN

SET NOCOUNT ON;
	
	
DECLARE	@Temp SMALLDATETIME

-- If @FromDate is later than @ToDate, switch the two dates
IF @FromDate > @ToDate
	SELECT	@Temp = @FromDate,
		@FromDate = @ToDate,
		@ToDate = @Temp

-- Remove the time portion and set @ToDate to one day later
SELECT	@FromDate = DATEADD(DAY, DATEDIFF(DAY, '19000101', @FromDate), '19000101'),
	@ToDate = DATEADD(DAY, DATEDIFF(DAY, '18991231', @ToDate), '19000101')	


SELECT t.Day,ISNULL(t1.Qty,0) AS Qty
FROM
(
    SELECT DATEADD(dd,number,@FromDate) AS [Day]
    FROM master..spt_values
    WHERE type='p'
    AND Number < DATEDIFF(DAY, @FromDate, @ToDate)
)t
LEFT JOIN
(
    SELECT     SUM(Qty) AS Qty, dateadd(day, datediff(day, 0, DateAdded), 0) AS TheDay
    FROM       dbo.tbl_Test
    WHERE      DateAdded  >= @FromDate
    AND        DateAdded  < dateadd(day, 1, @ToDate)
    GROUP BY   DateAdded
) t1
ON t1.TheDay=t.Day
Order By t.Day

Why is it not sum up correct?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 03:15:05

[code]
SELECT SUM(Qty) AS Qty, dateadd(day, datediff(day, 0, DateAdded), 0) AS TheDay
FROM dbo.tbl_Test
WHERE DateAdded >= @FromDate
AND DateAdded < dateadd(day, 1, @ToDate)
GROUP BY ~~DateAdded~~ dateadd(day, datediff(day, 0, DateAdded), 0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources