Monthly Report using SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello friends,
Myself Prashant, I want to create

report for monthly basis.

I have created StoredProcedure which takes date, I want to pass days of month in loop to this SP.
Can anyone help me to know how to carry out this task, I'm really confused.

Thanks in advance

Posted 8-Dec-10 23:09pm

dhage.prashant01

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Toniyo Jackson · Answer 1 · 2010-12-08T23:16:00

Solution 1

Go through this,
http://msdn.microsoft.com/en-us/library/ms190782(SQL.90).aspx[^]
http://www.dotnetspider.com/resources/5750-Execute-stored-procedure-VB-NET.aspx[^]

Posted 8-Dec-10 23:16pm

Toniyo Jackson

Comments

dhage.prashant01 9-Dec-10 7:26am

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @ChkDate DATETIME

SET @StartDate = '1/10/2010'
SET @EndDate = '31/10/2010'
SET @ChkDate = '1/10/2010'

WHILE @ChkDate <= @EndDate
BEGIN
select MAX(actiondate) from tbl_action_history A
left outer join tbl_mechanicalbrief M on M.jobno = A.jobno and M.artworktype=1
left outer join tbl_iteration I on I.jobno=A.jobno and I.artworktype=2
where actionstatus in (55,56,20,33) and (actiondate between convert(datetime,''' + @ChkDate + '' 00:00:01.613',110) and convert(datetime,''' + @ChkDate + '' 23:59:59.613',110)) and A.jobtype not in (3,4,5,6,7,8)
END

OUTPUT:
Error: Conversion failed when converting date and/or time from character string.

-------------
How should proceed further. Please need solution

Toniyo Jackson 9-Dec-10 7:30am

Try this,
BEGIN
select MAX(actiondate) from tbl_action_history A
left outer join tbl_mechanicalbrief M on M.jobno = A.jobno and M.artworktype=1
left outer join tbl_iteration I on I.jobno=A.jobno and I.artworktype=2
where actionstatus in (55,56,20,33) and actiondate = @ChkDate and A.jobtype not in (3,4,5,6,7,8)
END

Toniyo Jackson 9-Dec-10 7:37am

Yes. Of course. Vote and accept the answer if its working.

dhage.prashant01 9-Dec-10 7:48am

I have tried your Solution, its giving me NULL output

I want to get last action time the QC head has taken in that day, so m using MAX(actiondate)

Toniyo Jackson 9-Dec-10 7:50am

Try only using the main table,
select MAX(actiondate) from tbl_action_history
where actiondate = @ChkDate

dhage.prashant01 9-Dec-10 7:52am

Actually that table contains 4 types of job details
i want only 2, so i have used joins