Database

--EXEC spCreateF0lder 'C:\ComName\Test\' ,'Test Folder 1'
CREATE PROCEDURE spCreateF0lder
  @DefaultPath varchar(MAX)
 ,@FolderName  VARCHAR(100)
AS
BEGIN

DECLARE @SqlScript VARCHAR (MAX)

SET @SqlScript = 'EXEC master.dbo.xp_cmdshell ''' + ' mkDir ' + @DefaultPath  + @FolderName + ''''


EXEC @SqlScript
Print @SqlScript

END
GO

BACKUP DATABASE TestDB
  TO DISK = 'e:\backups\TestDB.bak',
  WITH
    FORMAT, 
    MEDIANAME = 'TestDB_MediaSet1'

BACKUP LOG TestDB
  TO DISK = 'e:\backups\TestDB_log.bak'
  WITH
    FORMAT, 
    MEDIANAME = 'TestDB_MediaSet1'

USE master
  GO

  RESTORE DATABASE TestDB
    FROM DISK = 'e:\backups\TestDB.bak'
       
    WITH REPLACE

hvgyufg28fh38tyr78hf wrote:

Could Some one tell me , is this results are right for this search?

Punctuation is ignored.

Merry Christmas

SELECT     sum(CustomerAccountPayment.Amount + CustomerAccountPayment.FloatingAmount) as TotalPaid 
FROM         CustomerAccountPayment Inner JOIN
                      CustomerAccountPaymentDetail on CustomerAccountPayment.Id = CustomerAccountPaymentDetail.CustomerAccountPaymentID
Where CustomerAccountPayment.Void = 0
and CustomerAccountPayment.EntryDate BETWEEN @StartDate and @EndDate

SELECT        InvoicePayment.DateCreated AS InvoicePaymentDate,
 InvoicePayment.InvoiceID,
 InvoicePayment.Amount,
 Customer.Name, 
 Invoice.CustomerID,
 Invoice.InvoiceNumber
FROM InvoicePayment INNER JOIN
	Invoice ON InvoicePayment.InvoiceID = Invoice.ID INNER JOIN
	Customer ON Invoice.CustomerID = Customer.ID INNER JOIN
	PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID LEFT OUTER JOIN
	CustomerAccountPaymentDetail ON InvoicePayment.ID = CustomerAccountPaymentDetail.InvoicePaymentID
WHERE  Customer.ID = @CustomerID
	and (CustomerAccountPaymentDetail.ID IS NULL) 
	AND (Invoice.Void = 'False') 
	AND (PaymentMethod.IsCredit = 'True')
union
SELECT	InvoicePayment.DateCreated AS InvoicePaymentDate, 
	InvoicePayment.InvoiceID, 
	InvoicePayment.Amount - SUM(case CustomerAccountPayment.Void 
		when 'True' then 0 
		when 'False' then ROUND(CustomerAccountPaymentDetail.Amount, 2)
	end) AS Amount, 
	Customer.Name, 
	Invoice.CustomerID, 
	Invoice.InvoiceNumber
FROM InvoicePayment INNER JOIN
                      Invoice ON InvoicePayment.InvoiceID = Invoice.ID INNER JOIN
                      Customer ON Invoice.CustomerID = Customer.ID INNER JOIN
                      CustomerAccountPaymentDetail ON InvoicePayment.ID = CustomerAccountPaymentDetail.InvoicePaymentID INNER JOIN
                      PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID INNER JOIN
                      CustomerAccountPayment ON CustomerAccountPaymentDetail.CustomerAccountPaymentID = CustomerAccountPayment.ID
WHERE (Invoice.Void = 'False') 
	AND (PaymentMethod.IsCredit = 'True') 
	and Invoice.CustomerID = @CustomerID
GROUP BY InvoicePayment.DateCreated, InvoicePayment.InvoiceID, Invoice.InvoiceNumber, InvoicePayment.Amount, InvoicePayment.DateCreated, Customer.Name, 
                      Invoice.CustomerID
HAVING      (SUM(case CustomerAccountPayment.Void 
		when 'True' then 0 
		when 'False' then ROUND(CustomerAccountPaymentDetail.Amount, 2)
	end)  <> InvoicePayment.Amount)
ORDER BY Customer.Name

SELECT     InvoicePayment.DateCreated AS InvoicePaymentDate, 
InvoicePayment.InvoiceID, 
InvoicePayment.Amount, 
Customer.Name, 
Invoice.CustomerID, 
Invoice.InvoiceNumber
FROM         (Select * from CustomerAccountPayment where CustomerID = @CustomerID and EntryDate <=@EndDate) as CustomerAccountPayment INNER JOIN
                      CustomerAccountPaymentDetail ON CustomerAccountPayment.ID = CustomerAccountPaymentDetail.CustomerAccountPaymentID  RIGHT OUTER JOIN
                      InvoicePayment INNER JOIN
                      Invoice ON InvoicePayment.InvoiceID = Invoice.ID INNER JOIN
                      Customer ON Invoice.CustomerID = Customer.ID INNER JOIN
                      PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID ON CustomerAccountPaymentDetail.InvoicePaymentID = InvoicePayment.ID
WHERE (Customer.ID = @CustomerID) 
AND (CustomerAccountPaymentDetail.ID IS NULL) 
AND (Invoice.Void = 'False') 
AND (PaymentMethod.IsCredit = 'True') 
and (InvoicePayment.DateCreated <= @EndDate)
union
SELECT	InvoicePayment.DateCreated AS InvoicePaymentDate, 
	InvoicePayment.InvoiceID, 
	InvoicePayment.Amount - SUM(case CustomerAccountPayment.Void 
		when 'True' then 0 
		when 'False' then ROUND(CustomerAccountPaymentDetail.Amount, 2)
	end) AS Amount, 
	Customer.Name, 
	Invoice.CustomerID, 
	Invoice.InvoiceNumber
FROM InvoicePayment INNER JOIN
                      Invoice ON InvoicePayment.InvoiceID = Invoice.ID INNER JOIN
                      Customer ON Invoice.CustomerID = Customer.ID INNER JOIN
                      CustomerAccountPaymentDetail ON InvoicePayment.ID = CustomerAccountPaymentDetail.InvoicePaymentID INNER JOIN
                      PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID INNER JOIN
                      (Select * from CustomerAccountPayment where CustomerID = @CustomerID and EntryDate <=@EndDate) as CustomerAccountPayment ON CustomerAccountPaymentDetail.CustomerAccountPaymentID = CustomerAccountPayment.ID
WHERE (Invoice.Void = 'False') 
	AND (PaymentMethod.IsCredit = 'True') 
	and Invoice.CustomerID = @CustomerID
	and InvoicePayment.DateCreated <= @EndDate
GROUP BY InvoicePayment.DateCreated, InvoicePayment.InvoiceID, Invoice.InvoiceNumber, InvoicePayment.Amount, InvoicePayment.DateCreated, Customer.Name, 
                      Invoice.CustomerID
HAVING      (SUM(case CustomerAccountPayment.Void 
		when 'True' then 0 
		when 'False' then ROUND(CustomerAccountPaymentDetail.Amount, 2)
	end)  <> InvoicePayment.Amount)

loyal ginger wrote:

There are limitations on SQL Server Express.

Dmitry Khudorozhkov wrote:

First question: is this a good db structure?

Select *
From Users
 inner join Addresses on Addresses.UserID = User.ID
where Users.ID = @IDno