Displaying Null 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)

Displaying Null Values

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-28 : 15:10:00

I have a set of data for days of a Month , But on certain days there are no values recorded, I still would like to display them in my result set as NULL (if text) and 0 (If Int / Real). Can anyone please suggest suitable solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 15:13:54

How is your source data coming currently?

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-28 : 15:16:19

It has no records for the day and hence the result set would not display any data

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 15:17:50

Ok. Can i see your current query?

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-28 : 15:20:59

Hi Visak,

Its the same query as

SELECT
[ProductionDay]
,DATEPART(wk,StartTime) AS WeekCount
,(AVG([RNTDuration])) AS AvgRNT
,MIN([RNTDuration]) As LeastRNT
,MAX([RNTDuration]) As WorstRNT
,CASE WHEN [ProgramNo]=100 THEN dbo.fn_MinimumRNT(SubAssemblyNo,[Machine]) ELSE dbo.fn_MinimumRNT_sp([ProgramNo],[Machine],[ProductionDay]) END
,SUM([LoadingDuration]) AS LoadingTime
,[ProgramNo] AS ProgramNo
,COUNT(RNTID) AS PartsCount
,[SubAssemblyNo]
,[Sheetsize]
FROM [RPMS].[dbo].[List_MachineShopRNT]
where Machine = @iMachine and dateadd(month, datediff(month, 0, [StartTime]),0) = @dtMonthStartDate
Group by DATEPART(wk,StartTime), [ProductionDay],[Machine],[ProgramNo],[SubAssemblyNo],[Sheetsize]

Would like to Display all ProductionDays

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 15:22:26

Where is info about all Production Days stored?

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-28 : 15:26:27

You can now assume all Calender Days, How ever I would like to take your suggestion on how can I have such an Action Calender in my DB , I was thinking of having Non Production Days listed in a Table and Cross Join to eliminate from the Calender Days.

Suggestion would be highly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 15:28:32

Either you can have a table holding all the days with a bit field to indicate production days or you can just store non production days alone. More genric solution will be to create a full calendar table so that it can be used for all date related calculations.

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-28 : 15:39:01

Thanks Visakh,

I would go with your suggestion? and have the table as below:

ProductionDay smallDatetime (PK)
NoProduction Bit
ScheduledProdStart datetime
ScheduledProdEnd datetime
ScheduledDTstart datetime
ScdeduledDTEnd datetime

Now how do we move further?

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-28 : 15:44:06

Yes I can JOIN....

Sorry My question was , How do I populate the Calender days with default values for the month so that I can schedule a Job to run every month.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-28 : 15:49:24

Just use a function: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

coagulance
Yak Posting Veteran

78 Posts

Posted - 2008-04-28 : 15:58:13

Thank you , That was a real 'i' opener... Great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources