Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

sql 2005 query problem

hi i am trying to write down a query where a product has bought in to the system twice in different systems i am combining both and i am taking a date difference of the product when and i am getting noofdays i can get the last day for example it shows me twice and i want to show as only once this the query i have written and i want to show the noofdays only once date shows 09/06/2007 noofdays 36 and date shows 08/29/2007 noofdays 44 i want to show the date 09/06/2007 and 36 days how can i show that.

select storename,Description, serialnumber,max(datereceived)datereceived,noofdays from iqmphonesaging where storeid=12
group by storename,Description, serialnumber,noofdays
 order by description, noofdays
0
romeiovasu
Asked:
romeiovasu
  • 4
  • 3
  • 2
1 Solution
 
Ashish PatelCommented:
I didnt clearly get what you want exactly as output , but you can use "Case" in your select statement. And please paste some sample output of current query result and the one which you require.
0
 
appariCommented:
try

Select iqmphonesaging.* from iqmphonesaging join
(select storename,Description, serialnumber,max(datereceived) datereceived from iqmphonesaging where storeid=12 group by storename,Description, serialnumber ) MAxStore
on
iqmphonesaging.storename= MAxStore.storename
iqmphonesaging.Description = MAxStore.Description
iqmphonesaging.serialnumber = MAxStore.serialnumber
 order by iqmphonesaging.description, iqmphonesaging.noofdays
0
 
appariCommented:
try, i think i missed where part in outer query


Select iqmphonesaging.* from iqmphonesaging join
(select storename,Description, serialnumber,max(datereceived) datereceived from iqmphonesaging where storeid=12 group by storename,Description, serialnumber ) MAxStore
on
iqmphonesaging.storename= MAxStore.storename
and iqmphonesaging.Description = MAxStore.Description
and iqmphonesaging.serialnumber = MAxStore.serialnumber
and iqmphonesaging.storeid=12
 order by iqmphonesaging.description, iqmphonesaging.noofdays
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
romeiovasuAuthor Commented:
12      AT&T Lake Grove      2403      ATT BLACKBERRY 8300 CURVE      355772016410164      09/06/2007      36      31-90 Days
12      AT&T Lake Grove      2403      ATT BLACKBERRY 8300 CURVE      355772016410131      09/06/2007      36      31-90 Days
12      AT&T Lake Grove      2403      ATT BLACKBERRY 8300 CURVE      355772016410164      08/29/2007      44      31-90 Days
12      AT&T Lake Grove      2403      ATT BLACKBERRY 8300 CURVE      355772016410131      08/29/2007      44      31-90 Days

i am getting results like above but it is repeating i just want to display take the last date only
0
 
Ashish PatelCommented:
Try this and let me know
select storename,Description, Min(serialnumber),max(datereceived)datereceived,noofdays from iqmphonesaging where storeid=12
group by storename,Description, noofdays
 order by description, noofdays
0
 
romeiovasuAuthor Commented:
locationname      ATT BLACKBERRY 8300 CURVE      355772016410131      09/06/2007      36
locationname      ATT BLACKBERRY 8300 CURVE      355772016410131      08/29/2007      44
locationname      ATT BLACKBERRY 8300 CURVE      355772016410164      09/06/2007      36
locationname      ATT BLACKBERRY 8300 CURVE      355772016410164      08/29/2007      44

the serial number is repeating over there i just want to show the last date received into the system.
0
 
romeiovasuAuthor Commented:
if i try that
locationname      ATT BLACKBERRY 8300 CURVE      355772016410131      09/06/2007      36
locationname      ATT BLACKBERRY 8300 CURVE      355772016410131      08/29/2007      44

it is not showing the other one.but it just repeated the same one.
0
 
romeiovasuAuthor Commented:
ok i made it work what i did was

select storename,Description, serialnumber,max(datereceived)datereceived,max(noofdays) noofdays from iqmphonesaging where storeid=12
group by storename,Description,serialnumber
 order by description, noofdays

this is how it worked.
0
 
Ashish PatelCommented:
cheers!!!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now