February 9, 2006 at 2:58 pm
Hey everyone: I have a problem properly sorting my output from the following query:
select top 26 convert(varchar(10), DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)), 101) as WeekBeginning, count(i.ssnpn)
from icg.dbo.mmarchive m
left join bpcssql.dbo.ItemMasterNew_BPCS i on m.ssnpn = i.ssnpn
left join bpcssql.dbo.as400uid_bpcs u on u.userid = m.usuid
where m.ssvsn like 'DEL%'
and u.Dept = 'Item Content Group'
group by convert(varchar(10), DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)), 101)
order by convert(varchar(10), DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)), 101) desc
The output looks like this:
12/26/2005 |
12/19/2005 |
12/12/2005 |
12/05/2005 |
11/28/2005 |
11/21/2005 |
11/14/2005 |
11/07/2005 |
10/31/2005 |
10/24/2005 |
10/17/2005 |
10/10/2005 |
10/03/2005 |
02/06/2006 |
01/30/2006 |
01/23/2006 |
01/16/2006 |
01/09/2006 |
01/02/2006 |
But I would like it to have the 2006 data at the top in descending month, then day. Date conversions are my achilles heel.
Sorry for the format of this post, but I wanted to give as much information as possible.
Thanks,
JB
February 9, 2006 at 3:41 pm
I would use a derived table to calculate the required date, but keep it as a true date/time type.
Then outside the derived table, format/sort accordingly:
Select top 26 convert(varchar(10), WeekBeginning) As WeekBeginning, Count(ssnpn)
From
(
select DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)) as WeekBeginning,
i.ssnpn
from icg.dbo.mmarchive m
left join bpcssql.dbo.ItemMasterNew_BPCS i on m.ssnpn = i.ssnpn
left join bpcssql.dbo.as400uid_bpcs u on u.userid = m.usuid
where m.ssvsn like 'DEL%'
and u.Dept = 'Item Content Group'
) dt
Group By WeekBeginning
-- Order by true date/time type
Order By WeekBeginning Desc
February 9, 2006 at 3:54 pm
Oct 31 200 |
Oct 24 200 |
Oct 17 200 |
Oct 10 200 |
Oct 3 200 |
Nov 28 200 |
Nov 21 200 |
Nov 14 200 |
Nov 7 200 |
Jan 30 200 |
Jan 23 200 |
Jan 16 200 |
Ummm...I think either you or I fat fingered something. BRB.
February 9, 2006 at 3:56 pm
I fat fingered the delete key and dropped the 101 format code:
Select top 26 convert(varchar(10), WeekBeginning, 101) As WeekBeginning
February 9, 2006 at 4:01 pm
Fantabulous. Now I just have to re-run this thing which will take about 10 years.
Keeping fingers crossed.
February 9, 2006 at 4:17 pm
I really appreciate the suggestion. Alas, it is still not sorting correctly. The 2006 data is at the bottom of the list still. Hmmm. Since I am a date conversion know-nothing I appreciate any further assistance.
Justyna
February 9, 2006 at 4:26 pm
Doh. Derived table isn't generating a tru date/time datatype. Need to CAST() it:
select CAST( DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)) As datetime) as WeekBeginning
February 10, 2006 at 8:00 am
Select top 26 convert(varchar(11), WeekBeginning, 101) As WeekBeginning, Count(ssnpn)
From
(
select CAST( DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)) As datetime) as WeekBeginning,
i.ssnpn
from icg.dbo.mmarchive m
left join bpcssql.dbo.ItemMasterNew_BPCS i on m.ssnpn = i.ssnpn
left join bpcssql.dbo.as400uid_bpcs u on u.userid = m.usuid
where m.ssvsn like 'DEL%'
and u.Dept = 'Item Content Group'
) dt
Group By WeekBeginning
Order By WeekBeginning Desc
PW: Did I put the cast in the right place? I think I did. The result are still only in desc order on the month; all of the 2006 data is still at the bottom. God I need some coffee. I wish I could buy each of you, dear readers (and especially you PW), a coffee or whiskey or whatever of your choice. At this point I'm almost ready to promise my first born.
Why is dealing with dates such a pain in the |_|_| ?
February 10, 2006 at 8:29 am
What is the datatype of column mmdate, what does it contain and what is the calculation trying to achieve ?
February 10, 2006 at 8:33 am
You know what PW, against all odds, I think I fixed it.
Select top 26 convert(varchar(11), WeekBeginning, 101) As WeekBeginning, Count(ssnpn)
From
(
select CAST( DATEADD(day, (DATEPART(weekday, convert(char, mmdate, 112))-2)*-1, convert(char, mmdate, 112)) As datetime) as WeekBeginning,
i.ssnpn
from icg.dbo.mmarchive m
left join bpcssql.dbo.ItemMasterNew_BPCS i on m.ssnpn = i.ssnpn
left join bpcssql.dbo.as400uid_bpcs u on u.userid = m.usuid
where m.ssvsn like 'DEL%'
and u.Dept = 'Item Content Group'
) dt
Group By WeekBeginning
Order By datepart(yy, WeekBeginning) Desc, datepart(mm, Weekbeginning) Desc
I added the datepart to the order by clause and it worked.
Again, that you so much for your help. Couldn't have done it without you!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy