Stock report

[adyck]

Hello Forum! Here is my problem: I have a table called TblArticle (artnr, comment) and a table called TblArticlemoves (autoid, artnr, quantity, date). Now I need a report that looks like the following example: artnr ....|01.11.07 | 02.11.07 | 03.11.07 | 04.11.07 | 05.11.07 | ... A ........| 20 ........| 20 .........| 35.......... | 45 ........| .........15 | ... B........ | 5 ..........| 15......... |.25 ..........| 5 ......... | ..........20 | ... . . . The main thing is I need to display each day of a month, but I 'm not having an entry for each day of a month in my table TblArticlemoves. Therefore i need to calculate the stock quantity for each day of the month. How can i do this? regards adyck

[Prateek G]

you can loop through all the days of months and store count of moves in another table.

[SimulationofSai]

Doing this with straightforward SQL might get messy and complex. If you're up to it, create a temp table, fill in the days of the month and the stock value and PIVOT it to form the report.

SG Cause is effect concealed. Effect is cause revealed.

[Prateek G]

u can create a proc with following steps which will return a table.. 1) Get no. of days in month. say @no and @p = 1 2) while (@p < @no) 3) Get date and write query GetDate[^] 4> create table with date and count and return it. Lemme know if it is useful...

[adyck]

Hello Prateek G, excuse me, I'm a beginner but 1)what do you mean with @p? 3)can You give me some more hints 4)do You mean selecting the count from TblArticlemoves into a Date table? Thank You! Regards adyck

[adyck]

Hi SG, I've managed to create a temp table like this: Dim dt As DataTable = New DataTable("TblDate") dt.Columns.Add("artnr", Type.GetType("System.String")) Do While startDate < endDate dt.Columns.Add((startDate), Type.GetType("System.String")) startDate = startDate.AddDays(1) Loop Now, how do fill in the stock values into the TblDate? Regards adyck

[SimulationofSai]

I meant a Temp table in SQL Server. Instead of creating a tables with dates as columns, you can use the PIVOT command to prepare your report. PIVOT [^]is easy to master. Just look it up.

SG Cause is effect concealed. Effect is cause revealed.