Pivot Help please !!!! - 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 2005 Forums

Transact-SQL (2005)

Pivot Help please !!!!

Author

Topic

rijo
Starting Member

8 Posts

Posted - 2008-04-25 : 16:48:08

I have a table structure like this

Vendor Date Item

A1 04/21/2008 T1

A1 04/21/2008 T1

A1 04/21/2008 T1

C1 04/21/2008 T1

C1 04/21/2008 T1

D1 04/21/2008 T1

A1 04/11/2008 T1

A1 04/11/2008 T1

B1 04/11/2008 T1

C1 04/11/2008 T1

D1 04/11/2008 T1

D1 04/11/2008 T1

A1 04/1/2008 T1

A1 04/1/2008 T1

B1 04/1/2008 T1

B1 04/1/2008 T1

C1 04/1/2008 T1

D1 04/1/2008 T1

I want the result like
(date calculated from todays date 04/25/2008)

Vendor Count of item Count of item Count of item

last 10days last 20days last 25days

A1 3 5 7
B1 0 1 3
C1 2 3 4
D1 1 3 4

How can i write the query for this. Please help me ,

Mathew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 16:52:06

[code]SELECT Vendor,
SUM(CASE WHEN DATEDIFF(DAY, Date, GETDATE()) BETWEEN 0 AND 10 THEN 1 ELSE 0 END) AS Last10Days,
SUM(CASE WHEN DATEDIFF(DAY, Date, GETDATE()) BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS Last20Days,
SUM(CASE WHEN DATEDIFF(DAY, Date, GETDATE()) BETWEEN 21 AND 25 THEN 1 ELSE 0 END) AS Last25Days
FROM Table1
WHERE Date >= DATEADD(DAY, -26, GETDATE())
AND Date < DATEADD(DAY, 1, GETDATE())
GROUP BY Vendor
ORDER BY Vendor[/code]

E 12°55'05.25"
N 56°04'39.16"

rijo
Starting Member

8 Posts

Posted - 2008-04-25 : 17:04:02

Thank you for yout reply

but
I want the count of nuber of items for (last 10days), (last 20days),(last 25days )

Plese help me .

Mathew

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 17:15:11

What? I already posted a solution for you.
It can't be that difficult to edit the query...

SELECT		Vendor,
		SUM(CASE WHEN DATEDIFF(DAY, Date, GETDATE()) BETWEEN 0 AND 10 THEN 1 ELSE 0 END) AS Last10Days,
		SUM(CASE WHEN DATEDIFF(DAY, Date, GETDATE()) BETWEEN 0 AND 20 THEN 1 ELSE 0 END) AS Last20Days,
		SUM(CASE WHEN DATEDIFF(DAY, Date, GETDATE()) BETWEEN 0 AND 25 THEN 1 ELSE 0 END) AS Last25Days
FROM		Table1
WHERE		Date >= DATEADD(DAY, -26, GETDATE())
		AND Date < DATEADD(DAY, 1, GETDATE())
GROUP BY	Vendor
ORDER BY	Vendor

E 12°55'05.25"
N 56°04'39.16"

rijo
Starting Member

8 Posts

Posted - 2008-04-25 : 17:19:24

Im am sorry

I dint understand the solution
Where u find the count(Item)

Rijo

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 17:22:06

SUM(CASE ... THEN 1 ELSE 0 END)

If date interval check is satisfied you count this record (1), otherwise you don't count it (0).
All you have to do is SUM the records.

You could go

SELECT		Vendor,
		COUNT(CASE WHEN DATEDIFF(DAY, Date, GETDATE()) BETWEEN 0 AND 10 THEN 1 ELSE NULL END) AS Last10Days,
		COUNT(CASE WHEN DATEDIFF(DAY, Date, GETDATE()) BETWEEN 0 AND 20 THEN 1 ELSE NULL END) AS Last20Days,
		COUNT(CASE WHEN DATEDIFF(DAY, Date, GETDATE()) BETWEEN 0 AND 25 THEN 1 ELSE NULL END) AS Last25Days
FROM		Table1
WHERE		Date >= DATEADD(DAY, -26, GETDATE())
		AND Date < DATEADD(DAY, 1, GETDATE())
GROUP BY	Vendor
ORDER BY	Vendor

but then you will get warnings about aggregate a NULL value.
I prefer first suggestion with SUM(CASE ... THEN 1 ELSE 0 END).

E 12°55'05.25"
N 56°04'39.16"

rijo
Starting Member

8 Posts

Posted - 2008-04-25 : 17:38:44

Yes, you are right,

actually my situation is littile bit diffrent ,

suppose if the Item a number, means one vendor have 20 items . how can i find it.

sum(items) of each vendor for (last 10days), (last 20days),(last 25days )

I am really sorry to disturb you.

please help
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 17:44:48

The SUM(CASE WHEN ... THEN 1 ELSE 0 END) thingy
will calculate this for you!

Please run the query I suggest and report back if it works or not.

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-25 : 17:51:08

read this to understand what peso is doing:
http://weblogs.sqlteam.com/mladenp/archive/2008/02/04/Back-to-Basics-Count-Count-Count-Sum-or-how-to.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

rijo
Starting Member

8 Posts

Posted - 2008-04-25 : 17:56:16

Where should i mention the sum of Item the field Item.?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 18:03:10

In your original post Item column as VARCHAR, and have "T1" as value.
Do you mean you have lied for us and the Item column holds numeric values instead?

If that's the case, replace "THEN 1 ELSE 0 END" with "THEN Item ELSE 0 END".

E 12°55'05.25"
N 56°04'39.16"

rijo
Starting Member

8 Posts

Posted - 2008-04-28 : 12:43:00

That worked...

Thank you very much...

Rijo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources