SQLTeam.com | Weblogs | Forums

How to create this script


#1

I'm new to this and am looking for a bit of guidance, I am using Sage Act V18 - Using Microsoft SQL Server\MSSQL10_50.

I have a couple of fields within the database that are either true or false.

I want to be able to run a query from a report builder that says:

Between this date and that date:

There were this many new records created:

During this period this many Field1 = TRUE

Then repeat this for Field2 = TRUE

Then to present that in a report.

Can anyone suggest a script for this?

All fields are in main contact database.


#2

Create a stored procedure in your database, something like this:
CREATE PROCEDURE dbo.SomeStoredProcNameHere
@startDate DATE,
@endDate DATE
AS
SET NOCOUNT, XACT_ABORT ON;

	SELECT
		COUNT(*) AS RecordsCreated,
		SUM(CASE WHEN Field1 = 1 THEN 1 ELSE 0 END) AS CountOfField1,
		SUM(CASE WHEN Field2 = 1 THEN 1 ELSE 0 END) AS CountOfField2
	FROM
		YourTable
	WHERE
		YourDateColumn >= @startDate
		AND YourDateColumn < DATEADD(DAY,1,@endDate);
GO

Then use it in your client application like this:

 	@startDate = '20160101',
 	@endDate = '20160531';```

#3

Thank you, I will study this and see if I can make sense of it. :confounded::

Much appreciated.