SQL puzzle...
-
Hi, hope you can help.. I have a database on MSSQL2000 running through to a ASP front end.
The problem I have is that I have a table, which has a couple of significant rows, one is a date, another is the destination and the other is a quantity. What I want to achieve though the SQL is the ability to output the quantities that are due at a destination on each day for the next 7 days...
the datefield is configured as a smalldatetime field, the destination is a varchar and the quantity is a int[\i]
hope this makes sense. Been racking my brains for a week on this now to no avail....
-
Select Destination, datepart(Datefield), Quantity
From MysteryTable
Where DateFIeld between Getdate() and Dateadd(dd,7,Getdate())
This will return rows,
But can you clarify how you want your output to look
-
Ok,
First I am hoping that whe you said that you had a row that is a date you actually meant a column
assuming that, can you give an example of the OUTPUT you want ?
* Noel -
i meant columns
honest 
the output I am looking for is this..
DATE
DESTINATION
DESTINATION DETAIL 1 FOR EACH DESTINATION DUE FOR PARTICULAR DAY<brDESTINATION DETAIL 2 FOR EACH DESTINATION DUE FOR PARTICULAR DAY
total qty for above
DATE+1
DESTINATION
DESTINATION DETAIL 1 FOR EACH DESTINATION DUE FOR PARTICULAR DAY
DESTINATION DETAIL 2 FOR EACH DESTINATION DUE FOR PARTICULAR DAY
DESTINATION DETAIL 2 FOR EACH DESTINATION DUE FOR PARTICULAR DAY
total qty for above
hope this makes sense, thats the output I am looking for from the ASP page. So far i have each date and the destination, and then the detail for each destination for the day, but not the total qty for the day.
-
You will not achieve this type of formatting with straightforward SQL statements - Ray M's solution, possibly with an additional ORDER BY clause so that you get everything in date order, will return you a suitable recordset - you need to code the special formatting within ASP - in my opinion
-
I was planning to format it like that with ASP.
All i want SQL to give me is the line detail for each destination for each day (already got) and the quantity field,
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply