It is helpful at times to point an SQL statement to a month in time.
This task can easily be done with only the month and year of a particular date.
It is extraneous to code an SQL statement for a specific month and year and
for large transactional databases execution would simply take too long. Using a
simple variable in the statement allows a field date to be compared with the month
timeslice quite easily.
In the SQL Query Analyzer input the following and click Execute:
DECLARE @dtDate DATETIME;
SET @dtDate = '1/1/2012';
SELECT DATEADD(s,-0,DATEADD(mm, DATEDIFF(m,0,@dtDate)+0,0))
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))
The results are found to be:
2012-01-01 00:00:00.000 and
2012-01-31 23:59:59.000 respectively.
This represents the first month period in the year 2012
beginning at midnight of the first and running until the last second of the month
before the 24th hour of 2/1.
Let the Query Analyzer do the work. This technique works for leap years as well.
Simply change the date to 2/1/2012 and rerun.
Incorporating in a statement to glean all orders within the designated space in time we
can input:
DECLARE @dtDate DATETIME;
SET @dtDate = '1/1/2012';
SELECT o.ordernumber, o.orderdate, itm.itemid, itm.itemname,
count(oitm.linenumber) as totitems, sum(oitm.orderquantity) as totqty,
(CASE WHEN (SELECT traitvalue FROM itemtraits WHERE traitname = 'MID' AND itemid = itm.itemid) IS NULL THEN '' ELSE (SELECT traitvalue FROM itemtraits WHERE traitname = 'MID' AND itemid = itm.itemid) END) AS MFGID
FROM orders o
INNER JOIN orderitems oitm ON oitm.ordernumber = o.ordernumber
LEFT JOIN items itm ON itm.itemid = oitm.itemid
WHERE (o.orderdate >= DATEADD(s,-0,DATEADD(mm, DATEDIFF(m,0,@dtDate)+0,0))
AND o.orderdate <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0)))
GROUP BY o.ordernumber,o.orderdate,itm.itemid,itm.itemname
ORDER BY o.ordernumber
This technique allows a more dynamic method of selecting records in the month required.
The date required would be passed in via the user interface used whether web based or
application based.
SQL sorting can in itself be made dynamic as well, as this article in PHP demonstrates using MYSQL.
That technique can easily be translated into MSSQL as needed.
Join the Conversation