SQL Grouping Question for External BAQ's

Yahoo Archive
1
Should you use a CASE statement? With one Select



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Vic Drecchio
Sent: Thursday, February 14, 2013 2:37 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] SQL Grouping Question for External BAQ's





I'd take out the GROUP BY and I'd also do a SELECT DISTINCT.

Typing this on my awesome Nokia Windows Phone right now but should be like:

SELECT DISTINCT
InvcDtl.PartNum,
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
FROM InvcDtl ORDER BY 1

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of
mitchelljohn
Sent: Thursday, February 14, 2013 4:27 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] SQL Grouping Question for External BAQ's

I am creating a SQL view to be pulled into an external BAQ but I am
completely stuck.

I am able to create the view, however, the current grouping shows the total
for the year for each PartNum record. I am trying to show the total per
PartNum per year. I know that I can do a rollup but I am trying to get each
year's total as a column so that I can pull it into a BAQ as a single line.
If anyone can show me where I am screwing it up I'll buy you a beer in
Nashville at Insights.

SELECT
InvcDtl.PartNum,
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
FROM
InvcDtl
GROUP By InvcDtl.PartNum

Thanks,
John Mitchell
McAuliffe's Industrial

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
2
I am creating a SQL view to be pulled into an external BAQ but I am completely stuck.

I am able to create the view, however, the current grouping shows the total for the year for each PartNum record. I am trying to show the total per PartNum per year. I know that I can do a rollup but I am trying to get each year's total as a column so that I can pull it into a BAQ as a single line. If anyone can show me where I am screwing it up I'll buy you a beer in Nashville at Insights.

SELECT
InvcDtl.PartNum,
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
FROM
InvcDtl
GROUP By InvcDtl.PartNum


Thanks,
John Mitchell
McAuliffe's Industrial
3
I'd take out the GROUP BY and I'd also do a SELECT DISTINCT.



Typing this on my awesome Nokia Windows Phone right now but should be like:



SELECT DISTINCT
InvcDtl.PartNum,
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
FROM InvcDtl ORDER BY 1





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
mitchelljohn
Sent: Thursday, February 14, 2013 4:27 PM
To: vantage@yahoogroups.com
Subject: [Vantage] SQL Grouping Question for External BAQ's





I am creating a SQL view to be pulled into an external BAQ but I am
completely stuck.

I am able to create the view, however, the current grouping shows the total
for the year for each PartNum record. I am trying to show the total per
PartNum per year. I know that I can do a rollup but I am trying to get each
year's total as a column so that I can pull it into a BAQ as a single line.
If anyone can show me where I am screwing it up I'll buy you a beer in
Nashville at Insights.

SELECT
InvcDtl.PartNum,
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
(SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
FROM
InvcDtl
GROUP By InvcDtl.PartNum

Thanks,
John Mitchell
McAuliffe's Industrial



No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13

[Non-text portions of this message have been removed]
4
Vic,

I added distinct and removed the group by. No luck. It's still only showing the total for the year, not grouping by part by year. Thanks though,

John Mitchell
McAuliffe's Industrial


--- In vantage@yahoogroups.com, "Vic Drecchio" wrote:
>
> I'd take out the GROUP BY and I'd also do a SELECT DISTINCT.
>
>
>
> Typing this on my awesome Nokia Windows Phone right now but should be like:
>
>
>
> SELECT DISTINCT
> InvcDtl.PartNum,
> (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
> (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
> (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
> FROM InvcDtl ORDER BY 1
>
>
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
> mitchelljohn
> Sent: Thursday, February 14, 2013 4:27 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] SQL Grouping Question for External BAQ's
>
>
>
>
>
> I am creating a SQL view to be pulled into an external BAQ but I am
> completely stuck.
>
> I am able to create the view, however, the current grouping shows the total
> for the year for each PartNum record. I am trying to show the total per
> PartNum per year. I know that I can do a rollup but I am trying to get each
> year's total as a column so that I can pull it into a BAQ as a single line.
> If anyone can show me where I am screwing it up I'll buy you a beer in
> Nashville at Insights.
>
> SELECT
> InvcDtl.PartNum,
> (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
> (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
> (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
> FROM
> InvcDtl
> GROUP By InvcDtl.PartNum
>
> Thanks,
> John Mitchell
> McAuliffe's Industrial
>
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
>
> [Non-text portions of this message have been removed]
>
5
OH... wait.



In each of your subselects, you need to also state "where Partnum = Partnum"



This should do it:







SELECT DISTINCT



D.PartNum,



(SELECT SUM(D1.SellingShipQty) FROM InvcDtl D1 Where D1.PartNum = D.PartNum
AND Year(D1.ShipDate)=YEAR(GetDate())) AS "ThisYear",



(SELECT SUM(D2.SellingShipQty) FROM InvcDtl D2 Where D2.PartNum = D.PartNum
AND Year(D2.ShipDate)=YEAR(GetDate())-1) AS "LastYear",



(SELECT SUM(D3.SellingShipQty) FROM InvcDtl D3 Where D3.PartNum = D.PartNum
AND Year(D3.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"



FROM InvcDtl D



ORDER BY 1





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
mitchelljohn
Sent: Thursday, February 14, 2013 4:59 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: SQL Grouping Question for External BAQ's






Vic,

I added distinct and removed the group by. No luck. It's still only showing
the total for the year, not grouping by part by year. Thanks though,

John Mitchell
McAuliffe's Industrial

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Vic
Drecchio" wrote:
>
> I'd take out the GROUP BY and I'd also do a SELECT DISTINCT.
>
>
>
> Typing this on my awesome Nokia Windows Phone right now but should be
like:
>
>
>
> SELECT DISTINCT
> InvcDtl.PartNum,
> (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
> (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
> (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
> FROM InvcDtl ORDER BY 1
>
>
>
>
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of
> mitchelljohn
> Sent: Thursday, February 14, 2013 4:27 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] SQL Grouping Question for External BAQ's
>
>
>
>
>
> I am creating a SQL view to be pulled into an external BAQ but I am
> completely stuck.
>
> I am able to create the view, however, the current grouping shows the
total
> for the year for each PartNum record. I am trying to show the total per
> PartNum per year. I know that I can do a rollup but I am trying to get
each
> year's total as a column so that I can pull it into a BAQ as a single
line.
> If anyone can show me where I am screwing it up I'll buy you a beer in
> Nashville at Insights.
>
> SELECT
> InvcDtl.PartNum,
> (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
> (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
> (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
> FROM
> InvcDtl
> GROUP By InvcDtl.PartNum
>
> Thanks,
> John Mitchell
> McAuliffe's Industrial
>
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
>
> [Non-text portions of this message have been removed]
>



No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13

[Non-text portions of this message have been removed]
6
I would have tried something like this...

SELECT InvcDTL.PartNum,
SUM(CASE WHEN InvcHead.FiscalYear = YEAR(Getdate()) then InvcDTL.SellingShipQty END) as ThisYear,
SUM(CASE WHEN InvcHead.FiscalYear = YEAR(Getdate()-1) then InvcDTL.SellingShipQty END) as LastYear,
SUM(CASE WHEN InvcHead.FiscalYear = YEAR(Getdate()-2) then InvcDTL.SellingShipQty END) as TwoYearsAgo
FROM InvcHead INNER JOIN
InvcDTL ON InvcHead.InvoiceNum = InvcDTL.InvoiceNum
Group By InvcDTL.PartNum


Not sent from my Nokia 920. LOL

--Matt Caldwell


--- In vantage@yahoogroups.com, "Vic Drecchio" <vic.drecchio@...> wrote:
>
> OH... wait.
>
>
>
> In each of your subselects, you need to also state "where Partnum = Partnum"
>
>
>
> This should do it:
>
>
>
>
>
>
>
> SELECT DISTINCT
>
>
>
> D.PartNum,
>
>
>
> (SELECT SUM(D1.SellingShipQty) FROM InvcDtl D1 Where D1.PartNum = D.PartNum
> AND Year(D1.ShipDate)=YEAR(GetDate())) AS "ThisYear",
>
>
>
> (SELECT SUM(D2.SellingShipQty) FROM InvcDtl D2 Where D2.PartNum = D.PartNum
> AND Year(D2.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
>
>
>
> (SELECT SUM(D3.SellingShipQty) FROM InvcDtl D3 Where D3.PartNum = D.PartNum
> AND Year(D3.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
>
>
>
> FROM InvcDtl D
>
>
>
> ORDER BY 1
>
>
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
> mitchelljohn
> Sent: Thursday, February 14, 2013 4:59 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: SQL Grouping Question for External BAQ's
>
>
>
>
>
>
> Vic,
>
> I added distinct and removed the group by. No luck. It's still only showing
> the total for the year, not grouping by part by year. Thanks though,
>
> John Mitchell
> McAuliffe's Industrial
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Vic
> Drecchio" wrote:
> >
> > I'd take out the GROUP BY and I'd also do a SELECT DISTINCT.
> >
> >
> >
> > Typing this on my awesome Nokia Windows Phone right now but should be
> like:
> >
> >
> >
> > SELECT DISTINCT
> > InvcDtl.PartNum,
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
> > FROM InvcDtl ORDER BY 1
> >
> >
> >
> >
> >
> > From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
> Behalf Of
> > mitchelljohn
> > Sent: Thursday, February 14, 2013 4:27 PM
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > Subject: [Vantage] SQL Grouping Question for External BAQ's
> >
> >
> >
> >
> >
> > I am creating a SQL view to be pulled into an external BAQ but I am
> > completely stuck.
> >
> > I am able to create the view, however, the current grouping shows the
> total
> > for the year for each PartNum record. I am trying to show the total per
> > PartNum per year. I know that I can do a rollup but I am trying to get
> each
> > year's total as a column so that I can pull it into a BAQ as a single
> line.
> > If anyone can show me where I am screwing it up I'll buy you a beer in
> > Nashville at Insights.
> >
> > SELECT
> > InvcDtl.PartNum,
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
> > FROM
> > InvcDtl
> > GROUP By InvcDtl.PartNum
> >
> > Thanks,
> > John Mitchell
> > McAuliffe's Industrial
> >
> >
> >
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
> >
> >
> >
> >
> > -----
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
>
> [Non-text portions of this message have been removed]
>
7
Vic,

You rock!! That's exactly what I needed. Now the next question is how do I insert this into a UD table on a specific schedule... But I can play with that for a while and see if I can get anywhere with a script. Thanks again,

John Mitchell
McAuliffe's Industrial



--- In vantage@yahoogroups.com, "Vic Drecchio" <vic.drecchio@...> wrote:
>
> OH... wait.
>
>
>
> In each of your subselects, you need to also state "where Partnum = Partnum"
>
>
>
> This should do it:
>
>
>
>
>
>
>
> SELECT DISTINCT
>
>
>
> D.PartNum,
>
>
>
> (SELECT SUM(D1.SellingShipQty) FROM InvcDtl D1 Where D1.PartNum = D.PartNum
> AND Year(D1.ShipDate)=YEAR(GetDate())) AS "ThisYear",
>
>
>
> (SELECT SUM(D2.SellingShipQty) FROM InvcDtl D2 Where D2.PartNum = D.PartNum
> AND Year(D2.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
>
>
>
> (SELECT SUM(D3.SellingShipQty) FROM InvcDtl D3 Where D3.PartNum = D.PartNum
> AND Year(D3.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
>
>
>
> FROM InvcDtl D
>
>
>
> ORDER BY 1
>
>
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
> mitchelljohn
> Sent: Thursday, February 14, 2013 4:59 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: SQL Grouping Question for External BAQ's
>
>
>
>
>
>
> Vic,
>
> I added distinct and removed the group by. No luck. It's still only showing
> the total for the year, not grouping by part by year. Thanks though,
>
> John Mitchell
> McAuliffe's Industrial
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Vic
> Drecchio" wrote:
> >
> > I'd take out the GROUP BY and I'd also do a SELECT DISTINCT.
> >
> >
> >
> > Typing this on my awesome Nokia Windows Phone right now but should be
> like:
> >
> >
> >
> > SELECT DISTINCT
> > InvcDtl.PartNum,
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
> > FROM InvcDtl ORDER BY 1
> >
> >
> >
> >
> >
> > From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
> Behalf Of
> > mitchelljohn
> > Sent: Thursday, February 14, 2013 4:27 PM
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > Subject: [Vantage] SQL Grouping Question for External BAQ's
> >
> >
> >
> >
> >
> > I am creating a SQL view to be pulled into an external BAQ but I am
> > completely stuck.
> >
> > I am able to create the view, however, the current grouping shows the
> total
> > for the year for each PartNum record. I am trying to show the total per
> > PartNum per year. I know that I can do a rollup but I am trying to get
> each
> > year's total as a column so that I can pull it into a BAQ as a single
> line.
> > If anyone can show me where I am screwing it up I'll buy you a beer in
> > Nashville at Insights.
> >
> > SELECT
> > InvcDtl.PartNum,
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
> > FROM
> > InvcDtl
> > GROUP By InvcDtl.PartNum
> >
> > Thanks,
> > John Mitchell
> > McAuliffe's Industrial
> >
> >
> >
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
> >
> >
> >
> >
> > -----
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
>
> [Non-text portions of this message have been removed]
>
8
OK, so obviously you're on SQL and I really don't know much about your end
goal, but I would tell you to create a SQL Script, turn it into a Stored
Procedure, and use SQL Job Scheduler to execute that SP as often as you
desire.



I'm assuming you'd want a full UD table refresh each time it fires (rather
than an update or append).



So your SP would be something like:



1. Truncate table UDxxx

2. INSERT INTO UDxxx SELECT <then your SELECT query>



Here's a nice explanation of INSERT INTO and SELECT INTO:
http://www.blackwasp.co.uk/SQLSelectInsert.aspx



You could use the SELECT INTO clause but you'd want to DROP the UD table
first and add an IF EXISTS.. but I don't like to DROP Epicor tables, even if
they are only "harmless" UD tables. I prefer to truncate (delete all
records) and "refill" the table rather than delete and rebuild.









From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
mitchelljohn
Sent: Friday, February 15, 2013 9:11 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: SQL Grouping Question for External BAQ's





Vic,

You rock!! That's exactly what I needed. Now the next question is how do I
insert this into a UD table on a specific schedule... But I can play with
that for a while and see if I can get anywhere with a script. Thanks again,

John Mitchell
McAuliffe's Industrial

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Vic
Drecchio" wrote:
>
> OH... wait.
>
>
>
> In each of your subselects, you need to also state "where Partnum =
Partnum"
>
>
>
> This should do it:
>
>
>
>
>
>
>
> SELECT DISTINCT
>
>
>
> D.PartNum,
>
>
>
> (SELECT SUM(D1.SellingShipQty) FROM InvcDtl D1 Where D1.PartNum =
D.PartNum
> AND Year(D1.ShipDate)=YEAR(GetDate())) AS "ThisYear",
>
>
>
> (SELECT SUM(D2.SellingShipQty) FROM InvcDtl D2 Where D2.PartNum =
D.PartNum
> AND Year(D2.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
>
>
>
> (SELECT SUM(D3.SellingShipQty) FROM InvcDtl D3 Where D3.PartNum =
D.PartNum
> AND Year(D3.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
>
>
>
> FROM InvcDtl D
>
>
>
> ORDER BY 1
>
>
>
>
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of
> mitchelljohn
> Sent: Thursday, February 14, 2013 4:59 PM
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Re: SQL Grouping Question for External BAQ's
>
>
>
>
>
>
> Vic,
>
> I added distinct and removed the group by. No luck. It's still only
showing
> the total for the year, not grouping by part by year. Thanks though,
>
> John Mitchell
> McAuliffe's Industrial
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> , "Vic
> Drecchio" wrote:
> >
> > I'd take out the GROUP BY and I'd also do a SELECT DISTINCT.
> >
> >
> >
> > Typing this on my awesome Nokia Windows Phone right now but should be
> like:
> >
> >
> >
> > SELECT DISTINCT
> > InvcDtl.PartNum,
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
> > FROM InvcDtl ORDER BY 1
> >
> >
> >
> >
> >
> > From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
> Behalf Of
> > mitchelljohn
> > Sent: Thursday, February 14, 2013 4:27 PM
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > Subject: [Vantage] SQL Grouping Question for External BAQ's
> >
> >
> >
> >
> >
> > I am creating a SQL view to be pulled into an external BAQ but I am
> > completely stuck.
> >
> > I am able to create the view, however, the current grouping shows the
> total
> > for the year for each PartNum record. I am trying to show the total per
> > PartNum per year. I know that I can do a rollup but I am trying to get
> each
> > year's total as a column so that I can pull it into a BAQ as a single
> line.
> > If anyone can show me where I am screwing it up I'll buy you a beer in
> > Nashville at Insights.
> >
> > SELECT
> > InvcDtl.PartNum,
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())) AS "ThisYear",
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())-1) AS "LastYear",
> > (SELECT SUM(InvcDtl.SellingShipQty) FROM InvcDtl Where
> > Year(InvcDtl.ShipDate)=YEAR(GetDate())-2) AS "TwoYearsAgo"
> > FROM
> > InvcDtl
> > GROUP By InvcDtl.PartNum
> >
> > Thanks,
> > John Mitchell
> > McAuliffe's Industrial
> >
> >
> >
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date:
02/14/13
> >
> >
> >
> >
> > -----
> > No virus found in this message.
> > Checked by AVG - www.avg.com
> > Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date:
02/14/13
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13
>
> [Non-text portions of this message have been removed]
>



No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5603 - Release Date: 02/14/13




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2238 / Virus Database: 2639/5605 - Release Date: 02/15/13

[Non-text portions of this message have been removed]