how to change null constraint into notnull constraint(Below is my table creation)

Question

USE [PumaLms]
GO

/****** Object:  Table [dbo].[TicketEntryMasters]    Script Date: 11/29/2012 17:57:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TicketEntryMasters](
    [TicketNumber] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [varchar](50) NULL,
    [IssueCategoryName] [varchar](50) NULL,
    [IssueName] [varchar](50) NULL,
    [Description] [varchar](50) NULL,
    [ContactPerson] [varchar](50) NULL,
    [ContactNumber] [varchar](50) NULL,
    [FileAttachements] [varchar](50) NULL,
    [FileAttachements1] [varchar](50) NOT NULL,
    [FileAttachements2] [varchar](50) NULL,
    [FileAttachements3] [varchar](50) NULL,
    [FileAttachements4] [varchar](50) NULL,
    [TicketID] [int] NULL,
 CONSTRAINT [UQ__TicketEn__CBED06DA0F624AF8] UNIQUE NONCLUSTERED
(
    [TicketNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[TicketEntryMasters]  WITH NOCHECK ADD  CONSTRAINT [n_null_test] CHECK  (([TicketID] IS NOT NULL))
GO

ALTER TABLE [dbo].[TicketEntryMasters] CHECK CONSTRAINT [n_null_test]
GO

ALTER TABLE [dbo].[TicketEntryMasters]  WITH NOCHECK ADD  CONSTRAINT [nn_null_test] CHECK  (([TicketID] IS NULL))
GO

ALTER TABLE [dbo].[TicketEntryMasters] CHECK CONSTRAINT [nn_null_test]
GO

Answer 1

SELECT NALOG1.GBRV, NALOG1.DATA,
Min(IIf(([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null)) AS pockm,
Max([NALOG1.KRAJKM]) AS krajkm,
Max([NALOG1.KRAJKM])-Min(IIf(([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null)) AS RAZLIKA,
Max(NOVI.DATAP) AS Poslprov,
Max(NOVI.KMP1) AS KMP,
Max(NOVI.DATAS) AS Poslserv,
Max(NOVI.KMS1) AS KMS,
Sum(NALOG1.km) AS SUMKM,
IIf(Sum(NALOG1.km)>=(([NOVI].[KMS1]+[NOVI].[KMS2])-1000) ,
'ZA SERVIS',
(IIf(Sum(NALOG1.KM)>=(([NOVI].[KMP1]+[NOVI].[KMP2])-1000),'PROVERKA',' '))) AS Zabeleska
FROM NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR GROUP BY NALOG1.GBRV, NALOG1.DATA, NOVI.KMP1, NOVI.KMP2, NOVI.KMS1, NOVI.KMS2, NOVI.AB HAVING (((NALOG1.GBRV)=[@gbr1])and ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) AND ((NALOG1.DATA)>=[@data1] And (NALOG1.DATA)<=[@data2]))

(I don't know what did you mean by XXXX) and the result was the same. So I don't get one row for a single date, but several.

Answer 2

try This



SELECT NALOG1.GBRV, NALOG1.DATA,
Min(IIf(([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null)) AS pockm,
Max([NALOG1.KRAJKM]) AS krajkm,
Max([NALOG1.KRAJKM])-Min(IIf(([NALOG1].[POCKM]>0),[NALOG1.POCKM],Null)) AS RAZLIKA,
Max(NOVI.DATAP) AS Poslprov,
Max(NOVI.KMP1) AS KMP,
Max(NOVI.DATAS) AS Poslserv,
Max(NOVI.KMS1) AS KMS,
Sum(NALOG1.km) AS SUMKM,
IIf(Sum(NALOG1.km)>=(([NOVI].[KMS1]+[NOVI].[KMS2])-1000) AS XXXXX  ,
'ZA SERVIS',
(IIf(Sum(NALOG1.KM)>=(([NOVI].[KMP1]+[NOVI].[KMP2])-1000),'PROVERKA',' '))) AS Zabeleska
FROM NALOG1 INNER JOIN NOVI ON NALOG1.GBRV = NOVI.GBR GROUP BY NALOG1.GBRV, NALOG1.DATA, NOVI.KMP1, NOVI.KMP2, NOVI.KMS1, NOVI.KMS2, NOVI.AB HAVING (((NALOG1.GBRV)=[@gbr1])and ((NOVI.AB)=[@ab1] or (NOVI.AB)=[@ab2]) AND ((NALOG1.DATA)>=[@data1] And (NALOG1.DATA)<=[@data2]))