October 8, 2010 at 8:29 am
Hi, all
group by/date/week/moth/year/product
need help with this report and this is the code
SELECT
CT.NAME AS BILL_TO_NAME,
IT.ITEMNAME,
SUM(SL.qtyordered) as quantity,
FROM
[x].[dbo].x SL (NOLOCK)
INNER JOIN
x.[dbo].x ST (NOLOCK)
ON SL.x = ST.x
AND SL.x = ST.x
INNER JOIN
x.[dbo].x CT (NOLOCK)
ON CT.x = ST.x
AND SL.x = CT.x
INNER JOIN
x.[dbo].x IT (NOLOCK)
ON SL.x = IT.x
AND SL.x = IT.x
WHERE IT.x = 'Fluor-A Day 0.50mg Chewable Tablets' AND
--EXCLUDE Returns
ST.x='' AND
--EXCLUDE Sample Orders. Orders for zero dollars
SL.x > '0' --AND SL.Createddatetime between @StartDate and @EndDate
GROUP BY CT.x,IT.x
--dont know if this the right way i am doing--please see the attached image--
Thank you all for looking at it..............
October 8, 2010 at 10:34 am
saimddr (10/8/2010)
Hi, allgroup by/date/week/moth/year/product
need help with this report and this is the code
SELECT
CT.NAME AS BILL_TO_NAME,
IT.ITEMNAME,
SUM(SL.qtyordered) as quantity,
FROM
[x].[dbo].x SL (NOLOCK)
INNER JOIN
x.[dbo].x ST (NOLOCK)
ON SL.x = ST.x
AND SL.x = ST.x
INNER JOIN
x.[dbo].x CT (NOLOCK)
ON CT.x = ST.x
AND SL.x = CT.x
INNER JOIN
x.[dbo].x IT (NOLOCK)
ON SL.x = IT.x
AND SL.x = IT.x
WHERE IT.x = 'Fluor-A Day 0.50mg Chewable Tablets' AND
--EXCLUDE Returns
ST.x='' AND
--EXCLUDE Sample Orders. Orders for zero dollars
SL.x > '0' --AND SL.Createddatetime between @StartDate and @EndDate
GROUP BY CT.x,IT.x
--dont know if this the right way i am doing--please see the attached image--
Thank you all for looking at it..............
Well the first thing I would do is create a calendar table containing date, week of year, weekendingdate, monthname, monthnumber, quarter (just in case, or for future use) and year. Actually when creating a calendar table I would add a lot more columns, but these will do for you immediate need.
Then join the calendar table to your query and add the date, weekendingdate, monthname and/or monthnumber, and year to your select statement.
The resulting dataset should give you everything you need to build your report. Your picture looks like you probably want to use a matrix report
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply