SQL Server (T-SQL)

From a recent email: 

I am trying to use SmartList Builder to build a query where a calculation is required showing the number of days an A/R transaction is past due.  Obviously, I have the DOCDATE from RM20101 but I need a column that tells me the current date so I can make this comparison…and I am not sure if there is a SQL Server table that I can pull in that shows this information to me.  I am pretty certain that there is not a GP table with current date in it just sitting there by default.

Answer:

Select getdate(), convert(varchar(10),getdate(),101)

Getdate() returns a date/time, the second part will strip the time

After issuing this command:

 


DROP index drive.ix_drives

I get this message:

An explicit DROP INDEX is not allowed on index 'drive.ix_drives'. It is being used for UNIQUE KEY constraint enforcement.

Then answer was to issue this command: 

 


ALTER TABLE drive DROP CONSTRAINT ix_drives

From a recent email:

Steve,
 
Need to run a query in SQL to find customers who have not purchased a particular product.  In this case “FNADE*”
 
I can easily query customers who have purchased a particular or range of products, but never had to query a list of customers who have not purchased a particular product…
 
Can you get me going in the right direction?
 
Thanks.

Answer:

This is a tricky one. There are several ways to do it, but this seems simplest:

First, get a list of customers that HAVE gotten a product

      select distinct custnmbr
            from sop30200 h
                  join sop30300 l on l.sopnumbe = h.sopnumbe and h.soptype = l.soptype
            where itemnmbr like 'SERVICE, HOURLY'

select custnmbr
      from rm00101 r
      where custnmbr not in (
      select distinct custnmbr
            from sop30200 h
                  join sop30300 l on l.sopnumbe = h.sopnumbe and h.soptype = l.soptype
            where itemnmbr like 'SERVICE, HOURLY'
      )