Query without using cursor - 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)

Query without using cursor

Author

Topic

mrajani
Starting Member

13 Posts

Posted - 2007-12-10 : 18:24:39

Hi,

I have a table with the following fields

purchasedate itemcode

I want to generate a report which shows the last 18 weeks purchases itemwise.

Week starting is from Monday. I had all the 18 weeks fromdate and todate for each week.

How can I write a query without using cursors for each item and week.

I need to insert these values into a seperate temporary table which is only used for reports.

How can I give condition for all 18 weeks and count for each item for a week?

thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-10 : 18:26:04

Please post a data example to make your problem clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 18:41:56

Something similar to

SELECT wt.FromDate, wt.ToDate, COUNT(dt.Date)
FROM WeekTable AS wt
LEFT JOIN DataTable AS dt ON dt.Date BETWEEN wt.FromDate AND wt.ToDate
GROUP BY wt.FromDate, wt.ToDate

E 12°55'05.25"
N 56°04'39.16"

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-12-11 : 09:36:19

You should also have the itemcode, try:
CREATE TABLE #items
(itemcode INT,
purchaseDate DATETIME)

CREATE TABLE #weekData
(FROM_date DATETIME,
TO_date DATETIME)

INSERT INTO #items
SELECT 1 , '10 May 2007'
UNION ALL
SELECT 1, '10 May 2007'
UNION ALL
SELECT 2, '10 May 2007'
UNION ALL
SELECT 2, '22 May 2007'
UNION ALL
SELECT 2, '23 May 2007'

INSERT INTO #weekData
SELECT '8 May 2007' , '15 May 2007'
UNION ALL
SELECT '21 May 2007' , '28 May 2007'

SELECT wt.From_Date, wt.To_Date, COUNT(dt.PurchaseDate) as 'count',dt.ItemCode
FROM #WeekData AS wt
LEFT JOIN #items AS dt ON dt.purchaseDate BETWEEN wt.From_Date AND wt.To_Date
GROUP BY wt.From_Date, wt.To_Date,dt.itemcode

DROP TABLE #items
DROP TABLE #weekData

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources