Adding Constants to a query - 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

Adding Constants to a query

Author

Topic

Pregz
Starting Member

2 Posts

Posted - 2012-03-22 : 10:01:19

Hi Guys

I have following output

Yesr Month DayID Day Count Number Cost
2012 2 3 03/02/2012 228 42467 (R30) 6840
2012 2 6 06/02/2012 307 42467 (R30) 9210
2012 2 7 07/02/2012 86 42467 (R30) 2580
2012 2 9 09/02/2012 970 42467 (R30) 29100
2012 2 10 10/02/2012 2715 42467 (R30) 81450

using SQL :

select YEAR(sentdate) Year,
MONTH(sentdate) Month,
DAY(sentdate) DayID,
CONVERT(varchar,sentdate,103) as Day,
COUNT( *) as SMSsent,
case MSISDN when '42467' then MSISDN+' (R30)' end as MSISDN,
case MSISDN when '42467' then Cast((COUNT(*)*30) as float) end as Cost
from tblMobileMessages
where GatewayType like '%simfarm%' and MSISDN in ('42467') and SendStatus=2
and datediff(mm,sentdate,getdate())<=1
group by year(sentdate), MONTH(sentdate),CONVERT(varchar,sentdate,103) ,DAY(sentdate),MSISDN
order by year(sentdate), MONTH(sentdate),Day(sentdate)

I want to alter this query or find a better way to include constant values in the output i.e i would like to include a record for every day even if there is no data in the database e.g

2012 2 2 02/02/2012 228 42467 (R30) 6840
2012 2 3 03/02/2012 0 42467 (R30) 0
2012 2 4 04/02/2012 0 42467 (R30) 0
2012 2 5 05/02/2012 0 42467 (R30) 0
2012 2 6 06/02/2012 307 42467 (R30) 9210
2012 2 7 07/02/2012 86 42467 (R30) 2580
2012 2 9 09/02/2012 970 42467 (R30) 29100
2012 2 10 10/02/2012 2715 42467 (R30) 81450

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-22 : 10:28:06

One easy solution is to create a Date/Calendar table and join to that. You could also do the same with the function or CTE, but I'd suggest a table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources