Query Result - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Query Result

Author

Topic

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-08-29 : 21:12:31

Hi guys, heed help..

I have a data here:


COMPANYID	BLCOUNT	        MONTHYEAR
1	        15	        May 2013
1	        67	        June 2013
1	        9	        July 2013

here is my code for now:

		  SELECT COMPANYID, SUM(BLCOUNT) AS BLCOUNT, [MONTHNAME] + ' ' + CAST([YEAR] AS VARCHAR(10)) AS [MONTHYEAR] FROM 
		  (SELECT COMPANYID, COUNT(BLNUMBER) AS BLCOUNT, DATENAME(m, RECEIVEDATE) AS [MONTHNAME], YEAR(RECEIVEDATE) AS [YEAR], DATEPART(m, RECEIVEDATE) AS SORTBYMONTH
		   FROM OSUSR_G8V_BLINFO
		   WHERE DATEPART(m, RECEIVEDATE) >= 4 AND DATEPART(m, RECEIVEDATE) <= 7 AND DATEPART(year, RECEIVEDATE) = 2013 AND COMPANYID = 1
		  GROUP BY COMPANYID, RECEIVEDATE) AS BLLIST
		  GROUP BY COMPANYID, [MONTHNAME], [YEAR], SORTBYMONTH
		  ORDER BY SORTBYMONTH

The result of my code is only three rows. what I want is like this


COMPANYID	BLCOUNT	        MONTHYEAR
1	        0	        April 2013
1	        15	        May 2013
1	        67	        June 2013
1	        9	        July 2013

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-08-29 : 21:25:08

[code]
SELECT COMPANYID,
SUM(BLCOUNT) AS BLCOUNT,
[MONTHNAME] + ' ' + CAST([YEAR] AS VARCHAR(10)) AS [MONTHYEAR]
FROM (
SELECT COMPANYID,
COUNT(BLNUMBER) AS BLCOUNT,
DATENAME(m, RECEIVEDATE) AS [MONTHNAME],
YEAR(RECEIVEDATE) AS [YEAR],
DATEPART(m, RECEIVEDATE) AS SORTBYMONTH
FROM OSUSR_G8V_BLINFO
WHERE DATEPART(m, RECEIVEDATE) >= 4 AND DATEPART(m, RECEIVEDATE) <= 7
AND DATEPART(year, RECEIVEDATE) = 2013 AND COMPANYID = 1
GROUP BY COMPANYID, RECEIVEDATE

UNION ALL

SELECT 1 as COMPANYID,
0 as BLCOUNT,
DATENAME(month, dateadd(month, number - 1, '20130101')) as MONTHNAME,
number as SORTBYMONTH
FROM master..spt_values
WHERE type = 'P'
AND number between 4 and 7
) AS BLLIST
GROUP BY COMPANYID, [MONTHNAME], [YEAR], SORTBYMONTH
ORDER BY SORTBYMONTH[/code]

KH
[spoiler]Time is always against us[/spoiler]

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-08-29 : 21:36:46

How about with multiple company to show?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-08-29 : 21:39:59

cross join your company table to it


SELECT	COMPANYID, 
	0 as BLCOUNT, 
	DATENAME(month, dateadd(month, number - 1, '20130101')) as MONTHNAME,
	number as SORTBYMONTH
FROM	Company CROSS JOIN master..spt_values
WHERE	type	= 'P'
AND	number	between 4 and 7

KH
[spoiler]Time is always against us[/spoiler]

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-09-01 : 22:05:12

Thanks. How about date?


SELECT	COMPANYID, 
	0 as BLCOUNT, 
	dateadd(dd, number - 1, '20130101')) as RECEIVEDDATE,
FROM	Company CROSS JOIN master..spt_values
WHERE	type	= 'P'
AND	number	between 4 and 7

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-09-01 : 22:13:09

quote:

Originally posted by chriztoph

Thanks. How about date?


SELECT	COMPANYID, 
	0 as BLCOUNT, 
	dateadd(dd, number - 1, '20130101')) as RECEIVEDDATE,
FROM	Company CROSS JOIN master..spt_values
WHERE	type	= 'P'
AND	number	between 4 and 7

what do you mean ?

the 4 and 7 is the month

KH
[spoiler]Time is always against us[/spoiler]

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-09-01 : 22:17:06

Sorry 4 and 7 should be date. for example 01-04-2013 and 01-07-2013


SELECT	COMPANYID, 
	0 as BLCOUNT, 
	dateadd(dd, number - 1, '20130101')) as RECEIVEDDATE,
FROM	Company CROSS JOIN master..spt_values
WHERE	type	= 'P'
AND	number	between '01-04-2013' and '01-07-2013'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-09-01 : 22:48:09

why do you want all the dates between Apr & July ? your end result is group by month anyway

KH
[spoiler]Time is always against us[/spoiler]

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-09-02 : 01:16:42

I have 3 criteria and those criteria are monthly, weekly and by date.
So if I want it monthly the result would be like my first question but if I want it by date it should be daily.

I just want to show all dates between the given by the user just like my first question.

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-09-02 : 04:39:37

The result that I need is like this:


CompanyID BLCount ReceivedDate
1	  0	  2013-01-04
1	  0	  2013-01-05
1	  1	  2013-01-06
1	  0	  2013-01-07

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-09-02 : 04:55:49

But in your 1st post, the result that you want is group by month not day

quote:
Originally posted by chriztoph
The result of my code is only three rows. what I want is like this

COMPANYID BLCOUNT MONTHYEAR
1 0 April 2013
1 15 May 2013
1 67 June 2013
1 9 July 2013

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-09-02 : 04:57:47

quote:
Originally posted by chriztoph

I have 3 criteria and those criteria are monthly, weekly and by date.
So if I want it monthly the result would be like my first question but if I want it by date it should be daily.

I just want to show all dates between the given by the user just like my first question.

use F_TABLE_DATE and left join to your table
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

KH
[spoiler]Time is always against us[/spoiler]

chriztoph
Posting Yak Master

184 Posts

Posted - 2013-09-02 : 05:20:01

quote:
Originally posted by khtan

But in your 1st post, the result that you want is group by month not day
quote:
Originally posted by chriztoph
The result of my code is only three rows. what I want is like this

COMPANYID BLCOUNT MONTHYEAR
1 0 April 2013
1 15 May 2013
1 67 June 2013
1 9 July 2013

KH
[spoiler]Time is always against us[/spoiler]

Yes that my requirement for monthly. But I need another query for daily in which my parameters is date.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources