Crosstab query with subqueries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Crosstab query with subqueries

Author

Topic

CP
Starting Member

9 Posts

Posted - 2012-03-21 : 15:32:58

I'm using Microsoft SQL Server 2008. So bascially this is a sales report that I am creating and I've got the "month" section set up. The output will give the the customer, item, 2011 current month, 2012 current month, and the month variance. What I try to do next is add in the 2011 current qtr, 2012 current qtr and the qtr variance. I'm getting a Msg 102, level 15, state 1, line 233 error. I am unable to troubleshoot this. I'm hoping someone can take a look at this and determine where my issue is. Thank you for taking the time to look at this.

SELECT     BUILDING_CURRENT_MONTH_LAST_YEAR.ACCOUNTNUM AS CUSTACCOUNT, 
BUILDING_CURRENT_MONTH_LAST_YEAR.ITEMID AS [ITEM NUMBER], 
ISNULL(BUILDING_CURRENT_MONTH_LAST_YEAR.LINEAMTSUM,0) AS [CURRENT MONTH 2011], 
ISNULL(BUILDING_CURRENT_MONTH_THIS_YEAR.LINEAMTSUM,0) AS [CURRENT MONTH 2012 YTD], 
ISNULL(BUILDING_CURRENT_MONTH_THIS_YEAR.LINEAMTSUM,0) - ISNULL(BUILDING_CURRENT_MONTH_LAST_YEAR.LINEAMTSUM,0) AS [MONTH VARIANCE],
ISNULL(BUILDING_CURRENT_QTR_LAST_YEAR.LINEAMTSUM,0) AS [CURRENT MONTH 2011], 
ISNULL(BUILDING_CURRENT_QTR_THIS_YEAR.LINEAMTSUM,0) AS [CURRENT MONTH 2012 YTD], 
ISNULL(BUILDING_CURRENT_QTR_THIS_YEAR.LINEAMTSUM,0) - ISNULL(BUILDING_CURRENT_QTR_LAST_YEAR.LINEAMTSUM,0) AS [QTR VARIANCE],
ISNULL(BUILDING_CURRENT_YTD_LAST_YEAR.LINEAMTSUM,0) AS [CURRENT MONTH 2011], 
ISNULL(BUILDING_CURRENT_YTD_THIS_YEAR.LINEAMTSUM,0) AS [CURRENT MONTH 2012 YTD], 
ISNULL(BUILDING_CURRENT_YTD_THIS_YEAR.LINEAMTSUM,0) - ISNULL(BUILDING_CURRENT_YTD_LAST_YEAR.LINEAMTSUM,0) AS [QTR VARIANCE]


FROM
/*START OF MONTH*/
/*BUILDING_CURRENT_MONTH_LAST_YEAR*/

(SELECT     COMPLETEINVLINES.LINEAMTSUM, KEY_CUST_ITEM.ACCOUNTNUM, KEY_CUST_ITEM.ITEMID
FROM          (SELECT     CUSTTABLE_2.ACCOUNTNUM, CUSTINVOICETRANS_2.ITEMID
FROM          CUSTTABLE AS CUSTTABLE_2 INNER JOIN
CUSTINVOICEJOUR AS CUSTINVOICEJOUR_2 ON CUSTTABLE_2.ACCOUNTNUM = CUSTINVOICEJOUR_2.ORDERACCOUNT AND 
CUSTTABLE_2.DATAAREAID = CUSTINVOICEJOUR_2.DATAAREAID INNER JOIN
CUSTINVOICETRANS AS CUSTINVOICETRANS_2 ON CUSTINVOICEJOUR_2.INVOICEID = CUSTINVOICETRANS_2.INVOICEID AND 
CUSTINVOICEJOUR_2.DATAAREAID = CUSTINVOICETRANS_2.DATAAREAID
WHERE      (CUSTTABLE_2.DATAAREAID = N'CMC') AND (CUSTINVOICETRANS_2.ITEMID <> '') AND (MONTH(CUSTINVOICETRANS_2.INVOICEDATE) 
= MONTH(GETDATE())) AND (YEAR(CUSTINVOICETRANS_2.INVOICEDATE) >= YEAR(GETDATE()) - 1)
GROUP BY CUSTTABLE_2.ACCOUNTNUM, CUSTINVOICETRANS_2.ITEMID
UNION
SELECT     CUSTTABLE.ACCOUNTNUM, CUSTINVOICETRANS.ITEMID
FROM         Ax09_Production.dbo.CUSTTABLE AS CUSTTABLE INNER JOIN
Ax09_Production.dbo.CUSTINVOICEJOUR AS CUSTINVOICEJOUR ON CUSTTABLE.ACCOUNTNUM = CUSTINVOICEJOUR.ORDERACCOUNT AND 
CUSTTABLE.DATAAREAID = CUSTINVOICEJOUR.DATAAREAID INNER JOIN
Ax09_Production.dbo.CUSTINVOICETRANS AS CUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID AND 
CUSTINVOICEJOUR.DATAAREAID = CUSTINVOICETRANS.DATAAREAID
WHERE     (CUSTTABLE.DATAAREAID = N'DAY') AND (CUSTINVOICETRANS.ITEMID <> '') AND (MONTH(CUSTINVOICETRANS.INVOICEDATE) 
= MONTH(GETDATE())) AND (YEAR(CUSTINVOICETRANS.INVOICEDATE) >= YEAR(GETDATE()) - 1)
GROUP BY CUSTTABLE.ACCOUNTNUM, CUSTINVOICETRANS.ITEMID) AS KEY_CUST_ITEM LEFT OUTER JOIN
(SELECT     ACCOUNTNUM, ITEMID, SUM(LINEAMOUNT) AS LINEAMTSUM
FROM          (SELECT     CUSTTABLE_1.ACCOUNTNUM, CUSTINVOICETRANS_1.ITEMID, CUSTINVOICETRANS_1.LINEAMOUNT
FROM          CUSTTABLE AS CUSTTABLE_1 INNER JOIN
CUSTINVOICEJOUR AS CUSTINVOICEJOUR_1 ON CUSTTABLE_1.ACCOUNTNUM = CUSTINVOICEJOUR_1.ORDERACCOUNT AND 
CUSTTABLE_1.DATAAREAID = CUSTINVOICEJOUR_1.DATAAREAID INNER JOIN
CUSTINVOICETRANS AS CUSTINVOICETRANS_1 ON CUSTINVOICEJOUR_1.INVOICEID = CUSTINVOICETRANS_1.INVOICEID AND 
CUSTINVOICEJOUR_1.DATAAREAID = CUSTINVOICETRANS_1.DATAAREAID
WHERE      (CUSTTABLE_1.DIMENSION = N'DAY') AND (CUSTINVOICETRANS_1.ITEMID <> '') AND 
(MONTH(CUSTINVOICETRANS_1.INVOICEDATE) = MONTH(GETDATE())) AND (YEAR(CUSTINVOICETRANS_1.INVOICEDATE) = 2011)
UNION ALL
SELECT     CUSTTABLE.ACCOUNTNUM, CUSTINVOICETRANS.ITEMID, CUSTINVOICETRANS.LINEAMOUNT
FROM         Ax09_Production.dbo.CUSTTABLE AS CUSTTABLE INNER JOIN
Ax09_Production.dbo.CUSTINVOICEJOUR AS CUSTINVOICEJOUR ON 
CUSTTABLE.ACCOUNTNUM = CUSTINVOICEJOUR.ORDERACCOUNT AND 
CUSTTABLE.DATAAREAID = CUSTINVOICEJOUR.DATAAREAID INNER JOIN
Ax09_Production.dbo.CUSTINVOICETRANS AS CUSTINVOICETRANS ON 
CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID AND 
CUSTINVOICEJOUR.DATAAREAID = CUSTINVOICETRANS.DATAAREAID
WHERE     (CUSTTABLE.DATAAREAID = N'DAY') AND (CUSTINVOICETRANS.ITEMID <> '') AND (MONTH(CUSTINVOICETRANS.INVOICEDATE) 
= MONTH(GETDATE())) AND (YEAR(CUSTINVOICETRANS.INVOICEDATE) = 2011)) AS INVLINESFLAT
GROUP BY ACCOUNTNUM, ITEMID) AS COMPLETEINVLINES ON KEY_CUST_ITEM.ACCOUNTNUM = COMPLETEINVLINES.ACCOUNTNUM AND 
KEY_CUST_ITEM.ITEMID = COMPLETEINVLINES.ITEMID) AS BUILDING_CURRENT_MONTH_LAST_YEAR 


INNER JOIN

(SELECT     KEY_CUST_ITEM.ACCOUNTNUM, KEY_CUST_ITEM.ITEMID, COMPLETEINVLINES.LINEAMTSUM

FROM

/*KEY_CUST_ITEM*/

(SELECT     CUSTTABLE_2.ACCOUNTNUM, CUSTINVOICETRANS_2.ITEMID
FROM          CUSTTABLE AS CUSTTABLE_2 INNER JOIN
CUSTINVOICEJOUR AS CUSTINVOICEJOUR_2 ON CUSTTABLE_2.ACCOUNTNUM = CUSTINVOICEJOUR_2.ORDERACCOUNT AND 
CUSTTABLE_2.DATAAREAID = CUSTINVOICEJOUR_2.DATAAREAID INNER JOIN 
CUSTINVOICETRANS AS CUSTINVOICETRANS_2 ON CUSTINVOICEJOUR_2.INVOICEID = CUSTINVOICETRANS_2.INVOICEID AND 
CUSTINVOICEJOUR_2.DATAAREAID = CUSTINVOICETRANS_2.DATAAREAID
WHERE      (CUSTTABLE_2.DATAAREAID = N'CMC') AND (CUSTINVOICETRANS_2.ITEMID <> '') AND (MONTH(CUSTINVOICETRANS_2.INVOICEDATE) 
= MONTH(GETDATE())) AND (YEAR(CUSTINVOICETRANS_2.INVOICEDATE) >= 2011)
GROUP BY CUSTTABLE_2.ACCOUNTNUM, CUSTINVOICETRANS_2.ITEMID
UNION
SELECT     CUSTTABLE.ACCOUNTNUM, CUSTINVOICETRANS.ITEMID
FROM         Ax09_Production.dbo.CUSTTABLE AS CUSTTABLE INNER JOIN
Ax09_Production.dbo.CUSTINVOICEJOUR AS CUSTINVOICEJOUR ON CUSTTABLE.ACCOUNTNUM = CUSTINVOICEJOUR.ORDERACCOUNT AND 
CUSTTABLE.DATAAREAID = CUSTINVOICEJOUR.DATAAREAID INNER JOIN
Ax09_Production.dbo.CUSTINVOICETRANS AS CUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID AND 
CUSTINVOICEJOUR.DATAAREAID = CUSTINVOICETRANS.DATAAREAID
WHERE     (CUSTTABLE.DATAAREAID = N'DAY') AND (CUSTINVOICETRANS.ITEMID <> '') AND (MONTH(CUSTINVOICETRANS.INVOICEDATE) 
= MONTH(GETDATE())) AND (YEAR(CUSTINVOICETRANS.INVOICEDATE) >= 2011)
GROUP BY CUSTTABLE.ACCOUNTNUM, CUSTINVOICETRANS.ITEMID) AS KEY_CUST_ITEM

LEFT OUTER JOIN

(SELECT     ACCOUNTNUM, ITEMID, SUM(LINEAMOUNT) AS LINEAMTSUM

FROM

/*BUILDING_CURRENT_MONTH_THIS_YEAR*/


(SELECT     CUSTTABLE_1.ACCOUNTNUM, CUSTINVOICETRANS_1.ITEMID, CUSTINVOICETRANS_1.LINEAMOUNT
FROM          CUSTTABLE AS CUSTTABLE_1 INNER JOIN
CUSTINVOICEJOUR AS CUSTINVOICEJOUR_1 ON CUSTTABLE_1.ACCOUNTNUM = CUSTINVOICEJOUR_1.ORDERACCOUNT AND 
CUSTTABLE_1.DATAAREAID = CUSTINVOICEJOUR_1.DATAAREAID INNER JOIN
CUSTINVOICETRANS AS CUSTINVOICETRANS_1 ON CUSTINVOICEJOUR_1.INVOICEID = CUSTINVOICETRANS_1.INVOICEID AND 
CUSTINVOICEJOUR_1.DATAAREAID = CUSTINVOICETRANS_1.DATAAREAID
WHERE      (CUSTTABLE_1.DIMENSION = N'DAY') AND (CUSTINVOICETRANS_1.ITEMID <> '') AND 
(MONTH(CUSTINVOICETRANS_1.INVOICEDATE) = MONTH(GETDATE())) AND (YEAR(CUSTINVOICETRANS_1.INVOICEDATE) = 2012)
UNION ALL
SELECT     CUSTTABLE.ACCOUNTNUM, CUSTINVOICETRANS.ITEMID, CUSTINVOICETRANS.LINEAMOUNT
FROM         Ax09_Production.dbo.CUSTTABLE AS CUSTTABLE INNER JOIN
Ax09_Production.dbo.CUSTINVOICEJOUR AS CUSTINVOICEJOUR ON 
CUSTTABLE.ACCOUNTNUM = CUSTINVOICEJOUR.ORDERACCOUNT AND 
CUSTTABLE.DATAAREAID = CUSTINVOICEJOUR.DATAAREAID INNER JOIN
Ax09_Production.dbo.CUSTINVOICETRANS AS CUSTINVOICETRANS ON 
CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID AND 
CUSTINVOICEJOUR.DATAAREAID = CUSTINVOICETRANS.DATAAREAID
WHERE     (CUSTTABLE.DATAAREAID = N'DAY') AND (CUSTINVOICETRANS.ITEMID <> '') AND (MONTH(CUSTINVOICETRANS.INVOICEDATE) 
= MONTH(GETDATE())) AND (YEAR(CUSTINVOICETRANS.INVOICEDATE) = 2012)) AS INVLINESFLAT


GROUP BY ACCOUNTNUM, ITEMID) AS COMPLETEINVLINES ON KEY_CUST_ITEM.ACCOUNTNUM = COMPLETEINVLINES.ACCOUNTNUM AND 
KEY_CUST_ITEM.ITEMID = COMPLETEINVLINES.ITEMID) AS BUILDING_CURRENT_MONTH_THIS_YEAR ON 
BUILDING_CURRENT_MONTH_LAST_YEAR.ACCOUNTNUM = BUILDING_CURRENT_MONTH_THIS_YEAR.ACCOUNTNUM AND 
BUILDING_CURRENT_MONTH_LAST_YEAR.ITEMID = BUILDING_CURRENT_MONTH_THIS_YEAR.ITEMID
--ORDER BY CUSTACCOUNT, [ITEM NUMBER]


/*START OF QUARTER*/
/*BUILDING_CURRENT_QTR_LAST_YEAR*/
INNER JOIN

(SELECT     COMPLETEINVLINES.LINEAMTSUM, KEY_CUST_ITEM.ACCOUNTNUM, KEY_CUST_ITEM.ITEMID
FROM          (SELECT     CUSTTABLE_2.ACCOUNTNUM, CUSTINVOICETRANS_2.ITEMID
FROM          CUSTTABLE AS CUSTTABLE_2 INNER JOIN
CUSTINVOICEJOUR AS CUSTINVOICEJOUR_2 ON CUSTTABLE_2.ACCOUNTNUM = CUSTINVOICEJOUR_2.ORDERACCOUNT AND 
CUSTTABLE_2.DATAAREAID = CUSTINVOICEJOUR_2.DATAAREAID INNER JOIN
CUSTINVOICETRANS AS CUSTINVOICETRANS_2 ON CUSTINVOICEJOUR_2.INVOICEID = CUSTINVOICETRANS_2.INVOICEID AND 
CUSTINVOICEJOUR_2.DATAAREAID = CUSTINVOICETRANS_2.DATAAREAID
WHERE      (CUSTTABLE_2.DATAAREAID = N'CMC') AND (CUSTINVOICETRANS_2.ITEMID <> '') AND DATEPART(QQ,CUSTINVOICETRANS_2.INVOICEDATE) 
= { fn QUARTER(GETDATE())} AND (YEAR(CUSTINVOICETRANS_2.INVOICEDATE) >= YEAR(GETDATE()) - 1)
GROUP BY CUSTTABLE_2.ACCOUNTNUM, CUSTINVOICETRANS_2.ITEMID
UNION
SELECT     CUSTTABLE.ACCOUNTNUM, CUSTINVOICETRANS.ITEMID
FROM         Ax09_Production.dbo.CUSTTABLE AS CUSTTABLE INNER JOIN
Ax09_Production.dbo.CUSTINVOICEJOUR AS CUSTINVOICEJOUR ON CUSTTABLE.ACCOUNTNUM = CUSTINVOICEJOUR.ORDERACCOUNT AND 
CUSTTABLE.DATAAREAID = CUSTINVOICEJOUR.DATAAREAID INNER JOIN
Ax09_Production.dbo.CUSTINVOICETRANS AS CUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID AND 
CUSTINVOICEJOUR.DATAAREAID = CUSTINVOICETRANS.DATAAREAID
WHERE     (CUSTTABLE.DATAAREAID = N'DAY') AND (CUSTINVOICETRANS.ITEMID <> '') AND DATEPART(QQ,CUSTINVOICETRANS.INVOICEDATE) 
= { fn QUARTER(GETDATE())} AND (YEAR(CUSTINVOICETRANS.INVOICEDATE) >= YEAR(GETDATE()) - 1)
GROUP BY CUSTTABLE.ACCOUNTNUM, CUSTINVOICETRANS.ITEMID) AS KEY_CUST_ITEM LEFT OUTER JOIN
(SELECT     ACCOUNTNUM, ITEMID, SUM(LINEAMOUNT) AS LINEAMTSUM
FROM          (SELECT     CUSTTABLE_1.ACCOUNTNUM, CUSTINVOICETRANS_1.ITEMID, CUSTINVOICETRANS_1.LINEAMOUNT
FROM          CUSTTABLE AS CUSTTABLE_1 INNER JOIN
CUSTINVOICEJOUR AS CUSTINVOICEJOUR_1 ON CUSTTABLE_1.ACCOUNTNUM = CUSTINVOICEJOUR_1.ORDERACCOUNT AND 
CUSTTABLE_1.DATAAREAID = CUSTINVOICEJOUR_1.DATAAREAID INNER JOIN
CUSTINVOICETRANS AS CUSTINVOICETRANS_1 ON CUSTINVOICEJOUR_1.INVOICEID = CUSTINVOICETRANS_1.INVOICEID AND 
CUSTINVOICEJOUR_1.DATAAREAID = CUSTINVOICETRANS_1.DATAAREAID
WHERE      (CUSTTABLE_1.DIMENSION = N'DAY') AND (CUSTINVOICETRANS_1.ITEMID <> '') AND 
DATEPART(QQ,CUSTINVOICETRANS_1.INVOICEDATE) = { fn QUARTER(GETDATE())} AND (YEAR(CUSTINVOICETRANS_1.INVOICEDATE) = 2011)
UNION ALL
SELECT     CUSTTABLE.ACCOUNTNUM, CUSTINVOICETRANS.ITEMID, CUSTINVOICETRANS.LINEAMOUNT
FROM         Ax09_Production.dbo.CUSTTABLE AS CUSTTABLE INNER JOIN
Ax09_Production.dbo.CUSTINVOICEJOUR AS CUSTINVOICEJOUR ON 
CUSTTABLE.ACCOUNTNUM = CUSTINVOICEJOUR.ORDERACCOUNT AND 
CUSTTABLE.DATAAREAID = CUSTINVOICEJOUR.DATAAREAID INNER JOIN
Ax09_Production.dbo.CUSTINVOICETRANS AS CUSTINVOICETRANS ON 
CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID AND 
CUSTINVOICEJOUR.DATAAREAID = CUSTINVOICETRANS.DATAAREAID
WHERE     (CUSTTABLE.DATAAREAID = N'DAY') AND (CUSTINVOICETRANS.ITEMID <> '') AND DATEPART(QQ,CUSTINVOICETRANS.INVOICEDATE) 
= { fn QUARTER(GETDATE())} AND (YEAR(CUSTINVOICETRANS.INVOICEDATE) = 2011)) AS INVLINESFLAT
GROUP BY ACCOUNTNUM, ITEMID) AS COMPLETEINVLINES ON KEY_CUST_ITEM.ACCOUNTNUM = COMPLETEINVLINES.ACCOUNTNUM AND 
KEY_CUST_ITEM.ITEMID = COMPLETEINVLINES.ITEMID) AS BUILDING_CURRENT_QTR_LAST_YEAR 


INNER JOIN

(SELECT     KEY_CUST_ITEM_QTR.ACCOUNTNUM, KEY_CUST_ITEM_QTR.ITEMID, COMPLETEINVLINES.LINEAMTSUM

FROM

/*KEY_CUST_ITEM_QTD*/

(SELECT     CUSTTABLE_2.ACCOUNTNUM, CUSTINVOICETRANS_2.ITEMID
FROM          CUSTTABLE AS CUSTTABLE_2 INNER JOIN
CUSTINVOICEJOUR AS CUSTINVOICEJOUR_2 ON CUSTTABLE_2.ACCOUNTNUM = CUSTINVOICEJOUR_2.ORDERACCOUNT AND 
CUSTTABLE_2.DATAAREAID = CUSTINVOICEJOUR_2.DATAAREAID INNER JOIN 
CUSTINVOICETRANS AS CUSTINVOICETRANS_2 ON CUSTINVOICEJOUR_2.INVOICEID = CUSTINVOICETRANS_2.INVOICEID AND 
CUSTINVOICEJOUR_2.DATAAREAID = CUSTINVOICETRANS_2.DATAAREAID
WHERE      (CUSTTABLE_2.DATAAREAID = N'CMC') AND (CUSTINVOICETRANS_2.ITEMID <> '') AND DATEPART(QQ,CUSTINVOICETRANS_2.INVOICEDATE) = { fn QUARTER(GETDATE())} AND (YEAR(CUSTINVOICETRANS_2.INVOICEDATE) >= 2011)
GROUP BY CUSTTABLE_2.ACCOUNTNUM, CUSTINVOICETRANS_2.ITEMID
UNION
SELECT     CUSTTABLE.ACCOUNTNUM, CUSTINVOICETRANS.ITEMID
FROM         Ax09_Production.dbo.CUSTTABLE AS CUSTTABLE INNER JOIN
Ax09_Production.dbo.CUSTINVOICEJOUR AS CUSTINVOICEJOUR ON CUSTTABLE.ACCOUNTNUM = CUSTINVOICEJOUR.ORDERACCOUNT AND 
CUSTTABLE.DATAAREAID = CUSTINVOICEJOUR.DATAAREAID INNER JOIN
Ax09_Production.dbo.CUSTINVOICETRANS AS CUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID AND 
CUSTINVOICEJOUR.DATAAREAID = CUSTINVOICETRANS.DATAAREAID
WHERE     (CUSTTABLE.DATAAREAID = N'DAY') AND (CUSTINVOICETRANS.ITEMID <> '') AND DATEPART(QQ,CUSTINVOICETRANS.INVOICEDATE) = { fn QUARTER(GETDATE())} AND (YEAR(CUSTINVOICETRANS.INVOICEDATE) >= 2011)
GROUP BY CUSTTABLE.ACCOUNTNUM, CUSTINVOICETRANS.ITEMID) AS KEY_CUST_ITEM_QTR

LEFT OUTER JOIN

(SELECT     ACCOUNTNUM, ITEMID, SUM(LINEAMOUNT) AS LINEAMTSUM

FROM

/*BUILDING_CURRENT_QTR_THIS_YEAR*/


(SELECT     CUSTTABLE_1.ACCOUNTNUM, CUSTINVOICETRANS_1.ITEMID, CUSTINVOICETRANS_1.LINEAMOUNT
FROM          CUSTTABLE AS CUSTTABLE_1 INNER JOIN
CUSTINVOICEJOUR AS CUSTINVOICEJOUR_1 ON CUSTTABLE_1.ACCOUNTNUM = CUSTINVOICEJOUR_1.ORDERACCOUNT AND 
CUSTTABLE_1.DATAAREAID = CUSTINVOICEJOUR_1.DATAAREAID INNER JOIN
CUSTINVOICETRANS AS CUSTINVOICETRANS_1 ON CUSTINVOICEJOUR_1.INVOICEID = CUSTINVOICETRANS_1.INVOICEID AND 
CUSTINVOICEJOUR_1.DATAAREAID = CUSTINVOICETRANS_1.DATAAREAID
WHERE      (CUSTTABLE_1.DIMENSION = N'DAY') AND (CUSTINVOICETRANS_1.ITEMID <> '') AND 
DATEPART(QQ,CUSTINVOICETRANS_1.INVOICEDATE) = { fn QUARTER(GETDATE())} AND (YEAR(CUSTINVOICETRANS_1.INVOICEDATE) = 2012)
UNION ALL
SELECT     CUSTTABLE.ACCOUNTNUM, CUSTINVOICETRANS.ITEMID, CUSTINVOICETRANS.LINEAMOUNT
FROM         Ax09_Production.dbo.CUSTTABLE AS CUSTTABLE INNER JOIN
Ax09_Production.dbo.CUSTINVOICEJOUR AS CUSTINVOICEJOUR ON 
CUSTTABLE.ACCOUNTNUM = CUSTINVOICEJOUR.ORDERACCOUNT AND 
CUSTTABLE.DATAAREAID = CUSTINVOICEJOUR.DATAAREAID INNER JOIN
Ax09_Production.dbo.CUSTINVOICETRANS AS CUSTINVOICETRANS ON 
CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID AND 
CUSTINVOICEJOUR.DATAAREAID = CUSTINVOICETRANS.DATAAREAID
WHERE     (CUSTTABLE.DATAAREAID = N'DAY') AND (CUSTINVOICETRANS.ITEMID <> '') AND DATEPART(QQ,CUSTINVOICETRANS.INVOICEDATE) = { fn QUARTER(GETDATE())} AND (YEAR(CUSTINVOICETRANS.INVOICEDATE) = 2012)) AS INVLINESFLAT


GROUP BY ACCOUNTNUM, ITEMID) AS COMPLETEINVLINES ON KEY_CUST_ITEM_QTR.ACCOUNTNUM = COMPLETEINVLINES.ACCOUNTNUM AND 
KEY_CUST_ITEM_QTR.ITEMID = COMPLETEINVLINES.ITEMID) AS BUILDING_CURRENT_QTR_THIS_YEAR ON 
BUILDING_CURRENT_QTR_LAST_YEAR.ACCOUNTNUM = BUILDING_CURRENT_QTR_THIS_YEAR.ACCOUNTNUM AND 
BUILDING_CURRENT_QTR_LAST_YEAR.ITEMID = BUILDING_CURRENT_QTR_THIS_YEAR.ITEMID 
--ORDER BY CUSTACCOUNT, [ITEM NUMBER]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 15:46:45

didnt understand why you need such a complicated query for this.

I would have just done this as (just a stub for you to work on)


SELECT Customer,Item,
SUM(CASE WHEN MONTH(datefield) = MONTH(Getdate()) AND YEAR(datefield) = YEAR(Getdate())  THEN YourMeasurefield END) AS CurrentYrCurrentMonth,
SUM(CASE WHEN MONTH(datefield) = MONTH(Getdate()) AND YEAR(datefield) = YEAR(Getdate())-1  THEN YourMeasurefield END) AS PrevYrCurrentMonth,
SUM(CASE WHEN MONTH(datefield) = MONTH(Getdate()) AND YEAR(datefield) = YEAR(Getdate()) THEN YourMeasurefield ELSE -1 * YourMeasurefield  END) AS CurrentMonthDiff,
SUM(CASE WHEN YEAR(datefield) = YEAR(Getdate())  THEN YourMeasurefield END) AS CurrentYrCurrentQr,
SUM(CASE WHEN YEAR(datefield) = YEAR(Getdate())-1  THEN YourMeasurefield END) AS PrevYrCurrentQr,
SUM(CASE WHEN YEAR(datefield) = YEAR(Getdate()) THEN YourMeasurefield ELSE -1 * YourMeasurefield  END) AS CurrentQrDiff
FROM yourtables....
WHERE YEAR(datefield) BETWEEN YEAR(GETDATE())-1 AND YEAR(GETDATE())
AND DATEPART(qq,datefield) = DATEPART(qq,GETDATE())
GROUP BY Customer,Item

make sure you start with above and
1.Replace table part with correct table(s) names and also in case of multiple table add correct join conditions
2. Replace correct date field in all places
3. Replace correct measure field inside SUM ie Sales,stock etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-21 : 15:58:11

You've posted code all in caps and not formatted. Please us help you by posting this in a way that allows us to read it easily.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

CP
Starting Member

9 Posts

Posted - 2012-03-21 : 16:33:03

Thank you visakh16 I think that might work not sure why I went so complicated in the first place, although I thought there was a reason. I am testing out your query recommendation now.

tkizer I apologize about the lack of formatting I'm still pretty new at this and my formatting skills are, to say the least, a little lacking. I am testing visakh16's recommendation out now, if that doesn't end up working out for me I will go through and reformat the code. Sorry again for the hard read.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-21 : 16:38:32

thats ok
if its not working make sure you post some sample data with table structure and explain what you want as output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

CP
Starting Member

9 Posts

Posted - 2012-03-22 : 10:28:59

It works with one minor issue. If an account has sold an item within the past year and it wasn't within the month or quarter I get that item returned with the null value across all fields. Is there a way to get rid of this? For example:

Customer   Item   March2011   March2012   MarchDiff   Q1_2011   Q1_2012   Q1_Diff
736547     652    Null        Null        Null        Null      Null      Null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 15:11:43

it wont if you use filters like what i've shown. as I'm using filter to get only items that were sold in current quarter be it last year or present year. so i'm sure you're doing something different in your case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources