Ugerekort, månedsrekord

count group by week/month

Afhængig af database typen kan du bruge:
GROUP BY month([Document Date])
GROUP BY DATEPART(mm,[Document Date])

GROUP BY week([Document Date])
GROUP BY DATEPART(ww,[Document Date])
GROUP BY DATEPART(ww,[Document Date],2,2)
GROUP BY week([Document Date],3)

Det er ikke alle databaser der kan forstå at de skal bruge mandag som første dag i ugen og bruge fire dags reglen til ugenr.

Ok, det virkede med

strSQLMR = "SELECT TOP 1 PERCENT COUNT_BIG (No_) AS [AntalMR ordre], month([Document Date]) FROM dbo.[3S Company A_S$Sales Invoice Header] [3S Company A_S$Sales Invoice Header] WHERE ([Sell-to Customer No_] = '45') OR([Sell-to Customer No_] = '46') OR ([Sell-to Customer No_] = '47') OR ([Sell-to Customer No_] = '48') OR ([Sell-to Customer No_] = '49') GROUP BY month([Document Date]) ORDER BY month([Document Date]) desc"
Set RsMR= Conn.Execute(strSQLMR)
MRrekord = "<b>Maanedsrekord</b> (" &month([Document Date]) & ") : <b>" & RSMR("AntalMR Ordre") & "</b>"


mit output er i dette tilfælde "12" hvordan ved jeg hvad år det er.. om det er 06, 05. 04 osv?

Det er en MS SQL database.. kan ikke få uge nummer til at virke

Du skal have year med på selecten og group:
year([Document Date]), month([Document Date])

På Ms-sql skal du bruge:
DATEPART(ww,[Document Date])

Jeg får fejlen:

Der opstod en Microsoft VBScript-kørselsfejl (0x800A0005)
Procedurekaldet eller argumentet er ugyldigt: 'DatePart'


strSQLWR = "SELECT TOP 1 PERCENT COUNT_BIG (No_) AS [AntalWR ordre],DATEPART(ww,[Document Date]) FROM dbo.[3S Company A_S$Sales Invoice Header] [3S Company A_S$Sales Invoice Header] WHERE ([Sell-to Customer No_] = '45') OR([Sell-to Customer No_] = '46') OR ([Sell-to Customer No_] = '47') OR ([Sell-to Customer No_] = '48') OR ([Sell-to Customer No_] = '49') GROUP BY DATEPART(ww,[Document Date]) ORDER BY DATEPART(ww,[Document Date]) desc"
Set RsWR= Conn.Execute(strSQLWR)
WRrekord = "<b>Ugerekord</b>  (" &  DATEPART(ww,[Document Date]) & ") : <b>" & RSWR("AntalWR Ordre") & "</b>"

Først kunne man tro, at du havde brugt en forkert definition, men man kan anvende følge i SQL

Another important date/time function worth describing is DATEPART. This function will take a date/time expression and return a single part of the date, such as hour, minute, month, day, etc. A call to this function has the following form:

    DATEPART(datepart, date)

Where the datepart is one of the following: Year, yy, yyyy, quarter, qq, q, month, mm, m, dayofyear, dy, y, day, dd, d, week, wk, ww, weekday, dw,hour, hh, minute, mi, n, second, ss, s, millisecond, or ms. And date is a valid date expression. This function will return an integer representing the particular date part requested.



Problemet ligger nok i sætningen

WRrekord = "<b>Ugerekord</b>  (" &  DATEPART(ww,[Document Date]) & ") : <b>" & RSWR("AntalWR Ordre") & "</b>"

Hvis du skal have noget ud, skal du nok bruge RsWR(DATEPART(ww,[Document Date])

strSQLWR = "SELECT TOP 1 PERCENT COUNT_BIG (No_) AS [AntalWR ordre], DATEPART(ww,[Document Date]) FROM dbo.[3S Company A_S$Sales Invoice Header] [3S Company A_S$Sales Invoice Header] WHERE ([Sell-to Customer No_] = '45') OR([Sell-to Customer No_] = '46') OR ([Sell-to Customer No_] = '47') OR ([Sell-to Customer No_] = '48') OR ([Sell-to Customer No_] = '49') GROUP BY DATEPART(ww,[Document Date]) ORDER BY DATEPART(ww,[Document Date]) desc"
Set RsWR= Conn.Execute(strSQLWR)
WRrekord = "<b>Ugerekord</b>: <b>" & RSWR("AntalWR Ordre") & "</b>"


OK, har denne som virker, men får forkert antal ud af det.
Jeg ved ugerekord er på knap 1500, men her får jeg største ugerekort på ca 800.

nogen soms er fejlen?

Lukker, har oprettet nyt spm