monthly report - 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 2000 Forums

SQL Server Development (2000)

monthly report

Author

Topic

cubejaya
Starting Member

7 Posts

Posted - 2006-01-25 : 22:21:10

i want to produce a report based on the month and year selected. any ideas?

pls

ive tried so many, but none work :(

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-25 : 22:28:57

You have to provide more information.

What are you trying to do ?
What problem are you facing ?
Post your table DDL,
Some Sample Data & Expected Result

----------------------------------
'KH'

cubejaya
Starting Member

7 Posts

Posted - 2006-01-25 : 22:37:51

im using vb 6, have to select month and year, then i want all data within that month to shown on the flexgrid.

ive tried;

'SELECT *
'From orders
'WHERE order_date between to_date ('01/01/2000', 'dd/mm/yyyy')
'AND to_date ('30/01/2000', 'dd/mm/yyyy');

'SELECT DISTINCTROW DAILY.Date, DAILY.ItemName AS [Item Name], Sum(DAILY.Quantity) AS [Unit Sold], DAILY.Price AS [Price Per Unit], Sum(DAILY.Total) AS Total
'From DAILY
'WHERE (((DAILY.Date) Between Date()+"30" And Date()-"7"))
'GROUP BY DAILY.Date, DAILY.ItemName, DAILY.Price;

'SQL = "SELECT * FROM REQUEST WHERE date BETWEEN (month.date) = cboBulan.text AND (year.month) = cboYear.text"

'GROUP BY month.[Date By Month], month.priceCode, month.medService, month.Year
'HAVING (((month.[Date By Month])=cboBulan.Text) AND ((month.Year)=cboYear.Text));

'SQL = "SELECT * FROM REQUEST WHERE dateOut between 01/01/2006 AND 31/01/2006"

'SQL = "select * from REQUEST where dateOut BETWEEN '01/01/2000' AND '31/01/2000'" 'set as date
'SQL = "select * from REQUEST where dateOut BETWEEN dateOut('01/01/2000', 'dd/mm/yyyy') AND dateOut('30/01/2000', 'dd/mm/yyyy')"

SQL = "select * from REQUEST where dateOut between ('" & (Me.Text10.Text + Me.Text12.Text + Me.Text9.Text + Me.Text12.Text + Me.cboYear.Text) & "') AND ('" & (Me.Text11.Text + Me.Text12.Text + Me.Text9.Text + Me.Text12.Text + Me.cboYear.Text) & "')"

'dateOut = Format(dateOut, "dd/mm/yyyy")

'SQL = "select * from REQUEST where dateOut having ('" & ([dateOut, mm] = cboBulan.Text) & "') AND ('" & ([dateOut, yyyy] = cboYear.Text) & "')"

'SQL = "select * from REQUEST where date between ('" & Me.Text10.Text & Me.cboBulan.Text & Me.cboYear.Text & "') and ('" & Me.Text11.Text & Me.cboBulan.Text & Me.cboYear.Text & "')"

'Dim First_day As Parameter
'SQL = "select * from REQUEST where date between First_day(January(Sysdate, -1)) and Last_day(January(Sysdate, -1))"

rs.Open SQL, cn, adOpenKeyset, adLockOptimistic

BUT none work as supposed to be

i want a simple and easy enough syntax, pls

cubejaya
Starting Member

7 Posts

Posted - 2006-01-25 : 22:41:19

what i expect is that

the grid will show all dateOut, userName, itemName and amount

//
17/01/2006 linda a 1
18/01/2006 linda b 2
//

my table

userName
itemName
amount
date

allset as text

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-25 : 23:02:21

>> allset as text
the date column is a varchar ? what is the format of data in the date column ?

Suggestion :
try to run your query via the Query Analyser to confirm the query before coding in VB

----------------------------------
'KH'

cubejaya
Starting Member

7 Posts

Posted - 2006-01-25 : 23:23:52

i did set date as date/time, but this even gave me problem.. it says data type mismatch.. but when i change it to text, it could read but not as it supposed to, ya know.. what i mean, when it supposed to go for january, it shows february..

this is getting complicated, do you have any others idea how to generate monthly report?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-26 : 11:35:31