Need default values to appear in select statement

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

SQL Server Development (2000)

Need default values to appear in select statement

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-01-14 : 12:19:19

My sql is below. What I am trying to do is run a sum each day. If there is nothing for that date I need to still put the market with a 0.

Right now though obviously if there is no match there is not entry.

SELECT     market, 
CASE WHEN dateentered = '1/5/2010' 
then abs(sum(case when Billingrate = 0 THEN billingflatfee ELSE (billingrate * homes) / 1000 END))
When dateentered <> '1/5/2010'
Then 0 END as Total

FROM       tblContractDetail
WHERE     market in ('BOS', 'PHO') and dateentered = '1/5/2010' 
GROUP BY Market,  dateentered

So in this case with date 1/5/2010 i want to see BOS $100 and PHO $0 but only get BOS $100.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 12:24:02

for what you're asking you need to use a calendar table as master and then left join your table to that and then display output for all dates after converting nulls (no data dates) to 0

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 13:04:37

"you need to use a calendar table as master"

Or just a Tally / Numbers table with DATEADD() on to the "base" date for the report?

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-01-14 : 13:47:31

Thanks guys.

I ended up creating a view off a MasterCalender and having a field on whether data existed. And then joined that up to my original table with my case statement and it works.

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-01-14 : 16:54:43

ok what I thought I had is giving me dups.

What I think I need to do I can't seem to do -


SELECT  distinct
                      'BOS' AS Fran, CONVERT(varchar(10), dbo.MC_CalendarMaster.cmDate, 101) as MyDate,
       CASE CONVERT(varchar(10), dbo.MC_CalendarMaster.cmDate, 101)
         WHEN CONVERT(varchar(10), dbo.tblContractDetail.DateEntered, 101) and franchiseid = 'bos' 
	 then 1
         else 0
      END


FROM         dbo.tblContractDetail right OUTER JOIN
                      dbo.MC_CalendarMaster ON CONVERT(varchar(10), dbo.tblContractDetail.DateEntered, 101) = CONVERT(varchar(10), dbo.MC_CalendarMaster.cmDate, 101)
order by mydate

Basically in my when clause I am trying to when 2 items which I can't.

If I don't whenever I get an to where it puts a 1 or 0 I get both.

This seems to simple to have such tunnel vision on.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-15 : 00:09:43

dont convert dates to varchar for getting them in format you want. this can be very easily done at front end using formatting functions..

also didnt get what you mean by dups.do you mean you've multiple records existing for a date for same market?


SELECT  distinct
                      'BOS' AS Fran, DATEADD(dd,DATEDIFF(dd,0,dbo.MC_CalendarMaster.cmDate),0) as MyDate,
       CASE WHEN DATEADD(dd,DATEDIFF(dd,0,dbo.tblContractDetail.DateEntered),0) is not null and franchiseid = 'bos' 
	 then 1
         else 0
      END


FROM          dbo.MC_CalendarMaster 
LEFT JOIN dbo.tblContractDetail                  

ON DATEADD(dd,DATEDIFF(dd,0,dbo.tblContractDetail.DateEntered),0) =
DATEADD(dd,DATEDIFF(dd,0,dbo.MC_CalendarMaster.cmDate),0)
order by mydate

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-01-15 : 09:43:48

Thanks.

So when I say duplicates this is what I mean. If I run that for instance in the returned data I see Bos, 1-7-2010, 0 and Bos, 1-7-2010, 1.

That is something I dont want to happen. The thing is Bos doesn't have any entries in tblcontractdetail. So only Bos, 1-7-2010, 0 should return. I looked into the table further and there are entries in tblcontractdetail for franchiseid pho and fbb. So i think thats how the Bos, 1-7-2010, 1 gets in there and then the Bos, 1-7-2010, 0 because there are no entries for Bos. Thats why I was trying to limit in my case to only check for franchiseid bos.

Some dates come out perfect but it seems the dates that have other franchiseid's for that date screw up the set.

let me know if I am not making sense.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 10:27:24

Move this to a WHERE clause...

and franchiseid = 'bos'

and try like

SELECT  distinct
                      'BOS' AS Fran, DATEADD(dd,DATEDIFF(dd,0,dbo.MC_CalendarMaster.cmDate),0) as MyDate,
       CASE WHEN DATEADD(dd,DATEDIFF(dd,0,dbo.tblContractDetail.DateEntered),0) is not null 
	 then 1
         else 0
      END
FROM          dbo.MC_CalendarMaster 
LEFT JOIN dbo.tblContractDetail                  
ON DATEADD(dd,DATEDIFF(dd,0,dbo.tblContractDetail.DateEntered),0) =
DATEADD(dd,DATEDIFF(dd,0,dbo.MC_CalendarMaster.cmDate),0)
where dbo.tblContractDetail.franchiseid = 'bos'
order by mydate

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-01-15 : 10:35:21

That gives me no duplicates but puts a 1 at the end of every entry which is not accurate.

The case really is for if the date exists put a 1. If there is dateentry in tblcontractdetail I still need the date from mastercalender but a 0 next to it.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 10:51:53

Please show us some sample data and the expected output..

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-01-15 : 11:00:23

[code]
sample tblcontractdetail -

ConID, dateentered, franchiseid
1, 1/7/2010, bos
2, 1/8/2010, pho
3, 1/8/2010, fbb
4, 1/9/2010, bos
5, 1/8/2010, pho

mastercalender
- just holds a date for every day in year.
[/code]

Output with last post puts out bos, date, 1 for every day up through today.

Before the last suggestion I would get this -

bos, 1/7/2010, 1
bos, 1/8/2010, 0
bos, 1/8/2010, 1
bos, 1/9/2010, 1
bos, 1/9/2010, 0

what I want is -

bos, 1/7/2010, 1
bos, 1/8/2010, 0
bos, 1/9/2010, 1

Does that clear it up a little?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 11:24:26

Ohh yeah got it now...try this..

select 'bos'
,DATEADD(dd,DATEDIFF(dd,0,a.cmDate),0) as MyDate
,CASE WHEN DATEADD(dd,DATEDIFF(dd,0,b.DateEntered),0) is not null
	 then 1 else 0
END
from dbo.MC_CalendarMaster a
left join dbo.tblContractDetail b on DATEADD(dd,DATEDIFF(dd,0,b.DateEntered),0) = DATEADD(dd,DATEDIFF(dd,0,a.cmDate),0) and b.franchiseid = 'bos'
order by MyDate

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-01-15 : 11:30:57

That works but the 1, 0 portion is going off anything in tblcontratdetail instead of only doing 1,0 if a dateentered value is for franchiseid = 'bos'

So for instance ...
if dataset is -

1, 1/7/2010, bos
2, 1/8/2010, pho
3, 1/10/2010, bos

I get -

bos, 1/7/2010, 1
bos, 1/8/2010, 1
bos, 1/9/2010, 0
bos, 1/10/2010, 1

Which is almost perfect except bos, 1/8/2010, 1 should be bos, 1/8/2010, 0 since bos does not have an entry that day.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 11:43:11

I get proper results...here the sample data I built and the results..

declare @t table
(ConID int, DateEntered datetime, franchiseid varchar(10))
insert @t
select 1, '1/7/2010', 'bos'
union all select 2, '1/8/2010','pho'
union all select 4, '1/10/2010', 'bos'


declare @cal table (cmDate datetime)
insert @cal
select '1/7/2010'
union all select '1/8/2010'
union all select '1/9/2010'
union all select '1/10/2010'

Query

select 'bos'
,DATEADD(dd,DATEDIFF(dd,0,a.cmDate),0) as MyDate
,CASE WHEN DATEADD(dd,DATEDIFF(dd,0,b.DateEntered),0) is not null
	 then 1 else 0
END
from @cal a
left join @t b on DATEADD(dd,DATEDIFF(dd,0,b.DateEntered),0) = DATEADD(dd,DATEDIFF(dd,0,a.cmDate),0) and b.franchiseid = 'bos'
order by MyDate

Result

Fran MyDate                  flag
---- ----------------------- -----------
bos  2010-01-07 00:00:00.000 1
bos  2010-01-08 00:00:00.000 0
bos  2010-01-09 00:00:00.000 0
bos  2010-01-10 00:00:00.000 1

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-01-15 : 11:53:05

You are right.

I just had to format the date when I looking at the other data set to authenticate it.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 11:58:44

Ok..So the results are fine now?

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2010-01-15 : 12:08:03

Yea everything is great.

Thanks for you help. Appreciate it!