sql server 2008 - Write a query to get last seven month records from a table -

sql server 2008 - Write a query to get last seven month records from a table -

im having 2 tables tablea , tableb.

tablea master table.

tablea:

+-------+---------+ | id | asset | +-------+---------+ | 1 | asset1 | | 2 | asset2 | | 3 | asset3 | | 4 | asset4 | | 5 | asset5 | +-------+---------+

tableb:

+-------+---------+-------------+ | id | asset | scandate | +-------+---------+-------------+ | 1 | asset1 | 12/jun/2014 | | 2 | asset2 | 12/jun/2014 | | 3 | asset3 | 12/jun/2014 | | 4 | asset4 | 12/jun/2014 | | 5 | asset5 | 12/jun/2014 | | 6 | asset1 | 13/jun/2014 | | 7 | asset2 | 13/jun/2014 | | 8 | asset3 | 13/jun/2014 | | 9 | asset4 | 13/jun/2014 | | 10 | asset5 | 13/jun/2014 | | 11 | asset1 | 14/jun/2014 | | 12 | asset2 | 14/jun/2014 | | 13 | asset3 | 14/jun/2014 | | 14 | asset4 | 14/jun/2014 | | 15 | asset5 | 14/jun/2014 | | 16 | asset1 | 15/jun/2014 | | 17 | asset2 | 15/jun/2014 | | 18 | asset5 | 15/jun/2014 | | 19 | asset1 | 16/jun/2014 | | 20 | asset2 | 16/jun/2014 | | 21 | asset3 | 16/jun/2014 | | 22 | asset1 | 18/jun/2014 | | 23 | asset2 | 18/jun/2014 | | 24 | asset3 | 18/jun/2014 | | 25 | asset4 | 18/jun/2014 | | 26 | asset5 | 18/jun/2014 | | 27 | asset3 | 19/jun/2014 | | 28 | asset4 | 19/jun/2014 | | 29 | asset5 | 19/jun/2014 | | 30 | asset4 | 20/jun/2014 | | 31 | asset5 | 20/jun/2014 | +-------+---------+-------------+

i need check assets scanned on lastly 7 days.

i need output below:

+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | asset |13/jun/2014|14/jun/2014|15/jun/2014|16/jun/2014|17/jun/2014|18/jun/2014|19/jun/2014|20/jun/2014| +--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+ | asset1 | yes | yes | yes | yes | no | yes | no | no | | asset2 | yes | yes | yes | yes | no | yes | no | no | | asset3 | yes | yes | no | yes | no | yes | yes | no | | asset4 | yes | yes | no | no | no | yes | yes | yes | | asset5 | yes | yes | yes | no | no | yes | yes | yes | +--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

how write query above output. please help me out.

i tried below query:

declare @now datetime = getdate(); declare @7daysago datetime = dateadd(day,-10,@now); select lastscandate tableb scandate between @7daysago , @now grouping scandate

i came m khalid junaid idea

try this

select t1.asset, sum(case when cast(getdate() date) = cast(t1.scandate date) 1 else 0 end) current_day, sum(case when cast(dateadd(day, -1, getdate()) date) = cast(t1.scandate date) 1 else 0 end) last_day, sum(case when cast(dateadd(day, -2, getdate()) date) = cast(t1.scandate date) 1 else 0 end) second_last_day, sum(case when cast(dateadd(day, -3, getdate()) date) = cast(t1.scandate date) 1 else 0 end) third_last_day, sum(case when cast(dateadd(day, -4, getdate()) date) = cast(t1.scandate date) 1 else 0 end) fourth_last_day, sum(case when cast(dateadd(day, -5, getdate()) date) = cast(t1.scandate date) 1 else 0 end) fifth_last_day, sum(case when cast(dateadd(day, -6, getdate()) date) = cast(t1.scandate date) 1 else 0 end) sixth_last_day, sum(case when cast(dateadd(day, -7, getdate()) date) = cast(t1.scandate date) 1 else 0 end) seventh_last_day table1 t1 left bring together table2 t2 on(t1.asset = t2.asset) grouping t1.asset;

fiddle demo

output:

asset current_day last_day second_last_day third_last_day fourth_last_day fifth_last_day sixth_last_day seventh_last_day asset1 0 0 1 0 1 1 1 1 asset2 0 0 1 0 1 1 1 1 asset3 0 1 1 0 1 0 1 1 asset4 1 1 1 0 0 0 1 1 asset5 1 1 1 0 0 1 1 1

update

if want column value yes or no

try this

select t1.asset, case when sum(case when cast(getdate() date) = cast(t1.scandate date) 1 else 0 end) = 1 'yes' else 'no' end today, case when sum(case when cast(dateadd(day, -1, getdate()) date) = cast(t1.scandate date) 1 else 0 end) = 1 'yes' else 'no' end last_day, case when sum(case when cast(dateadd(day, -2, getdate()) date) = cast(t1.scandate date) 1 else 0 end) = 1 'yes' else 'no' end second_last_day, case when sum(case when cast(dateadd(day, -3, getdate()) date) = cast(t1.scandate date) 1 else 0 end) = 1 'yes' else 'no' end third_last_day, case when sum(case when cast(dateadd(day, -4, getdate()) date) = cast(t1.scandate date) 1 else 0 end) = 1 'yes' else 'no' end fourth_last_day, case when sum(case when cast(dateadd(day, -5, getdate()) date) = cast(t1.scandate date) 1 else 0 end) = 1 'yes' else 'no' end fifth_last_day, case when sum(case when cast(dateadd(day, -6, getdate()) date) = cast(t1.scandate date) 1 else 0 end) = 1 'yes' else 'no' end sixth_last_day, case when sum(case when cast(dateadd(day, -7, getdate()) date) = cast(t1.scandate date) 1 else 0 end) = 1 'yes' else 'no' end seventh_last_day table1 t1 left bring together table2 t2 on(t1.asset = t2.asset) grouping t1.asset;

live demo

output:

asset today last_day second_last_day third_last_day fourth_last_day fifth_last_day sixth_last_day seventh_last_day asset1 no no yes no yes yes yes yes asset2 no no yes no yes yes yes yes asset3 no yes yes no yes no yes yes asset4 yes yes yes no no no yes yes asset5 yes yes yes no no yes yes yes

sql-server-2008