How to do a SQL NOT NULL with a DateTime?

PROBLEM

---

How to do a simple DateTime with a NOT NULL?

I want to do something like this:

SELECT * FROM someTable WHERE thisDateTime IS NOT NULL

But that obviously doesn't work. I am using MS SQL 2005 if that matters. Thank you.

SOLUTION

---

erm it does work? I've just tested it?

/****** Object:  Table [dbo].[DateTest]    Script Date: 09/26/2008 10:44:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DateTest](
    [Date1] [datetime] NULL,
    [Date2] [datetime] NOT NULL
) ON [PRIMARY]

GO
Insert into DateTest (Date1,Date2) VALUES (NULL,'1-Jan-2008')
Insert into DateTest (Date1,Date2) VALUES ('1-Jan-2008','1-Jan-2008')
Go
SELECT * FROM DateTest WHERE Date1 is not NULL
GO
SELECT * FROM DateTest WHERE Date2 is not NULL

How to set a check on SQL Server?

PROBLEM

---

I need to add table called group with a column called code

How do I add a check constraint to the column so it will only allow the following alphabetic characters (D, M, O, P or T) followed by 2 numeric characters.

SOLUTION

---

simple check constraint is all you need

create table blatest(code char(3))

alter table blatest add  constraint ck_bla 
check (code like '[DMOPT][0-9][0-9]' )
GO

test

insert blatest values('a12') --fails
insert blatest values('M12')  --good
insert blatest values('D12') --good
insert blatest values('DA1') --fails

If you need it to be case sensitive then you have to create the constraint like this

alter table blatest add  constraint ck_bla 
check (code like '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS )
GO

D12 will succeed but d12 will not in that case

How to get total sum

PROBLEM

---

I have two tables.

Sales
------

ID    Charge    VAT
1     100      10
2     200      20


SaleProducts
------------

ID  Product    Auto   SalesID
1   aa         True   1 
2   bb         False  1

I want to get this

SaleOnProduct
-------------

ID    Product     Charge     VAT    Total   TotalAmount(All of total is plus)
1     aa          100        10     110     220  
2     aa          100        10     110     220

How can I do this. Please help me.

SOLUTION

---

declare @Sales table (ID int, Charge int, VAT int)
declare @SaleProducts table (ID int, Product char(2), Auto bit, SalesID int)

insert into @Sales values
(1, 100, 10),
(2, 200, 20)

insert into @SaleProducts values
(1, 'aa', 1, 1),
(2, 'bb', 0, 1)

select 
  SP.ID, 
  SP.Product,
  S.Charge,
  S.VAT,
  S.Charge+S.VAT as Total,
  sum(S.Charge+S.VAT) over() as TotalAmount
from @Sales as S
  inner join @SaleProducts as SP
    on S.ID = SP.SalesID