Help me to write SQL - 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

General SQL Server Forums

New to SQL Server Programming

Help me to write SQL

Author

Topic

venki.dmm
Starting Member

10 Posts

Posted - 2009-01-28 : 06:53:24

Hi guys,,

i have requirement... to create table and insert the data

rep_date country topupcurrent week topup_week -1
28-01-2009 ind 10 20
28-01-2009 us 28 12
28-01-2009 uk 57 12

the source table is like this

rep_date country topup_current_week
28-01-2009 ind 10
21-01-2009 ind 20
28-01-2009 uk 57
21-01-2009 uk 12
21-01-2009 us 28
21-01-2009 us 12

Can you please help me

Regards
Venki

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-28 : 07:00:22

Hi!
Here you have the "CREATE TABLE" and "INSERT" information from Books Online (it is for SQL Server 2005):
http://msdn.microsoft.com/en-us/library/ms174979(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms174979(SQL.90).aspx

Hope that helps you get through this problem.

Best Regards,
KF

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 07:02:50

declare @tbl table (rep_date datetime, country varchar(32),topup_current_week int)
insert into @tbl select
'01-28-2009','ind',10 union all select
'01-21-2009','ind',20 union all select
'01-28-2009','uk',57 union all select
'01-21-2009','uk',12 union all select
'01-21-2009','us',28 union all select
'01-21-2009','us',12
select country , max(topup_current_week)as maxval , min(topup_current_week) as minval
from @tbl group by country

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 07:05:51

how does the date values all become 28th?

venki.dmm
Starting Member

10 Posts

Posted - 2009-01-28 : 07:09:53

hi visakh16,
Thaks for you reply
when we insert the values in another table

out put like this:---
rep_date country topupcurrent_week topup_week -1
28-01-2009 ind 10 20
28-01-2009 us 28 12
28-01-2009 uk 57 12

we dont want the week-1 rep_date..

Regards,
Venki

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 07:14:27

[code]SELECT t.rep_date, t.country, t.topupcurrentweek,
(SELECT TOP 1 topupcurrentweek FROM Table WHERE country=t.country AND rep_date>=DATEADD(wk,-1,rep_date) ORDER BY rep_date DESC)as [topup_week -1]
FROM Table t[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 07:16:19

select (select max(rep_date) from @tbl) ,country , max(topup_current_week)as maxval , min(topup_current_week) as minval
from @tbl group by country

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 07:19:00

quote:
Originally posted by bklr

select (select max(rep_date) from @tbl) ,country , max(topup_current_week)as maxval , min(topup_current_week) as minval
from @tbl group by country

how can you be certain that max value will be value for current week and min() for prev week?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 07:22:01

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

select (select max(rep_date) from @tbl) ,country , max(topup_current_week)as maxval , min(topup_current_week) as minval
from @tbl group by country

how can you be certain that max value will be value for current week and min() for prev week?

his requirement is to get the max date to be display for every record for last week also
required o\p
rep_date country topupcurrent week topup_week -1
28-01-2009 ind 10 20
28-01-2009 us 28 12
28-01-2009 uk 57 12

check this once
declare @tbl table (rep_date datetime, country varchar(32),topup_current_week int)
insert into @tbl select
'01-28-2009','ind',10 union all select
'01-21-2009','ind',20 union all select
'01-28-2009','uk',57 union all select
'01-21-2009','uk',12 union all select
'01-21-2009','us',28 union all select
'01-21-2009','us',12

select (select max(rep_date) from @tbl) as rep_date ,country , max(topup_current_week)as maxval , min(topup_current_week) as minval
from @tbl group by country

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 07:32:11

see that output again. its not always maxval that comes for current week and minval that comes for prev week (see Ind as an example)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-28 : 07:33:33

[code]
declare @temp table (rep_date datetime, country varchar(32), topupcurrent int)
insert into @temp
select '01-28-2009', 'ind', 10 union all
select '01-21-2009', 'ind', 20 union all
select '01-28-2009', 'uk', 57 union all
select '01-21-2009', 'uk', 12 union all
select '01-21-2009', 'us', 28 union all
select '01-21-2009', 'us', 12

select case when rep_date = '01-28-2009' then t.rep_Date else t.rep_date+7 end as rep_date,t.country,t.topupcurrent,t1.topupcurrent from @temp as t
left join (select country,max(topupcurrent) as topupcurrent from @temp group by country ) as t1
on t1.country = t.country where t.topupcurrent <> t1.topupcurrent
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-28 : 08:07:34

[code]DECLARE @Sample TABLE
(
rep_date DATETIME,
country VARCHAR(3),
topup_current_week INT
)

SET DATEFORMAT DMY

INSERT @Sample
SELECT '28-01-2009', 'ind', 10 UNION ALL
SELECT '21-01-2009', 'ind', 20 UNION ALL
SELECT '28-01-2009', 'uk', 57 UNION ALL
SELECT '21-01-2009', 'uk', 12 UNION ALL
SELECT '28-01-2009', 'us', 28 UNION ALL
SELECT '21-01-2009', 'us', 12

DECLARE @thisweekdate DATETIME

SET @thisweekdate = '28-01-2009'

SELECT MAX(case when rep_date = @thisweekdate then rep_date else null end) AS rep_date,
country,
MAX(case when rep_date = @thisweekdate then topup_current_week else null end) AS [topupcurrent week],
MAX(case when rep_date = @thisweekdate - 7 then topup_current_week else null end) AS [topup_week -1]
FROM @Sample
WHERE rep_date IN (@thisweekdate - 7, @thisweekdate)
grouP by country[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources