Numbering data/Display horizontally

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 2005 Forums

Transact-SQL (2005)

Numbering data/Display horizontally

Author

Topic

RickK
Starting Member

4 Posts

Posted - 2010-03-25 : 20:55:52

I am working with the following code:

SELECT
umLocationID,
umEquipmentID,
DATEPART(yyyy, umReadingDate) as Year,
DATEPART(MM, umReadingDate) as Month,
sum(umConsumption1) as Consumption

FROM UM_UAIConsumptionDataSummary a
group by umLocationID,umEquipmentID,DATEPART(yyyy, umReadingDate), DATEPART(MM, umReadingDate)
order by DATEPART(yyyy, umReadingDate), DATEPART(MM, umReadingDate)

Which produces the following data:

000001 06031309 2010 2 2200.00000
000001 06031309 2010 3 1700.00000
000003 07030583 2010 2 10400.00000
000003 07030583 2010 3 9600.00000
000004 07030689 2010 2 3400.00000
000004 07030689 2010 3 900.00000
000005 07013779 2010 2 8400.00000
000005 07013779 2010 3 3500.00000
000007 07002718 2010 2 5600.00000
000007 07002718 2010 3 7100.00000
000008 07024877 2010 2 2200.00000
000008 07024877 2010 3 2200.00000

What I need to do is add number starting with one at the start of each new location. Now I know this is usually done on the frontend but this is just setting up a table for another view that will display consumption horizontally.

So in the end it will look something like this:

000001 06031309 2010 2 2200.00000 1
000001 06031309 2010 3 1700.00000 2
000003 07030583 2010 2 10400.00000 1
000003 07030583 2010 3 9600.00000 2

Here is a sample of that code that will be displaying the consumption:

Select
isnull(cv1.Consumption,0.0) Consumption1, isnull(cv2.Consumption,0.0) Consumption2,

From Blah

left outer join UM_UAIConsumptionDataSummary2 cv1 on c.umLocationID=cv1.umLocationID and c.umEquipmentID=cv1.umEquipmentID and cv1.ReadingNo = 1
left outer join UM_UAIConsumptionDataSummary2 cv2 on c.umLocationID=cv2.umLocationID and c.umEquipmentID=cv2.umEquipmentID and cv2.ReadingNo = 2

So in the end it will look something like this:

000001 06031309 2200.00 1700.00
000003 07030583 10400.00 9600.00

Is there a way to easy add in the numbers I need or is this completely the wrong way going about this? Thanks for any help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-25 : 21:00:57

[code]
SELECT
umLocationID,
umEquipmentID,
DATEPART(yyyy, umReadingDate) AS YEAR,
DATEPART(MM, umReadingDate) AS MONTH,
SUM(umConsumption1) AS Consumption,
row_number() OVER (partiton BY umLocationID ORDER BY umReadingDate) AS RowNo
FROM UM_UAIConsumptionDataSummary a
GROUP BY
umLocationID,
umEquipmentID,
DATEPART(yyyy, umReadingDate),
DATEPART(MM, umReadingDate)
ORDER BY
DATEPART(yyyy, umReadingDate),
DATEPART(MM, umReadingDate)
[/code]

for the 2nd part, you can use the PIVOT operator to display your data horizontally.

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-25 : 21:05:14

ORDER BY
DATEPART(yyyy, umReadingDate),
DATEPART(MM, umReadingDate)

Why not just use:

ORDER BY umReadingDate

There are 10 types of people in the world, those that understand binary, and those that don't.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-25 : 21:18:51

quote:
Originally posted by DBA in the making

ORDER BY
DATEPART(yyyy, umReadingDate),
DATEPART(MM, umReadingDate)

Why not just use:

ORDER BY umReadingDate

There are 10 types of people in the world, those that understand binary, and those that don't.

because umReadingDate it is not contained in either an aggregate function or the GROUP BY clause.

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-25 : 21:26:51

I'm pretty sure the following will work:


GROUP BY 
	umLocationID,
	umEquipmentID,
	umReadingDate

ORDER BY umReadingDate

There are 10 types of people in the world, those that understand binary, and those that don't.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-25 : 21:45:11

yes. but that is not what the OP wanted. The result will be different

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

RickK
Starting Member

4 Posts

Posted - 2010-03-26 : 14:18:53

Ok just to clairify a little we used the :

DATEPART(yyyy, umReadingDate) as Year,
DATEPART(MM, umReadingDate) as Month,

because we need to sum the consumption by month. We have times when there may be two readings in a given month.
Using the DATEPART helped with summing the data correctly but creates a problem with row_number().
Here's what I did:
SELECT
umLocationID,
umEquipmentID,
DATEPART(yyyy, umReadingDate) as Year,
DATEPART(MM, umReadingDate) as Month,
sum(umConsumption1) as Consumption ,
row_number() OVER (PARTITION BY umLocationID ORDER BY DATEPART(yyyy, umReadingDate), DATEPART(MM, umReadingDate)) AS RowNo

FROM UM_UAIConsumptionDataSummary a

where umLocationID ='011111'
group by umLocationID,umEquipmentID,DATEPART(yyyy, umReadingDate), DATEPART(MM, umReadingDate)
order by umLocationID, DATEPART(yyyy, umReadingDate), DATEPART(MM, umReadingDate)

Which produced the following:
011111 07091256 2009 3 1900.00000 1
011111 07091256 2009 4 3100.00000 2
011111 07091256 2009 5 3300.00000 3
011111 07091256 2009 6 2500.00000 4
011111 07091256 2009 7 2500.00000 5
011111 07091256 2009 8 3500.00000 6
011111 07091256 2009 9 700.00000 7
011111 07091256 2009 10 0.00000 8
011111 07091256 2009 11 0.00000 9
011111 07091256 2009 12 0.00000 10
011111 07091256 2010 1 0.00000 11
011111 07091256 2010 2 0.00000 12

Which is great but I really need to order newest to oldest starting with one. So I tried reordering but it appears that using the DatePart casues to sort incorrectly, not a like a true data:

011111 07091256 2009 12 0.00000 10
011111 07091256 2009 11 0.00000 9
011111 07091256 2009 10 0.00000 8
011111 07091256 2009 9 700.00000 7
011111 07091256 2009 8 3500.00000 6
011111 07091256 2009 7 2500.00000 5
011111 07091256 2009 6 2500.00000 4
011111 07091256 2009 5 3300.00000 3
011111 07091256 2009 4 3100.00000 2
011111 07091256 2009 3 1900.00000 1
011111 07091256 2010 2 0.00000 12
011111 07091256 2010 1 0.00000 11

Any ideas? Thanks for all the help.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 14:29:14

quote:
Originally posted by khtan

yes. but that is not what the OP wanted. The result will be different

I'm pretty sure you'll find the results will be exactly the same, because the DATEPART Function is deterministic. What makes you say the results will differ?

There are 10 types of people in the world, those that understand binary, and those that don't.

RickK
Starting Member

4 Posts

Posted - 2010-03-26 : 14:55:34

Well if I take of the DATEPART and sort by just umReadingDate I get the 2010 listed first which is what I am after but my consumption doesn't sum correctly.

011111 07091256 2010-02-17 00:00:00 0.00000 1
011111 07091256 2010-01-19 00:00:00 0.00000 2
011111 07091256 2009-12-14 00:00:00 0.00000 3
011111 07091256 2009-11-17 00:00:00 0.00000 4
011111 07091256 2009-10-20 00:00:00 0.00000 5
011111 07091256 2009-09-25 00:00:00 0.00000 6
011111 07091256 2009-09-17 00:00:00 700.00000 7
011111 07091256 2009-08-19 00:00:00 3500.00000 8
011111 07091256 2009-07-16 00:00:00 2500.00000 9
011111 07091256 2009-06-17 00:00:00 2500.00000 10
011111 07091256 2009-05-14 00:00:00 3300.00000 11
011111 07091256 2009-04-16 00:00:00 3100.00000 12
011111 07091256 2009-03-16 00:00:00 1700.00000 13
011111 07091256 2009-03-02 00:00:00 200.00000 14

With the DATEPART I get the following: (notice the 2010 is on the bottom but everything else is ordered correctly.

011111 07091256 2009 12 0.00000 10
011111 07091256 2009 11 0.00000 9
011111 07091256 2009 10 0.00000 8
011111 07091256 2009 9 700.00000 7
011111 07091256 2009 8 3500.00000 6
011111 07091256 2009 7 2500.00000 5
011111 07091256 2009 6 2500.00000 4
011111 07091256 2009 5 3300.00000 3
011111 07091256 2009 4 3100.00000 2
011111 07091256 2009 3 1900.00000 1
011111 07091256 2010 2 0.00000 12
011111 07091256 2010 1 0.00000 11

Which messes up the numbering. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 04:36:32

wat about this small modification in second query?


row_number() OVER (PARTITION BY umLocationID ORDER BY DATEPART(yyyy, umReadingDate) DESC, DATEPART(MM, umReadingDate) DESC) AS RowNo

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-27 : 05:46:18

quote:
Originally posted by DBA in the making

quote:
Originally posted by khtan

yes. but that is not what the OP wanted. The result will be different

I'm pretty sure you'll find the results will be exactly the same, because the DATEPART Function is deterministic. What makes you say the results will differ?

There are 10 types of people in the world, those that understand binary, and those that don't.

It is not about datepart is deterministic or not. It is the GROUP BY


DECLARE @sample TABLE
(
	[date]	datetime,
	val	int
)

INSERT INTO @sample
SELECT	'2010-01-01', 10 UNION ALL
SELECT	'2010-01-02', 20 UNION ALL
SELECT	'2010-02-01', 30 UNION ALL
SELECT	'2010-02-01', 40 UNION ALL
SELECT	'2010-03-02', 50 UNION ALL
SELECT	'2010-03-01', 60 UNION ALL
SELECT	'2010-03-01', 70

SELECT	Yr = DATEPART(YEAR, [date]), MN = DATEPART(MONTH, [date]), Tot = SUM(val)
FROM	@sample
GROUP BY DATEPART(YEAR, [date]), DATEPART(MONTH, [date])
ORDER BY DATEPART(YEAR, [date]), DATEPART(MONTH, [date])

/*
Yr          MN          Tot         
----------- ----------- ----------- 
2010        1           30
2010        2           70
2010        3           180

(3 row(s) affected)
*/

SELECT	Yr = DATEPART(YEAR, [date]), MN = DATEPART(MONTH, [date]), Tot = SUM(val)
FROM	@sample
GROUP BY [date]
ORDER BY [date]

/*
Yr          MN          Tot         
----------- ----------- ----------- 
2010        1           10
2010        1           20
2010        2           70
2010        3           130
2010        3           50

(5 row(s) affected)
*/

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-27 : 08:32:43

*Slaps forehead*

Thanx khtan. :)

There are 10 types of people in the world, those that understand binary, and those that don't.

RickK
Starting Member

4 Posts

Posted - 2010-04-01 : 08:20:07

I wanted to thank everyone for their help I did get this completed I had to work out a few more bugs. Here was the final code:

SELECT
umLocationID,
umEquipmentID,
umTar,
DATEPART(yyyy, umReadingDate) as Year,
DATEPART(MM, umReadingDate) as Month,
sum(umConsumption1) as Consumption ,
row_number() OVER (PARTITION BY umLocationID, umTar,umEquipmentID ORDER BY DATEPART(yyyy, umReadingDate) DESC,DATEPART(MM, umReadingDate) DESC, umTar ) AS RowNo
FROM UM_FKAAConsumptionDataSummary a

group by umLocationID,umEquipmentID, umTar, DATEPART(yyyy, umReadingDate), DATEPART(MM, umReadingDate)
order by umLocationID, umTar, umEquipmentID, DATEPART(yyyy, umReadingDate) DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources