Weekly report running into next month-problem

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)

Weekly report running into next month-problem

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-04-23 : 18:16:11

Hi friends

i have a table with the follwoing fields
customer_number customer_name visit_date.

i have to create a report of who all visited our business centre weekly
on every Monday. Everything seems to be running fine until this month end of april where the Monday is on may 3 and the week before includes
may 1(sat) and may 2(sun).

So this monday may 3 i need to create two reports from april 26 to 30 and the other one from may 1 to 3 second.

i want this to happen in a single stored procedure when the week goes into the next month two reports should be created or else only one report for that week in the month should be created based on the vist_date field.

thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 01:13:30

do you mean something like this?

SELECT DATENAME(mm,visit_date),
COUNT(customer_name) AS CustCnt
FROM table
WHERE visit_date >= DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0)
AND visit_date < DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
GROUP BY DATENAME(mm,visit_date)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-04-30 : 00:00:46

thanks for your response visakh ....
basically my problem is i want to send the info from the table to my supervisor in two reports...if the week runs into a new month
so i want to use the same procedure with if conditions to run it twice
if the report runs into a new month....
how can i find that out.. the report is to be run evry monday....

so this monday may 3 i need to create two separate reports from april 26 to 30 and the other one from may 1 to 3 second.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-30 : 02:19:27

but wont my query still give data for more than one month?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-05-07 : 14:16:47

hi visakh

sorry visakh by reports i meant two different excel reports that i need to send to my supervisor using the bcp utility.

the excel report gets the info from the table so

i have to run the bcp utility twice ...

once for the visit_date having same month and
again for the visit_date running into the second month
how would i do this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-07 : 14:20:51

for that you need two separate bcp statements with passing correct month date for both times. and take corresponding data from temp table which will be populated by the above query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources