SQL Query

[lannym]

Hello, I'm trying to complete a SQL Query to return a set of data for creating a dynamic chart.

For example; I would like to return the data for the last 12 months. So for Feb 2007, would return data for Jan 06 to Jan 07, and in Mar 07, data returned would be Feb 06 to Feb 07.

I'm somewhat new to SQL programming, if there is a better way to accoplish this goal, i'm open to ideas!

I'm creating this Query within another program (netcharts), which is used to create the chart and display it on a webpage.

(Another question: How can I change the date format of the returned date? I know based on what i have in the Query, it will only show the month as a number, but how would I have it show the month and the year (1/2006 or 2/2007 for example)

SELECT month([SQLTranDate]) as Month, sum([SQLTranAmt]) as Total
FROM chart1
/* Show Last year and current year data - 12 month only based on current month */
Where (year([SQLTranDate]) = year(getdate()) -2 and month([SQLTranDate]) <=  month(getdate()))
OR
(year([SQLTranDate]) = year(getdate()) -1 and month([SQLTranDate]) >  month(getdate()))
GROUP BY month([SQLTranDate])
ORDER BY month([SQLTranDate])

 

[vbkris]

SELECT datepart(m,[SQLTranDate])+'/'+datepart(yy,[SQLTranDate]) as MonthYear, sum([SQLTranAmt]) as Total
FROM chart1
/* Show Last year and current year data - 12 month only based on current month */
Where SQLTranDate between cast(month(GetDate())-1 as varchar)+'-1'+'-'+cast(year(GetDate())-1 as varchar)
,cast(month(GetDate())-1 as varchar)+'-1'+'-'+cast(year(GetDate()) as varchar)
GROUP BY datepart(m,[SQLTranDate])+'/'+datepart(yy,[SQLTranDate])
ORDER BY datepart(m,[SQLTranDate])+'/'+datepart(yy,[SQLTranDate])

ofcourse i assume that you use the MM/DD/YYYY format...


Known is handfull, Unknown is worldfull

 

[lannym]

Thank you, I put the code in, but get an error about the comma on line 4.

(I cut and paste the code in, and moved the line with the comma up to make it a long line, and it still shows the error.)

Data failure.
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 4: Incorrect syntax near ','.

 

[amrita418]

lannym,
This definitely is not the most elegant solution but should work.

select 		month([SQLTranDate]) as Month, 
		year(SQLTranDate]) as Year,
		sum([SQLTranAmt]) as Total
FROM 		chart1
where 		[SQLTranDate] >= convert(varchar(10), dateadd(day, -day(getdate()) + 1, dateadd(month, -12, getdate())), 101)
GROUP BY 	month([SQLTranDate]), year(SQLTranDate])
ORDER BY 	month([SQLTranDate]), year(SQLTranDate])

Best Regards,
AA

 

[lannym]

Thank you, I greatly appriciate everyeones help, looks like it's almost there. I wish i could be more helpful with changing the code, but i guess that why i'm here, being new to the programming side I'd like to learn wherever i can.

Show's both months for each year though.
Feb06 through --> Jan07 (ending up with 12 months)

(Output form current code)

Month Year Total
1 2005 21.1000
1 2006 259.5500
2 2006 12701.9500
3 2005 22.4000
3 2006 24811.4700
4 2005 1106.1800
4 2006 188896.2000
5 2006 1408.4000
6 2005 102.5000
6 2006 1136.8900
7 2006 8315.1400
8 2006 9526.9800
9 2006 904.1800
10 2006 812.2800
11 2006 187.5900
12 2005 841.9100
12 2006 588.3300

 

[amrita418]

lannym,
Can you post the query you are running? You should not get data older than 12 months with the query I posted so not sure how you are getting 2005 data.

Also, the only month you would see twice in the output would be feb because I did not filter current month. Do you want that filtered?

Best Regards,
AA

 

[lannym]

Current Code:
Note: there is no 2007 data in the table, and the current year is correct on the system.

select month([SQLTranDate]) as Month, year([SQLTranDate]) as Year, sum([SQLTranAmt]) as Total
FROM chart1
where [SQLTranDate] >= convert(varchar(10), dateadd(day, -day(getdate()) + 1, dateadd(month, -12, getdate())), 101)
GROUP BY     month([SQLTranDate]), year([SQLTranDate])
ORDER BY     month([SQLTranDate]), year([SQLTranDate])

 

[amrita418]

Try this:

select         convert(datetime, convert(varchar, month(SQLTranDate)) +  '/01/'
            +  convert(varchar, year(SQLTranDate))) as Year,
        	sum(SQLTranAmt) as Total
FROM         	chart1
where         	SQLTranDate >= convert(varchar(10), dateadd(day, -day(getdate()) + 1, dateadd(month, -12, getdate())), 101)
		and SQLTranDate < convert(varchar(10), dateadd(day, -day(getdate()) + 1, getdate()), 101)
GROUP BY     	convert(datetime, convert(varchar, month(SQLTranDate)) +  '/01/'
            +  convert(varchar, year(SQLTranDate)))
ORDER BY     	1

Best Regards,
AA

 

[lannym]

No results, only the "year" & "total" are showing.

 

[amrita418]

The Year field actually has date format so would see something like '2006-02-01 00:00:00.000' (one record per each month-year combination.

Isn't this the output you expected?
Year Total
2006-02-01 00:00:00.000 93
2006-03-01 00:00:00.000 1679
2006-04-01 00:00:00.000 258
2006-05-01 00:00:00.000 69
2006-06-01 00:00:00.000 1244 ?

 

[lannym]

I should have explained better, only the words 'year' and 'total' were showing on the screen. (no numbers)

something like this:

=================
Year Total






=================

 

[amrita418]

3 Questions:

1) Do you have any data after 2006 Jan?
2) What is the datatype of SQLTranDate?
3) What database are you using?

 

[lannym]

1) Do you have any data after 2006 Jan?

Yes

2) What is the datatype of SQLTranDate?

Data Type is 'char' **see note

3) What database are you using?

SQL 2005 Standard

**When you asked about the datatype, that made me think of something, i copied this data from our production db to my test server... On the production server, the date field is a 'smalldatetime'.

However, I didn't think anything off it as I was tinking with the year() and month() in my test db without problem. Obviously, that may not always be the case.

 

[amrita418]

Yes, when both the datatypes in the where clause are of datatype varchar your result set could be messy.

What does this get you?

select         convert(datetime, convert(varchar, month(SQLTranDate)) +  '/01/'
            +  convert(varchar, year(SQLTranDate))) as Year,
        	sum(SQLTranAmt) as Total
FROM         	chart1
where         	convert(datetime, SQLTranDate) >= convert(varchar(10), dateadd(day, -day(getdate()) + 1, dateadd(month, -12, getdate())), 101)
		and convert(datetime, SQLTranDate) < convert(varchar(10), dateadd(day, -day(getdate()) + 1, getdate()), 101)
GROUP BY     	convert(datetime, convert(varchar, month(SQLTranDate)) +  '/01/'
            +  convert(varchar, year(SQLTranDate)))
ORDER BY     	1

 

[lannym]

Look good now, thank you.

The number figure is acutally dollars ($). Do you think the number format or datatype should be changed?

(from test database)



This the result:

Year Total 
Feb 01, 2006 6435333.9500 
Mar 01, 2006 243451.4700 
Apr 01, 2006 134586.2000 
May 01, 2006 1345408.4000 
Jun 01, 2006 1133456.8900 
Jul 01, 2006 833455.1400 
Aug 01, 2006 934556.9800 
Sep 01, 2006 9356404.1800 
Oct 01, 2006 814532.2800 
Nov 01, 2006 045487.5900 
Dec 01, 2006 58934728.3300 
Jan 01, 2007 214534.1000