SQL Query return Qty zero with dates.

Hi,

Something that could cause a problem with your query is that you are filtering with material. So the specified product is not used in a specific month, then this month will not be shown (not even as 0).

You may want to move this filter on the join as in the following:

SELECT	SUM(ISNULL(oe_pick_ticket_detail.print_quantity, 0)) AS quaSum, 
		MONTH(MaharP21ExternalDB.dbo.tbl_DateCycle.Date_Full) AS aMonth, 
		YEAR(MaharP21ExternalDB.dbo.tbl_DateCycle.Date_Full) AS aYear, 
		inv_mast.item_id, 
		oe_pick_ticket.location_id
FROM	oe_pick_ticket 
		INNER JOIN oe_pick_ticket_detail 
			ON oe_pick_ticket.pick_ticket_no = oe_pick_ticket_detail.pick_ticket_no 
		RIGHT OUTER JOIN inv_mast 
			ON oe_pick_ticket_detail.inv_mast_uid = inv_mast.inv_mast_uid 
			AND inv_mast.item_id = 'MT118200'
		RIGHT OUTER JOIN MaharP21ExternalDB.dbo.tbl_DateCycle 
			ON CONVERT(varchar(10), oe_pick_ticket.print_date, 101) = CONVERT(varchar(10), MaharP21ExternalDB.dbo.tbl_DateCycle.Date_Full, 101)
WHERE	(MaharP21ExternalDB.dbo.tbl_DateCycle.Date_Full between dateadd(year,-1, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY( GETDATE())-1), GETDATE()),101)) AND GETDATE()) 
group by  YEAR(MaharP21ExternalDB.dbo.tbl_DateCycle.Date_Full) , Month(MaharP21ExternalDB.dbo.tbl_DateCycle.Date_Full), item_id, oe_pick_ticket.location_id
order by 3, 2 Desc 

Select allOpen in new window

Giannis

Could you provide the other data for the tbl_DataCycle table.

The only other data in tbl_DataCycle is Mon and Year.  
DateFull   Mon   Year
1/1/2013     1      2013
1/2/2013      1     2013

>> return all months within a Date range ...

is this table just a table of dates? (to achive the "all months")
MaharP21ExternalDB.dbo.tbl_DateCycle

I think the query needs to be turned upside-down, so you ensure "all months" are included in the data. I can really only guess at the joins involved but think this will work:

select
       inv_mast.item_id
     , oe_pick_ticket.location_id
     , YEAR(DATS.Date_Full) AS aYear
     , MONTH(DATS.Date_Full) AS aMonth
     , ISNULL(SUM(oe_pick_ticket_detail.print_quantity), 0) AS quaSum  
FROM MaharP21ExternalDB.dbo.tbl_DateCycle AS DATS
LEFT JOIN oe_pick_ticket ON oe_pick_ticket.print_date >= DATS.Date_Full and oe_pick_ticket.print_date < dateadd(month,1,DATS.Date_Full)
LEFT JOIN oe_pick_ticket_detail
     ON oe_pick_ticket.pick_ticket_no = oe_pick_ticket_detail.pick_ticket_no
LEFT JOIN inv_mast
     ON oe_pick_ticket_detail.inv_mast_uid = inv_mast.inv_mast_uid
    AND inv_mast.item_id = 'MT118200'
WHERE DATS.Date_Full >= dateadd(year, - 1, DATEADD(dd, - (DAY(GETDATE()) - 1), dateadd(day, datediff(day,0, GETDATE() ), 0)))

     -- AND DATS.Date_Full < GETDATE() --<< are you sure this is correct? "less than the next line is 1st of this month"
    AND DATS.Date_Full < DATEADD(dd, - (DAY(GETDATE()) - 1), dateadd(day, datediff(day,0, GETDATE() ), 0))

    GROUP BY
       inv_mast.item_id
     , oe_pick_ticket.location_id
     , YEAR(DATS.Date_Full)
     , MONTH(DATS.Date_Full)
 

Select allOpen in new window

Note I'm really not sure about using getdate() as the upper boundary of the date range, this will give you a partial month (of the current month), so I've included an alternative - you can choose to ignore it of course.

other notes on date ranges:
1. for accuracy don't use between
2. for performance don't convert datetime data to varchar

for more on 'between' see: "Beware of Between"

and if you don't believe me try these:
"Bad habits to kick : mis-handling date / range queries"
"What do BETWEEN and the devil have in common?"
"The ultimate guide to the datetime datatypes"

Paul.. Yes the tbl_DateCycle is just a list of all dates.

With your last post,  I needed to limit it to locationid on oe_pick_ticket because it was returning all locations.

But the query also sums up qtys in that date range..

NULL      101663      2013      1      8239.000000000
MT118200      101663      2013      1      42.000000000
NULL      101663      2013      2      235338.000000000
MT118200      101663      2013      2      260.000000000
NULL      101663      2013      3      277402.000000000
MT118200      101663      2013      3      24.000000000
NULL      101663      2013      4      232261.000000000
MT118200      101663      2013      4      38.000000000
NULL      101663      2013      5      246687.000000000
NULL      101663      2013      6      209232.000000000
NULL      101663      2012      7      275079.000000000
NULL      101663      2012      8      263512.000000000
NULL      101663      2012      9      232827.000000000
MT118200      101663      2012      9      -11.000000000
NULL      101663      2012      10      249563.000000000
MT118200      101663      2012      10      -19.000000000
NULL      101663      2012      11      218762.000000000
MT118200      101663      2012      11      85.000000000
NULL      101663      2012      12      183029.000000000
MT118200      101663      2012      12      731.000000000



As seen on Pauls Query and  jyparask Query.. It repeats the same date group twice.

0.000000000      12      2012      NULL      NULL
6.000000000      12      2012      MT118200      101663
0.000000000      11      2012      NULL      NULL
0.000000000      10      2012      NULL      NULL
-1.000000000      10      2012      MT118200      101663
0.000000000      9      2012      NULL      NULL
0.000000000      8      2012      NULL      NULL
0.000000000      7      2012      NULL      NULL
0.000000000      7      2013      NULL      NULL
0.000000000      6      2013      NULL      NULL
0.000000000      5      2013      NULL      NULL
0.000000000      4      2013      NULL      NULL
2.000000000      4      2013      MT118200      101663
0.000000000      3      2013      NULL      NULL
0.000000000      2      2013      NULL      NULL
26.000000000      2      2013      MT118200      101663
0.000000000      1      2013      NULL      NULL

Thanks that works..  I was hoping for a Single SQL Query.. But I can make this work.