Date Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date Query

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-17 : 07:14:48

Hi

I have two regarding questions regarding queries involving dates.

I have a number of tables in the database i am using, one of which holds date periods of a financial year so it looks like something below:

DatePeriod:
ID, Begin, End
Jan08,01/01/2008,25/01/2008
Feb08,01/02/2008,29/02/2008
Mar08,03/03/2008,28/03/2008
...
...

The tables actual data begins from Jan07.

Another table shows transactions made, something which looks like:

Transactions:
ID, DateMade, CustomerID, ProductID
22,24/01/2008,111,222
23,27/01/2008,117,222
24,27/02/2008,011,233
...
...

The first query I am trying to do is to perform a count of all transactions made during a financial month period as specified by the user. So i guess I would use a variable such as @month which links to the DatePeriod table. This is what I'm not sure on how to do.

The second query is performing a count of all the transactions made within the last 12 months of the month specified by the user.

Can anyone help with this?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-17 : 15:41:41

SELECT COUNT(*)
FROM Transactions t
INNER JOIN DatePeriod d
ON t.DateMade BETWEEN d.Begin AND d.End
WHERE d.ID=@Period

you pass the period through @Period and thie gives total number of trasactions.

Second query will be

SELECT COUNT(*)
FROM Transactions t
INNER JOIN DatePeriod d
ON t.DateMade BETWEEN d.Begin AND d.End
WHERE d.ID>DATEADD(mm,-12,'01 ' + @Period)

Assuming your period is in month year format like Jan 07,..

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-19 : 08:32:33

Hi

Thanks for that!!!

In regards to the second query, The ID column is a nvarchar datatype, where the value will be typed in as Jan07 and not a timedate datatype.

I was ideally trying to do a Between statement where the Yearly value would be found between dates of EndDate of the month in question and StartDate of the month of a year back.

For example if i chose the ID of April08 the EndDate would be 27/04/2008 and going back 12 months including April so it would be starting for May07 which is 01/05/2007. So the query would be doing something similar to DateMade Between 01/05/2007 AND 27/04/2008.

Does anyone know if this is possible because I am totally stuck at the moment?

Thanks

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-05-19 : 09:10:48

Sorry my mistake, the ID field will also be a DateTime datatype.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources