SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Column or expression in SELECT list not valid		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

donalejandro61
Starting Member

4 Posts
Posted - 03/08/2013 :  15:40:59  Show Profile  Reply with Quote
This is a SQL question and I am running out of ideas I need help from a pro, below is my SQL statement what I am trying to do is to get amount totals by month, the field IDSHPD is a date field that will show as follows for example "20130101" What I am getting is by the day along with the month I just want by the month only. If I take out IDSHPD I get and error message "Column or Expression in SELECT list not valid" Can anybody give me a work around solution. I am a newbie. Thank you in advance.

SELECT CASE SUBSTR(IDSHPD, 5, 2)
WHEN 01 THEN 'Jan' WHEN 02 THEN 'Feb' WHEN 03 THEN 'Mar' WHEN 04 THEN 'Apr' WHEN 05 THEN 'May' WHEN 06 THEN 'May' WHEN 06 THEN 'Jun' WHEN 07
THEN 'Jul' WHEN 08 THEN 'Aug' WHEN 09 THEN 'Sep' WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' ELSE '' END AS "Month",
SUBSTR(IDSHPD, 5, 2) AS Mo, SUBSTR(IDSHPD, 7, 2) AS "Day", SUBSTR(IDSHPD, 1, 4) AS "Year", SUM(IDAMTP) AS Amount, IDSHPD
FROM MEMDTANNA.INSDTL
WHERE (SUBSTR(IDSHPD, 1, 4) = "YEAR"(CURRENT_DATE))
GROUP BY SUBSTR(IDSHPD, 1, 4), SUBSTR(IDSHPD, 5, 2), IDSHPD, SUBSTR(IDSHPD, 7, 2)
ORDER BY IDSHPD

James K
Flowing Fount of Yak Knowledge

1527 Posts
Posted - 03/08/2013 :  16:00:14  Show Profile  Reply with Quote
Are you using Microsoft SQL Server? Some of the syntax doesn't seem like Microsoft T-SQL.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1527 Posts
Posted - 03/08/2013 :  16:04:28  Show Profile  Reply with Quote
The following should be similar to your query, except, I made a few changes to make it more robust.
SELECT LEFT(DATENAME(month, IDSHPD),3)  AS "Month",
       MONTH(IDSHPD)                     AS Mo,
       DAY(IDSHPD)                       AS "Day",
       YEAR(IDSHPD)                      AS "Year",
       SUM(IDAMTP)                       AS Amount,
       IDSHPD
FROM   MEMDTANNA.INSDTL
WHERE  IDSHPD >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
       AND IDSHPD < DATEADD(yy, 1 + DATEDIFF(yy, 0, GETDATE()), 0)
GROUP BY
       LEFT(DATENAME(MONTH, IDSHPD), 3),
       MONTH(IDSHPD),
       DAY(IDSHPD),
       YEAR(IDSHPD)
ORDER BY
       YEAR(IDSHPD),MONTH(IDSHPD),DAY(IDSHPD)
If you want to group by month, remove the DAY as in
SELECT LEFT(DATENAME(month, IDSHPD),3)  AS "Month",
       MONTH(IDSHPD)                     AS Mo,
       YEAR(IDSHPD)                      AS "Year",
       SUM(IDAMTP)                       AS Amount,
       IDSHPD
FROM   MEMDTANNA.INSDTL
WHERE  IDSHPD >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
       AND IDSHPD < DATEADD(yy, 1 + DATEDIFF(yy, 0, GETDATE()), 0)
GROUP BY
       LEFT(DATENAME(MONTH, IDSHPD), 3),
       MONTH(IDSHPD),
       YEAR(IDSHPD)
ORDER BY
       YEAR(IDSHPD),MONTH(IDSHPD)
Go to Top of Page

donalejandro61
Starting Member

4 Posts
Posted - 03/08/2013 :  17:13:34  Show Profile  Reply with Quote
Thanks James, the SQL is based on DB2 tables using Visual Studios I will give this a shot and make any changes if needed. I appreciate your help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 03/09/2013 :  01:02:38  Show Profile  Reply with Quote
this is ms sql server forum. So solutions posted here are guaranteed to work only in sql server. If you're using db2, try your luck at db2 forum in www.dbforums.com

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000