Help with query in access (1 Viewer)

[krish03]

SELECT
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH = 1 GROUP BY SYR_CTR_START_MONTH) "JAN" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH = 2 GROUP BY SYR_CTR_START_MONTH) "FEB" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH = 3 GROUP BY SYR_CTR_START_MONTH) "MAR" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH = 4 GROUP BY SYR_CTR_START_MONTH) "APR" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH = 5 GROUP BY SYR_CTR_START_MONTH) "MAY" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH = 6 GROUP BY SYR_CTR_START_MONTH)" JUN" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH = 7 GROUP BY SYR_CTR_START_MONTH) "JUL" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH = 8 GROUP BY SYR_CTR_START_MONTH) "AUG" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH = 9 GROUP BY SYR_CTR_START_MONTH) "SEP" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH = 10 GROUP BY SYR_CTR_START_MONTH) "OCT" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH = 11 GROUP BY SYR_CTR_START_MONTH) "NOV" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH = 12 GROUP BY SYR_CTR_START_MONTH) "DEC" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH in( 1,2,3) ) "Q1" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH in( 4,5,6) ) "Q2" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH in( 7,8,9) ) "Q3" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH in( 10,11,12) ) "Q4" ,
(select sum(SYR_SUM_AMZ_INV_AMT) from NTLRESULTS_AZ_SMY_YPC_RPT_TBL where SYR_UPD_USER='EXCEPTION2' and SYR_CTR_START_MONTH in( 1,2,3,4,5,6,7,8,9,10,11,12) )" FY"
output should look like this for 2013
jan feb mar apr mar jun jul aug sep oct nov dev q1 q2 q3 q4 fy(full year)
Amt amt amtamt amt amt amt amt amt amt amt amt amt amt amt amt

 

[krish03]

I am getting syntax error (missing operator) in the query

 

[plog]

The syntax error is that you have an incomplete statement. You start off with a SELECT clause but never put a FROM clause to go with it. Your query essentially looks like this:

[FONT=Calibri][B]SELECT[/B][/FONT]
[FONT=Calibri] (select sum(SYR_SUM_AMZ_INV_AMT) from...[/FONT]
[FONT=Calibri] (select sum(SYR_SUM_AMZ_INV_AMT) from...[/FONT]
[FONT=Calibri] ...[/FONT]
[FONT=Calibri] ...[/FONT]
[FONT=Calibri] ...[/FONT]
[FONT=Calibri] (select sum(SYR_SUM_AMZ_INV_AMT) from...[/FONT]

That bolded SELECT starts an incomplete SQL statement. It needs a FROM. But the good news is that's not the issue on how to solve what you want to do. To get what you want in one query you will need 16 conditional fields in your query.

This is the pattern your query should follow:

SELECT SUM(IIf([SYR_CTR_START_MONTH]=1, SYR_SUM_AMZ_INV_AMT, 0) AS JAN, 
    SUM(IIf([SYR_CTR_START_MONTH]=2, SYR_SUM_AMZ_INV_AMT, 0) AS FEB, 
    SUM(IIf([SYR_CTR_START_MONTH]=3, SYR_SUM_AMZ_INV_AMT, 0) AS MAR,
    ...
    ...
    ...
    SUM(IIf([SYR_CTR_START_MONTH]<=3, SYR_SUM_AMZ_INV_AMT, 0) AS Q1,  
    SUM(IIf([SYR_CTR_START_MONTH]>=4 AND (SYR_CTR_START_MONTH]<=6, SYR_SUM_AMZ_INV_AMT, 0) AS Q2,  
    ...
    ...
    SUM([SYR_CTR_START_MONTH]) AS FY
FROM NTLRESULTS_AZ_YPC_RPT_TBL
WHERE SYR_UPD_USER='EXCEPTION2';

Every field determines if the current row falls within its timeframe (January, February, Q1, etc). If it does, it includes it and will add them all up for that specific timeframe, if not it will add 0 for that record.

This could be simpler if you didn't mix timeframes. By mixing timeframes (months, quarters, years) you have to do it this way, rather than using a Cross-Tab query. If you want just one timeframe, google that term.

 

[krish03]

Thankx man....

 

[krish03]

thanx..will try this....

 

[krish03]

SUM([SYR_CTR_START_MONTH]) AS FY
unable to get the whole amount for year.....

 

[krish03]

Kk got it..it should be
sum(syr_sum_amz_inv_amt) as fy

 

[krish03]

What if i need that query to run in sql server..wil it work or any changes..

 

[krish03]

SELECT SUM(IIf([SYR_CTR_START_MONTH]=1, SYR_SUM_AMZ_INV_AMT, 0)) AS JAN,
SUM(IIf([SYR_CTR_START_MONTH]=2, SYR_SUM_AMZ_INV_AMT, 0)) AS FEB,
SUM(IIf([SYR_CTR_START_MONTH]=3, SYR_SUM_AMZ_INV_AMT, 0)) AS MAR,
SUM(IIF([SYR_CTR_START_MONTH] = 4 ,SYR_SUM_AMZ_INV_AMT ,0)) AS APR ,
SUM( IIF ([SYR_CTR_START_MONTH] = 5 ,SYR_SUM_AMZ_INV_AMT ,0)) AS MAY ,
SUM( IIF ([SYR_CTR_START_MONTH] = 6 ,SYR_SUM_AMZ_INV_AMT ,0)) AS JUN ,
SUM( IIF ([SYR_CTR_START_MONTH] = 7 ,SYR_SUM_AMZ_INV_AMT ,0)) AS JUL ,
SUM( IIF ([SYR_CTR_START_MONTH] = 8 ,SYR_SUM_AMZ_INV_AMT ,0)) AS AUG ,
SUM( IIF ([SYR_CTR_START_MONTH] = 9 ,SYR_SUM_AMZ_INV_AMT ,0)) AS SEP ,
SUM( IIF ([SYR_CTR_START_MONTH] = 10 ,SYR_SUM_AMZ_INV_AMT ,0)) AS OCT ,
SUM( IIF ([SYR_CTR_START_MONTH] = 11 ,SYR_SUM_AMZ_INV_AMT ,0)) AS NOV ,
SUM( IIF ([SYR_CTR_START_MONTH] = 12 ,SYR_SUM_AMZ_INV_AMT ,0)) AS DEC ,
SUM(IIf([SYR_CTR_START_MONTH]<=3, SYR_SUM_AMZ_INV_AMT, 0)) AS Q1,
SUM(IIf([SYR_CTR_START_MONTH]>=4 AND [SYR_CTR_START_MONTH]<=6, SYR_SUM_AMZ_INV_AMT, 0)) AS Q2,
SUM(IIf([SYR_CTR_START_MONTH]>=7 AND [SYR_CTR_START_MONTH]<=9, SYR_SUM_AMZ_INV_AMT, 0)) AS Q3,
SUM(IIf([SYR_CTR_START_MONTH]>=10 AND [SYR_CTR_START_MONTH]<=12, SYR_SUM_AMZ_INV_AMT, 0)) AS Q4,
SUM(SYR_SUM_AMZ_INV_AMT) AS FY
FROM NTLRESULTS_AZ_SMY_YPC_RPT_TBL
WHERE SYR_UPD_USER='EXCEPTION2';
............THIS ONE IN SQL...............WILL WORK OR NOT

 

[plog]

No idea if it will work in SQL Server. Are you afraid running it might blow up the world? I normally don't do this, but I personally guarantee that code will not destroy civilization in its entirety. Maybe a 3rd world country or small African village, but that's absolute worst case scenario.