Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

3 x COUNTs on 2nd table for DATEPART(Month, tabledatefield) 1

Status
Not open for further replies.
Ach005ki

Ach005ki

Technical User
May 14, 2007
43
GB
Hi guys!

I've tried hard to produce the code myself but I'm struggling. I'm but a mere student of these dark arts!

I don't have access to Query Analyzer, SPs or other SQL Server 2000 functions - my only query tool is MS Query (which makes it a little difficult to write code in the SQL View box which is approx 50 chars x 10 lines visible ... I'm just glad I can use Scintilla Text Editor to write the code with, lol).

I have to produce a list of items (from our inventory-master table) for a particular range of products, and then cross reference this with our sales-order tables (detail and master/header table) in order to provide a count of sales orders by month for the last three months.

I can't think of any way other than 3 correlated subqueries, each focussing on the month for the sales order entry date... but I thought this would have quite an impact on database performance when the query is refreshed.

I have tried to use [tt]SUM(CASE...[/tt] and an [tt]LOJ InvMaster --> derived table [/tt] but, with such, I'm getting multiple instances of StockCode for those items which have sales orders in more than one of the months being queried... this I expected, but I can't turn the corner to get one instance of StockCode and then separate columns for a count of sales order numbers for each of the last three month's sales (April, May and June).

The best I can do at present is along the lines of this simple code (with highlighted section repeated for datepart(month,... ) values of 4, 5 & 6 (April, May & June).
I can post my actual code if it makes any difference, but there are other pieces of information which distract from my objective here.
Code: 
SELECT 
  StockCode,[COLOR=red yellow]
  ( SELECT 
      COUNT (DISTINCT SalesOrder) 
    FROM
      SorDetail
      INNER JOIN 
        SorMaster
        ON
          SorDetail.SalesOrder = SorMaster.SalesOrder
    WHERE
      SorDetail.MStockCode = InvMaster.StockCode AND
      DATEPART( Month, SorMaster.EntryDate ) = 4 AND
      DATEPART( Year,  SorMaster.EntryDate ) = 2007
  ) AS 'NoOrdsApr'[/color]
FROM
 InvMaster
WHERE
 client = 'F'

Mark, HH Associates
 
Sort by date Sort by votes
... but I thought this would have quite an impact on database performance when the query is refreshed.
Click to expand...
have you tested it? maybe it runs fine :)


here's an alternative...
Code: 
SELECT * 
  FROM (
SELECT StockCode
     , Year(SorMaster.EntryDate) as YYYY
     , Month((SorMaster.EntryDate)) as MM
     , COUNT(DISTINCT SorMaster.SalesOrder) as Number_of_Orders
  FROM InvMaster
INNER
  JOIN SorDetail
    ON SorDetail.MStockCode = InvMaster.StockCode 
INNER 
  JOIN SorMaster
    ON SorMaster.SalesOrder = SorDetail.SalesOrder  
       ) as data
 WHERE InvMaster.client = 'F'
   AND YYYY = 2007 AND MM IN (6,5,4)


r937.com | rudy.ca
 
Upvote 0 Downvote
Sorry Rudy, I mustn't have explained this properly - fields in [blue]blue[/blue] are relevant to the query

[tt]InvMaster (contains details about the item, name, size, product class, etc)
=========
[blue]StockCode[/blue]
Description
ExtraDescript
PackSize
PrdClass
DateCreated
...

SorDetail (contains details about Sales Order items
=========
[blue]SalesOrder
MStockCode[/blue]
MQtyOrdered
MQtyShipped
MQtyBackOrder
...

SorHeader
=========
[blue]SalesOrder
EntryDate[/blue]
Customer
Branch
...[/tt]

So my data would be like this:

[tt]InvMaster (contains details about the item, name, size, product class, etc)
=========
[blue]StockCode,[/blue] Description, ExtraDescript, PackSize, PrdClass...
Item0001

SorDetail (contains details about Sales Order items
=========
[blue]SalesOrder, MStockCode,[/blue] MQtyOrdered, MQtyShipped, MQtyBackOrder
300010 Item0027 12 10 2
300010 Item0022 25 25 0
300010 Item0035 4 4 0
...
300121 Item0010 12 12 0
300122 Item0202 2 0 1
300122 Item0035 8 8 0
...
300180 Item0027 10 0 10
300180 Item0029 32 32 0

SorHeader
=========
[blue]
SalesOrder, EntryDate,[/blue] Customer, Branch
300010 2007-04-12 Cust001 D12
300011 2007-04-13 Cust002 D22
...
300121 2007-05-04 Cust034 D02
300122 2007-05-05 Cust010 D06
...
300180 2007-06-09 Cust001 D12
[/tt]

In my query, I would like to show
[tt]
StockCode AprOrds MayOrds JunOrds
Item0010 0 1 0
Item0022 1 0 0
Item0027 1 0 1
Item0029 0 0 1
Item0035 1 1 0
Item0202 0 1 0
[/tt]

Mark, HH Associates
 
Upvote 0 Downvote
Hi again Rudy (and all who view this)!

When I try to run your query, I get an error message:

Could not add the table '('


Mark, HH Associates
 
Upvote 0 Downvote
Hello again Rudy,
Did I tell you how much I hate using MSQuery, and how much I envy those of you with access to Query Analyzer et al?

Code for my derived table which then shows me a separate row for each month's orders
NB: [blue]InvWarehouse[/blue] is the table that actually stores the field determining which client the stock belongs to - in previous examples I simplified it a little.

Code: 
(
SELECT 
  InvMaster.StockCode 
  , Year(SorMaster.EntrySystemDate) as YYYY
  , Month(SorMaster.EntrySystemDate) as MM
  , COUNT(DISTINCT SorMaster.SalesOrder) as Number_of_Orders
FROM 
  SysproCompanyH.dbo.InvMaster InvMaster
  INNER
	 JOIN SysproCompanyH.dbo.SorDetail SorDetail
		ON SorDetail.MStockCode = InvMaster.StockCode 
  INNER 
	 JOIN SysproCompanyH.dbo.SorMaster SorMaster
		ON SorMaster.SalesOrder = SorDetail.SalesOrder  
  INNER
	 JOIN SysproCompanyH.dbo.InvWarehouse InvWarehouse
		ON InvMaster.StockCode = InvWarehouse.StockCode
WHERE 
  InvWarehouse.Warehouse = 'BF'
  AND Year(SorMaster.EntrySystemDate) = 2007 AND Month(SorMaster.EntrySystemDate) IN (6,5,4)		
GROUP BY
  InvMaster.StockCode
  , Year(SorMaster.EntrySystemDate) 
  , Month(SorMaster.EntrySystemDate)
) AS OrdCount

If you could then help me link this to the main outer query so that I get something like the list below, I'd be most grateful!
[tt]
StockCode AprOrds MayOrds JunOrds
Item0010 0 1 0
Item0022 1 0 0
Item0027 1 0 1
Item0029 0 0 1
Item0035 1 1 0
Item0202 0 1 0
[/tt]

Mark, HH Associates
 
Upvote 0 Downvote
Without the outer paren I get the following...
(MSQuery seems to have a problem assigning column aliases depending on which table is being worked on, but I've learned to sigh, smile & move on from that)

[tt]
StockCode
FMC-100-855.22-DC 2007 6 1
FMC-100-855.22/A 2007 4 1
FMC-100-855.22/A 2007 5 1
FMC-100-855.22/B 2007 4 2
....
[/tt]

Mark, HH Associates
 
Upvote 0 Downvote
so it's working?
Code: 
select StockCode
     , sum(
        case when MM = 4
             then Number_of_Orders
             else 0 end ) as AprilOrders 
     , sum(
        case when MM = 5
             then Number_of_Orders
             else 0 end ) as MayOrders 
     , sum(
        case when MM = 6
             then Number_of_Orders
             else 0 end ) as JuneOrders 
  from (
       SELECT InvMaster.StockCode 
          ...
       ) AS OrdCount
group
    by StockCode

r937.com | rudy.ca
 
Upvote 0 Downvote
Hi Rudy,

The query runs but appears to be sometimes multiplying the month counts but not consistently so - as the data below shows!
[ponder]
Inner query as coded in my post above returns the following:
[tt]
FMC-281-337.48 2007 4 1
FMC-281-337.48 2007 5 2
FMC-281-337.48 2007 6 1
FMC-281-362.05 2007 4 8
FMC-281-362.05 2007 5 6
FMC-281-362.05 2007 6 5
FMC-2CQCWWA 2007 4 6
FMC-2CQCWWA 2007 5 7
[/tt]

full query (code at foot of this post) returns the following for the above items.. I've stripped out the extraneous columns
[tt]
FMC-281-337.48 6 12 6
FMC-281-362.05 32 24 20
FMC-2CQCWWA 6 7 0
[/tt]

expected results
[tt]
FMC-281-337.48 1 2 1
FMC-281-362.05 8 6 5
FMC-2CQCWWA 6 7 0
[/tt]

Rudy, I'd just like to say at this point that I have nothing but immense admiration and the deepest of gratitude for the time and effort you and your counterparts put into reading and responding to requests for assistance in the Tek-Tips forums.
[smarty][medal]

I only hope that I can emulate you (and your counterparts) in your altruistic, benevolent and charitable manner ... and (with the assistance you've provided) to one day propagate my accumulated knowledge for the betterment of others.
[reading][idea][bigsmile]

I know this sounds like "get the tissues out, he's gonna blub"[cry] but I really am thankful of the help this forum (and you in particular) have provided me with - God bless you![angel]

my (our) full code is reproduced below:
Code: 
SELECT 
	  InvMaster.StockCode AS 'StockCode' 
	, InvMaster.ProductClass AS 'PrdClass'
	, SalProdClass.Description AS 'PrdClassDescription' 
	, InvMaster.Description AS 'Desc' 
	, InvMaster.LongDesc AS 'ExtraDescription'
	, InvMaster.AlternateKey1 AS 'PackSize'
	, InvWarehouse.QtyOnHand AS 'InStock' 
	, Max(InvFifoLifo.LastReceiptDate) AS 'LastReceipt'
	
	, ItemHold = 
	 CASE InvMaster.StockOnHold
			WHEN 'P' THEN 'Partial'
			WHEN 'F' THEN 'Full'
			ELSE 'No'
	 END
	 
	, HaveStock = 
	 CASE InvWarehouse.QtyOnHand
			WHEN 0 THEN 0
			ELSE 1
	 END
	 

	, (
	 SELECT 
			count(InvMultBin.Bin)
	 FROM 
			SysproCompanyH.dbo.InvMultBin InvMultBin 
	 WHERE 
			InvMultBin.StockCode = InvMaster.StockCode
			AND InvMultBin.Bin like 'F%' 
			AND InvMultBin.QtyOnHand1 >$0 
	) AS 'PalletCount'

	, (
	 SELECT 
			count(InvMultBin.Bin)
	 FROM 
			SysproCompanyH.dbo.InvMultBin InvMultBin 
	 WHERE 
			InvMultBin.StockCode = InvMaster.StockCode
			AND InvMultBin.Bin like 'G%' 
			AND InvMultBin.QtyOnHand1 >$0 
	) AS 'PickFaceCount'

	, SUM (
			CASE 
				WHEN SorCounts.EntryDateMonth = 4 
					THEN SorCounts.OrderCount
					ELSE 0
			END
			) AS AprOrders

	, SUM (
			CASE 
				WHEN SorCounts.EntryDateMonth = 5
					THEN SorCounts.OrderCount
					ELSE 0
			END
			) AS MayOrders

	, SUM (
			CASE 
				WHEN SorCounts.EntryDateMonth = 6
					THEN SorCounts.OrderCount
					ELSE 0
			END
			) AS JunOrders

FROM 
	SysproCompanyH.dbo.InvMaster InvMaster

	INNER 
		JOIN SysproCompanyH.dbo.InvWarehouse InvWarehouse 
			ON InvMaster.StockCode = InvWarehouse.StockCode 
		
	INNER 
		JOIN SysproCompanyH.dbo.SalProductClassDes SalProdClass
			ON InvMaster.ProductClass = SalProdClass.ProductClass
		
	LEFT OUTER 
		JOIN SysproCompanyH.dbo.InvFifoLifo InvFifoLifo 
			ON InvWarehouse.StockCode = InvFifoLifo.StockCode 
			AND InvWarehouse.Warehouse = InvFifoLifo.Warehouse
	LEFT OUTER 
		JOIN 
		(
			SELECT 
				InvMaster.StockCode as OrdStockCode
				, Year(SorMaster.EntrySystemDate) as EntryDateYear
				, Month(SorMaster.EntrySystemDate) as EntryDateMonth
				, COUNT(DISTINCT SorMaster.SalesOrder) as OrderCount
			FROM 
				SysproCompanyH.dbo.InvMaster InvMaster
				INNER
					JOIN SysproCompanyH.dbo.SorDetail SorDetail
						ON SorDetail.MStockCode = InvMaster.StockCode 
				INNER 
					JOIN SysproCompanyH.dbo.SorMaster SorMaster
						ON SorMaster.SalesOrder = SorDetail.SalesOrder  
				INNER
					JOIN SysproCompanyH.dbo.InvWarehouse InvWarehouse
						ON InvMaster.StockCode = InvWarehouse.StockCode
			WHERE 
				InvWarehouse.Warehouse = 'BF'
				AND Year(SorMaster.EntrySystemDate) = 2007 AND Month(SorMaster.EntrySystemDate) IN (6,5,4)        
			GROUP BY
				InvMaster.StockCode
				, Year(SorMaster.EntrySystemDate) 
				, Month(SorMaster.EntrySystemDate)
		) AS SorCounts
			On InvMaster.StockCode = SorCounts.OrdStockCode

WHERE 
	InvWarehouse.Warehouse='BF'  
GROUP BY 
	InvMaster.StockCode, 
	InvMaster.Description, 
	InvMaster.LongDesc,
	InvMaster.AlternateKey1, 
	InvMaster.ProductClass,
	InvMaster.StockOnHold,
	SalProdClass.Description,
	InvWarehouse.QtyOnHand

ORDER BY 
	InvMaster.StockCode

Mark, HH Associates
 
Upvote 0 Downvote
mark, i dunno if i have actually helped you at all...

that last query sort of looks like what we've been working on, but it's obviously not the same...

r937.com | rudy.ca
 
Upvote 0 Downvote
Rudy, you most certainly have helped me get this far!

I created an actual table in our database, then took the derived table SQL and inserted the results into this table.

Code: 
DELETE FROM SysproCompanyH.dbo.tblSorCount 
INSERT INTO SysproCompanyH.dbo.tblSorCount 
SELECT InvMaster.StockCode 
     , Year(SorMaster.EntrySystemDate) 
     , Month(SorMaster.EntrySystemDate) 
     , COUNT(DISTINCT SorMaster.SalesOrder) 
  FROM SysproCompanyH.dbo.InvMaster InvMaster
 INNER
  JOIN SysproCompanyH.dbo.SorDetail SorDetail
    ON SorDetail.MStockCode = InvMaster.StockCode 
 INNER 
  JOIN SysproCompanyH.dbo.SorMaster SorMaster
    ON SorMaster.SalesOrder = SorDetail.SalesOrder  
 INNER
  JOIN SysproCompanyH.dbo.InvWarehouse InvWarehouse
    ON InvMaster.StockCode = InvWarehouse.StockCode
 WHERE InvWarehouse.Warehouse = 'BF'
   AND Year(SorMaster.EntrySystemDate) = 2007   
 GROUP 
    BY InvMaster.StockCode
     , Year(SorMaster.EntrySystemDate) 
     , Month(SorMaster.EntrySystemDate)

I then modified the original query to use the correlated subquery approach ...

Code: 
SELECT
...
   [green], (
     SELECT tblSorCount.OrderCount
       FROM SysproCompanyH.dbo.tblSorCount 
      WHERE tblSorCount.StockCode = InvMaster.StockCode
        AND tblSorCount.EntryDateMonth = 4
     ) AS 'No_Ords_Apr'[/green]

   [red], (
     SELECT tblSorCount.OrderCount
       FROM SysproCompanyH.dbo.tblSorCount 
      WHERE tblSorCount.StockCode = InvMaster.StockCode
        AND tblSorCount.EntryDateMonth = 5
     ) AS 'No_Ords_May'[/red]
	
   [blue], (
     SELECT tblSorCount.OrderCount
       FROM SysproCompanyH.dbo.tblSorCount 
      WHERE tblSorCount.StockCode = InvMaster.StockCode
        AND tblSorCount.EntryDateMonth = 6
      ) AS 'No_Ords_Jun'[/blue]
...

This gives me two benefits

1. I don't need to re-run the derived-table SQL every time I run the report ... as the count is of orders placed in the past, these values shouldn't change. The only other thing I should factor in is whether the order was cancelled (but I can do this). Should I need to add counts for previous or future months' and/or years' orders I can do so without affecting the data already in the table.
If I wanted to broaden this to include other clients' order counts I could either add a client distinguishing field to the table or create separate tables for each client (etc).

my results are now inline with those I expected (as above)
[tt]
FMC-281-337.48 1 2 1
FMC-281-362.05 8 6 5
FMC-2CQCWWA 6 7
[/tt]

Thanks once again for your help! Here's my current code in its entirety ...
I hope you can see I've adpoted your code formatting style (although I cba getting it to display 100% true here).
The preceding commas make it easier to notice at a glance where one is missing, and the indentation makes it easier to see the difference between reserved words and objects (etc).

Code: 
SELECT InvMaster.StockCode AS 'StockCode'
     , InvMaster.ProductClass AS 'PrdClass'
     , SalProdClass.Description AS 'PrdClassDescription'
     , ISNULL( InvMaster.Description + InvMaster.LongDesc, InvMaster.Description ) AS 'Descr'
     , InvMaster.AlternateKey1 AS 'PackSize'
     , InvWarehouse.QtyOnHand AS 'InStock' 
     , Max(InvFifoLifo.LastReceiptDate) AS 'LastReceipt'
	
     , ItemHold = 
       CASE InvMaster.StockOnHold
         WHEN 'P' THEN 'Partial'
         WHEN 'F' THEN 'Full'
         ELSE 'No'
       END
 
     , HaveStock = 
       CASE InvWarehouse.QtyOnHand
         WHEN 0 THEN 0
         ELSE 1
       END

     , (
       SELECT count(InvMultBin.Bin)
         FROM SysproCompanyH.dbo.InvMultBin InvMultBin 
        WHERE InvMultBin.StockCode = InvMaster.StockCode
          AND InvMultBin.Bin like 'F%' 
          AND InvMultBin.QtyOnHand1 >$0 
       ) AS 'PalletCount'

     , (
	SELECT count(InvMultBin.Bin)
	  FROM SysproCompanyH.dbo.InvMultBin InvMultBin 
	 WHERE InvMultBin.StockCode = InvMaster.StockCode
            AND InvMultBin.Bin like 'G%' 
            AND InvMultBin.QtyOnHand1 >$0 
       ) AS 'PickFaceCount'

     , (
	SELECT tblSorCount.OrderCount
	  FROM SysproCompanyH.dbo.tblSorCount 
	 WHERE tblSorCount.StockCode = InvMaster.StockCode
           AND tblSorCount.EntryDateMonth = 4
       ) AS 'No_Ords_Apr'

     , (
	SELECT tblSorCount.OrderCount
	  FROM SysproCompanyH.dbo.tblSorCount 
	 WHERE tblSorCount.StockCode = InvMaster.StockCode
           AND tblSorCount.EntryDateMonth = 5
       ) AS 'No_Ords_May'

     , (
	SELECT tblSorCount.OrderCount
	  FROM SysproCompanyH.dbo.tblSorCount 
	 WHERE tblSorCount.StockCode = InvMaster.StockCode
           AND tblSorCount.EntryDateMonth = 6
       ) AS 'No_Ords_Jun'

  FROM SysproCompanyH.dbo.InvMaster InvMaster
	
 INNER 
  JOIN SysproCompanyH.dbo.InvWarehouse InvWarehouse 
    ON InvMaster.StockCode = InvWarehouse.StockCode 
		
 INNER 
  JOIN SysproCompanyH.dbo.SalProductClassDes SalProdClass
    ON InvMaster.ProductClass = SalProdClass.ProductClass
		
  LEFT OUTER 
  JOIN SysproCompanyH.dbo.InvFifoLifo InvFifoLifo 
    ON InvWarehouse.StockCode = InvFifoLifo.StockCode 
   AND InvWarehouse.Warehouse = InvFifoLifo.Warehouse
  LEFT OUTER 
  JOIN SysproCompanyH.dbo.tblSorCount
    On InvMaster.StockCode = tblSorCount.StockCode

 WHERE InvWarehouse.Warehouse='BF'  
 GROUP BY 
       InvMaster.StockCode
     , InvMaster.Description
     , InvMaster.LongDesc
     , InvMaster.AlternateKey1
     , InvMaster.ProductClass
     , InvMaster.StockOnHold
     , SalProdClass.Description
     , InvWarehouse.QtyOnHand

 ORDER BY 
       InvMaster.StockCode

Mark, HH Associates
 
Upvote 0 Downvote
If you are a student you can get MS software for free or nearly free. Or you could use MS Access ADP (sorry).

Christiaan Baes
Belgium

"My old site" - Me
 
Upvote 0 Downvote
ty both for your helpful comments re MSQuery - unfortunately QueryExpress appears to require some part of .Net that is either permission restricted or not installed ... maybe I should mention that in addition to the challenge of writing code via MSQuery, I am also logging into a Citrix program neighbourhood Windows Server 2003 Std Ed virtual PC ... cue the deep sighs.

I've already asked our dba for an alternate query engine and this is "in hand" (but it has been so since Feb), cue more deep sighs.

Mark, HH Associates
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
716
Andrzejek
Andrzejek
SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
804
SashaBuilder3
SashaBuilder3
Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
469
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
964
gmmastros
gmmastros
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
631
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top